Where you've gone wrong is in treating one line in the example code I sent
you as three separate lines. The underscore character at the end of a line
of code is a continuation character and means that the following line is
actually a continuation of the current line. This makes the code more easily
readable as you can see the whole line, split up over several lines, in the
width of the VBA editor window. WhereCondition and OpenArgs are arguments of
the OpenForm method. The first one filters the form being opened, the second
one passes a value to the form. I've named them specifically, which is why
they are followed by := in the code rather than just relying on their
positions in the argument list and putting in loads of commas (the colon
before the = sign is essential when you do this). Again this makes for
greater readability of the code. Notice also that I've not used the
acFormAdd setting because, if I understand you rightly, you want to show any
existing record(s) in the CheckSheetUTI form which match the current record
in the first form. If there are no matching records then the CheckSheetUTI
form would open at a new record.
The other mistake you've made is to put the code which should go in the
CheckSheetUTI form's Open event procedure in the Click event procedure of the
OpenCheckSheet button on the first form.
So, the code for the button's Click event procedure should go like this:
Private Sub OpenCheckSheet_Click()
On Error GoTo Err_OpenCheckSheet_Click
RunCommand acCmdSaveRecord
DoCmd.OpenForm "CheckSheetUTI", _
WhereCondition:="Sign_Organism_No" & Me.Sign_Organism_No, _
OpenArgs:=Me.Sign_Organism_No
DoCmd.Close acForm, Me.Name
Exit_OpenCheckSheet_Click:
Exit Sub
Err_OpenCheckSheet_Click:
MsgBox Err.DESCRIPTION
Resume Exit_OpenCheckSheet_Click
End Sub
The other code goes in the Open event procedure of form CheckSheetUTI. To
do this open the form in design view and make sure the Properties Sheet is
open by selecting Properties from the View menu if its not. In the combo box
at the top of the properties sheet select Form from the drop down list.
Select the Event tab in the properties sheet and then select the On Open
event property. Click on the 'build' button (the one on the right with 3
dots). Select Code Builder in the dialogue which opens. The VBA window
should now open at the form's Open event procedure with the first and last
lines of the procedure already in place. Add the following 3 lines of code
between these existing lines to set the Sign_Organism_No control's
DefaultValue property to the value you passed from the first form:
If Not IsNull(Me.OpenArgs) Then
Me.Sign_Organism_No.DefaultValue = """" & Me.OpenArgs & """"
End If
What should now happen when you click the button on the first form, if there
are any records in the CheckSheetUTI form's underlying table which match the
current Sign_Organism_No in the first form, then the from will open with just
those record(s) (and the usual blank form for adding a new record). If there
are no matching records then the form will open at a blank record for you to
add a new one with the Sign_Organism_No value from the first form already in
place. Until you add some more data to the form it won't be 'Dirty' as the
value in Sign_Organism_No is just a default value until you start to add
more data, so at this stage you can abandon adding a new record simply by
closing the form if you wish. If you add more data, however, the new record
will be saved when you close the form and the Sign_Organism_No field will
have the value you passed to it.
I hope I've followed Einstein's dictum and made it as simple as possible,
but no more so. If you have any problem's however, post back here. At first
reading it might sound a bit scary, but if you take it step by step you
should be OK. After doing this sort of thing a few times it becomes a piece
of cake, and takes less time to do it than it has taken me to describe it.
Let me know how you get on.
