RE: looking up value in combo box
- From: Ted <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 14 Mar 2006 09:10:29 -0800
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
"SteveS" wrote:
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.)
"Ted" wrote:
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
"SteveS" wrote:
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?
ie:
Private Sub Primary_AfterUpdate()
Me.Primary.Requery
'-----snip--------
There are three SQL statements that are(?) the record sources for three
combo boxes(?), but you didn't provide the respective control names. (Or the
4th control)
It looks like the two tables are related 1-to-1 on field [PrimaryID]. Is
this right?
----
So, if you could provide the structure of the tables (fieldNames & dataTypes)
The control names and type of control (combo ,list ,text box) and a small
sample of the data and the results expected, it would be very helpful.
I'm not trying to be harsh, but without knowing this I can only give you a
general idea of how to try and fix the problems.
Or you could send me the mdb, if the size is under 1 meg after you do a
compact/repair and then Winzip it. There should be the form & the two tables
with some data (20 -30 records each).
I have an Yahoo account - my ID is limbim53 you know how to make a valid
email address ;)
We *can* get this working....
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
"Ted" wrote:
it occurred to me that i might've used some of the code to help with this, so
here's the vba:
Option Compare Database
Option Explicit
Private Sub Primary_AfterUpdate()
Me.Primary.Requery
Me.Title.SetFocus
Me.Title = "Enter title"
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(1)
End Sub
i'm using and here's the SQL queries:
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 #], Protocol.PrimaryID, Protocol.Description FROM
Protocol INNER JOIN [Primary] ON Protocol.PrimaryID = Primary.PrimaryID WHERE
(((Protocol.PrimaryID)=[Forms]![Temporary Look Up]![Primary]) AND
((Protocol.Description)=[Forms]![Temporary Look Up]![Title]));
hth,
-ted
"Ted" wrote:
i am using a2k.
i have a form called 'Temporary Look Up' with four controls on it. the 2nd,
3rd and 4th are inter-related in the sense that when the first is chosen from
the combo box list of availables, it provides a list of available values for
the 2nd one (conditioned on the choice of the 1st). when the 2nd is chosen,
it uniquely determines the value of the third.
whenever i determine the values of the first two, the value available in the
combo box of the 3rd's is blank.
does this speak for some obvious offense i have committed?
-ted
- Follow-Ups:
- Re: looking up value in combo box
- From: SteveS
- Re: looking up value in combo box
- References:
- RE: looking up value in combo box
- From: SteveS
- RE: looking up value in combo box
- From: Ted
- RE: looking up value in combo box
- From: SteveS
- RE: looking up value in combo box
- Prev by Date: Re: Data using DateAdd function not showing table.
- Next by Date: Re: Queries
- Previous by thread: RE: looking up value in combo box
- Next by thread: Re: looking up value in combo box
- Index(es):
Relevant Pages
|
Loading
