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

 

[KenSheridan via AccessMonster.com]

You can do it more simply than as described in the link you posted, and also
have the search be cumulative, i.e. as you enter a value into each search box
the form will drill down to the records which match. You don't need a
subform for this, just a form based on the query. To illustrate this with a
simple example lets say you have a table Contacts and you want to search on
FirstName and LastName fields:

1. Create a form based on the table and give it a form header or footer as
desired. Save it as frmContacts.

2. In the header or footer add two unbound text boxes and name then
txtFirstName and txtLastname. Label them appropriately. These are the
controls in which you enter the names to search by.

3. In the AfterUpdate event procedures of each of the two unbound text boxes
requery the form with:

Me.Requery

4. Change the RecordSource property of the form to the following query. You
don't need to save it as a query, just entering the SQL statement as the
RecordSource property is enough:

SELECT * FROM Contacts
WHERE (LastName=Forms!frmContacts!txtLastName
OR Forms!frmContacts!txtLastName IS NULL)
AND (FirstName=Forms!frmContacts!txtFirstName
OR Forms!frmContacts!txtFirstName IS NULL);

5. Save the amended form.

When you open the form it will show all records. If you enter a name into
the txtFirstName control in the header or footer it will be requeried to show
only those contacts with that first name. If you then enter a name into the
txtLastName text box it will narrow down the records to the contact(s) with
the first and last name entered. The reverse is also true of course.

The key thing to note is that in the above query's WHERE clause each OR
operation is wrapped in parentheses to force it to evaluate independently of
the AND operations. This in effect makes each search parameter optional by
examining each for a value or for NULL, so values can be entered in as few or
as many as required to search the records. I've just included the two for
the sake of simplicity, but you can have as many as you wish; you just need
to tack on another parenthesised OR expression like the above for each one,
with an AND between each parenthesised expression.

Ken Sheridan
Stafford, England