Update a query based on user input in a form.

[Guest]

I am trying to export a .txt query file with user specified data. A parameter
doesn't work because I get the error message when exporting that "too few
parameter. Expected 1" message. From reading other posts, I made a form that
will act as input for the query instead of just the parameter. Can someone
help me write the OnClick event that will update query. The query name is
qryInvoiceExport. I need the user to input the invoice number so that the
details of that particular invoice are listed. Also does anyone know if I
will also need to set the value of the parameter in addition to using the
form as input? Any guidance is much appreciated.

 

[Guest]

The easiest way to do this is to have a control on the form to enter the
invoice number. Lets call it txtInvoiceNumber (clever naming, what?). And
let's call the form frmInvLookup.

Then in the query builder, put the reference to the form and control in the
Criteria row of the Invoice Number column:

Forms!frmInvLookup!txtInvoiceNumber

Now all you need in the Click event of the command button is whatever
command you need to run the export. I would also suggest you check to see
that a value is in the control and that it actuall exists in the table before
you do the export and get nothing:

Do While True
If IsNull(Me.txtInvoiceNumber) Then
If MsgBox("No Invoice Number Entered", vbRetryCancel) = vbCancel
Then
DoCmd.Close acForm, Me.Name, acSaveNo
Exit Do
Else
Me.txtInvoiceNumber.SetFocus
End If
ElseIf IsNull(DLookup("[INVOICE_NUMBER]", "tblInvoice", _
"[INVOICE_NUMBER] = '" & Me.txtInvoiceNUmber & "'") Then
If MsgBox("Invoice Number " & Me.txtInvoiceNumber & " Not
Found", _
vbRetryCancel) = vbCancel Then
DoCmd.Close acForm, Me.Name, acSaveNo
Exit Do
Else
Me.txtInvoiceNumber.SetFocus
End If
Else
DoCmd.TransferText........
Exit Do
End If