command button to open form needs to assign values

[Guest]

I have an Animal Record form that shows the record for an individual animal.
I created a command button that opens the Animal Journal form.

I need to preset the values in the Animal Journal form so that the [Animal
Journal].[Animal ID] field is automatically set to the Animal ID from the
Animal Record form, and I would also like to display the Animal Record Name
on the Animal Journal form.

I played around with the OpenArgs property of my DoCmd.Open, but this does
not seem to work.

Here's the code:

Private Sub Add_AJ_Entry_Click()
On Error GoTo Err_Add_AJ_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Animal Journal"

stLinkCriteria = "[Animal Records.Animal ID]=" & Me![Animal ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Add_AJ_Entry_Click:
Exit Sub

Err_Add_AJ_Entry_Click:
MsgBox Err.Description
Resume Exit_Add_AJ_Entry_Click

End Sub

 

[Carl Rapson]

I have an Animal Record form that shows the record for an individual
animal.
I created a command button that opens the Animal Journal form.

I need to preset the values in the Animal Journal form so that the [Animal
Journal].[Animal ID] field is automatically set to the Animal ID from the
Animal Record form, and I would also like to display the Animal Record
Name
on the Animal Journal form.

I played around with the OpenArgs property of my DoCmd.Open, but this does
not seem to work.

Here's the code:

Private Sub Add_AJ_Entry_Click()
On Error GoTo Err_Add_AJ_Entry_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Animal Journal"

stLinkCriteria = "[Animal Records.Animal ID]=" & Me![Animal ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Add_AJ_Entry_Click:
Exit Sub

Err_Add_AJ_Entry_Click:
MsgBox Err.Description
Resume Exit_Add_AJ_Entry_Click

End Sub

Can there be more than one entry in Animal Journal for each animal, or is
there only one entry? If there's more than one, how are they differentiated?

If there's only one entry in Animal Journal for each animal, here's what I
would do:

In the Click event, check to see if there's already a record in Animal
Journal for this Animal ID and add a new record to Animal Journal if
necessary:

If (DCount("*","[Animal Journal]","[Animal ID]=" & Me.[Animal ID]) = 0
Then
DoCmd.RunSQL "INSERT INTO [Animal Journal] ([Animal ID]) VALUES (" &
Me.[Animal ID] & ")"
End If

Next open the form positioned to the Animal ID record:

DoCmd.OpenForm "Animal Journal",,,"[Animal ID]=" & Me.[Animal ID]

If there can be more than one record in Animal Journal for the Animal ID,
the process will be a little more complicated.

If Animal Record Name is a field in the Animal Record table, you can display
it on the Animal Journal form by using an unbound textbox control and
populating it in that form's Current event:

txtAnimalRecordName = DLookUp("[Animal Record Name]","[Animal
Record]","[Animal ID]=" & Me.[Animal ID])

Be sure to use your own control, field, and table names in place of the
sample values I've used.

Carl Rapson