ComboBox lookup

[QueryStumped]

Frustrated.. I have a form with a combo box lookup, pulling from a table names. It not bringing up last names that have a ‘ such as O’Daniel. I am not a programmer but know how to work behind the scenes. Please advise. Can I put something in the properties of the combobox?

 

[The_Doc_Man]

My first thought is not that it is failing to look up the value but rather that because of that apostrophe, it might be unable to display the value.

We would need to see the query that is the combo box's .RowSource to better assess what is going on. If you have a named query, then don't show us the name, show us the query. If you have literal SQL for the .RowSource, then show us that instead.

 

[Dreamweaver]

I'm with the_doc_man post the SQL

 

[QueryStumped]

My first thought is not that it is failing to look up the value but rather that because of that apostrophe, it might be unable to display the value.

We would need to see the query that is the combo box's .RowSource to better assess what is going on. If you have a named query, then don't show us the name, show us the query. If you have literal SQL for the .RowSource, then show us that instead.

Thanking for responding the only thing in the query is, pulling down the name field from the main table. It shows the O’ last names when I run the query. It shows in the combo box when you want to search by last name, but it won’t bring up the members info down below on the main form, to update phone numbers.

 

[Dreamweaver]

Make sure the column widths are wide enough and the total width for the combo is the sum of all column widths +501

 

[Isaac]

Thanking for responding the only thing in the query is, pulling down the name field from the main table. It shows the O’ last names when I run the query. It shows in the combo box when you want to search by last name, but it won’t bring up the members info down below on the main form, to update phone numbers.

So are you saying that the problem isn't really with "what shows in the combobox is different than I'd expect based on the rowsource", but rather, once something is selected from the combobox, whatever code you have that is supposed to filter the form isn't working as expected?
If that's correct, can you post the combobox's AfterUpdate code (or wherever you have this code to filter the form?)

 

[QueryStumped]

Thanking for responding the only thing in the query is, pulling down the name field from the main table. It shows the O’ last names when I run the query. It shows in the combo box when you want to search by last name, but it won’t bring up the members info down below on the main form, to update phone numbers.

Could it be something in the after update if the properties of the combo box. I see a Where Condition =“[Name]=“&””&[Screen].[ActiveControl]&””

So are you saying that the problem isn't really with "what shows in the combobox is different than I'd expect based on the rowsource", but rather, once something is selected from the combobox, whatever code you have that is supposed to filter the form isn't working as expected?
If that's correct, can you post the combobox's AfterUpdate code (or wherever you have this code to filter the form?)

the AfterUpdate shows Embedded Macro, when I click on that it shows
Where Condition == “[Name]=“&””&[Screen].[ActiveControl]&””

 

[Isaac]

I see. I don't use macros ever, so I'll bow out and maybe someone else can help.

 

[QueryStumped]

My first thought is not that it is failing to look up the value but rather that because of that apostrophe, it might be unable to display the value.

We would need to see the query that is the combo box's .RowSource to better assess what is going on. If you have a named query, then don't show us the name, show us the query. If you have literal SQL for the .RowSource, then show us that instead.

Thanking for responding the only thing in the query is, pulling down the name field from the main table. It shows the O’ last names when I run the query. It shows in the combo box when you want to search by last name, but it won’t bring up the members info down below on the main form, to update phone numbers.

I noticed when I looked at the properties of the combobox, the AfterUpdate shows Embedded Macro, I know I didn’t put that macro in there...when I click on that it shows
Where Condition == “[Name]=“&””&[Screen].[ActiveControl]&””.

 

[The_Doc_Man]

Well, there is a problem of sorts... An .AfterUpdate at least in theory CAN be triggered even if the form is not current, since it is possible to use VBA to update a control. Therefore, relying on [Screen].[ActiveControl] might be questionable. Not saying this actually happened for you. Just pointing out that in Access there are many ways to do a task and some of them work more reliably than others.

 

[June7]

WHERE condition must be SQL compatible. SQL will have issue with special characters like apostrophe and quote mark in data.

I don't use macros nor do I ever use "Screen.ActiveAnything". I use explicit reference.

To handle apostrophe:

"[Name] = """ & strLinkCriteria & """"

If quote mark in data is the issue (perhaps representing inch unit), be sure to use apostrophe delimiter:

"fieldname = '" & strLinkCriteria) & "'"

Or to handle both apostrophe and quote

"fieldname = '" & Replace(strLinkCriteria, "'", "''") & "'"

Or

"fieldname = """ & Replace(strLinkCriteria), Chr(34), Chr(34) & Chr(34)) & """"

Or maybe use TempVar parameters - but I've never used TempVars so not sure about this.

 

[arnelgp]

on your macro:

Condition = [Name] = [Forms]![yourFormNameHere]![yourComboNameHere]