Help with "Search Form" please...

[Ron S]

Hello everyone,

I am trying to finish an application and I have been strugling with the last two forms.

What I am trying to do is create a two search forms with unbound text boxes and a subform for the results.

I have looked all over the place for examples and I have not had any luck finding any that match what I need. The examples that I have found, I have played with and nothing seems to work.

I am using Access 2003 (Format 2000)

Tables:
tblFCR: ID, Date, Last Name, First Name, Middle, DOB, Case #, Race, Height, Weight, Hair, Marks, Reason, Location, Cooperative, Comments, AKA, Gang Affiliation, and Status.

tblCaseNumber: ID, Date, Case #, Officer, Officer #, Incident, and Description.

Any help would be appreciated.

Ron

[MMcCarthy]

Hello everyone,

I am trying to finish an application and I have been strugling with the last two forms.

What I am trying to do is create a two search forms with unbound text boxes and a subform for the results.

I have looked all over the place for examples and I have not had any luck finding any that match what I need. The examples that I have found, I have played with and nothing seems to work.

I am using Access 2003 (Format 2000)

Tables:
tblFCR: ID, Date, Last Name, First Name, Middle, DOB, Case #, Race, Height, Weight, Hair, Marks, Reason, Location, Cooperative, Comments, AKA, Gang Affiliation, and Status.

tblCaseNumber: ID, Date, Case #, Officer, Officer #, Incident, and Description.

Any help would be appreciated.

Ron

You need to apply a filter presumably behind the click event of a button (cmdSearch)

Private Sub cmdSearch_Click()
 
   Me.SubformObjectName.Form.Filter = "[FieldName]=" & Me.textBoxName
   Me.SubformObjectName.Form.Requery
 
End Sub
 

Note: SubformObjectName is not always the name of the subform itself. To find the name click on the frame around the subform in main form view and open the properties. Under the Other tab check the value in the Name property.

Mary

[NeoPa]

Subforms are always difficult to deal with.
Do you need subforms for your situation?
Perhaps a form with two unbound ComboBoxes would work for you.
The principle would be the same but referencing controls simpler.
What are the two items you would like to search on?
Last question, what field(s) do your two tables link on?

[Ron S]

Subforms are always difficult to deal with.
Do you need subforms for your situation?
Perhaps a form with two unbound ComboBoxes would work for you.
The principle would be the same but referencing controls simpler.
What are the two items you would like to search on?
Last question, what field(s) do your two tables link on?

This is what I have had to do thus far..

To make things easier for the end user, I have created a tabbed form. The first two tabs are for data entry and each have two subforms, the first is for data entry and the second is for viewing/selecting the records.

The last two tab's contain the text boxes and another subform for the results.

I would attach this application for you to look at but I was forced to import all of our past data from our old db (Paradox 3.5) that was starting to crash.

Below is a pic of the search form that I am working with.

Thankyou again for your help

[NeoPa]

Ron,

I can't help in any detailed way as I don't know how the tables are linked.
For the concept though, you need to determine in your code what fields the operator wants to select on (see which have data entered) then build up a WHERE clause in your SQL string which reflects them all in the format

WHERE (([YourDate=#11/25/2006#) AND (Next test) AND (Last test))

If you want the SQL for the earlier part (SELECT... FROM... ) then an easy way to create this is to design a query in the design grid then switch to SQL view and copy it across to your code.