Add Another SQL Statement in the On Current Event

[Ajwebb]

I have an SQL Select Statement in the On Current Event of my form, which is the Source for a List box i have on my form. It works fine, but i am trying to add another select statement but am unsure of how.

My statment begins 'Dim sqlstr As String' and has the select statement underneath.

Can anyone tell me the correct way to add more statement please.

Thanks Everyone

Anthony

 

[markgrizzle]

To replace the listbox statement with another one, use the following

'create a string variable to hold the sql statement
dim sql as string

'build the sql select statement
'make sure the listbox bound column is the first field
'in your statement
sql = "Select Field1, Field2 From TableOrQueryName;"

'update the listbox rowsource with the new statement
me.ListboxName.rowsource = sql

end sub

 

[1127]

After You set up the source of the list box (lst.rowsource=sqlstr) You can use the same variable for the next list or combo.

After You set up the source for a listbox You don't need anymore the data saved in the variable because the source of the listbox it's setted and he dont use the variable anymore.

But I don't undestand why you change the source of the same listbox in the OnChange event twice.

 

[Ajwebb]

Thanks for replying guys. I think i confused things so I'll try and explain in a bit more detail.

I have a list box called List20 which has 7 fields within
it.

Originally the following code worked fine:-

Dim sqlstr1 As String

slstr1 = "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

Underneath i have another list box called list18 which i
wanted the results of to be based on list20.

I was unsure where to put the code so at the end of my
first line i added a commar and wrote sqlstr2 as string.

At the bottom of the statement for List20 i started adding
the statement for List18, which is when i started having
all this trouble.

The fields i wanted on List18 where:-

Learn_id, Provi_id, Lprog_id, Title_la and so on.

These fields came from the Table [Learning Activity
Dataset], so i wanted Learn_id, Provi_id and Lprog_id from
the Learning Activity Dataset to be equal to those of
List20.

In list20 the above fields are columns 0,1 and 2 so i
tried saying:- Where [Learning Activity Dataset].Learn_id
= me.list20.column(0) and so on.

This did not work and brought up the error - The Select statement includes a reserved word or an
argument name that is mispelled or missing, or the
punctuation is incorrect, so i thought
it must be because of my list box. Next i tried adding
unbound text boxes which got there information from List20
and tried saying:- Where [Learning Activity
Dataset].Learn_id = me.text22 and so on, but i still had
the error.

I am unsure of how to make the second list box work, so any help will be greatly appreciated.

Thanks

Anthony

 

[markgrizzle]

It looks as though everything you're doing is correct. My guess is it may be a timing issue.

If the first listbox is populated in the form's current event, and the second tries to update immediately after, the code for the second has fired long before the first list's values have been loaded.

Is there anyway to delay the loading of the second listbox? Perhaps you could issue a GotFocus statement to set the form focus to the first listbox, then trying to populate the second?

You might also try using a do loop to test the first listbox for a value before updating the second's contents based on it.
Mark

 

[Ajwebb]

Thanks for replying Mark.

Am unsure how to do a do loop?

Ant.

 

[markgrizzle]

I took another look at your message, and I think I have a better solution. List20 will only have a value after you've selected a row in it. You'll need to use List20's AfterUpdate event to populate list18.