Execute Query "strSQL SELECT " via Access Form

[Guest]

What is the proper way to VB code for executing a Select Query via an access
form when the form prompts user to enter record key data and you want to read
a table and its alternate/secondary table for matching records. Would I use
DCount or Count function to obtain the number of matching records found in
both tables and could someone suggest the VB code for the DCount or Count
function, I'v tried and not had much luck in obtaining the number of records
in temporary result set.

The following is the code from a Query that would have to be executed in
order to obtain the record information via form input:

SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM,
DPS_FR_CASE_RECORDS.TICKLE_DATE, DPS_FR_CASE_RECORDS.PRTNO_NUM,
DPS_FR_CASE_RECORDS.PRTD_CDE, DPS_FR_CASE_RECORDS.FR_FILE_NUM,
DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT, DPS_FR_CASE_RECORDS.LIC_STAT_CDE,
DPS_FR_CASE_RECORDS.LIC_DL_STAY_CDE, DPS_FR_CASE_RECORDS.RESULT_CDE,
DPS_FR_CASE_RECORDS.RESULT_MO_CDE, DPS_FR_CASE_RECORDS.RESULT_YR_CDE,
DPS_FR_CASE_RECORDS.REVO_DATE, DPS_FR_CASE_RECORDS.OFCR_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM,
DPS_FR_CASE_RECORDS.REQ_RECD_DATE, DPS_FR_CASE_RECORDS.HRG_DATE,
DPS_FR_CASE_RECORDS.HRG_TIME_TXT, DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT,
DPS_FR_CASE_RECORDS.LOC_CDE, DPS_FR_CASE_RECORDS.ATTY_NUM,
DPS_FR_CASE_RECORDS.LIC_FIRST_NME, DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME, DPS_FR_CASE_RECORDS.LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT, DPS_FR_CASE_RECORDS.LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE, DPS_FR_CASE_RECORDS.LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE, DPS_FR_CASE_RECORDS.LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME,
DPS_FR_CASE_RECORDS.DOA_ADDR_TXT, DPS_FR_CASE_RECORDS.DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE, DPS_FR_CASE_RECORDS.DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE, DPS_FR_CASE_RECORDS.FLAG_CDE,
DPS_FR_CASE_RECORDS.BATCH_DATE, DPS_FR_CASE_RECORDS.BATCH_NUM,
DPS_FR_CASE_RECORDS.MEMO1_TXT, DPS_FR_CASE_RECORDS.MEMO2_TXT,
DPS_FR_CASE_RECORDS.MEMO3_TXT
FROM DPS_FR_CASE_RECORDS
WHERE
(((DPS_FR_CASE_RECORDS.CASE_NUM_YR)=[Forms]![FRF-Search-By-Case]![unbtxt_SEARCH_CASE_YR])
AND
((DPS_FR_CASE_RECORDS.CASE_NUM)=[Forms]![FRF-Search-By-Case]![unbtxt_SEARCH_CASE_NUM]));


Thanks in advance

 

[Nikos Yannacopoulos]

Though not very clear on what exactly you are trying to achieve, please
note that:

You cannot execute a select query in VBA. You can only execute action
queries (append, update, delete, make-table).

Generally, in order is to count query records matching the criteria
entered in unbound controls on a form, then your options include:

1. construct the query SQL in code, open it as a recordset and do a
recordcount;
2. save the query, using references to the form controls in the
criteria, and do a DCount() on the saved query;
3. save the query without any criteria (on the particular fields), and
do a DCount() on the saved query, including the references to the form
controls in the criteria argument of the DCount().

In this case, if you are just wanting to count records in
DPS_FR_CASE_RECORDS (be it a table or a saved query, it makes no
difference) matching the values entered in the form, then all you need
is a plain DCount() (like in 3 above) on DPS_FR_CASE_RECORDS, something
like:

lngRecordsFound = DCount("*", DPS_FR_CASE_RECORDS, _
"CASE_NUM_YR=" & [Forms]![FRF-Search-By-Case]![unbtxt_SEARCH_CASE_YR] _
" And CASE_NUM=" &
[Forms]![FRF-Search-By-Case]![unbtxt_SEARCH_CASE_NUM]

(watch out for text wrapping) where I have assumed both fields to be
numeric.

HTH,
Nikos