Lookup Combo Box Question

Bachfile

Registered User.
Local time
Today, 12:32
Joined
Jul 4, 2002
Messages
41
I am constructing a database for a small museum. I've got two main forms - one for entering a donor of an artifact collection's name and one for entering detailed information about each artifact in a collection.

The donor form has input for the donor's name, address and phone number and has a subform where many artifacts can be attributed to a single donor.

The object data entry form allows the user to input detailed data about each artifact to enable them to be found and sorted easily later on. When dealing with thousands of artifacts, it becomes really handy to have a combo box that a user can look for the object's unique accession number and automatically find that record.

I tried adding a lookup combo box, but whenever I select the field or any field for that matter to look up a record I get this error:

"Data cannot be retrieved from the source you have selected. You must select a different table or query to continue in the wizard."

What does this error really mean and is there some way I can fix it?
 
The query you use as the rowsource for the combo must be different from the recordsource of the form is how I interpret the error messag.
 
Thanks for the advice Pat. I will definitely look into that. In the meantime I was wondering how you would go about creating a lookup combo box without using the Access Wizard? If you could help me out on that one that would be great!

Thanks,

Bachfile
 
Ok Pat, I got around the stupid wizard and entered everything correctly, but it still doesn't work.

It looks up the accession numbers fine, but when I select one it gives the following error message:

"Data type mismatch in criteria expression"

Here's the code that it is referring to:

Private Sub Combo88_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
-> rs.FindFirst "[AccessionNumber] = " ' " & Me![Combo88] & " ' "
Me.Bookmark = rs.Bookmark

End Sub

The line with the arrow in front of it is the line of code that the program highlights after hitting the debug button of the error message while trying to use the lookup box in form view.


Any help would be much appreciated!

Thanks!
 
If [AccessionNumber] is text, the syntax is:
rs.FindFirst "[AccessionNumber] = '" & Me![Combo88] & "'"

If [AccessionNumber] is numeric, the syntax is:
rs.FindFirst "[AccessionNumber] = " & Me![Combo88]
 
Thanks a million Pat!

Works perfectly now! This will save a lot of time trying to find a record!

Cheers,

Bachfile
 
Similar Wizard Error

I am getting the same error:

"Data cannot be retrieved from the source you have selected. You must select a different table or query to continue in the wizard."

I have a brand new form, just put a combo-box or listbox in it with the wizard on. If I select a query based on one table, I get this error, on another table I don't.

The form has no RecordSource.

I'll keep looking but any thoughts are appreciated!
 
Well,

I found an answer but I don't understand it.

One of the fields of the query was using a criteria of a function in the Main Module. I changed this to the same value but located in a textbox on the same form to be the criteria instead.

With this the listbox wizard is allowing me to move on to completion.

Why is this?
 
ERROR-on one machine but not another

Data cannot be retrieved from the source you have selected. You must select a different table or query to continue in the wizard."

I get this error only on my machine, not on others??? Any ideas
 
Jack,

There is not enough information in your message to help. Can it be assumed that:

1) This concerns the source for a combo box
2) You developed a program on it and migrated it to other PCs
If so, then did the combo box work on your PC prior to migration?
3) Are the other PCs are configured differently than yours?
Say O/S or Office package
4) The nature of the migration
Was it a simple copy and paste or some kind of install?
5) How do you assign the RecordSource, in the combo box properties or in VBA code?
6) Can you run the RecordSource outside of the combo-box?
For example, a query can pattern an SQL call. Some folks use queries and assign them to the combo box in VBA. By running these you can determine if your data source is sound or if it has errors.

Please at least paste in the code where you assign the RecordSource with explanatory descriptions of variables as needed to understand the code.

HTH, Sorrells
 

Users who are viewing this thread

Back
Top Bottom