jump to a record using VBA

[drazen]

Hi, I have a DB with 1000's of clients in it. one of the fields is the 'Ref number' of the client. I am looking at putting another field on the form 'Associated with' Where I would put another ref number, then have a button next to it that will jump me to that ref number (like husband and wife) But I am struggling.
How do I target and select a specific ref number and display it?

the other problem I have is the ref numbers were not formatted properly at the start, so 23 123 or 1023 would all be valid ref numbers. If i put '23' in the Associated field, i need to show only '23'

can anyone point me in the right direction please?
Thanks

 

[The_Doc_Man]

First, if the "ref number" is numeric, there is no chance of getting the wrong one. If your search uses the "=" operator, there is no chance of getting the wrong one. If the ref number is stored as a text field internally, then there are possible uses of the "LIKE" operator that COULD give you more than one option. Therefore, store your ref numbers numerically and never use "LIKE" on any of them.

Second, one way to do this type of "navigation" (I'm emphasizing the keyword for that action) involves using the command-button wizard to perform a form operation to move to a specific record. If you do that, the wizard will ask you how to identify the record. Go ahead and build that command button, then look at the VBA event code that it builds for you. It should show you exactly how to jump your form to a new / different record.

Internally, it may involve a "DoCmd.GoTo Record" operation. You can do a SEARCH on the DoCmd.GoTo operation to see how you might use that from VBA.

 

[Solo712]

Hi, I have a DB with 1000's of clients in it. one of the fields is the 'Ref number' of the client. I am looking at putting another field on the form 'Associated with' Where I would put another ref number, then have a button next to it that will jump me to that ref number (like husband and wife) But I am struggling.
How do I target and select a specific ref number and display it?

the other problem I have is the ref numbers were not formatted properly at the start, so 23 123 or 1023 would all be valid ref numbers. If i put '23' in the Associated field, i need to show only '23'

can anyone point me in the right direction please?
Thanks

Hi drazen,
this is easy to do. Put this as the the click event of the button.

Me.RecordsetClone.FindFirst "AssocID = " & Me!MyReferenceField
If Not Me.RecordsetClone.Nomatch Then ' record found
   Me.Undo
   Me.Bookmark = Me.RecordsetClone.Bookmark   'jump to it     
Else
   MsgBox "Reference record was not found"
End If

Now, this assumes that the reference field is numeric. You show valid entries for the associating field which indicate it is a string.That could work too but only if the associating field is referenced exactly the same way with spaces, dashes, etc. in the other field. In that case the first line should read:

Me.RecordsetClone.FindFirst "AssocID = '" & Me!MyReferenceField &"'"

Best,
Jiri

 

[Dreamweaver]

There is a lookup combo box wizard that does that