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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chip - 18 Nov 2005 20:32 GMT

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

Reply to this Message

Douglas J. Steele - 18 Nov 2005 20:51 GMT

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 "

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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
[quoted text clipped - 25 lines]
> Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
> End Sub

Reply to this Message

chip - 18 Nov 2005 21:47 GMT

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

> Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
> "[AF Bases].Base_Name, " & _
[quoted text clipped - 46 lines]
> > Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
> > End Sub

Reply to this Message

Douglas J. Steele - 18 Nov 2005 22:43 GMT

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...

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks, Doug, but that still gives me "Expected End of Statement" errors
> in
[quoted text clipped - 78 lines]
>> > Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
>> > End Sub

Reply to this Message

david@epsomdotcomdotau - 18 Nov 2005 23:40 GMT

> 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)

> Thanks, Doug, but that still gives me "Expected End of Statement" errors in
> several locations (it moves depending on what I try to correct).
[quoted text clipped - 70 lines]
> > > Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
> > > End Sub

Reply to this Message

chip - 18 Nov 2005 21:58 GMT

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

> Me.cboBase_ID.RowSource = "SELECT [AF Bases].Base_ID, " & _
> "[AF Bases].Base_Name, " & _
[quoted text clipped - 46 lines]
> > Me.cboBase_ID = Me.cboBase_ID.ItemData(0)
> > End Sub

Reply to this Message

RobFMS - 20 Nov 2005 03:09 GMT

For what it may be worth:

Tip #16: Data in a combo box control on a continuous form/datasheet
disappears
http://www.fmsinc.com/free/tips.html#tip16foraccess

Rob Mastrostefano

Signature

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

Reply to this Message