Combo Box problems

[eprman1]

Hi,

I have created a form to input data to a table. The basic form works well. I
use a ComboBox to select data from a different table, (The combobox looks up
all customer IDs (CUSTID) from a second table (EPRCUST). The choice is made
and stored as a foreign key. This works fine.

Now, each CUSTID can have 4 users. These are in the EPRCUST table.
The user info needs to be entered using the form. I would like to have a
ComboBox give a choice of the 4 users for the CUSTID that was previously
selected.

I can't seem to get only the 4 users in the combo box. Is this possible?

 

[Jeff Boyce]

It sounds like this would be a candidate for "Cascading ComboBoxes". Try
Access HELP and www.mvps.org/access for leads to this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[eprman1]

Jeff,

Thanks for the help. I tried it, but it still didn't work. I have to be
missing something really easy.

I write a separate query to test and the query works. I first tried getting
all the CUSTIDs and it worked. I then set the criteria to an actual CUSTID
and it brought only that one. But when I use the

WHERE (((EPRCUST.CUSTID)=[FORMS]![SERVICE RECORDS3]![CUSTID]))

criteria, I get no data returned.

I also put in the after update:
Private Sub CustID_AfterUpdate()
Me.ContactName = Null
Me.ContactName.Requery
Me.ContactName = Me.ContactName.ItemData(0)
End Sub

Any suggestions???

 

[Jeff Boyce]

That approach only works if the form is open.

Can you:

open the form
select a value in the first combo box
use F11 to open the database window
navigate to the queries
open the "second" combo box's query in design view
run the second query

Does it return what you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I tried it, but it still didn't work. I have to be
missing something really easy.

I write a separate query to test and the query works. I first tried
getting
all the CUSTIDs and it worked. I then set the criteria to an actual CUSTID
and it brought only that one. But when I use the

WHERE (((EPRCUST.CUSTID)=[FORMS]![SERVICE RECORDS3]![CUSTID]))

criteria, I get no data returned.

I also put in the after update:
Private Sub CustID_AfterUpdate()
Me.ContactName = Null
Me.ContactName.Requery
Me.ContactName = Me.ContactName.ItemData(0)
End Sub

Any suggestions???

It sounds like this would be a candidate for "Cascading ComboBoxes". Try
Access HELP and www.mvps.org/access for leads to this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[eprman1]

Jeff,

I can do all below, but I get no result. Only the header info.

Everything works fine in simple query mode, but it will not work with the
form. I always get a blank result, or no choices in the second combo box.

The problem seems to be that the criteria statement cannot find the value of
the first combo box.

That approach only works if the form is open.

Can you:

open the form
select a value in the first combo box
use F11 to open the database window
navigate to the queries
open the "second" combo box's query in design view
run the second query

Does it return what you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I tried it, but it still didn't work. I have to be
missing something really easy.

I write a separate query to test and the query works. I first tried
getting
all the CUSTIDs and it worked. I then set the criteria to an actual CUSTID
and it brought only that one. But when I use the

WHERE (((EPRCUST.CUSTID)=[FORMS]![SERVICE RECORDS3]![CUSTID]))

criteria, I get no data returned.

I also put in the after update:
Private Sub CustID_AfterUpdate()
Me.ContactName = Null
Me.ContactName.Requery
Me.ContactName = Me.ContactName.ItemData(0)
End Sub

Any suggestions???

It sounds like this would be a candidate for "Cascading ComboBoxes". Try
Access HELP and www.mvps.org/access for leads to this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a form to input data to a table. The basic form works
well.
I
use a ComboBox to select data from a different table, (The combobox
looks
up
all customer IDs (CUSTID) from a second table (EPRCUST). The choice is
made
and stored as a foreign key. This works fine.

Now, each CUSTID can have 4 users. These are in the EPRCUST table.
The user info needs to be entered using the form. I would like to have
a
ComboBox give a choice of the 4 users for the CUSTID that was
previously
selected.

I can't seem to get only the 4 users in the combo box. Is this
possible?

 

[Joan Wild]

It is important that when you select something on your form, you set the
focus away from that control, or your query won't "see" it. So select
something and hit the tab key, or click in another control on the form; then
try the query.

--
Joan Wild
Microsoft Access MVP

Jeff,

I can do all below, but I get no result. Only the header info.

Everything works fine in simple query mode, but it will not work with the
form. I always get a blank result, or no choices in the second combo box.

The problem seems to be that the criteria statement cannot find the value
of
the first combo box.

That approach only works if the form is open.

Can you:

open the form
select a value in the first combo box
use F11 to open the database window
navigate to the queries
open the "second" combo box's query in design view
run the second query

Does it return what you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I tried it, but it still didn't work. I have to
be
missing something really easy.

I write a separate query to test and the query works. I first tried
getting
all the CUSTIDs and it worked. I then set the criteria to an actual
CUSTID
and it brought only that one. But when I use the

WHERE (((EPRCUST.CUSTID)=[FORMS]![SERVICE RECORDS3]![CUSTID]))

criteria, I get no data returned.

I also put in the after update:
Private Sub CustID_AfterUpdate()
Me.ContactName = Null
Me.ContactName.Requery
Me.ContactName = Me.ContactName.ItemData(0)
End Sub

Any suggestions???

:

It sounds like this would be a candidate for "Cascading ComboBoxes".
Try
Access HELP and www.mvps.org/access for leads to this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a form to input data to a table. The basic form works
well.
I
use a ComboBox to select data from a different table, (The combobox
looks
up
all customer IDs (CUSTID) from a second table (EPRCUST). The choice
is
made
and stored as a foreign key. This works fine.

Now, each CUSTID can have 4 users. These are in the EPRCUST table.
The user info needs to be entered using the form. I would like to
have
a
ComboBox give a choice of the 4 users for the CUSTID that was
previously
selected.

I can't seem to get only the 4 users in the combo box. Is this
possible?

 

[eprman1]

I've been doing that right along, but no luck.

Thanks for the input

It is important that when you select something on your form, you set the
focus away from that control, or your query won't "see" it. So select
something and hit the tab key, or click in another control on the form; then
try the query.

--
Joan Wild
Microsoft Access MVP

Jeff,

I can do all below, but I get no result. Only the header info.

Everything works fine in simple query mode, but it will not work with the
form. I always get a blank result, or no choices in the second combo box.

The problem seems to be that the criteria statement cannot find the value
of
the first combo box.

That approach only works if the form is open.

Can you:
open the form
select a value in the first combo box
use F11 to open the database window
navigate to the queries
open the "second" combo box's query in design view
run the second query

Does it return what you expect?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I tried it, but it still didn't work. I have to
be
missing something really easy.

I write a separate query to test and the query works. I first tried
getting
all the CUSTIDs and it worked. I then set the criteria to an actual
CUSTID
and it brought only that one. But when I use the

WHERE (((EPRCUST.CUSTID)=[FORMS]![SERVICE RECORDS3]![CUSTID]))

criteria, I get no data returned.

I also put in the after update:
Private Sub CustID_AfterUpdate()
Me.ContactName = Null
Me.ContactName.Requery
Me.ContactName = Me.ContactName.ItemData(0)
End Sub

Any suggestions???

:

It sounds like this would be a candidate for "Cascading ComboBoxes".
Try
Access HELP and www.mvps.org/access for leads to this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a form to input data to a table. The basic form works
well.
I
use a ComboBox to select data from a different table, (The combobox
looks
up
all customer IDs (CUSTID) from a second table (EPRCUST). The choice
is
made
and stored as a foreign key. This works fine.

Now, each CUSTID can have 4 users. These are in the EPRCUST table.
The user info needs to be entered using the form. I would like to
have
a
ComboBox give a choice of the 4 users for the CUSTID that was
previously
selected.

I can't seem to get only the 4 users in the combo box. Is this
possible?