GoToRecord won't Go To!

[jnuus]

On my main user form, I have a command button that brings up a dialog box with a list of all the names in the recordset. Selecting a name in the list box and clicking ShowRecord is supposed to take the user to the selected record, but instead it always displays the same record in the recordset regardless of which name is selected. Here is the code for the ShowRecord command button that is sets my main form to the selected record:

Private Sub ShowRecord_Click()

' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

' Store the recordset for the Subscribers form.
Set rst = Forms!frmMain.RecordsetClone

' Locate the record for the selected subscriber.
rst.FindFirst "MemberID = " & List0

' Set the form's Bookmark property to move to the record.
Forms!frmMain.Bookmark = rst.Bookmark

' Close the dialog box.
DoCmd.Close acForm, "GoToRecordDialog"

End Sub

Any help will be much appreciated.

 

[KenReay]

Hi
are you actually getting a match

why not try an if rst.nomatch just to check after your findfirst ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jnuus]

I added the lines after the find first -

If rst.NoMatch Then
msg = MsgBox("No match!", vbOK + vbExclamation)
End If

and it comes up each time, so I don't think I am getting a match. But I haven't the first clue as to why.

No matter what record I have showing in frmMain, when I call the list box and select a name, it always returns the first record in the recordset.

Any ideas? Thanks again.

 

[1127]

Why You use a declared recordset?

Why don't just search on the frmmain.recordsetclone

The whole code don't have logic

You set up a recordset with the form's recordsetclone, search for something and after that you bookmark the first recorset.

By the way.

After You find the record save the ID (or whatever you have) into a variable and make an other Find on the recordsetclone.

The bookmark IT'S NOT THE SAME IN THE BOUTH RECORDSET. It's not like the Fox!

 

[jnuus]

I needed this procedure for my application to track personnel data at work, plus I want to get better with VBA, so I bought the book "Access 2002 Visual Basic for Applications Step by Step." The code I am using is straight out of the book, I just substituted my frmMain for their Subscribers form. The code works perfectly with their sample app that came on the CD, but for some reason will not work in my app.

I have copied and re-copied it verbatim (substituting my form and ID of course), built and re-built the dialog box exactly as they demonstrate in the book, all to no avail.

Definitely needing some help on this one...

 

[Eupher]

What is the MultiSelect property of the listbox? If it's "Simple" or "Extended", the value of the field will always be null, and you'll have to step through the ItemsSelected property to get the value(s). Try setting the MultiSelect property to "None". If that doesn't work, set a break in your code and see what the value of the listbox is at the FindFirst statement.

Ken S.

 

[jnuus]

Well,

I started researching other ways to find records in a recordset and found some examples that looked fairly logical, so I used their concept and changed the syntax of my code (actually it was Microsoft's code from the how-to book). After playing around with it a little while, *Presto* - it worked. The new code is:

Private Sub ShowRecord_Click()
' Find the selected record, then close the dialog box.

Dim rst As DAO.Recordset

Set rst = Forms!frmMain.Recordset.Clone

rst.FindFirst "[MemberID] = '" & [List0] & "'"

If Not rst.EOF Then Forms!frmMain.Bookmark = rst.Bookmark

DoCmd.Close acForm, "GoToRecordDialog"

End Sub

What an excellent VBA learning experience. I am now beginning to understand why this works and the other didn't.

Thanks to all who responded with help. But don't stray too far - I'm sure I'll be here asking again at some point!