Can someone PLEASE Help with a combo box???!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've posted several times and gotten some assistance with refreneces to
links, but I can't seem to get the supplied help to make my situation work...!

I'm getting very frustrated and cannnot seem to figure this out...I know
there must be a solution!

The best I can offer to describe my dillemma is this code, based on a link
from "Marsh" who kindly supplied it to me in another post...but like I said,
I can't seem to make the translation...

I have a combo box "cboSponsoring MAJCOM_ID" which is on a form. I want to
use it to "filter" or limit another combo box "cboBase_ID" using an
"AfterUpdate" Event Procedure but it doesnt seem to work...

Where have I gone wrong????

Here's the code best I can give it:

Private Sub cboSponsoringMAJCOM_ID_AfterUpdate()
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, [AF
Bases].Base_Name, MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
" ([AF Bases] INNER JOIN MAJCOM_ID_Table ON [AF
Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) &"
" ORDER BY [AF Bases].Base_Name "
Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
End Sub
 
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Me.cboSponsoring MAJCOM_ID & _
" ORDER BY [AF Bases].Base_Name "

This assumes Base_MAJCOM is a numeric field (and that the bound field in
cboSponsoring MAJCOM_ID is the appropriate numeric field). If it's text, you
need to put quotes around the reference to the combobox:

Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Chr$(34) & _
Me.cboSponsoring MAJCOM_ID & Chr$(34) & _
" ORDER BY [AF Bases].Base_Name "
 
Thanks, Doug, but that still gives me "Expected End of Statement" errors in
several locations (it moves depending on what I try to correct).

In my table, Base_MAJCOM is a TEXT field so I tried your second iteration...

What are the Chr$(34) statements doing? Not sure I understand...

See, part of the problem is this database has faced a little "mission creep"
and was doing just fine the way I had it developed (albeit, not that
efficiently, but it worked).

So, the form the combo boxes are on is linked to a table and the form is
tabbed so there are parent child issues, etc. Not sure that makes any
difference for my purposes, but it sure seems to complicate things for my
understanding.

For what it's worth, I've gotten the qurey to work before, but the criteria
doesn't update when I change the MAJCOM name to limit the Base Name to the
new MAJCOM. That is my ultimate goal and can't seem to make happen.

Thank you very much for your insight, keep it coming!

Chip

Douglas J. Steele said:
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Me.cboSponsoring MAJCOM_ID & _
" ORDER BY [AF Bases].Base_Name "

This assumes Base_MAJCOM is a numeric field (and that the bound field in
cboSponsoring MAJCOM_ID is the appropriate numeric field). If it's text, you
need to put quotes around the reference to the combobox:

Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Chr$(34) & _
Me.cboSponsoring MAJCOM_ID & Chr$(34) & _
" ORDER BY [AF Bases].Base_Name "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



chip said:
I've posted several times and gotten some assistance with refreneces to
links, but I can't seem to get the supplied help to make my situation
work...!

I'm getting very frustrated and cannnot seem to figure this out...I know
there must be a solution!

The best I can offer to describe my dillemma is this code, based on a link
from "Marsh" who kindly supplied it to me in another post...but like I
said,
I can't seem to make the translation...

I have a combo box "cboSponsoring MAJCOM_ID" which is on a form. I want
to
use it to "filter" or limit another combo box "cboBase_ID" using an
"AfterUpdate" Event Procedure but it doesnt seem to work...

Where have I gone wrong????

Here's the code best I can give it:

Private Sub cboSponsoringMAJCOM_ID_AfterUpdate()
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, [AF
Bases].Base_Name, MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
" ([AF Bases] INNER JOIN MAJCOM_ID_Table ON [AF
Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) &"
" ORDER BY [AF Bases].Base_Name "
Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
End Sub
Click to expand...
Click to expand...
 
Also, what are the impacts of the relationships between these two entities
(Base_ID Table) & (MAJCOM_ID_Table) ? How do they affect my results?
Understanding that may help me better understand what I am doing wrong...

Thanks,

Chip

Douglas J. Steele said:
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Me.cboSponsoring MAJCOM_ID & _
" ORDER BY [AF Bases].Base_Name "

This assumes Base_MAJCOM is a numeric field (and that the bound field in
cboSponsoring MAJCOM_ID is the appropriate numeric field). If it's text, you
need to put quotes around the reference to the combobox:

Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Chr$(34) & _
Me.cboSponsoring MAJCOM_ID & Chr$(34) & _
" ORDER BY [AF Bases].Base_Name "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



chip said:
I've posted several times and gotten some assistance with refreneces to
links, but I can't seem to get the supplied help to make my situation
work...!

I'm getting very frustrated and cannnot seem to figure this out...I know
there must be a solution!

The best I can offer to describe my dillemma is this code, based on a link
from "Marsh" who kindly supplied it to me in another post...but like I
said,
I can't seem to make the translation...

I have a combo box "cboSponsoring MAJCOM_ID" which is on a form. I want
to
use it to "filter" or limit another combo box "cboBase_ID" using an
"AfterUpdate" Event Procedure but it doesnt seem to work...

Where have I gone wrong????

Here's the code best I can give it:

Private Sub cboSponsoringMAJCOM_ID_AfterUpdate()
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, [AF
Bases].Base_Name, MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
" ([AF Bases] INNER JOIN MAJCOM_ID_Table ON [AF
Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) &"
" ORDER BY [AF Bases].Base_Name "
Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
End Sub
Click to expand...
Click to expand...
 
Try copy-and-pasting the exact code you're using, in case you made an error
transcribing.

Chr$(34) is an easy way of inserting a double-quote (")

As to your other question
Also, what are the impacts of the relationships between these two
entities
(Base_ID Table) & (MAJCOM_ID_Table) ? How do they affect my results?
Understanding that may help me better understand what I am doing wrong...
Click to expand...

I don't understand what your question is. These are your tables, not mine:
how are they related?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



chip said:
Thanks, Doug, but that still gives me "Expected End of Statement" errors
in
several locations (it moves depending on what I try to correct).

In my table, Base_MAJCOM is a TEXT field so I tried your second
iteration...

What are the Chr$(34) statements doing? Not sure I understand...

See, part of the problem is this database has faced a little "mission
creep"
and was doing just fine the way I had it developed (albeit, not that
efficiently, but it worked).

So, the form the combo boxes are on is linked to a table and the form is
tabbed so there are parent child issues, etc. Not sure that makes any
difference for my purposes, but it sure seems to complicate things for my
understanding.

For what it's worth, I've gotten the qurey to work before, but the
criteria
doesn't update when I change the MAJCOM name to limit the Base Name to the
new MAJCOM. That is my ultimate goal and can't seem to make happen.

Thank you very much for your insight, keep it coming!

Chip

Douglas J. Steele said:
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Me.cboSponsoring MAJCOM_ID & _
" ORDER BY [AF Bases].Base_Name "

This assumes Base_MAJCOM is a numeric field (and that the bound field in
cboSponsoring MAJCOM_ID is the appropriate numeric field). If it's text,
you
need to put quotes around the reference to the combobox:

Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Chr$(34) & _
Me.cboSponsoring MAJCOM_ID & Chr$(34) & _
" ORDER BY [AF Bases].Base_Name "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



chip said:
I've posted several times and gotten some assistance with refreneces to
links, but I can't seem to get the supplied help to make my situation
work...!

I'm getting very frustrated and cannnot seem to figure this out...I
know
there must be a solution!

The best I can offer to describe my dillemma is this code, based on a
link
from "Marsh" who kindly supplied it to me in another post...but like I
said,
I can't seem to make the translation...

I have a combo box "cboSponsoring MAJCOM_ID" which is on a form. I
want
to
use it to "filter" or limit another combo box "cboBase_ID" using an
"AfterUpdate" Event Procedure but it doesnt seem to work...

Where have I gone wrong????

Here's the code best I can give it:

Private Sub cboSponsoringMAJCOM_ID_AfterUpdate()
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, [AF
Bases].Base_Name, MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
" ([AF Bases] INNER JOIN MAJCOM_ID_Table ON [AF
Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) &"
" ORDER BY [AF Bases].Base_Name "
Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
doesn't update when I change the MAJCOM name

Listcount is a way to force MyCbo to requery, when a
simple MyCbo.Requery isn't working. In the after update
event for MAJCOM, put something like this:

n = MyCbo.listcount

Often, MyCbo.Requery is not required at all, because
MyCbo requeries when the form changes, or the Cbo
rowsource changes. .ListCount works for those cases
where MyCbo does not requery because Access optimises
out the requery action

OTOH, if your cbo is not showing any rows at all, it
probably because you haven't got the RowSource right
yet.

(david)

chip said:
Thanks, Doug, but that still gives me "Expected End of Statement" errors in
several locations (it moves depending on what I try to correct).

In my table, Base_MAJCOM is a TEXT field so I tried your second iteration...

What are the Chr$(34) statements doing? Not sure I understand...

See, part of the problem is this database has faced a little "mission creep"
and was doing just fine the way I had it developed (albeit, not that
efficiently, but it worked).

So, the form the combo boxes are on is linked to a table and the form is
tabbed so there are parent child issues, etc. Not sure that makes any
difference for my purposes, but it sure seems to complicate things for my
understanding.

For what it's worth, I've gotten the qurey to work before, but the criteria
doesn't update when I change the MAJCOM name to limit the Base Name to the
new MAJCOM. That is my ultimate goal and can't seem to make happen.

Thank you very much for your insight, keep it coming!

Chip

Douglas J. Steele said:
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Me.cboSponsoring MAJCOM_ID & _
" ORDER BY [AF Bases].Base_Name "

This assumes Base_MAJCOM is a numeric field (and that the bound field in
cboSponsoring MAJCOM_ID is the appropriate numeric field). If it's text, you
need to put quotes around the reference to the combobox:

Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
"[AF Bases].Base_Name, " & _
"MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
"([AF Bases] INNER JOIN MAJCOM_ID_Table " & _
"ON [AF Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) " & _
"WHERE [AF Bases].Base_MAJCOM =" & Chr$(34) & _
Me.cboSponsoring MAJCOM_ID & Chr$(34) & _
" ORDER BY [AF Bases].Base_Name "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



chip said:
I've posted several times and gotten some assistance with refreneces to
links, but I can't seem to get the supplied help to make my situation
work...!

I'm getting very frustrated and cannnot seem to figure this out...I know
there must be a solution!

The best I can offer to describe my dillemma is this code, based on a link
from "Marsh" who kindly supplied it to me in another post...but like I
said,
I can't seem to make the translation...

I have a combo box "cboSponsoring MAJCOM_ID" which is on a form. I want
to
use it to "filter" or limit another combo box "cboBase_ID" using an
"AfterUpdate" Event Procedure but it doesnt seem to work...

Where have I gone wrong????

Here's the code best I can give it:

Private Sub cboSponsoringMAJCOM_ID_AfterUpdate()
Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, [AF
Bases].Base_Name, MAJCOM_ID_Table.SponsoringMAJCOM_ID FROM " & _
" ([AF Bases] INNER JOIN MAJCOM_ID_Table ON [AF
Bases].Base_MAJCOM=MAJCOM_ID_Table.Sponsoring_MAJCOM) &"
" ORDER BY [AF Bases].Base_Name "
Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top