Steve, the following is the SQL I’m using in my three cascading queries in
linear order, followed by the relevant VBA:
SELECT DISTINCT Primary.PrimDescription
FROM [Primary]
ORDER BY Primary.PrimDescription;
SELECT Protocol.Description
FROM [Primary] INNER JOIN Protocol ON Primary.PrimaryID = Protocol.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]));
SELECT Protocol.[IRB #]
FROM Protocol INNER JOIN [Primary] ON Protocol.PrimaryID = Primary.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]) AND
((Protocol.Description)=[Forms]![Temporary Look Up]![Title]));
Option Compare Database
Option Explicit
Private Sub Primary_AfterUpdate()
Me.Primary.Requery
Me.Title.SetFocus
Me.Title = "Enter"
Me.Title.Requery
Me.IRB_Number = "Enter"
Me.IRB_Number.Requery
End Sub
Private Sub Title_AfterUpdate()
Me.Title.Requery
Me.IRB_Number.SetFocus
Me.IRB_Number.Requery
Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
End Sub
Private Sub IRB_Number_AfterUpdate()
Me.IRB_Number.Requery
End Sub
I have changed the name of the “Title_†(w/ the underscore) field to just
“Titleâ€.
This ‘seems’ to work. The intent of the
Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
Code immediately above is to enter the value of the first entry in the list
returned from the 3rd query into the control – my understanding is the IRB
number is uniquely defined by the combination of the two controls preceding.
The ‘weird’ thing about the way it works is that sometimes it will enter an
IRB number in the control and other times it won’t even though there are IRB
numbers found in the ‘Protocol’ table for the Primary and Title combination
the user chooses L . When the IRB number is not entered, clicking on the
combo box arrow results in what looks like a blank value.
:
Yes, I'm still watching this thread
Is the name of the second combo box "Title_" or "Title". The after update code
has two underlines; the extra underline makes the code a different subroutine.
This is the way I would set the properties of the combo boxes. (of course, I
can't see your tables or form, so it is mostly a guess):
Combo 1 *****
Name = Primary (I would name it cboPrimary)
Rowsource = SELECT DISTINCT Primary.PrimDescription
FROM [Primary]
ORDER BY Primary.PrimDescription;
AfterUpdate code =
Private Sub Primary_AfterUpdate()
'clear value of combo boxes
Me.Title_ = "Enter"
Me.IRB_Number = "Enter"
'requery to set rowsource
Me.Title_.Requery
Me.IRB_Number.Requery
'move to combo box
Me.Title_.SetFocus
End Sub
Combo 2 ******
Name = Title_ (maybe it should be "Title"?)
Rowsource = SELECT Protocol.Description
FROM [Primary] INNER JOIN Protocol ON Primary.PrimaryID = Protocol.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]));
AfterUpdate code =
Private Sub Title__AfterUpdate()
'clear value of combo box
Me.Title_ = "Enter"
'requery to set rowsource
Me.IRB_Number.Requery
'move to combo box
Me.IRB_Number.SetFocus
'I don't know what this is supposed to do!!!
'Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
End Sub
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Ted wrote:
hey steve,
i'm back and scratching my head 'cause it's been a while since i got it to
work and now it isn't for some reason. so, i'm hoping you're reading these
and will reset the clock so to speak and give you what i know:
the user selects a field called 'Primary' using the SQL
SELECT DISTINCT Primary.PrimDescription
FROM [Primary]
ORDER BY Primary.PrimDescription;
in a combo box where the 'AfterUpdate' VBA is:
Private Sub Primary_AfterUpdate()
Me.Primary.Requery
Me.Title_.SetFocus
Me.Title_ = "Enter"
Me.Title_.Requery
Me.IRB_Number = "Enter"
Me.IRB_Number.Requery
End Sub
once picked, the user selects the 'Title' from the next combobox using SQL
SELECT Protocol.Description
FROM [Primary] INNER JOIN Protocol ON Primary.PrimaryID = Protocol.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]));
where the 'AfterUpdate' VBA is:
Private Sub Title__AfterUpdate()
Me.Title_.Requery
Me.IRB_Number.SetFocus
Me.IRB_Number.Requery
Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
End Sub
which 'should' populate the last combobox, IRB_Number with the uniqeu value
of the IRB_Number.
only it doesn't!!?? it just gives you a blank value when you click on the
down arrow.
any thoughts?
-ted
:
Glad you got it working...
I'll hang around for a while <g>.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
steve,
many thanks for the good advice....i had occasion to re-examine the query
behind one of the controls and found the recursive occlusion responsible for
the failure to resolve the IRB number controls value. it's a good thing you
veni, vidi velcroed
stick around,
-ted
:
Ted,
It sounds like you are describing what is (commonly) called "cascading combo
boxes".
Unfortunately, you didn't use a naming convention and my crystal ball is in
for repairs
, so it very difficult to determine just what is what.
This is what I did figure out:
There is at least four controls: 3 that are combo boxes, 1 that is ??
There is a control named "Primary"
There is a control named "Title"
There is a control named "IRB_Number" (which might be a listbox)
There is a table named: Primary
with fields: PrimaryID, long, PK
PrimDescription, text
and a table named: Protocol
with fields: PrimaryID, long (PK or FK)
Description, text
IRB #, ???
The two afterupdate subs don't make sense; why are you requering the same
control right after you changed it?
