Linking combo boxes and records (1 Viewer)

[KirstenII]

I'm trying to teach myself Access 2K, but there's a couple of things I can't get to work. How do I link the selection that's made in a combo box on one form to the related record on another form? I can get the other form to open, but not to the correct record.

 

[BarkerD]

I assume you open the second form with a command button.

On the ON Click Event of the Command button, add a little bit of code.

'First, check to make sure there is a valid value in the combobox.

If IsNull(me.cmbName) = True Then
'Set focus to combo box to enter valid info.
msgbox$("No Value in Combo Box")
me.cmbName.SetFocus

Else

'Set Filter Criteria
Dim strCriteria as String
Dim strDocName as String

strCriteria = "[FieldName] = """ & me.cmbName & """"
strDocName = "YourFormName"

DoCmd.OpenForm strDocName,,,strCriteria

End If

'This code allows you to open a form and filter the results based on strCriteria. FieldName requires the name of the underlying field on the form you are opening which is related to the value in the combobox.

Duane Barker

 

[KirstenII]

That worked quite nicely, thank-you very much. I don't know VBA yet, so this has been a pretty steep learning curve.

One more question...
Would it be possible just to go to the selected record rather than filtering out the other records??? I would like the user to be able to select the record they want from the combo box but still be able to page through the other records on the form.

 

[ntp]

Instead of specifying criteria in the docmd.openform part you would pass the value from your combobox as an argument. This can be done like this:

docmd.openform strDocName,,,,me.cmbName

Since you were specifying criteria in the openform method I assume your form is based on some table or query. In that case you would have to do the following:

In the OnLoad event procedure for the form you include these lines -

me.recordsetclone.findfirst "[FieldName] = "'" & me.cmbName & "'"
me.bookmark = me.recordsetclone.bookmark

this will make the form open to the relevant record without filtering out thge other records.

I had answered a similar questions (same solution) previously. You can find it here:
http://www.access-programmers.co.uk/ubb/Forum4/HTML/001883.html

ntp