List Box Problems - Please Help

  • Thread starter Thread starter Anthony Webb
  • Start date Start date
A

Anthony Webb

I have two list boxes on my form and have the following
code on the On Current Event:-

Private Sub Form_Current()
Dim sqlstr1 As String, sqlstr2 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

sqlstr2 = "select [Learning Activity Dataset].learn_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].PROVI_ID,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].lprog_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Lacti_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Laim_ref,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Title_la,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Startdte,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].eenddate,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].termdate,"
sqlstr2 = sqlstr2 & " From [Learning Activity Dataset]"
sqlstr2 = sqlstr2 & " Where [Learning Activity
Dataset].learn_id = '" & Me.Text22 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].provi_id = '" & Me.Text24 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].lprog_id = '" & Me.Text26 & "';"
Me.List18.RowSource = sqlstr2

I believe the problem lies in the first bit where i have
put: Dim sqlstr1 As String, sqlstr2 As String

As if i delete the first part sqlstr1 as string and insert
sqlstr2 as string instead and the delete all of code
relating to the first list box, iot works fine.

It is only when i have the code for both that it does not
work.

I keep getting the following statement:

The Select statement includes a reserved word or an
argument name that is mispelled or missing, or the
punctuation is incorrect.

Any Ideas?

Thanks Everyone

Anthony
 
I would check the sqlstr1 for errors in field names etc. That is what the error
message is suggesting.

Change your code temporarily to stop after the SQL string is built. Then
examine the SQL string to see what is wrong with the 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] & "';"

Debug.Print sqlstr1
Stop

Then go to the debug window and copy the SQL into a query and see what errors
are produced.


Anthony said:
I have two list boxes on my form and have the following
code on the On Current Event:-

Private Sub Form_Current()
Dim sqlstr1 As String, sqlstr2 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

sqlstr2 = "select [Learning Activity Dataset].learn_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].PROVI_ID,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].lprog_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Lacti_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Laim_ref,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Title_la,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Startdte,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].eenddate,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].termdate,"
sqlstr2 = sqlstr2 & " From [Learning Activity Dataset]"
sqlstr2 = sqlstr2 & " Where [Learning Activity
Dataset].learn_id = '" & Me.Text22 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].provi_id = '" & Me.Text24 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].lprog_id = '" & Me.Text26 & "';"
Me.List18.RowSource = sqlstr2

I believe the problem lies in the first bit where i have
put: Dim sqlstr1 As String, sqlstr2 As String

As if i delete the first part sqlstr1 as string and insert
sqlstr2 as string instead and the delete all of code
relating to the first list box, iot works fine.

It is only when i have the code for both that it does not
work.

I keep getting the following statement:

The Select statement includes a reserved word or an
argument name that is mispelled or missing, or the
punctuation is incorrect.

Any Ideas?

Thanks Everyone

Anthony
Click to expand...
 
Put the statement

Debug.Print sqlstr1

directly in front of

Me.List20.RowSource = sqlstr1

Once the code has run, open the Debug window (Ctrl-G), and see what's
printed there. Does it look like valid SQL? If not, you've got an error in
how you're constructing the string. If it looks fine to you, try posting the
string back here so we can take a look at it.

--
Doug Steele, Microsoft Access MVP



Anthony Webb said:
I have two list boxes on my form and have the following
code on the On Current Event:-

Private Sub Form_Current()
Dim sqlstr1 As String, sqlstr2 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

sqlstr2 = "select [Learning Activity Dataset].learn_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].PROVI_ID,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].lprog_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Lacti_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Laim_ref,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Title_la,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Startdte,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].eenddate,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].termdate,"
sqlstr2 = sqlstr2 & " From [Learning Activity Dataset]"
sqlstr2 = sqlstr2 & " Where [Learning Activity
Dataset].learn_id = '" & Me.Text22 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].provi_id = '" & Me.Text24 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].lprog_id = '" & Me.Text26 & "';"
Me.List18.RowSource = sqlstr2

I believe the problem lies in the first bit where i have
put: Dim sqlstr1 As String, sqlstr2 As String

As if i delete the first part sqlstr1 as string and insert
sqlstr2 as string instead and the delete all of code
relating to the first list box, iot works fine.

It is only when i have the code for both that it does not
work.

I keep getting the following statement:

The Select statement includes a reserved word or an
argument name that is mispelled or missing, or the
punctuation is incorrect.

Any Ideas?

Thanks Everyone

Anthony
Click to expand...
 
I'll start again, as i think i'm confusing things and
confusing myself.

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, so i thought
it must be because of my list box. Next i tried adding
ubound 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.

Make anymore sense?

Thanks

Anthony

-----Original Message-----
I have two list boxes on my form and have the following
code on the On Current Event:-

Private Sub Form_Current()
Dim sqlstr1 As String, sqlstr2 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

sqlstr2 = "select [Learning Activity Dataset].learn_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].PROVI_ID,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].lprog_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Lacti_id,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Laim_ref,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Title_la,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].Startdte,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].eenddate,"
sqlstr2 = sqlstr2 & " [Learning Activity
Dataset].termdate,"
sqlstr2 = sqlstr2 & " From [Learning Activity Dataset]"
sqlstr2 = sqlstr2 & " Where [Learning Activity
Dataset].learn_id = '" & Me.Text22 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].provi_id = '" & Me.Text24 & "'"
sqlstr2 = sqlstr2 & " and [Learning Activity
Dataset].lprog_id = '" & Me.Text26 & "';"
Me.List18.RowSource = sqlstr2

I believe the problem lies in the first bit where i have
put: Dim sqlstr1 As String, sqlstr2 As String

As if i delete the first part sqlstr1 as string and insert
sqlstr2 as string instead and the delete all of code
relating to the first list box, iot works fine.

It is only when i have the code for both that it does not
work.

I keep getting the following statement:

The Select statement includes a reserved word or an
argument name that is mispelled or missing, or the
punctuation is incorrect.

Any Ideas?

Thanks Everyone

Anthony
.
Click to expand...
 
Back
Top