Combobox/linking record difficulties

[Chucky]

I've created combobox1 in my form1 which takes data from a
column in a table1 in the same database. Now, when I drop
down my box, and click on it, is there a way for me to
link each individual value to its individual record?
Please help?

And to clarify... I created a 2nd form using the wizard
and it basically made a box for each of my fields located
in the table1. This 2nd form has those buttons on the
bottom where I can change which record I see. Is there a
way for me to link to that specific record number in a
form? If so, how?

In addition, if the values from my combobox cannot be
linked by just clicking on the number, is there a way for
me to create a command button that links the value
selected which would then link to the specific record in
form2?

PLEASE HElp! =)

 

[Tim Ferguson]

I've created combobox1 in my form1 which takes data from a
column in a table1 in the same database. Now, when I drop
down my box, and click on it, is there a way for me to
link each individual value to its individual record?
Please help?

The easiest and most flexible way of acheiving this is to show your users
the Query by Form functionality built into Access -- that way you don't
have to do any programming at all.

The complex way to do this is to add a little bit of vba to the form that
takes the combobox value and sets the form's recordsource to it

Private Sub cboLookUp_Change()

' remember the value for simplicity
Dim strLookUpValue as String
Dim rst as Recordset ' or DAO.Recordset if you have to keep
' a reference to ADO...

' and retrieve it
strLookUpValue = cboLookUp.Value
' this should be quoted if it's a string value:
' strLookUpValue = Chr$(34) & cboLookUp.Value & Chr$(34)

' now move the form using a copy of its recordset
Set rst = Me.RecordsetClone

' find the record
rst.FindFirst "MyPKField = " & strLookUpValue

' Big problem if it's not there!
If rst.NoMatch then
MsgBox "Oh Golly :- bad news"
Stop

End If

' Now move the form
Me.BookMark = rst.BookMark

' and tidy up
rst.Close

End Sub

remember this is untested air code, and the error trapping is quite
inadequate: eg what happens if there is no selection?

All the best


Tim F