List Box

[Ant. W]

Is it possible to base a list boxes results on another
list box?

The reason is I have tried to do this and failed miserably.

On my form i have a list with the following Select
Statement on the On Current Event of my form:-

Dim sqlstr1 As String

sqlstr1 = "SELECT [Learning Programme Dataset].LEARN_ID, "
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].PROVI_ID,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].lprog_id,"
sqlstr1 = sqlstr1 & " [Learning Programme Dataset].
[TYPE_LPG],"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_LPROGSTART,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_dueend,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_actualenddate"
sqlstr1 = sqlstr1 & " FROM [Learning Programme Dataset]"
sqlstr1 = sqlstr1 & " WHERE [Learning Programme
Dataset].LEARN_ID = '" & Me.[LEARN_ID] & "'"
sqlstr1 = sqlstr1 & " AND [Learning Programme
Dataset].Provi_id = '" & Me.[PROVI_ID] & "';"
Me.List20.RowSource = sqlstr1

This works fine.

What i am confused on is how i would base my the new list
box on the first one or how i would start the next
statement.

The fields i would like are:-

Learn_id
Provi_id
Lprog_id
Title_la

and they come from a table called [Learning Activity
Dataset].

What i would like to say Select the above fields from
Learning Activity Dataset where Learn_id = me.list20.column
(0), but am unsure how to start after the first list box
statement has finished.

Also i beleive i may have a timing issue as they are both
trying to run at the same time.

Can anyone please help me.

Thanks Everyone

Anthony

 

[Matt Weyland]

Try storing some value from the list box as a function in
a module.

Create a module. Declare a public function called getID
or something like that.

Public strID as string

Public Function GetID() as string
GetID = strID
End Function

You have to do it this way because any Queries stored
cannot read module variables only functions.

Once you have this created, create a new query and in the
column for Learn_ID in the criteria row right click then
go to build. On the lower left side you should see a
listing of different objects with in the DB. Click on
Functions then in the second pane you should see the
listing of all of the modules for the project. Find your
new module, then in the third pane find the function and
you are there.

On the click event store the Learn_ID with the following
code

module1.strid = combobox.colum(x) 'fill in the x with the
applicable column number. Columns are numbered left to
right starting at 0, and also includes not shown columns.
me.secondcombobox.requery
me.refresh

This should work
If you have any questions just let me know.

Matt Weyland
Data Analyst
StratisHealth
(e-mail address removed)

-----Original Message-----
Is it possible to base a list boxes results on another
list box?

The reason is I have tried to do this and failed miserably.

On my form i have a list with the following Select
Statement on the On Current Event of my form:-

Dim sqlstr1 As String

sqlstr1 = "SELECT [Learning Programme Dataset].LEARN_ID, "
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].PROVI_ID,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].lprog_id,"
sqlstr1 = sqlstr1 & " [Learning Programme Dataset].
[TYPE_LPG],"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_LPROGSTART,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_dueend,"
sqlstr1 = sqlstr1 & " [Learning Programme
Dataset].isf_actualenddate"
sqlstr1 = sqlstr1 & " FROM [Learning Programme Dataset]"
sqlstr1 = sqlstr1 & " WHERE [Learning Programme
Dataset].LEARN_ID = '" & Me.[LEARN_ID] & "'"
sqlstr1 = sqlstr1 & " AND [Learning Programme
Dataset].Provi_id = '" & Me.[PROVI_ID] & "';"
Me.List20.RowSource = sqlstr1

This works fine.

What i am confused on is how i would base my the new list
box on the first one or how i would start the next
statement.

The fields i would like are:-

Learn_id
Provi_id
Lprog_id
Title_la

and they come from a table called [Learning Activity
Dataset].

What i would like to say Select the above fields from
Learning Activity Dataset where Learn_id = me.list20.column
(0), but am unsure how to start after the first list box
statement has finished.

Also i beleive i may have a timing issue as they are both
trying to run at the same time.

Can anyone please help me.

Thanks Everyone

Anthony

.