Combo boxes

[Bob Matthews]

Hi

If you have more than one combo box in your database where somebody is
entering 2 or 3 characters to initiate a search, is there some sort of
initialization required whilst moving between the combo boxes so that they
always work?

Bob

 

[Al Campagna]

Bob,
Not really sure I understand what you mean.
Give us more details on what the combos do, and some sample
values, and if some combos rely on others combo values...

If some of the combos are dependent on other combo values, for
filtering... (aka cascading combos) you will need to Requery
when combo values are changed.
EX: If cbo2 relies on cbo1 for a filtering value, Requery cbo2 on the
AfterUpdate event of cbo1.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Bob Matthews]

Hi Al and thank you for your interest.............

each of 3 combos work independently - simply accepting input from the
keyboard namely 2 or 3 letters of a surname

then the combo box shows about 16 values based on the input - you choose one
and the form that the combo is on is updated with the chosen record.

all 3 combos were created using the wizard and all 3 appear to be identical
except for field names

however, one combo always works and the other two may begin to work but if
you go back to the main menu where you have 3 choices as to which table you
wish to query, then you soon find that 2 of the 3 will NOT accept input nor
update the record ????

It seems really odd behaviour to me

Bob M

 

[BruceM via AccessMonster.com]

It's still hard to understand. Are these unbound search combo boxes, to go
to a record based on the surname (in an employee database or some such thing)?
If so, why are there three of them? How do the three tables you can select
from the main form relate to the form's Record Source? What is the VBA code
in the After Update event for the combo that works, and for one of the ones
that is inconsistent?

Hi Al and thank you for your interest.............

each of 3 combos work independently - simply accepting input from the
keyboard namely 2 or 3 letters of a surname

then the combo box shows about 16 values based on the input - you choose one
and the form that the combo is on is updated with the chosen record.

all 3 combos were created using the wizard and all 3 appear to be identical
except for field names

however, one combo always works and the other two may begin to work but if
you go back to the main menu where you have 3 choices as to which table you
wish to query, then you soon find that 2 of the 3 will NOT accept input nor
update the record ????

It seems really odd behaviour to me

Bob M

Bob,
Not really sure I understand what you mean.

[quoted text clipped - 14 lines]

 

[Bob Matthews]

Hi Bruce


It's still hard to understand...................

Are these unbound search combo boxes, to go to a record based on the surname
(in an employee database or some such thing)?

Yes - three distinct tables - three different sources of genealogical data

What is the VBA code in the After Update event for the combo that works, and
for one of the ones
that is inconsistent?

This one always works :-

SELECT [Portraits].[ID], Portraits.[Last Name] & ", " &
Portraits.[First/Initials] AS Fullname FROM Portraits ORDER BY [Last Name],
[First/Initials];

These ones do not ALWAYS work :-

SELECT [Debtors].[ID], Debtors.[Surname] & ", " & Debtors.[Christian Names]
AS Fullname FROM Debtors ORDER BY [Surname], [Christian Names];

SELECT [OSAP].[ID], [OSAP].[Full Name] FROM OSAP;

Bob M

 

[BruceM via AccessMonster.com]

That looks as if it could be the Row Source SQL for the combo boxes. Was it
in the Row Source line of the data tab on the Property Sheet? (See the next
paragraph if you need more information about the Property Sheet.) If so, in
the first case you would see a listing of names from the Portraits table (or
query); in the second from the Debtors table; in the third from the OSAP
table.

The question now is what is supposed to happen when you select a name
(assuming you can select a name)? "Does not work" is very general. Click
the combo box, to select it, click View >> Properties, and click the Event
tab of the Property Sheet. Look for After Update. If it says [Event
Procedure], click the three dots, which will open the VBA editor and show you
the After Update code. Copy what you see there for the combo box that works
and the ones that do not.

This reply assumes Access 2003 or earlier. I don't know if the interface is
exactly the same for 2007 and 2010.

Hi Bruce

It's still hard to understand...................

Are these unbound search combo boxes, to go to a record based on the surname
(in an employee database or some such thing)?

Yes - three distinct tables - three different sources of genealogical data

What is the VBA code in the After Update event for the combo that works, and
for one of the ones
that is inconsistent?

This one always works :-

SELECT [Portraits].[ID], Portraits.[Last Name] & ", " &
Portraits.[First/Initials] AS Fullname FROM Portraits ORDER BY [Last Name],
[First/Initials];

These ones do not ALWAYS work :-

SELECT [Debtors].[ID], Debtors.[Surname] & ", " & Debtors.[Christian Names]
AS Fullname FROM Debtors ORDER BY [Surname], [Christian Names];

SELECT [OSAP].[ID], [OSAP].[Full Name] FROM OSAP;

Bob M

 

[Bob Matthews]

Hi Bruce

The question now is what is supposed to happen when you select a name
(assuming you can select a name)?

The form that you are on updates to the new record

"Does not work" is very general.

One can not enter any input

Click

the combo box, to select it, click View >> Properties, and click the Event
tab of the Property Sheet. Look for After Update. If it says [Event
Procedure], click the three dots, which will open the VBA editor and show
you
the After Update code. Copy what you see there for the combo box that
works
and the ones that do not.

all three have identical After Update code

, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0))

Bob M

 

[BruceM via AccessMonster.com]

You posted a fragment of code. If it is all of the code, it will not do
anything. Can you compile the code? I will say from the scrap I can see
that the syntax is probably wrong:
="[ID] = " & Str(Nz(Screen.ActiveControl,0))
should probably be:
"[ID] = " & Str(Nz(Screen.ActiveControl,0))

Further, since you are for some reason converting a number to a string you
should try changing the quotes:

"[ID] = " " " & Str(Nz(Screen.ActiveControl,0)) & " " " "

However, if ID is a number field you shouldn't be converting to a string at
all. Why ActiveControl? If ID is in the combo box, and it is a number field,
just do:

"[ID] = " & Me.ComboBoxName

For text:

"[ID] = " " " & Me.ComboBoxName & " " " "

There seems little point to Nz in the After Update event, as you have just
made a selection, so the value will not be null. If the combo box is null,
is there a record with ID = 0?

I believe the problem in part is that there are three tables, but the form
can be bound to only one table at a time. I will hazard a guess that if you
select a different table, a different combo box will work. At the main form
where you choose which of three tables to query, how does that happen? Is
there any connection between the main form and the one that is giving you
problems?

Hi Bruce

The question now is what is supposed to happen when you select a name
(assuming you can select a name)?

The form that you are on updates to the new record

"Does not work" is very general.

One can not enter any input

Click

the combo box, to select it, click View >> Properties, and click the Event
tab of the Property Sheet. Look for After Update. If it says [Event

[quoted text clipped - 3 lines]

works
and the ones that do not.

all three have identical After Update code

, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0))

Bob M

 

[Bob Matthews]

Hi Bruce

This code was created by the combo box wizard, not me

I believe the problem in part is that there are three tables, but the form
can be bound to only one table at a time.

I have 3 forms, one for each table.

I will hazard a guess that if you

select a different table, a different combo box will work. At the main
form
where you choose which of three tables to query, how does that happen? Is
there any connection between the main form and the one that is giving you
problems?

The Main Menu asks which of the 3 tables you wish to query
On your choice you are presented with one of 3 subforms, each of which has a
combo box for a name search

Bob M

Hi Bruce

The question now is what is supposed to happen when you select a name
(assuming you can select a name)?

The form that you are on updates to the new record

"Does not work" is very general.

One can not enter any input

Click

the combo box, to select it, click View >> Properties, and click the
Event
tab of the Property Sheet. Look for After Update. If it says [Event

[quoted text clipped - 3 lines]

works
and the ones that do not.

all three have identical After Update code

, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0))

Bob M

 

[BruceM via AccessMonster.com]

If that is all of the code, that is at least part of the problem. If it
started with something like DoCmd.OpenForm or maybe some kind of FindFirst
code it would make some sense, but it is meaningless as it is.

Does the code compile?
Is ID a Number field?
How does the main form ask for the table to query?

Hi Bruce

This code was created by the combo box wizard, not me

I believe the problem in part is that there are three tables, but the form
can be bound to only one table at a time.

I have 3 forms, one for each table.

I will hazard a guess that if you

select a different table, a different combo box will work. At the main
form
where you choose which of three tables to query, how does that happen? Is
there any connection between the main form and the one that is giving you
problems?

The Main Menu asks which of the 3 tables you wish to query
On your choice you are presented with one of 3 subforms, each of which has a
combo box for a name search

Bob M

Hi Bruce
[quoted text clipped - 20 lines]

Bob M

 

[Bob Matthews]

Hi Bruce

If that is all of the code, that is at least part of the problem. If it
started with something like DoCmd.OpenForm or maybe some kind of FindFirst
code it would make some sense, but it is meaningless as it is.

Does the code compile?

I do not compile anything ???

Is ID a Number field?

It is an autonumber field (i.e. the key)

How does the main form ask for the table to query?

On the main form you choose which of 3 tables you wish to query
Each choice takes you to a subform
On the subform appears the various fields in each record and a combo box to
search for a name

Bob M

Hi Bruce

This code was created by the combo box wizard, not me

I believe the problem in part is that there are three tables, but the
form
can be bound to only one table at a time.

I have 3 forms, one for each table.

I will hazard a guess that if you

select a different table, a different combo box will work. At the main
form
where you choose which of three tables to query, how does that happen?
Is
there any connection between the main form and the one that is giving
you
problems?

The Main Menu asks which of the 3 tables you wish to query
On your choice you are presented with one of 3 subforms, each of which has
a
combo box for a name search

Bob M

Hi Bruce

[quoted text clipped - 20 lines]

Bob M

 

[BruceM via AccessMonster.com]

Open the VBA editor. At the top of each code module you should see:
Option Compare Database
Option Explicit
If Option Explicit is not there, add it.

To compile, click Debug >> Compile.

So on the main form there is a combo box or command buttons or option group
or something. I was asking about the specifics, but in any case there is
something like a Click event or After Update event that brings you somehow to
a subform, which seems not to be related to the main form, which seems to be
an unbound form that you are using as a switchboard.

Does each subform have its own Record Source as set on the Property Sheet?
If not, how is the Record Source set?

Hi Bruce

If that is all of the code, that is at least part of the problem. If it
started with something like DoCmd.OpenForm or maybe some kind of FindFirst
code it would make some sense, but it is meaningless as it is.

Does the code compile?

I do not compile anything ???

Is ID a Number field?

It is an autonumber field (i.e. the key)

How does the main form ask for the table to query?

On the main form you choose which of 3 tables you wish to query
Each choice takes you to a subform
On the subform appears the various fields in each record and a combo box to
search for a name

Bob M

Hi Bruce
[quoted text clipped - 27 lines]

Bob M

 

[Bob Matthews]

Hi Bruce

I have a 2007 ms Access database
It has three independent tables and each table has a form

The main menu has three buttons, one for each of the tables - you choose
which one you wish to search

On choosing one of the three, you get a form on the screen
Each form has a name search combo box
All three combo boxes were set up by the combo box wizard

One (the portraits one) always works. The other two don't inthat you can not
enter any input into the combo box.

There is no VBA code to compile

The After Update for the combo box is an embedded macro,
namely.........................

, , First, ="[ID] = " & Str(Nz(Screen.ActiveControl,0)) [created by the
wizard]

The Row Source for the combo box is............................

SELECT [Portraits].[ID], Portraits.[Last Name] & ", " &
Portraits.[First/Initials] AS Fullname FROM Portraits ORDER BY [Last Name],
[First/Initials];

Bob