goto record on form by autoID number

[BlueAvatar]

hi,
i have simple database where some records are related to others. i want to be able to put a button on the form to pickup the related value out of a field (integer at the moment) and use that number to go to the record with the same ID (auto assigned). I understand how to make the button but i dont know how to access fields within the table for the current record.
thanks in advance.

 

[mymou]

BlueAvatar

Could use something like a doubleclick event (instead of a button) on code number which opens up the appropiate form and then does a simple find on that form (using that code)

DoCmd.OpenForm "MyFormName" 'this is now the active form
FindRecord (Me.CodeID)'find in this active form - using 'CodeID' (which is the name of the control where the ID you are using is stored) from form where event took place.

This will open up the new form with all records - showing the single one you are interested in. An alternative is to use a filter when you open up the form

(I think this is part of the button wizard anyway. But:
stLinkCriteria = "[CodeID]=" & Me.CodeID
DoCmd.OpenForm stDocName ', , , stLinkCriteria
)

Any help?

Stew

PS you can also use the 'expression builder' for examples of how to reference controls - using syntax like Forms!MyFormName!MyControlName

 

[jflachman]

I think you were asking to stay in the same form, but go to the new record. Is that right?
If so, there are a couple of ways to do that,b ut first let me ask a question. Does your ID number mean anything to the user? If not, they should never see it or even need to know that it exists.
Either way, there is a reasonably simple solution that the ComboBox wizard can help implement.

Create a combobox on your form. When the wizard opens, select the option to "Find a record on my form based on the value I select in my combo box"
Hit Next and select the field you want to see in your combo box. This may be just the ID if that is relevant to the user. If it is not, then it should be the ID and something that IS relevant to your user, like a customer name or whatever.

Hit Next. If the ID is not relevant, make sure to check the "Hide Key Column" box. Continue through the wizard.

Now when you change the combo box, the form will go to that record.

Let me know if that doesn't solve your problem
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[BlueAvatar]

Thanks for the tips, but it didnt work quite the way i intended it..
i want to stay on the same form.
i have a field on the form that is from the database that shows the number of a record that is related to it. i want to be able to click on a button next to that field and have it go to that related record..

sorry guys, i'm not an access programmer so i dont know the right jargon to describe my situation.

 

[mymou]

BlueAvatar

Your situation seems identical to mine. FindRecord did the trick. Did you try FindRecord? What happened?

Stew

 

[jflachman]

OK, if you don't want the combo box itself to change the record, then add this code to the Click event on button:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Nz(Me![ComboBoxName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Where RecordID is the matching field in your record that you want to find and ComboBoxName is obviously the name of the combobox with the ID that you want to locate.

If that doesn't do what you want, clarify further and I'll try to help.
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[jflachman]

OK, if you don't want the combo box itself to change the record, then add this code to the OnClick event on button:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RecordID] = " & Str(Nz(Me![ComboBoxName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Where RecordID is the matching field in your record that you want to find and ComboBoxName is obviously the name of the combobox with the ID that you want to locate.

If that doesn't do what you want, clarify further and I'll try to help.
Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt