Form combo box - All or parameter

[Dega]

SELECT tblTourDate.TourDate, tblTours.TourID, tblTourDate.TourID, tblTours.TourName
FROM tblTours LEFT JOIN tblTourDate ON tblTours.TourID = tblTourDate.TourID
WHERE (((tblTours.TourID)=[frmBookings].[TourID]));
_____________________________

This query returns all records without the WHERE statement (criteria) but if I point to tblTours.TourID which is on the frmBookings, I am prompted for a parameter value. This syntax is not correct but I have tried many variations.

I am looking to return all departure dates from tblTourDate. Its a one to many from tblTours -> tblTourDate with TourID being in both tables.

So on the frmBookings the TourID displays the tour name, and the TourDate should show those records (departure dates) accordingly.



The query works but the subform frmBookings is the problem.

Sorry for the explanation but it's really one combo box showing results based upon another combo boxes selection.

Thanks

[ssanfu]

Try this:

SELECT tblTourDate.TourDate, tblTours.TourID, tblTourDate.TourID, tblTours.TourName
FROM tblTours LEFT JOIN tblTourDate ON tblTours.TourID = tblTourDate.TourID
WHERE tblTours.TourID =[forms]![frmBookings].[TourID];

[Dega]

I am still being prompted for a paramater value when running the form which has been a problem all along.

Running the query you provided without the form gives the correct results and a paramater value is expected then.

Don't know why this is not working because I have referenced forms before in query criteria without this problem.

Thanks

[ssanfu]

It is confusing that you have a TourID in table tblTourDate, a TourID in table tblTours and a control named TourID.

Have the TourID as the PK in two tables that are linked results in a 1 to 1 relationship. I would think that the PK from tblTours would be a FK in tblTourDate, resulting in a 1 to many relationship. (One TourName can have many tour dates.)

Then the control name of TourID. Maybe rename it "txtTourID" (txt = text control).

I have found that whenever there is a prompt in a query, it means I have made a spelling error - the query cannot find the control/field/name.

I am still being prompted for a paramater value when running the form which has been a problem all along.

I would suspect a spelling error or a different name for the control.

[Dega]

Closer to the problem!

When I set a tour (Seattle-Las Vegas) on the subform (frmBookings) and then close the form ... then reopen it the correct dates are displayed for choosing. So the problem is when the Main and subform is open changing tours and dates will not work and a paramater value is then asked for. Giving the correct parameter displays the correct dates.

I am not good at VB and suspect I need an event or something ?

Thanks

[ssanfu]

When I set a tour (Seattle-Las Vegas) on the subform (frmBookings) and then close the form ... then reopen it the correct dates are displayed for choosing. So the problem is when the Main and subform is open changing tours and dates will not work and a paramater value is then asked for. Giving the correct parameter displays the correct dates.

I've been trying to understand your structure. You have a main form with a subform. In the main form, you select the tour and the subform should show the dates of the tour?

If that is right, usually the main/sub forms are linked - when you select/change a tour, the sub form automatically re-queries/refreshes. The are in a 1 to many relationship.
But your tables are in a 1 to 1 relationship. So the dates won't change until you close/open the form or you force a re-query using code.

Maybe I misunderstand. Would you tell more about your form/structure?

[Dega]

The main form is customer information only (tblcustomers) and the custID is passed to the subform (tblbookings).

I am starting from scratch and would like to know the following if anyone can point me in the right direction.

Two tables (tbTours) & (tblTourDate)

I have a table that displays info in (tblTours) which is TourID (PK) & TourName.

Another table (tblTourDate) has TourID and TourDate. There are many tour departures assigned to a single TourID.

I would like to have a combo box that displays the TourName (control name) which populates another combo box which displays the many TourDate (control name) for that specific TourID.

I have the TourName form control being populated with a query already ... it's just getting another control name (TourDate ???) populated with the correct dates.
Thanks

[Dega]

SELECT tblTourDates.TourID, tblTourDates.TourDate
FROM tblTourDates
WHERE (((tblTourDates.TourID)=[Form]![frmBookings].[TourID]));

The parameter value error is still being thrown when using the form ...

[Dega]

SELECT tblTourDates.TourID, tblTourDates.TourDate, tblTours.TourID
FROM tblTours INNER JOIN tblTourDates ON tblTours.TourID = tblTourDates.TourID
WHERE (((tblTourDates.TourID)=[Forms]![frmCustomers]![frmBookings].[Form]![combo1]));

This works somewhat but I need a "requery" somewhere. The TourID (combo1) is being recognized by (combo2) but I need to close the form and reopen as I haven't figured out a way to requery while the form is open.

combo1 holds the TourID (query)
combo2 holds the TourDate (query)

[Dega]

Resolved.

Me.combo2.Requery

Used the event 'On Exit' for combo1.