Form based parameter query

Question

I have created an unbound form (frmInvParameters) with controls txtDepart_FROM, txtDepart_TO (with Data Type of Short Date) and txtWhich_Venue and a button to run the query qryInvs.

In qryInvs I have put Between [Forms]![frmInvParameters]![txtDepart_FROM] And [Forms]![frmInvParameters]![txtDepart_TO] in criteria of DepartDate field and Like "*" & [Forms]![frmInvParameters]![txtWhich_Venue] & "*" in the Venue field's criteria. Show check boxes have a tick in each.

But on entering control values on the form and running the query I am being asked to "Enter Parameter Value" for txtDepart_From, txtDepart_TO and txtWhich_Venue.  If I simply click OK on these dialog boxes without entering anything the query runs and correct results are produced.  If I enter values in these boxes same as or different to those on the frmInvParameters they are ignored and query produces results per the settings in the form.

 Is there some way to stop those dialogs appearing as they are not necessary?

Answer 1

A form's Dirty property only becomes True when the value of data in a control bound to a column in the table on which the form is based changes.  Consequently, this will not be the case with an unbound control, as you have here.  A common place to drop down a combo box's list is in its GotFocus event procedure, which is triggered when the user moves focus to the control with the mouse or keyboard.

In some situations you might want to drop down a combo box when another control is updated, or when a control's LostFocus event is trggered.  The code below is an example of the former from my Relationships demo, which you can find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

When the strength in which a drug is prescribed is selected in the cboStrength combo box, the cboDrugForm combo box is requeried to list only the forms (tablet, capsule etc) in which the drug is available in the selected strength, and the combo box is dropped down ready for the user to make a selection.  Note that focus has to be moved to the combo box with the SetFocus method before it can be dropped down:

Private Sub cboStrength_AfterUpdate()

    On Error GoTo Err_Handler

    ' assign selected unit to txtUnit control

    Me.txtUnit = Me.cboStrength.Column(1)

    ' requery cboDrugForm control to show forms

    ' for selected drug/strength/unit

    With Me.cboDrugForm

        .Requery

        .SetFocus

        .Dropdown

    End With

Exit_Here:

    Exit Sub

Err_Handler:

    MsgBox Err.Description & " (" & Err.Number & ") ", vbExclamation, "Error"

    Resume Exit_Here

End Sub

Answer 2

I have set up a combo box and when something is entered in there a matching record shows up.  I am however unable to make the combo box dropdown for the user to select a venue - Record Source property of the control is blank and in OnDirty event code I have put me.cboWhichVenue.Dropdown but this does not work.  

Combo boxes on other forms with similar format setting and OnDIrty code do dropdown showing a list of matching records.  All these have a control source so may be not having a control source is preventing the dropdown.  Is there any other way to make the combo box dropdown?

Answer 3

This is what I meant by fully qualifying your parameters

PARAMETERS [Forms]![frmInvParameters]![txtDepart_FROM:] DateTime, [Forms]![frmInvParameters]![txtDepart_TO:] DateTime, [txtWhich_Venue] Text ( 255 );

SELECT DISTINCTROW Bookings.VenueID, Bookings.ArrivalDate, CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0))) AS DepartDate, Venues.VenueCode, Bookings.ReservataionID, Bookings.VenueRef, Bookings.Nights, Bookings.CustomerID, Bookings.Guest, Venues.VenueName, [TAXrate].[stdTAXrate],0) AS TAXr, Bookings.DateCancelled

FROM TAXrate, Venues INNER JOIN Bookings ON Venues.VenueID = Bookings.VenueID

WHERE (((CVDate(DateAdd("d",Nz([Nights],0),Nz([ArrivalDate],0)))) Between [Forms]![frmInvParameters]![txtDepart_FROM] And [Forms]![frmInvParameters]![txtDepart_TO]) AND ((Venues.VenueCode) Like "*" & [Forms]![frmInvParameters]![txtWhich_Venue] & "*") AND ((Bookings.DateCancelled) Is Null)

ORDER BY Bookings.VenueID, Bookings.ArrivalDate, Bookings.ReservationID;

Answer 4

Thank you for suggesting using a combo box, this looks to be a much better way and I will give it a try.

I have however figured out why those dialog boxes were popping up when running the query.  I had put txtDepart_FROM, txtDepart_TO and txtWhich_Venue in Parameters section of query to define their data type.  But since these controls are not in the query dialog boxes came up asking for a value.  Having removed them from Parameters I no longer get them and query runs as it should.

My apologies to you and other contributors for wasting your time.