Run SQL on Another form Listbox

[Mj-Egerton]

Hello all, fairly new to VBA/SQL but i know a little bit

stuck on this problem

Dim Opt1 As String
Dim strSQL7 As String



Opt1 = Option1.Value

If Option1 = True Then

            
            
strSQL7 = "SELECT Booking.BookingID, Booking.TeacherInitials, Booking.RoomAddress, Booking.MinibooksRequired, Booking.Required, Booking.[Required Date]" & _
          "FROM Booking" & _
          "ORDER BY Booking.[TeacherInitials];"


DoCmd.OpenForm "FrmViewBookings"
MsgBox strSQL7
FrmViewBookings.List0.RowSource = strSQL7
FrmViewBookings.List0.Requery
End If
End Sub

now the code is in a small user form that will pop-up allowing the user to select an option(1 out of 5 Radio Buttons) to group/order the data in a listbox on another form, when i run it i get a Run-Time Error '424' Object not found. I have checked the names in the Table.[Field] SQL statements and the are all correct, where am i going wrong?

Im running Access 2007 on windows XP

Thanks

 

[DCrake]

Try

With FrmViewBookings
   .List0.RowSource = strSQL7
   .List0.Requery
End With

 

[Mj-Egerton]

Try

With FrmViewBookings
   .List0.RowSource = strSQL7
   .List0.Requery
End With

Thanks for your reply david, ive tried the code but am still getting the error, it seems to be highlighting the

.List0.RowSource = strSQL7

line, which makes me think its something in the SQL statement but i have no clue what it is

 

[DCrake]

strSQL7 = "SELECT BookingID, teacherInitials, RoomAddress, MinibooksRequired, [Required], [Required Date] " & _
          "FROM Booking " & _
          "ORDER BY [TeacherInitials];"

Try this revised sql. You did not have spaces beofre the FROM and ORDER BY.

Also if only using 1 table youonly need to refer to it in the FROM line. Also words like Required are Access Reserved words. Need to enclose them in [] to signify a field name. Would rename ASAP.

 

[Mj-Egerton]

strSQL7 = "SELECT BookingID, teacherInitials, RoomAddress, MinibooksRequired, [Required], [Required Date] " & _
          "FROM Booking " & _
          "ORDER BY [TeacherInitials];"

Try this revised sql. You did not have spaces beofre the FROM and ORDER BY.

Also if only using 1 table youonly need to refer to it in the FROM line. Also words like Required are Access Reserved words. Need to enclose them in [] to signify a field name. Would rename ASAP.

again thanks for your reply,tried the code & no change, still getting the error

ps it would be hard to change the table name Required because it is a huge part of the project, but its almost done and this is the last bit so i cant imagine it being a big problem

 

[DCrake]

Are you sur your spelling are correct for table and field names? Which line does it error on?

 

[Mj-Egerton]

Are you sur your spelling are correct for table and field names? Which line does it error on?

yes im sure spellings are correct, they were copied/pasted from SQL view in query builder

 .List0.RowSource = strSQL7

That is the line it keeps highlighting

 

[DCrake]

What does strSQL7 say?

Try Debug.print strSQL7

Can you post your db?

 

[Mj-Egerton]

What does strSQL7 say?

Try Debug.print strSQL7

Can you post your db?

i am not sure what you mean by what does it say,it contains the code for the SQL statement do you mean the error message?

"Run-time error 424 object required" is the message returned by debug.print


Regarding the posting of the Database, it is an A-Level project so i would prefer not posting it on the internet, sorry

 

[DCrake]

As You sql is not influenced by your form why don;t you simply create a query using the same syntax and bind the query to the listbox?

 

[Mj-Egerton]

As You sql is not influenced by your form why don;t you simply create a query using the same syntax and bind the query to the listbox?

The listbox is used for other things mainly to display bookings, i simply wanted to give the user the option to sort the records, am i going the wrong way about this?

this also has to be written in VBA as the a-level course requires the program to be written in a high level programming language

Thanks

 

[Mj-Egerton]

hi david, ive just found the soloution to the problem, instead of putting all the radio buttons and code etc in a differnt form, ive put it on the form with the listbox. problem solved. thanks for your help, ive given rep and thanked