Let's take a closer look at the line you're using to open the form...
DoCmd.OpenForm stDocName, , , stLinkCriteria
if you type DoCmd.OpenForm into the VBA editor, followed by a space, you
will see a list of arguments brought up by intellisense... these are various
options, or parameters (or however you want to think of them, but they are
actually called arguments), that you can pass to dictate some base properties
of the form that is being opened...
DoCmd.OpenForm FormName, View, Filter, Where, Datamode, Windowmode, OpenArgs
FormName: self explanitory (should be!)
View: see dropdown list of options
Filter: Filters the form records to whatever criteria you pass
Where: Restrics records by whatever criteria you pass
Datamode: see dropdown, probably nothing to worry about for now
Windowmode: see Datamode description
OpenArgs: any misc values you want to pass to the form
Filter vs Where Clause: Say you're opening a form with 10k records... a
filter will load all 10k records even if the filter will only show 3 of them.
A Where clause, on the other hand, will only load the 3 records ratherthan
all 10k. Hence, using a Where clause is much more efficient, and filter's
should only be used at a specific need...
The boilerplate cmd button wizard enters the Form Name into the variable
stDocName, and a where clause into a variable stLinkCriteria. So with your
where clause set to open the form with only a single record, that of which
has the same ID as what you pass in your criteria. Therefore, you are
noticing that none of the other records are accessible.
You could, if you wanted, move this stLinkCriteria to the Filter argument,
and when you want to show records in the popup form, use the following line
to turn the filter off:
Me.FilterOn = False
However, this requires a bit more code than necessary for your purposes. To
do what you want, the general method is to pass the ID as an OpenArg (misc
data for the form), and read the OpenArg when the form opens, and navigate as
required.
The reason you are getting the invalid use of null error when opening the
form is because no information is being passed as an OpenArg (and therefore
Me.OpenArgs in the form is Null).
Basically, you want to leave the Where argument blank (so the records aren't
restricted), and move that ID to the OpenArgs argument. Also, you can get
rid of those two variables that are needlessly supplied by the wizard... the
finished product will look like this:
Private Sub Command129_Click()
On Error GoTo Err_Command129_Click
DoCmd.OpenForm "LeafletDetail", , , , , CStr(Me![LeafletID])
Exit_Command129_Click:
Exit Sub
Err_Command129_Click:
etc etc
End Sub
Note that OpenArgs takes a String (text) data, rather than a number. We use
CStr() to convert the number to a string, and on the other side, will use
CLng() to convert that string back to a number.
next...
Private Sub Form_Open(Cancel As Integer)
Dim lID As Long
LeafletID = CLng(Me.LeafletID)
With Me.RecordsetClone
.FindFirst "[LeafletID] = " & LeafletID
If .NoMatch Then
MsgBox "Record Not Found!"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Dim lID As Long
LeafletID = CLng(Me.LeafletID)
instead of using LeafletID = CLng(Me.LeafletID), you want:
lID = CLng(Me.OpenArgs)
You are not yet working with the LeafletID of this form (Me.LeafletID), and
using a variable name that is the same as a field or control name is never a
good idea.
.FindFirst "[LeafletID] = " & LeafletID
again, LeafletID is not a good variable name, IMO. Change this line to:
.FindFirst "LeafletID = " & lID
(side note: square brackets around field/table/form names are only required
if you use spaces in your names, which is supposed to be avoided)
Those few minor corrections should work... hopefully the drawn out
explanation will give you WHY it works.
One more additional note...
Checking for Nulls in OpenArgs (or anything else):
If Len(Nz(Me.OpenArgs, "")) = 0 Then
Cancel = True 'close form if no openargs supplied
End If
Check the VBA help on Len() and Nz() functions and you should be able to
figure out what's going on there. The above example will prevent the error
from raising and subsequently close the form if no OpenArg is supplied.
hth
--
Jack Leachwww.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
Cpthooker said:
Open the form without supplying a where clause, and pass the ID of the record
you want as the OpenArg... in the Open event of the detail form, use the
recordset bookmark to make the said ID current. For this example we'll
pretend you have a numeric ID:
Private Sub Form_Open(Cancel As Integer)
Dim lID As Long
lID = Clng(Me.OpenArgs)
With Me.RecordsetClone
.FindFirst "[IDField] = " & lID
If .NoMatch Then
MsgBox "Record Not Found!"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
hth
--
Jack Leachwww.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
I have been pulling my hair out trying to figure out how to pull this
off, what I have is a list of the records in the database and a wayto
open the detailed form showing more data. But when I used the wizard
to make the command button it prevents me from changing the record in
the detailed form as it is locked onto the choosen record which is no
good as I want the user to be able to pick another record. What is the
best way to pull this off?
.- Hide quoted text -
- Show quoted text -
Sorry but its not working I have changed what I thought needed to be
changed to match my db, but when I click on the command button it
brings up an error saying 'Invalid use of null'
This is what I am using for the command button to get onto the form
Private Sub Command129_Click()
On Error GoTo Err_Command129_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "LeafletDetail"
stLinkCriteria = "[LeafletID]=" & Me![LeafletID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command129_Click:
Exit Sub
Err_Command129_Click:
MsgBox Err.Description
Resume Exit_Command129_Click
and this is the code on the opening form
Private Sub Form_Open(Cancel As Integer)
Dim lID As Long
LeafletID = CLng(Me.LeafletID)
With Me.RecordsetClone
.FindFirst "[LeafletID] = " & LeafletID
If .NoMatch Then
MsgBox "Record Not Found!"
Else
Me.Bookmark = .Bookmark
End If
End With
Maybe you can see something I missed
.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
