please help (1 Viewer)

[Treason]

I have a combo-box, that I would like to update into a record where 2 values meet. For example, If ID# in the combo-box is 0000, then find record with ID# 0000 in the form. This has been a complicated ordeal for me.

I have looked into the RecordsetClone property hoping to find some answers. I came across this code in MS help.

Sub SupplierID_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!SupplierID)
rst.FindFirst "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

Where do I put this code? Am I just stupid? I tried ListBox7_AfterUpdate() ..

 

[Randomblink]

Access Wizard

Private Sub ComboBoxExample_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[insertTableNameHere].[insertFieldNameHere] = " & Str(Me![ComboBoxExample])
Me.Bookmark = rs.Bookmark
End Sub

You have to set your record source of your combo-box to whatever table you are searching through.

So, for example...

If I had Table A that held all of my employees.
And if Table A has two fields.
Field #1) Employee_IDNumber
Field #2) Employee_Name
And if Table A was named tbl_EmployeeTable

Then the code about would be:


Private Sub ComboBoxExample_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[tbl_EmployeeTable].[Employee_IDNumber]= " & Str(Me![ComboBoxExample])
Me.Bookmark = rs.Bookmark
End Sub

And I would set the RecordSource of the combo-box to that table with Employee_IDNumber being the bound field and the first field.

Then, whenever you select an Employee from the drop down box, it will check the drop down box...find the Employee_IDNumber...and it will query the table and grab the Employee who has that IDNumber...

The ONLY problem is...
You really should have it checking a field that is a primary key or at least one that Unique...Otherwise, if you have several Employees who share the same Employee_IDNumber, then you could get some errors...

UNLESS you have like a datasheet form you are using...

Well, that is my two cents worth...with a nickel thrown in at the end here...

My nickel answer is this...
I am curious why you don't use the combo-box wizard to create this combo-box?

If you use the wizard, you can select to have the combo-box look up records on the form based on the selection...

The code I dropped in above was take from creating a quick combo-box using the wizard and then cutting-and-pasting the code directly to my message above...

Just wondering...