Dynamic RowSource Problem

[Welsh Punk]

Hi clever ones - havent dipped into access for a while and I have a problem which is probably easy to fix but my head is not straight today lol!

I have four combo boxes that the user has to select (select a date a time a sitting position(window or aisle W or A) and a forwards or backwards facing (F or B). On the onchange event of all the combos I am assigning their seltex to public variables which I want to use in building some sql. I have a button click event then that builds the sql and then needs to assign it to another combo called cboSeats which displays available seating numbers. When I assign the cboSeats rowsource to the sql it does not display anything. I have copied the sql into a query and it brings back rows. Maybe I am not assigning it correctly. Any advice please

Code:::

Private Sub cmdMakeChoice_Click()
On Error GoTo Err_cmdMakeChoice_Click
Dim strFree As String
Dim strSQL As String
strDate = Format(strDate, "dd/mm/yyyy")
strFree = "Free"
strSQL = "SELECT tblSeats.Seat FROM tblJourney INNER JOIN tblSeats ON tblJourney.JourneyID = tblSeats.Journey WHERE tblJourney.Date = '" & strDate & "'"
strSQL = strSQL & " AND tblJourney.Time = '" & strTime & "'"
strSQL = strSQL & " AND tblSeats.Position = '" & strWindowAisle & "'"
strSQL = strSQL & " AND tblSeats.Facing = '" & strFacing & "'"
strSQL = strSQL & " AND tblSeats.[Booking Ref] ='" & strFree & "'"
cboSeats.RowSource = strSQL
cboSeats.Visible = True
'MsgBox (cboSeats.RowSource)
Exit_cmdMakeChoice_Click:
Exit Sub
Err_cmdMakeChoice_Click:
MsgBox Err.Description
Resume Exit_cmdMakeChoice_Click

End Sub

Thanks in advance

 

[WIS]

Code:::
strDate = Format(strDate, "dd/mm/yyyy")
strFree

Use "dd/mmm/yyyy". dd/mm/yyyy can result in the American date format, believe it or not. eg 2/5/09 2 May can show as 5 Feb.

 

[wiklendt]

how does this look if you toggle the query view to design? does it make sense to you, or can you see where the issue is?

 

[JANR]

When I assign the cboSeats rowsource to the sql it does not display anything.

Looks like a Requery of the combobox is missing from your code. Insert it after you assign strSQL to the rowsource.

cboSeats.RowSource = strSQL
Me!cboSeats.Requery
'cboSeats.Visible = True <----- I don't think you need this line

JR

 

[Welsh Punk]

Tried the requery and the same....I am making the cboSeats visible on the click event as it is hidden until the user clicks on the button.

I put a break point in the code and using the immediate widow printed out the strSQL which is this

SELECT tblSeats.Seat FROM tblJourney INNER JOIN tblSeats ON tblJourney.JourneyID = tblSeats.Journey WHERE tblJourney.Date = '03/07/2009' AND tblJourney.Time = '11:30:00' AND tblSeats.Position = 'W' AND tblSeats.Facing = 'F' AND tblSeats.[Booking Ref] ='Free'

and used this in a new query and it brings back 32 seats. There is no date problem as I have changed the dates in the tblJourney table to strings.

Must be something silly lol!!!

 

[Welsh Punk]

Okay I have put two msgboxes to count the records in the cboSeats. Before the button is clicked there are 768 seats. When the dyanmic sql runs and the combo is requeried the message box outputs that there are no records. This is maddening - as when I copy the sql from the immediate window and use it in a query it brings back records - but not in the code. Any ideas clever ones - this is driving me mad......

 

[Welsh Punk]

Okay I recreated the combo box from scratch. The message boxes now show 768 records b4 the click and 32 after BUT it is not showing the 32 - its just an empty combo