Need some input on an Event Procedure for a search

[John Key]

I am building my first database with Access and need some advise
please.

Imagine two main data tables, say tblCustomers and tblProduct. Each
Product has a series of 'Features' tickboxes which you want to be able
to crosscheck against what the Customer is looking for.

The Product display form has a command button that starts the
crosscheck query from the visible Product against Customers who would
be interested in it.

What I am unsure about is the best method of selecting the single
record from tblProducts for the query to be checked against.

For instance,

Having set the focus on the particular primary key of the visible
record I am searching against. (Me!idsProductID.SetFocus) and saved
the focussed record primary key as integer (x = Me!idsProductID) so I
can close the visible form (Me.Visible = False)

Is it then better to Dimension rsProduct As New ADODB.Recordset and
use a SQLstmt to select from tblProducts where idsProductID=x in order
to then start the SQL query to filter for the customers (records) who
fall within product criteria (long WHERE, AND sequence)? Or is there a
better way? If indeed the first way works.

The query would then make a temporary table displaying the filtered
customers. The idea would then be that a form would display this
temporary table and would be editable for the user to filter out
further customers based on 'loose' criteria and further commands would
create either a mailing list or direct email list and then append the
Transaction History table so that both Customer and Product mailout
history is visible in subforms.

Not being that familiar with VB and SQL commands I am unsure whether
what I am suggesting is the best method, especially using a single
record from one table as the basis of checking against all records
from another without resorting to a parameter query.

I am trying to avoid the user having to fill in a prompt box, I would
rather they see a record displayed in a form and click on a command
button that would start the search on the visible record.

I hope the above is clear and thanks in advance for your help.

John

 

[John Nurick]

Hi John,

You'll need to explain more clearly what's in your tables and what you
want to achieve for the user. Don't worry too much at this stage about
the details of doing it.

It sounds as if you want to query the Products table for products whose
Features match the requirements of a particular customer - or is it that
you want to query the Customers table for customers whose requirements
match the features of a particular product?

Or do you just want a Products form where you can select various
Features and see the products that offer those features? If so, you
don't need any coding or fancy stuff: just use Access's built-in Filter
By Form functionality.

 

[John Key]

Thanks John for your reply.

The Product table includes fields with price, location, type, general
description, a picture, and a number of tickboxes which describe
specific Features which some Customers would be looking for.

The Customers table includes contact details, a minimum and maximum
price they would be willing to pay, type, location, and the same
tickboxes should they want to specify exact Features.

The Product form displays the product recordset one at a time and has
command buttons to add records, print records, search for records,
close form, and the specific command for a mailout which is the event
I described earlier.

In this instance I am only concerned about finding out which customers
match a single Product record so that I can mail them the details.

I already have written the SQL query to match the various fields, so
no worries there. I am just looking for a method outside of a
parameter query where the single record is brought forward as the
basis for the query.

The technical skill of the users will be very low so the idea is that
the mailout query is made on the single Product record that they are
looking at by pressing the Mailout command button.

I hope the above clarifies what I am trying to achieve.

Many thanks

JK

 

[John Nurick]

It sounds as if all the values for the query criteria are on the
Products form. In that case, a parameter query that gets its data from
the form is usually by far the simplest way to go.

In case you're not familiar with it, the syntax is like this:

Forms![FormName]![ControlName]

So where in an ordinary parameter query you might use something like
this:
[Please enter price:]
you would have something like
Forms![frmProducts]![txtPrice]
to get the value from the textbox.

 

[John Key]

Hi John

Yours is the sensible solution, and one that I will no doubt end up
doing, but I had a brainwave in the middle of the night and realised I
could try it with just needed a slight alteration to the SQL query.
I'll give that a go over the weekend, give up with frustration in the
early hours and then just change it to what you suggested ;-)

Many thanks for your help.

JK

It sounds as if all the values for the query criteria are on the
Products form. In that case, a parameter query that gets its data from
the form is usually by far the simplest way to go.

In case you're not familiar with it, the syntax is like this:

Forms![FormName]![ControlName]

So where in an ordinary parameter query you might use something like
this:
[Please enter price:]
you would have something like
Forms![frmProducts]![txtPrice]
to get the value from the textbox.

Thanks John for your reply.

The Product table includes fields with price, location, type, general
description, a picture, and a number of tickboxes which describe
specific Features which some Customers would be looking for.

The Customers table includes contact details, a minimum and maximum
price they would be willing to pay, type, location, and the same
tickboxes should they want to specify exact Features.

The Product form displays the product recordset one at a time and has
command buttons to add records, print records, search for records,
close form, and the specific command for a mailout which is the event
I described earlier.

In this instance I am only concerned about finding out which customers
match a single Product record so that I can mail them the details.

I already have written the SQL query to match the various fields, so
no worries there. I am just looking for a method outside of a
parameter query where the single record is brought forward as the
basis for the query.

The technical skill of the users will be very low so the idea is that
the mailout query is made on the single Product record that they are
looking at by pressing the Mailout command button.

I hope the above clarifies what I am trying to achieve.

Many thanks

JK