How to build / code a search form

R

rich

Hello,

I am a fairly novice Access programmer looking for best practices/examples
on building a search form. Also, on checking for the result of a query.

I would like to build a form that queries for and displays an employee
record. There should be 2 ways to query: By the employee number, if it is
known, or by the lastname and firstname (2 fields) if the ID number is
unknown.

Also, is there a way to check the result of a SQL query?
For examply, if the query finds a matching record, an internal code is set
to 100.
If now matching row is found, the code might be set to 999.

Lastly, does Access have a schema or system catalog that can be queried
similar to SQL Server?

TIA,
Rich
 
D

Dale Fye

Rich,

1. There are several techniques for searching forms. Allen Browne has a
good site and has a description at this url:
http://www.allenbrowne.com/ser-62.html

2. If the query you are checking is a saved query, you can use the DCOUNT(
) domain function to determine whether any records were returned. It might
look like:

IF DCOUNT("ID", "qry_SomeQuery") = 0 then
'no records found code
Else
'records were found code
ENDIF

3. Does Access have a schema or system catalog that can be queried similar
to SQL Server? It depends on what you are trying to query. Access has a
mSysObjects table(this is a hidden system object) that contains a list of the
tables, queries, forms, ... but does not go down to the columns level that
SQL Server does.
You can view this table by selecting Tools -> Options from the Access menu,
then checking the "System Objects" table on the View tab.

HTH
Dale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top