Interdependent forms

[Joan]

The situation I'm working with is rather difficult to explain, but please
bear with me. I will try to explain it as best as I can. I am working with
an AddLitters form, and AddDam and AddSire forms. When entering information
on the AddLitters form there are two combo-boxes called DamRegNumber and
SireRegNum to enter the Sire and Dam of the Litter. I have code written in
both of their NotInList events that displays a message asking the user if
they would like to enter a new sire or dam if the number entered is not in
the Sires or Dams tables. If the user clicks "Yes" then either a
AddDamwAddLitter or a AddSirewAddLitter form opens depending upon which
combo box a number is entered in. Each of these forms has a txtRegNumber
for the new Sire or Dam and two combo-boxes (called SireRegNum and
DamRegNum) to enter the registration numbers(primary key) of the new Sire's
or new Dam's father and mother (sire and dam). These combo boxes also have
code for their NotInList event to open a form to add them if they are also
NOT in the table.

This process can keep repeating itself and starts with the AddLitter form.
However, the same process could start with either the AddDam form or the Add
Sire form. Consider the AddDam form where if for instance the registration
number of the new Dam's dam is not in the table, a form could open in which
to add the new Dam's dam. Likewise if the new Dam's sire is not in the list.



Below I've copied my code from the AddDamwAddLitter form (varNewValue and
varDNewValue are both global public variables). Is there a simpler way to do
this, considering all the reiterations of this process that could occur? As
you can see on AddDamwAddLitter form's On Close event, I requery the
AddLitters![DamRegNumber] and set the text property of
AddLitters![DamRegNumber] to varNewValue. Doing it the way I am, I need a
different AddDam or AddSire form for each different instance because of the
OnClose event referring to the form from which the present form was called.
For instance now I have an AddDam form, an AddDamwAddLitter form , and an
AddDamwAddDam form. Likewise for adding Sires. And each of these forms also
have combo boxes with NotInList events for it's Dam and Sire. This is
getting way too cumbersome and confusing to code. Is there a better way to
code this so that I only need to use one AddDam or one AddSire form??

Joan



<Option Compare Database
<Option Explicit

<Private Sub DamRegNum_NotInList(NewData As String, Response As Integer)
< If Not IsNull(DLookup("[Dam Reg Number]", "Dams", "DamRegNum=""" & NewData
& """")) Then
< Response = DATA_ERRADDED
< Exit Sub
< End If
< If MsgBox("""" & NewData & """ is not in the Dam list. Would you like
to add it?", 33) <> 1 Then
< Me.DamRegNum.Text = " "
< Response = DATA_ERRCONTINUE
< Exit Sub
< End If
< varDNewValue = Me!DamRegNum.Text
< Me.DamRegNum.Text = " "
< DoCmd.OpenForm "AddDamwAddDam", , , , acFormAdd 'Data Entry Mode
< Response = DATA_ERRCONTINUE
<End Sub


<Private Sub Form_Close()
< Forms![AddLitter]![DamRegNumber].Requery
< Forms![AddLitter]![DamRegNumber].Text = varNewValue
<End Sub

<Private Sub SireRegNum_NotInList(NewData As String, Response As Integer)
< If Not IsNull(DLookup("[Sire Reg Number]", "Sires", "SireRegNum=""" &
NewData & """")) Then
< Response = DATA_ERRADDED
< Exit Sub
< End If
< If MsgBox("""" & NewData & """ is not in the Sire list. Would you like
to add it?", 33) <> 1 Then
< Me.SireRegNum.Text = " "
< Response = DATA_ERRCONTINUE
< Exit Sub
< End If
< varSNewValue = Me!SireRegNum.Text
< Me.SireRegNum.Text = " "
< DoCmd.OpenForm "AddSire", , , , acFormAdd 'Data Entry Mode
< Response = DATA_ERRCONTINUE
<End Sub

Below is the code for the AddLitters![DamRegNumber]'s NotInList event:

<Private Sub DamRegNumber_NotInList(NewData As String, Response As Integer)

< If Not IsNull(DLookup("[Dam Reg Number]", "Dams", "DamRegNumber=""" &
NewData & """")) Then
< Response = DATA_ERRADDED
< Exit Sub
< End If
< If MsgBox("""" & NewData & """ is not in the Dam list. Would you like
to add it?", 33) <> 1 Then
< Me.DamRegNumber.Text = " "
< Response = DATA_ERRCONTINUE
< Exit Sub
< End If
< varNewValue = Me!DamRegNumber.Text
< Me.DamRegNumber.Text = " "
< DoCmd.OpenForm "AddDamwAddLitter", , , , acFormAdd 'Data Entry Mode
< Response = DATA_ERRCONTINUE
<End Sub

 

[Joan]

Hi Albert,

I think that maybe I am still missing something. I used the 3 lines of code
that you recommended for the AddLitter!DamRegNum's NotInList event:

<If MsgBox("""" & NewData & """ is not in the Dam list. Would you like to
add it?", 33) <> 1 Then
< DoCmd.OpenForm "AddDamwAddLitter", , , , acFormAdd, , NewData 'Data
Entry Mode
< Response = acDataErrAdded

On the AddDamwAddLitter form I set the forms cycle property to current
record, and turned off the navigation buttons on the bottom. I tried setting
the Allow Additions to No but then the controls on the form did not appear
at all. So I changed it back to Yes. I also put the code in the
AddDamwAddLitter form's On-Load event that would set the default of the
desired field to the openargs. When I test this in the AddLitter form by
putting in a DamRegNumber that is not in the list. I get the above message
and click "Yes". Than I get the message from Access which states: "The
text you entered isn't an item in the list. Select an item from the list or
enter text that matches on of the listed items." The AddDamwAddLitter form
does not even open.

Then I tried putting back in the following lines after the If statemnt and
before DoCmd.OpenForm:

<Me.DamRegNumber.Text = " "
< Response = DATA_ERRCONTINUE
< Exit Sub
< End If

Then the DoCmd line works as the AddDamwAddLitter form opens with the number
entered on the AddLitter form appearing as the default value in the
txtRegNumber control of the AddDamwAddLitter form. However, I still get the
message: "The text you entered isn't an item in the list. Select....."

Am puzzled as to why this still does not work.?

Joan

 

[Albert D. Kallal]

We are missing the acDialog here.

Sorry, and your parameters/commas are mixed up.

The code should be like:

If MsgBox("Add new source type?", vbOKCancel) = vbOK Then

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

Response = acDataErrAdded

End If

Hence, you need the acDialog for the above to work.

As for turning off the navigation buttons etc, that does not actually need
to be done, but is just so much better for your users as to not to confuse
them so much.

Also, turning off the allow additions is just again a thing that prevents
the user from accidentally adding more then one record during this whole
process. It is not needed, but should work.

The use of acFormAdd in the above will over ride the allow additions setting
in the forms property setting, but does so for ONLY ONE record (which is
exactly what we need).