when a search form opens a result form I'm being asked about aparameter?

[Morris]

Hi, I'm really stuck and obviously I'm doing something wrong?

I've got a 'search' form - frmJobSearch. After specifing all the
criteria this form invokes a 'result' form - frmJobs.

I use this code to open the result form:


-----------------------------------------------------------
cmdSearch_Click()
(...)
If strWhere = "" Then
DoCmd.OpenForm "frmJobs", acNormal '<================== doesn't
error
DoCmd.Close acForm, Me.Name
Else
'Pre-check - if there's 0 records, don't bother to open the form!
strSQL = qryRSource.SQL
strSQL = Left(strSQL, Len(strSQL) - 3) & " WHERE " & strWhere
Set rstPreCheck = New ADODB.Recordset
rstPreCheck.Open strSQL, CurrentProject.Connection, adOpenStatic
If Not rstPreCheck.BOF Then 'there is at least one record
'Form_Jobs.RecordSource = strSQL
Me.Visible = False
DoCmd.OpenForm "frmJobs", acNormal, , strWhere '<============
errors
DoCmd.Close acForm, Me.Name, acSaveNo
Else
MsgBox "I'm unable to find any jobs based on criteria you
specified, please change your criteria.", vbInformation
End If

rstPreCheck.Close
Set rstPreCheck = Nothing
End If
-----------------------------------------------------------


And if I haven't put any criteria on a search form, everything is
fine. The program gets to this line:

DoCmd.OpenForm "frmJobs", acNormal '<================== doesn't error

and opens the form. But if I type anything in, then this line:

DoCmd.OpenForm "frmJobs", acNormal, , strWhere '<============
errors

errors.

Now I also need to mention that frmJobs has a subform - frmInvoices.
There is a ListBox (lstInvoices) on a Subform which I requery using
main form's (frmJobs) Current event:


--------------------------------------------------------------
Private Sub Form_Current()
If Me.frmInvoices.Form.lstInvoices.RowSource = "" Then
Me.frmInvoices.Form.lstInvoices.RowSource = "SELECT
Invoices.InvoiceNumber, Invoices.InvoiceAmount,
Invoices.InvoiceCurrency, Invoices.InvoiceReceivedOn,
Invoices.InvoicePaidOn, Invoices.CurrencyRate, Invoices.UserName FROM
Invoices WHERE (((Invoices.JobID)=Forms!frmJobs!txtJobID))"
End If
Me.frmInvoices.Form.lstInvoices.Requery

End Sub
---------------------------------------------------------------



So what happens is that it actually doesn't give any error message,
but it asks me for parameter:
"Enter Parameter Value: SELECT Invoices.InvoiceNumber" so I click OK,
then another pop-up:
"Enter Parameter Value: Invoices.UserName FROM Invoices WHERE
(((Invoices.JobID)=Forms!frmJobs!txtJobID));" and after I click OK the
form opens.

Now, I thought that maybe when I invoke "docmd.openform (...)" it
automatically checks for subform's control's rowsource. So I made sure
that originally subform's lstInvoice's rowsource is empty and gets
populated on 'Current' event, when those values are already
populated.. but maybe I'm wrong?

Any help?

thanks, Morris

 

[OldPro]

Hi, I'm really stuck and obviously I'm doing something wrong?

I've got a 'search' form - frmJobSearch. After specifing all the
criteria this form invokes a 'result' form - frmJobs.

I use this code to open the result form:

-----------------------------------------------------------
cmdSearch_Click()
(...)
If strWhere = "" Then
DoCmd.OpenForm "frmJobs", acNormal '<================== doesn't
error
DoCmd.Close acForm, Me.Name
Else
'Pre-check - if there's 0 records, don't bother to open the form!
strSQL = qryRSource.SQL
strSQL = Left(strSQL, Len(strSQL) - 3) & " WHERE " & strWhere
Set rstPreCheck = New ADODB.Recordset
rstPreCheck.Open strSQL, CurrentProject.Connection, adOpenStatic
If Not rstPreCheck.BOF Then 'there is at least one record
'Form_Jobs.RecordSource = strSQL
Me.Visible = False
DoCmd.OpenForm "frmJobs", acNormal, , strWhere '<============
errors
DoCmd.Close acForm, Me.Name, acSaveNo
Else
MsgBox "I'm unable to find any jobs based on criteria you
specified, please change your criteria.", vbInformation
End If

rstPreCheck.Close
Set rstPreCheck = Nothing
End If
-----------------------------------------------------------

And if I haven't put any criteria on a search form, everything is
fine. The program gets to this line:

DoCmd.OpenForm "frmJobs", acNormal '<================== doesn't error

and opens the form. But if I type anything in, then this line:

DoCmd.OpenForm "frmJobs", acNormal, , strWhere '<============
errors

errors.

Now I also need to mention that frmJobs has a subform - frmInvoices.
There is a ListBox (lstInvoices) on a Subform which I requery using
main form's (frmJobs) Current event:

--------------------------------------------------------------
Private Sub Form_Current()
If Me.frmInvoices.Form.lstInvoices.RowSource = "" Then
Me.frmInvoices.Form.lstInvoices.RowSource = "SELECT
Invoices.InvoiceNumber, Invoices.InvoiceAmount,
Invoices.InvoiceCurrency, Invoices.InvoiceReceivedOn,
Invoices.InvoicePaidOn, Invoices.CurrencyRate, Invoices.UserName FROM
Invoices WHERE (((Invoices.JobID)=Forms!frmJobs!txtJobID))"
End If
Me.frmInvoices.Form.lstInvoices.Requery

End Sub
---------------------------------------------------------------

So what happens is that it actually doesn't give any error message,
but it asks me for parameter:
"Enter Parameter Value: SELECT Invoices.InvoiceNumber" so I click OK,
then another pop-up:
"Enter Parameter Value: Invoices.UserName FROM Invoices WHERE
(((Invoices.JobID)=Forms!frmJobs!txtJobID));" and after I click OK the
form opens.

Now, I thought that maybe when I invoke "docmd.openform (...)" it
automatically checks for subform's control's rowsource. So I made sure
that originally subform's lstInvoice's rowsource is empty and gets
populated on 'Current' event, when those values are already
populated.. but maybe I'm wrong?

Any help?

thanks, Morris

Where is the code that fills strWhere? It must not have a valid
filter expression.

Access asks for a "Parameter Value" when the field referenced in the
SQL string doesn't exist in the target table with that name. It is
often a spelling error that causes it.