How can I link a query with a form 2

[ajaeger]

I want to allow users to enter values for several fields in a form and then use those values in a query to return data. For example, my table is "Customers" and I have fields for Id, FirstName, LastName, Company, Department, Address1, Address2, City, State, Zip, Phone, Fax, Email, DateAdded etc...

I'd like to create a form that allows users to enter a value for LastName, and Department. Then, they would click a button and the query would pull data based on the 2 values that they entered.

How do I create something like this.

Thanks.

 

[Orion45]

It sounds to me like you want to create a search feature. The code should look something like this:

Dim rsGetAddress As DAO.Recordset
Dim qdftemp As QueryDef
Dim strsql as string
Dim strLast as string
Dim strDept as string
Dim dbs as Database

Set dbs = CurrentDb
strLast = me.txtLastName
strDept = me.txtDepartment
strSQL = "SELECT Customers.Address1, Customers.City FROM Customers WHERE (((Customers.LastName)='" & strLast & "') AND Department='"& strDept &"');"
Set qdftemp = dbs.CreateQueryDef("", strSQL)
qdftemp.ReturnsRecords = True
Set rsGetAddress = qdftemp.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Set Forms("frmUserData").Recordset = rsGetAddress
docmd.requery
rsGetAddress.close
set rsGetAddress = Nothing

When you do this you need to make sure that the field names and control sources on your form are named exactly the same as the fields in the table.

 

[dalebeitz]

If I understand correctly, you've got another form that's actually allowing insertion and editing of your customer data. You either want to enhance this form to support lookups, or create an entirely new form that does lookups.

Let's assume you want to enhance your entry form. The following technique sets the filter property of your form so it only displays those records that match the search criteria.

Put a command button on yor entry form that opens a pop-up form. Create the pop-up form with the fields you might want to search on (Last Name, Company, Deptartment, etc.). Also add a command button to the pop-up that executes the search. In the search button's on-click event, add code like the following:

Note: I use <> brackets to denote items you need to replace.

Dim mySQL As String

' check if first field was supplied
If Nz(1stFld) <> &quot;&quot; Then
mySQL = &quot;<1stFld'sNameInTable> = &quot; & 1stFld
End If

' check if second field was supplied
If Nz(2ndFld) <> &quot;&quot; Then
' check if partial SQL exists, if so, concatenate onto it.
If mySQL <> &quot;&quot; Then
mySQL = mySQL & &quot; AND &quot;
End If
mySQL = mySQL & &quot;<2ndFld'sNameInTable> = &quot; & 2ndFld
End If

If mySQL = &quot;&quot; Then
[Forms]![<customerformname>].FilterOn = False
Else
[Forms]![<CustomerFormName>].Filter = mySQL
[Forms]![<CustomerFormName>].FilterOn = True
End If
DoCmd.Close acForm, &quot;<SearchFormName>&quot;

For each additional search criterion, add an additonal IF NZ block.

Back on your entry form, also add a button to clear the search. In it's on-click event, set the filter value to &quot;&quot;, and set FilterOn to False.

Hope this helps!
Dale Beitz
dale.beitz@agreliantgenetics.com

 

[ajaeger]

Dale-

Thank you so much for your instructions.

You said to &quot;create the pop-up form with the fields you might want to search on (Last Name, Company, Department, etc.)&quot;. I created the form linked to the Customer table and pulled in those fields. Now when I open my pop-up form, I see data already populating the fields. How do I get the fields to link but be empty? Or am I creating the form improperly for the search I need to run?

Thanks.

 

[dalebeitz]

Sorry for not replying sooner, I've been working on other projects for the last month and haven't kept up with the forum.
To answer your question, the fields on the pop-up search criteria form should be unbound fields. They're not being used for data entry, they're just to specify your search criteria. Label them using the same labels that are on your main form so your users know what they are. You could create your search form in design view without using the wizard and drag text boxes onto the form, or if you've already got the search form layed out just go into the properties for each field and clear the CONTROL SOURCE property.
To clarify the code, wherever you see an NZ(1stfld), the name 1stfld refers to the names of the unbound text fields on your search form. Wherever you see <1stfieldnameintable>, that's the actual name of the column in the table which the unbound field represents.
Hope this isn't too late to help!
Dale