Setting up a query...

[Norm Sanders]

Ok...it beat me...hehehe

I put together a Baseball card db for my father in law. Db itself is working
fine, form looks good, all the controls work fine, adds records, etc.

Now comes the hard part. What I want to do is set up a form where I can
query the entire db for whatever criteria I decide on. In other words, if I
put Cal in the criteria/search field, I want it to pull up every record with
that criteria in it, and update the record count on this form to reflect how
many records CONTAIN that criteria. So if I have say 12 Cal Ripken cards, I
want to be able to open the form, type in Cal, and have it tell me I have 12
records.

As you may have gathered, I am a complete neophyte where Access is
concerned, so please be gentle with me...

 

[MDW]

-----Original Message-----
Ok...it beat me...hehehe

I put together a Baseball card db for my father in law. Db itself is working
fine, form looks good, all the controls work fine, adds records, etc.

Now comes the hard part. What I want to do is set up a form where I can
query the entire db for whatever criteria I decide on. In other words, if I
put Cal in the criteria/search field, I want it to pull up every record with
that criteria in it, and update the record count on this form to reflect how
many records CONTAIN that criteria. So if I have say 12 Cal Ripken cards, I
want to be able to open the form, type in Cal, and have it tell me I have 12
records.

As you may have gathered, I am a complete neophyte where Access is
concerned, so please be gentle with me...

ask.

I assume you don't want to have to type in the SQL itself,
so you'd probably want to have a combo box of some sort
indicating which criteria (i.e., column(s)) you want to
search on. Were it me, here's what I'd do (assume that the
box you type the criteria is called "txtValue" and the
drop down box with the different column names is
called "cboColName":

Private Sub cmdSearchGo_Click()

cboColName.SetFocus
strColName = cboCols.Value
txtValue.SetFocus
strValue = txtValue.Text

strSQL = "SELECT [Column List],Count([SomeColumnName]) AS
MyCount FROM TableName WHERE " & strColName & "='" &
strValue & "'"

Set objRS = DBEngine.Workspaces(0).Databases
(0).OpenRecordSet(strSQL)

The value of objRS("MyCount") would tell you how many
records were returned.

Couple of caveats:
1.) The code above assumes all values are strings (note
the single quotes.
2.) The records returned will ONLY show exact matches. So,
if you typed in "Cal", it would only show you the records
where the entire name WAS "Cal". In all reality, you'd
probably want a "Select Case" statement to build a couple
variations on that SQL statement, and if you're searching
on a name, you would probably use something
like "...WHERE " & strColName & " LIKE '*" & strValue
& "*'"

HTH