Query based on form open

[Guest]

I have a customer database that sales people use to track their customers. On
opening the sales rep can search for the customer by account no, phone
number, contact name, or company name which then opens the main form,
frmCustomers. For security reasons I don't want the sales rep to have access
to the main table only to a query (so they can't copy the customer list). How
do I have the query look up the customer based on the form that is open and
the criteria that is selected? I hope this makes sense.

 

[Allen Browne]

Set the RecordSource of the form, so it uses the criteria you want.

For example, if the form is bound to tblCustomer, and you want to show only
the customers where the SalesRepID field is 99:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
strSql = "SELECT * FROM tblCustomer WHERE SalesRepID = 99;"
Me.RecordSource = strSql
End If

Now as the rep browses through the records, they can see only those assigned
to this rep.

If you are trying to create a flexible search so they can find a customer
quickly, see:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
or:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html