combo box lookup problem

I have a form with some combo boxes used as lookup fields: customer
Name, Customer Number, Address, Phone number. I have imported the
customer information form another data base. The lookup fields are
designed to be able to search for a customer and display all of its
info at the bottom of the form. 90% of the customers work fine. There
are some that won't display thier info. It seems that only the lookup
field for Customer Name is not working all the time, the rest work.
If you type in a customer's name, it will stay in the lookup field and
not display on the bottom of the form, If you type in thier address or
account number, it works. I've tried to delete the customer record and
re-enter it, but I get the same results. Why is this not working with
some and works fine with the rest?

re: combo box lookup problem

linda wrote:
[color=blue]
> I have a form with some combo boxes used as lookup fields: customer
> Name, Customer Number, Address, Phone number. I have imported the
> customer information form another data base. The lookup fields are
> designed to be able to search for a customer and display all of its
> info at the bottom of the form. 90% of the customers work fine. There
> are some that won't display thier info. It seems that only the lookup
> field for Customer Name is not working all the time, the rest work.
> If you type in a customer's name, it will stay in the lookup field and
> not display on the bottom of the form, If you type in thier address or
> account number, it works. I've tried to delete the customer record and
> re-enter it, but I get the same results. Why is this not working with
> some and works fine with the rest?
>[/color]
Since you say it works for 90% of the customers and not for the other
10% then I would suggest you have a relationship line in the query
(wherever that is) that is an inner join, not a Left join.

For example, lets say I had a customer table and a customer type table.
The customer type tells the op if it is a Big account or Small
account. You have a field in Customer call CustomerType. In 90% of the
records, you have a customer type. In the other 10, it is missing.
Since the query is linking the customer to the type, and the type isn't
found, it can't be displayed as it doesn't meet your criteria.

Anyway, I am 90% sure that that is your problem...a query that looks
good but doesn't allow for all records to be displayed. If the query
can be viewed in the query builder, dbl-click on the relationship line
to change the join type.

re: combo box lookup problem

That does make sence, but I don't think that's the problem, I dont'
have a query, I used combo boxes. I looked at the combo's in visual
basic, and I don't see anything wrong with them, combo 32 is the one
that doesn't see all of the customer names, combo 34 has always worked
fine. Is there another query that it may have generated that I'm not
aware of?


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerName] = '" & Me![Combo32] & "'"
Me.Bookmark = rs.Bookmark

Me!Combo32.Value = ""
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Address] = '" & Me![Combo34] & "'"
Me.Bookmark = rs.Bookmark

Me!Combo34.Value = ""
End Sub

re: combo box lookup problem

That does make sence, but I don't think that's the problem, I dont'
have a query, I used combo boxes. I looked at the combo's in visual
basic, and I don't see anything wrong with them, combo 32 is the one
that doesn't see all of the customer names, combo 34 has always worked
fine. Is there another query that it may have generated that I'm not
aware of?


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

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerName] = '" & Me![Combo32] & "'"
Me.Bookmark = rs.Bookmark

Me!Combo32.Value = ""
End Sub

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Address] = '" & Me![Combo34] & "'"
Me.Bookmark = rs.Bookmark

Me!Combo34.Value = ""
End Sub

re: combo box lookup problem

linda wrote:[color=blue]
> That does make sence, but I don't think that's the problem, I dont'
> have a query, I used combo boxes. I looked at the combo's in visual
> basic, and I don't see anything wrong with them, combo 32 is the one
> that doesn't see all of the customer names, combo 34 has always worked
> fine. Is there another query that it may have generated that I'm not
> aware of?[/color]

Open up your form in design mode. In the data tab, copy the
recordsource to the clipboard (if SQL, else open up the query). Now
open up a new query in design mode, don't add tables, click View/SQL
from the menu, and paste the SQL into it.

Now, open up the customer table and get the customer id for your problem
customer. Now see if that customer ID exists in the recordset of the
new query. I doubt it exists.

If it DOES exist, then check for any filters you may have set in the form.
[color=blue]
>
>
> Private Sub Combo32_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[CustomerName] = '" & Me![Combo32] & "'"
> Me.Bookmark = rs.Bookmark
>
> Me!Combo32.Value = ""
> End Sub
>
> Private Sub Combo34_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[Address] = '" & Me![Combo34] & "'"
> Me.Bookmark = rs.Bookmark
>
> Me!Combo34.Value = ""
> End Sub
>[/color]

re: combo box lookup problem

I wild guess:

You say this works 90% of the time.

1st: Check to see if you can get the Customer Name to show in a simple
query. If you can't then maybe the data was entered incorrectly. Are
there leading spaces in the data fields? See if you can get it to work
without a combo field first (query). When it's working in a query,
then get the combo field going again.

2nd: Are there mutliples of a last name? and if so is your form
'allowed' to display multiple records?

See if you can get it to work in a query and then slowly build in the
more complicated features.

Good luck.

Clare Allan

re: combo box lookup problem

Ok, I've tried that, the customer is in there and I don't have any
filters set on the form or query. Now what?

re: combo box lookup problem

is it possible that when you select one combo field Combo34=address
then it filters out customers in Combo 32=Name who don't have an
address that is possible when the content of address is X ?

Also, I would start with a simple query with basic prompt parameters
(and query for the customers that don't show), when that works, create
a blank form and throw a combo box=name on it. Make that work. Then,
put an address combo box on the form and see if you can get it to work
there. BTW, did you confirm that you are able to get the customers to
appear in a simple query? If you can, then build your framework bit by
bit.

Also, is it always the same customers that don't show? There might be
something there.

re: combo box lookup problem

trynittee@lycos.com wrote:
[color=blue]
> is it possible that when you select one combo field Combo34=address
> then it filters out customers in Combo 32=Name who don't have an
> address that is possible when the content of address is X ?
>
> Also, I would start with a simple query with basic prompt parameters
> (and query for the customers that don't show), when that works, create
> a blank form and throw a combo box=name on it. Make that work. Then,
> put an address combo box on the form and see if you can get it to work
> there. BTW, did you confirm that you are able to get the customers to
> appear in a simple query? If you can, then build your framework bit by
> bit.
>
> Also, is it always the same customers that don't show? There might be
> something there.
>[/color]
It's something that, if we could see the form and data in action, I
believe we'd catch the error quickly. Debugging this thru the newsgroup
is difficult.

In a case like this, I usually use debug.print to get the current SQL
string used to find the record. Then I create a query of that sql.
Then see if I can find the record. To debug this she'll need to do it
one step at a time. All of a sudden, Voila, she'll spot the error.

re: combo box lookup problem

Your code will not work if the customer name has an apostrophe in it.
Also check to be sure that the bound column of Combo32 has what you're
looking for in it. You might want to consider using Combo32.Text
rather than a raw reference to Combo32 as part of your search criteria
since [Combo32] will return the value of the bound column, not
necessarily what displays in the control on the form.

HTH,
Bruce