Convert SearchForRecord Macro to VBA

[Phred]

Access 2007, Not compiled, not split, Windows 7, Moderate VBA and Access skills.



I have a main Customer form with a combo box. The combo box was created by the wizard. You select the Customer Name from the combo box and the corresponding record is populated on the main form. The wizard created the macro for the combobox. The macro is as follows:
SearchForRecord, , First, ="[CustomerID] = " & Str(Nz(Screen.ActiveControl,0))

RecordSource = SELECT tbl_customers.customerID, tbl_customers.customername FROM tbl_customers ORDER BY tbl_customers.customername;

The combobox is unbound.

I would like to convert this SearchForRecord macro over to VBA so I have better control over what happens. None of my four attempts work. I can't figure what I am doing wrong.

1. DoCmd.SearchForRecord , , acFirst, "[CustomerID] = '" & Me.Combo80.Column(0) & "'"
2. DoCmd.SearchForRecord , , acFirst, "[CustomerID] = '" & ActiveControl.Combo80.Column(0) & "'"
3. DoCmd.SearchForRecord , , acFirst, "[CustomerID] = " & Str(Nz(Screen.ActiveControl, 0)) & "'"
4. DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[CustomerID] = '" & Me.Combo80.Column(0) & "'"

Can anyone help?

Thanks Phred

[ketbdnetbp]

Phred - Untested in A2007 (works in A2003) but, you might be able to use the following vba in an after update event of the combo:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Me![Combo80]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

All the best,

Jim

[Phred]

Jim:

I need to pick up Column(0) which is the CustomerID. This doesn't look like it is picking it up. Am I missing something?

Thanks Fred

[ketbdnetbp]

Fred - If the bound column on the combo's properties sheet is 1 and that points to the CustomerID, then you shouldn't have to specify the column. Have you tried the code? If it doesn't work, you could also try:

Dim rs As Object
dim x as Long
x = Me![Combo80].Column(0)
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & x
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Hope this helps,

Jim

[Phred]

The code seems to work but I can't figure out why. You are right the bound column on the property sheet is 1 (CustomerID). Column(2) is Customer name.

The thing that throws me is that everywhere else I have dedicated the column(0), This is new to me.

Thank you for your help Jim

Fred