question boxes

[awsmitty]

I am trying to build a form that will search a table. This is based on the
following link,
http://en.allexperts.com/q/Using-MS-Access-1440/Access-Search-Form.htm. I
have a form that contains a subform. That subform is based on a query w2nich
is based on a table that I'm trying to search. When I double click the form,
or try to open it, instead on opening, a series of question boxes comes up.
These question boxes are the fields that I'm trying to search. If I put in
the data, the right record is found, but what I wanted was for the form to
open, then put in the data, click on a command button and the record be
found, or all records that fit the search.

Something tells me the problem is in the query its self, but I don't know
what.

Thanks,
awsmitty

 

[John W. Vinson]

I am trying to build a form that will search a table. This is based on the
following link,
http://en.allexperts.com/q/Using-MS-Access-1440/Access-Search-Form.htm. I
have a form that contains a subform. That subform is based on a query w2nich
is based on a table that I'm trying to search. When I double click the form,
or try to open it, instead on opening, a series of question boxes comes up.
These question boxes are the fields that I'm trying to search. If I put in
the data, the right record is found, but what I wanted was for the form to
open, then put in the data, click on a command button and the record be
found, or all records that fit the search.

Something tells me the problem is in the query its self, but I don't know
what.

Thanks,
awsmitty

Please post your actual code, the SQL of the query, and the name of your form
and subform.

 

[Mrs. Ugh]

It sounds like the subform is not properly linked to the query. It is asking
you what the fields are because it does not know what field is associated
with them. However, if you are trying to search multiple fields at the same
time, the example will not work anyway...Here is a quick outline of how I do
it:
1. Build a form (based on a query, say called SearchQuery) that displays all
the info you want and shows all records. Let's call it DetailForm.
2. Build a form that is not based on any table or query call it SearchForm.
Add unbound text boxes to represent each of the fields you want to search on
(i.e. StartDate and EndDate). You can also add Option Groups or Combo Boxes.
I recommend having Combo Boxes bound to a query based on the table you are
searching (i.e. if you want to find a particular last name, create a query of
all of the last names in your table - without the duplicates.) I name the
fields SearchLastName, SearchStartDate, SearchEndDate.
3. Next, I create the search criteria using code. Add a button to SearchForm
that opens DetailForm. Call the button RunSearch. I use the wizard to create
the button, then modify the code myself. In our example, it would look
something like this:

Private Sub RunSearch_Click()
On Error GoTo Err_RunSearch_Click
Dim stDocName As String
Dim stWhereCond As String
Dim stLastName As String
Dim stMsgText As String

stDocName = "DetailForm"
If (Nz(Me![SearchLastName],0) <> 0 then
stLastName = Me![SearchLastName]
stWhereCond = "(LastName = " & stLastName & ")"

'if start date add start date to where condition

If (Nz(Me![SearchStartDate], 0) <> 0) Then
stWhereCond = stWhereCond & " AND (DateDue >= #" &
Me![SearchStartDate] & "#)"
End If

'if end date entered, add it to the where condition
If (Nz(Me![SearchEndDate], 0) <> 0) Then
stWhereCond = stWhereCond & " AND (DateDue <= #" &
Me![SearchEndDate] & "#)"
End If

DoCmd.OpenForm stDocName, acNormal, , stWhereCond

DoCmd.Close acForm, "SearchForm"

Exit_RunSearch_Click:
Exit Sub

Err_RunSearch_Click:
MsgBox Err.Description
Resume Exit_RunSearch_Click

End Sub

I had code to ensure you had a name to search on; you need to add code to
check if stWhereCond is "" and if it is not adding the "AND" condition.
Hopefully this isn't too confusing...

Instead of the code, you can enter the cirteria into your query as in the
example you had, but I find getting all of the possible combinations or
entries and non-entries to be confusing.
Jill