Populate form based on listbox running from SQL

[nunan]

Hi

Could anyone offer some help please?

I have a form FrmReservations. On this form is a listbox AvailListBox running an sql query which finds data from 2 tables TblVehicles and TblReservations based on a date range which is in a subform FrmReservationDates.

The sql for the listbox is:
SELECT Reg, Colour, Model FROM TblVehicles WHERE Reg Not In (SELECT Reg FROM TblReservations WHERE ResStartDate Between [FrmReservationDates].Form![StartDate] And [FrmReservationDates].Form![EndDate] OR ResEndDate Between [FrmReservationDates].Form![StartDate] And [FrmReservationDates].Form![EndDate]);

This all works perfectly thanks to PHV!

On the form I have all the fields from TblVehicles and I want these to populate once I select an item from the listbox AvailListBox. However, I can't work out how to get this data to populate.

Any help is appreciated.

Thanks

 

[Remou]

Is your form bound to tblVehicles? If so, you can use the After Update event of the listbox to find your record:

[tt]Me.Recordset.FindFirst "Reg='" & Me.ListBox & "'"[/tt]

If the form is not bound, you can set the RecordSource programmatically:

[tt]Me.RecordSource = "Select * From tblVehicles Where Reg='" & Me.ListBox & "'"[/tt]

Both examples assume that Reg is in the bound coulumn, if not, you will need to reference the Column property of the listbox.