Read data from query that is not connected to the form

  • Thread starter Thread starter Diana
  • Start date Start date
D

Diana

Hello,

I have a form with a button appending data from Excel to
Access. I have set primary keys in the Access table in a
way that no dublicate values can be appended from Excel.
The warnings are turned off.

What I would like to do is check the count of lines in
the main table before and after the export. My guess is
that I need to use recordset but I don't know all the
steps. I would like to clarify that I need the data read
from the form.

Thank you for all your help.
Diana
 
If you are just wanting to count you can simply use the DCount() function
(can use a query as well as a table for its source) rather than a recordset.
DCount also accepts a where clause if you want to restrict the records
returned.

For eg
DCount("[field1]", "myTable", "[field2] = 5")

will count all the records in myTable where field2 = 5. Use the help for
more info.
Not sure how you want to 'check' this value, but as an example
msgbox "The number of records before import are: " & DCount("[field1]",
"myTable", "[field2] = 5")
' DO YOUR IMPORT
msgbox "The number of records after import are: " & DCount("[field1]",
"myTable", "[field2] = 5")

I am not sure what you mean by "needing the data read from the form" wrt
counting the number of records...?


If you wanted to do this in a recordset then you need something like the
following. I have assumed you have created a query to return the records
you want to count (this way you can incorporate a where in there as well),
but have set it out so that you could use your own SQL statement if you
wanted.

dim dbs as dao.database '(or could just use dbs as database)
dim rst as dao.recordset '(or could just use rst as recordset)
dim queryDef as dao.queryDef '(as above)
dim strSQL as string

set dbs = currentDb

'either
strSQL = "Select * from myTable"
'or
set queryDef = dbs.QueryDefs("myQuery")
strSQL = queryDef.SQL

set rst = dbs.OpenRecordset(strSQL)
if rst.BOF then
'no records returned, deal with it here
else
'populate
rst.moveLast
rst.movefirst
msgbox "There are " rst.RecordCount " records"
endif


queryDef.close
rst.close
dbs.close
 
Back
Top