Find record based on Combo box selection (not working) (1 Viewer)

grane236

Registered User.
Local time
Tomorrow, 00:38
Joined
Nov 19, 2012
Messages
15
Could someone please tell me what Im doing wrong? I want to retrieve a record from my table (business details) based on the business selected in a drop down. But my code says its a new business every time, regardless of whether an existing business is chosen from the drop down:

Private Sub Combo135_AfterUpdate()
Dim rs As DAO.Recordset
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Business Name] = " & Me.Combo135
If rs.NoMatch Then
MsgBox "New Business?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub
 

Isskint

Slowly Developing
Local time
Today, 16:38
Joined
Apr 25, 2012
Messages
1,302
I would presume [Business Name] is a text field? If so you need to use single quotes around the parameter you are supplying, EG;

rs.FindFirst "[Business Name] = '" & Me.Combo135 & "'"
 

grane236

Registered User.
Local time
Tomorrow, 00:38
Joined
Nov 19, 2012
Messages
15
Thankyou Isskint,

But I still dont retrieve my record regardless of whether an existing business is chosen. Perhaps I need to identify my table first for my business name field?
 

Isskint

Slowly Developing
Local time
Today, 16:38
Joined
Apr 25, 2012
Messages
1,302
How are you providing the list for combo135? Can you post the RowSource SQL?
 

grane236

Registered User.
Local time
Tomorrow, 00:38
Joined
Nov 19, 2012
Messages
15
The Combobox is simply bound to the field Business Name in the table Business Details.
I tried using an unbound combo box but still had no luck.
 

Isskint

Slowly Developing
Local time
Today, 16:38
Joined
Apr 25, 2012
Messages
1,302
Ok, 3 things that come to mind;

  1. The combobx is bound to a table with Business Names in it. Does the combobox return the name of the business or an ID for the business?
  2. Is there a matching field type for Business Name in the record source of your form?
  3. I wonder, did you generate your code via the ComboBox wizard or build it yourself? The 'normal' syntax for a find value in recordset combobox is like this;

Code:
Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Business Name] = '" & Me.Combo135 & "'"
    If Not rs.EOF Then 
           Me.Bookmark = rs.Bookmark
    Else
           msgbox "New Business?"
    End If
 

grane236

Registered User.
Local time
Tomorrow, 00:38
Joined
Nov 19, 2012
Messages
15
HI Isskint,

The combobox returns the name of the business. I do have a matching field type for business name (text field) in my form, but all im doing is copying what was selected in the drop down to this text field.
 

Isskint

Slowly Developing
Local time
Today, 16:38
Joined
Apr 25, 2012
Messages
1,302
Can you confirm you have changed the FindFirst line - EG rs.FindFirst "[Business Name] = '" & Me.Combo135 & "'"

The only thing that makes sense (to me) is the combobox is not returning the value you expect. Check what the combobx is returning as a value after update (MsgBox Combobox135).
 

grane236

Registered User.
Local time
Tomorrow, 00:38
Joined
Nov 19, 2012
Messages
15
The combobox retrieves the right text (if I hover over it in VBA it shows me the combobox135= "Roddick Enterprises"for example). I think its finding the business name in the table to compare it to which isnt working. I should mention my form has fields from two tables. Im only trying to return records for the fields from one of those tables.
 

Isskint

Slowly Developing
Local time
Today, 16:38
Joined
Apr 25, 2012
Messages
1,302
Can you post the combobox RowSource? Ideally it would be useful to see a DB with the relevant tables,queries and forms.
 

Users who are viewing this thread

Top Bottom