What you want to do is to create an unbound form with a combo box and a button.
Use the combo box wizard to select the field from the table you would like to populate the box with.
Then place a button on the form using the button wizard, and have that wizard open the form in edit mode (select Open Form from the list).
You will get button code that looks like this on the button's On Click Event:
'==========
Private Sub btnOpenForm1_Click()
On Error GoTo Err_btnOpenForm1_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btnOpenForm1_Click:
Exit Sub
Err_btnOpenForm1_Click:
MsgBox Err.Description
Resume Exit_btnOpenForm1_Click
End Sub
'==========
Notice the stLinkCriteria variable. Notice also that it is not defined. To define it, add this code:
'==========
Private Sub btnOpenForm1_Click()
On Error GoTo Err_btnOpenForm1_Click
Dim stDocName As String
Dim stLinkingData As String
Dim stLinkCriteria As String
stLinkingData = Me.cboYourComboBox
stLinkCriteria = "[PKField] = " & "'" & stLinkingData & "'"
stDocName = "Form1"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btnOpenForm1_Click:
Exit Sub
Err_btnOpenForm1_Click:
MsgBox Err.Description
Resume Exit_btnOpenForm1_Click
End Sub
'==========
That should do it.
Please note that the combo box, while showing meaningful data, is actually bound to the Primary Key field [PKField] in your look up table, so that is the data you are collecting to pass to the form.
If you limit the combo box to the list, then you won't have to worry about error handling for invalid data. The error handlers will do that automatically.
One of the advantages of this method is that it only calls the data that the form needs for that record, and not all the records, so it should work pretty quickly.
To accomodate your users, you may want to put another combo box on the form that is opened so that once it is opened, the user can look up another record without having to go back to the main form again.
Use the qizard to add a combo box that looks up the same data (NOT to find a record on your form!) and then put this code in the combo box's After Update Event:
'==========
Me.RecordSource = "SELECT * from tblYourTable WHERE PKField = " & Str(cboNewComboBox)
'==========
When a user selects meaningful data from the combo box, the form will reload with the new data. Note the Primary Key is still being passed.
HTH
mmcdonal
|
