Go to specific record via code?

  • Thread starter Thread starter Fr@nk
  • Start date Start date
F

Fr@nk

Hi. I have a form (subform, actually) that fires code when the user
double-clicks a line in a listbox. The code opens up another form, and
filters based on the line (record) the user double-clicked on in the first
window. Usually, the recordset for the second form is one or two records,
and the navigation controls show this. So far, so good.

How can I (in my code) not only filter the opening up of the second form,
but then, if there are more than one records displayed in the "new" form,
have the form display a specific one (which will have a match to the listbox
line the user originally clicked on)? Right now, when the new form is
displaying multiple records (correctly), it displays what appears to be the
last record usually. If the user had double-clicked a different listbox line
(to get the new form to open), this may or may not be the correct record
displayed. The user must navigate to the correct record.

I know this sounds confusing, but my question is pretty simple I think: I
have the right recordset for my newly-displayed form (via the filter,
applied in the code); I just need to know how to _display_ a particular
record in that form's recordset, without the user having to navigate to it.

Can I do a Find (based on a criteria I can easily specify in the code), get
that record's ID, then tell the form to display the record (docmd.goto?)
with that ID? If so, what's the syntax for that? Thanks.


F
 
Access 2000 and above:

Private Sub Form_Load()
On Error Goto HandleError

Dim rst as ADODB.Recordset
Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")
Set rstContact = New ADODB.Recordset
Set rst=me.RecordSetClone

rst.Find "[ID]=" & IDVariable
Me.Bookmark = rst.Bookmark

rst.close
Set rst=Nothing

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub

Access 97:

Private Sub Form_Load()
On Error Goto HandleError

Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")

me.RecordSetClone.FindFirst "[ID]=" & lngID
me.Bookmark=me.RecordSetClone.Bookmark

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub
 
-----Original Message-----
Access 2000 and above:

Private Sub Form_Load()
On Error Goto HandleError

Dim rst as ADODB.Recordset
Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")
Set rstContact = New ADODB.Recordset
Set rst=me.RecordSetClone

rst.Find "[ID]=" & IDVariable
Me.Bookmark = rst.Bookmark

rst.close
Set rst=Nothing

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub

Access 97:

Private Sub Form_Load()
On Error Goto HandleError

Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")

me.RecordSetClone.FindFirst "[ID]=" & lngID
me.Bookmark=me.RecordSetClone.Bookmark

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub
Click to expand...

Thanks. I tried the code above (under Access97), but I get
a "Syntax error (missing operator) in expression" error on
the FindFirst line. I'm using Access2K, but .find doesn't
seem to be available; I'm using FindFirst. I find that
findfirst is very picky about the WHERE syntax. Any other
tips?


F
 
Hmmm,
My A2000 code was sloppy last time. Here it is again with
some revisions. I have more suggestions below.

Access 2000 and above:

Private Sub Form_Load()
On Error Goto HandleError

Dim rst as ADODB.Recordset
Dim lngID as Long

lngID=Forms("NameOfParentForm")("NameOfSubForm")
("NameOfListBox")

Set rst = New ADODB.Recordset
Set rst=me.RecordSetClone

rst.Find "[ID]=" & lngID
Me.Bookmark = rst.Bookmark

rst.close
Set rst=Nothing

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub

Couple things. When referring to the list box on your sub
form to get the ID value. Use this syntax,
Forms("NameOfParentForm")("NameOfSubForm")
("NameOfListBox")

If the ID is of a Text datatype, use this criteria syntax
"[ID]='" & IDVariable & "'"
in the Find or FindFirst
**Make sure you change the "IDVariable" to the actual
name of the variable you use(which I didn't illustrate
properly last time)
The code should be in the Load or Open event of the form
that opens when you double click the list box, not in the
form where the list box resides.
If none of this fixes the issue, please post the exact
code you are using and at which line the program breaks.
Thanks!

-----Original Message-----
-----Original Message-----
Access 2000 and above:

Private Sub Form_Load()
On Error Goto HandleError

Dim rst as ADODB.Recordset
Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")
Set rstContact = New ADODB.Recordset
Set rst=me.RecordSetClone

rst.Find "[ID]=" & IDVariable
Me.Bookmark = rst.Bookmark

rst.close
Set rst=Nothing

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub

Access 97:

Private Sub Form_Load()
On Error Goto HandleError

Dim lngID as Long

lngID=Forms("NameOfYourOpenForm")("NameOfTheListBox")

me.RecordSetClone.FindFirst "[ID]=" & lngID
me.Bookmark=me.RecordSetClone.Bookmark

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub
Click to expand...

Thanks. I tried the code above (under Access97), but I get
a "Syntax error (missing operator) in expression" error on
the FindFirst line. I'm using Access2K, but .find doesn't
seem to be available; I'm using FindFirst. I find that
findfirst is very picky about the WHERE syntax. Any other
tips?


F
.
Click to expand...
 
SFAxess said:
Hmmm,
My A2000 code was sloppy last time. Here it is again with
some revisions. I have more suggestions below.

Access 2000 and above:

Private Sub Form_Load()
On Error Goto HandleError

Dim rst as ADODB.Recordset
Dim lngID as Long

lngID=Forms("NameOfParentForm")("NameOfSubForm")
("NameOfListBox")

Set rst = New ADODB.Recordset
Set rst=me.RecordSetClone

rst.Find "[ID]=" & lngID
Me.Bookmark = rst.Bookmark

rst.close
Set rst=Nothing

ExitHere:
Exit Sub
HandleError:
msgbox Err.Description
Resume ExitHere
End Sub

Couple things. When referring to the list box on your sub
form to get the ID value. Use this syntax,
Forms("NameOfParentForm")("NameOfSubForm")
("NameOfListBox")

If the ID is of a Text datatype, use this criteria syntax
"[ID]='" & IDVariable & "'"
in the Find or FindFirst
**Make sure you change the "IDVariable" to the actual
name of the variable you use(which I didn't illustrate
properly last time)
The code should be in the Load or Open event of the form
that opens when you double click the list box, not in the
form where the list box resides.
If none of this fixes the issue, please post the exact
code you are using and at which line the program breaks.
Thanks!
Click to expand...

Well, I got it to work. Of course my ID variable was a string (but that
wasn't the problem). I still used FindFirst; and it finally worked...after I
tweaked the syntax a lot: rs.FindFirst ("[EventID] =" & """" &
Forms!frmMyForm!Subform...etc.... & """". It was something like that: lots
of quotes. I've had to do that before. Like I said, FindFirst seems to be
very finicky about syntax.

Anyway, thanks very much for pointing me in the right direction--your tip to
put the code in the open event of the new form did the trick.


F
 
Back
Top