Find Record Combo Box

[puppy39]

I created a unbound combo box using the access wizard in order to filter a form based on what is selected in the combo box. The rowsource of the combobox is SELECT LeadID, CompanyName FROM tbl_Leads_Main ORDER BY CompanyName. I want the combo box to display only the CompanyName. The problem is some records will have the CompanyName field with a null value or with a value of "Confidential". When this happens the code stops at the "If Not rs.EOF then Me.Bookmark = rs.Bookmark. I am sure that it does not know what to do since we have several records where the CompanyName has the same value and does not know which one to pick.

How can I make this search for
SSearch = (LeadID = LeadID and CompanyName = CompanyName)
rs.find = sSearch

This is the code how it is now.

Private Sub Combo581_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[LeadID] = " & Str(Nz(Me![Combo581], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[lameid]

If LeadID is unquiqe in the list, leadid is the bound column and the first column; you should be fine. If you want to display company name and not leadID, simply make the first column width 0.

I don't know if they ever fixed it but earlier versions of Access at least had problems if the first column wasn't the bound column. I've just always done it that way since I had a problem.

 

[puppy39]

That is exactly how I have it but when the user selects the second record with the same company name nothing happens even though the recordID is different.

 

[lameid]

rs.Find "[LeadID] = " & Str(Nz(Me![Combo581], 0))

str is an old basic function...

rs.Find "[LeadID] = " & cstr(Nz(Me![Combo581], 0))

Or

rs.Find "[LeadID] = " & Nz(Me![Combo581], "0")

 

[puppy39]

Thank yor for replying. Using
rs.Find "[LeadID] = " & cstr(Nz(Me![Combo581], 0))
or
rs.Find "[LeadID] = " & Nz(Me![Combo581], "0")

I get the following error message
Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range or are in confilect with one another

 

[puppy39]

I made a few changes but now I get a different error message

Run-time error '2001'
You canceled the previous operation

The code stops at the Me.Bookmark = rs.Bookmark
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[TheAceMan1]

How are ya puppy39 . . .

Instead of:

[blue]   If Not rs.EOF Then Me.Bookmark = rs.Bookmark[/blue]

Use:

[blue]   If Not rs.[purple][b]NoMatch[/b][/purple] Then Me.Bookmark = rs.Bookmark[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

Nice Catch Aceman...

I never noticed that the wizard code is less than ideal.

 

[puppy39]

Thank you both for the help. I changed the rs.EOF to rs.NoMatch and now I get :-(

Run-time erro '438':
Object doesn't support this property or method.

 

[lameid]

Please post the current version of the code...

What is the datatype of LeadID?

How does the row source of Combo58 compare to the recordsource to the form? Is it the same table, are they related, can you have data in the Combo58 data that is not in the form data?

 

[puppy39]

The LeadID is a "int" autonumber primary field. The database is a ADP linking to SQL

ComboBox586

Rowsource ComboBox:SELECT CodeCompanyName, LeadID FROM tbl_Leads_Main ORDER BY CodeCompanyName and the

Rowsource Type: Table/View/StoredProc

The record source of the form is tbl_Leads_Main and I don't think the combobox will have data that is not in the form since it is pulling from the same table.

Once again Thank you!

 

[lameid]

From your row source I can see you didn't make the LeadID the first column. I'm assuming your bound column is 1 as otherwise it won't work. The column property used below has a 0 based index/array so 0 is first where as bound coulmn 1 is first.

rs.Find "[LeadID] = " & Nz(Me![Combo581].column(1), 0)

 

[puppy39]

After selecting a couple of different records I am back to the errors of if I use:

rs.EOF
Run-time error '2001'
You canceled the previous operation

The code stops at the "Me.Bookmark = rs.Bookmark"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

rs.NoMatch

Run-time error '438':
Object doesn't support this property or method.
The code stops at the "If Not rs.Match Then"
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

 

[lameid]

I would change the row source of the combo to:

SELECT LeadID, CodeCompanyName FROM tbl_Leads_Main ORDER BY CodeCompanyName

Set the properties to...

Bound Column: 1
Column Count: 2
Column Widths: 0";1"


Note for column widths you might use a width other than 1 for your company name.

Finally, change back the code to have leadid use the value instead of the column property.

 

[puppy39]

Same problem with the Me.Bookmark = rs.Bookmark

 

[lameid]

Try Changing

Dim rs As Object

To

Dim rs As ADODB.Recordset

I'm hoping for a at least a more meaningful error.

 

[puppy39]

The combo box is working finally! I have been testing and testing for the last several hours. This is my conclusion. The problem I am having is with If Not rs.EOF Then Me.Bookmark = rs.Bookmark or with the ADP/SQL connection. After selecting several records is when I get the:
Run-time error '2001'
You canceled the previous operation

The code stops at the "Me.Bookmark = rs.Bookmark"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

But I wait a few seconds and then it starts working again. It is almost like a timing issue. I wish I can eliminate it entirely but just done know... I guess I am going to have to put up with that. :-( Once again I can't thank you enough for all of the help.

 

[lameid]

How many records are in the tables?

ADP's by default limit to so many records. I want to say 100,000 but I'm not in an environment that has an ADP anymore.... I think you can change this some where under tools and options. Or perhaps the data is just loading asynchronously to the tables.

Once I made one ADP I swore it was the last. I'd rather use an MDB front end to a SQL database and use SQL Management Studio for the object maintenance.

 

[TheAceMan1]

puppy39 . . .

Questions?
[ol][li]If [blue]CompanyName[/blue] is null, do you still want to lookup via [blue]LeadID[/blue]?[/li]
[li]Is [blue]LeadID[/blue] [purple]numeric[/purple] or [purple]text[/purple] in the table?[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]