Form/Subform

I have adapted an existing search form to meet my needs which utilizes the
gcriteria that you mentioned.

The search form enables the user to search for a person utilizing multiple
criteria to narrow down the search. It is opened from a button on the subject
detail form.

I made the changes you recommended but now when I enter a new name and say
yes to adding the new data it takes me to the subject details main input
form..that's good. The form had data from the subject table before but now
it seems to be gone.

My search form is based on the subject details form. I know that there is
data there but it won't recall it when I put my search criteria in. The
records indicate "1 of 1" but do not list anyone. There is also a button to
display all subjects when I hit that button it does the same thing.

It seems that the subject table is not populated or the subject detail form
is not populating from the right table. However when I go to add a subject to
the subjectcomplaints subform the population of subjects is there.

Very confused right now.

Scott





"Dirk Goldgar" wrote:

(Please see my comments in-line)

"Srowe" wrote in message
...

There are lots of things going on here causing the various problems you
report, and some that you didn't report this time, but that I see coming up.

Her is the code that I have for the NotinList event on my SubjId combobox:

Private Sub SubjId_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String: CR = Chr$(13)


If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' Subject is not in the list." & CR & CR
Msg = Msg & "Do you want to add the subject?"
If MsgBox(Msg, 32 + 4) = 6 Then

Your code would be self-documenting if you used the named constants that
have been defined for use with the MsgBox function:

If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then


DoCmd.OpenForm "frmCustomers1", , , , A_ADD, A_DIALOG, NewData

You are using outdated constants here. Although these constants would work,
I'd recommend this instead:

DoCmd.OpenForm "frmCustomers1", , , , acFormAdd, acDialog,
NewData

With or without the above changes, if the user chooses to add the new
customer, you want to tell Access that this has happened by setting the
Response argument of the NotInList procedure to acDataErrAdded:

Response = acDataErrAdded


End If

End Sub


This gets me to my main entry form "frmcustomer1". Once I am in that form
I
want to be able to add the new subject and all of their pertinant data.
The
nessage I get is: "You can not add or change a record because a record is
required in the table 'complaint'

I am guessing that this occurs because you still have a ComplaintNum field
in the Subject table, and you have a relationship set between that field and
the Complaint table, with referential integrity enforced, and the
ComplaintNum field in table Subject has a default value of 0. Since we
changed your table design to use table SubjectsComplaints to link Subject
and Complaint in a many-to-many relationship, you should delete the direct
relationship between Subject and Complaint, and remove the ComplaintNum
field from the Subject table. Note, though, that if you have data in that
field that you haven't otherwise captured into SubjectsComplaints, you
should take care of that first.

Why is your form is named "frmCustomer1" when it's bound to the Subject
table? I'm guessing you are adapting another database or template, and
forgot to rename the form.

Another message pops up saying that I can not save the record an error
occurred.

That would be a result of the earlier error, so curing the first should
eliminate this message.

This is the code that is associated to the 'frmcustomers1'
Option Compare Database

You ought to have a statement

Option Explicit

following the "Option Compare Database" statement. That will save you no
end of debugging headaches.

You should set the VB Editor option, "Require Variable Declaration", to
cause the editor to automatically add the "Option Explicit" statement to all
new modules you create.

Private Sub cmdAll_Click()

Dim LSQL As String

'Clear criteria
GCriteria = ""

Is "GCriteria" a global variable declared in some other module? I see no
declaration for it in the posted code.


'Display all customers
LSQL = "select * from Subject"

Form_frmCustomers1.RecordSource = LSQL
Form_frmCustomers1.Caption = "Subject Detail Form"

Do not use this syntax to refer to the form. It works, under the right
circumstances, but it is flawed. Where necessary, use

Forms!frmCustomers1

or

Forms("frmCustomers1")

However, in this case, the code is running on the form you want to refer to,
so it will be more efficient just to use the "Me" keyword:

Me.RecordSource = LSQL
Me.Caption = "Subject Detail Form"


MsgBox "All Subjects are now displayed."

End Sub

Private Sub cmdReport_Click()

'Open report
DoCmd.OpenReport "rptCustomers", acViewPreview, , GCriteria

End Sub

Private Sub cmdSearch_Click()

DoCmd.OpenForm "frmSearch1", , , , , acDialog

End Sub



Private Sub Form_Open(Cancel As Integer)

'Clear criteria when form is first opened
GCriteria = ""

End Sub

I don't see anything in the rest of the code to comment on, except that I
see you are passing the NewData from the combo box to frmCustomers1 via
OpenArgs, but I don't see you using it anywhere.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Srowe" wrote in message
...
I have adapted an existing search form to meet my needs which utilizes the
gcriteria that you mentioned.

The search form enables the user to search for a person utilizing multiple
criteria to narrow down the search. It is opened from a button on the
subject
detail form.

I made the changes you recommended but now when I enter a new name and say
yes to adding the new data it takes me to the subject details main input
form..that's good. The form had data from the subject table before but
now
it seems to be gone.

My search form is based on the subject details form. I know that there is
data there but it won't recall it when I put my search criteria in. The
records indicate "1 of 1" but do not list anyone. There is also a button
to
display all subjects when I hit that button it does the same thing.

It seems that the subject table is not populated or the subject detail
form
is not populating from the right table. However when I go to add a subject
to
the subjectcomplaints subform the population of subjects is there.

Very confused right now.

The code in the NotInList event explicitly opens the form to add new records
*only*. Your original code was:

DoCmd.OpenForm "frmCustomers1", , , , A_ADD, A_DIALOG, NewData

My suggested revision, using more up-to-date constants, was:

DoCmd.OpenForm "frmCustomers1", , , , acFormAdd, acDialog, NewData

That doesn't change the meaning of the statement, though. The constants
A_ADD and acFormAdd both say to open the form in data entry mode, meaning
that no existing records are to be displayed -- only new records can be
added. That makes sense if the form is being opened for the sole purpose of
adding a new Subject. I assumed that was what you wanted, since that's what
your original code said to do.

If you don't want that, it's possible to open the form in normal mode;
however, opening in data entry mode seems appropriate to me in this case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Yes you are very right. And it makes perfect sense. In fact that was going
to be my next question is how do I get the entry form to be blank when I want
to add somebody...problem solved.

I will come up with another way of searching for somebody and displaying the
number of incidents they have been involved with.

Your help has been much appreciated.

Scott
"Dirk Goldgar" wrote:

"Srowe" wrote in message
...
I have adapted an existing search form to meet my needs which utilizes the
gcriteria that you mentioned.

The search form enables the user to search for a person utilizing multiple
criteria to narrow down the search. It is opened from a button on the
subject
detail form.

I made the changes you recommended but now when I enter a new name and say
yes to adding the new data it takes me to the subject details main input
form..that's good. The form had data from the subject table before but
now
it seems to be gone.

My search form is based on the subject details form. I know that there is
data there but it won't recall it when I put my search criteria in. The
records indicate "1 of 1" but do not list anyone. There is also a button
to
display all subjects when I hit that button it does the same thing.

It seems that the subject table is not populated or the subject detail
form
is not populating from the right table. However when I go to add a subject
to
the subjectcomplaints subform the population of subjects is there.

Very confused right now.

The code in the NotInList event explicitly opens the form to add new records
*only*. Your original code was:

DoCmd.OpenForm "frmCustomers1", , , , A_ADD, A_DIALOG, NewData

My suggested revision, using more up-to-date constants, was:

DoCmd.OpenForm "frmCustomers1", , , , acFormAdd, acDialog, NewData

That doesn't change the meaning of the statement, though. The constants
A_ADD and acFormAdd both say to open the form in data entry mode, meaning
that no existing records are to be displayed -- only new records can be
added. That makes sense if the form is being opened for the sole purpose of
adding a new Subject. I assumed that was what you wanted, since that's what
your original code said to do.

If you don't want that, it's possible to open the form in normal mode;
however, opening in data entry mode seems appropriate to me in this case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)