When and how to open recordsets 1

[GNorman]

I'm creating a small app that makes updates to a table based on user input. So I'm trying to open recordsets in a way that the Sub can see them every time the user inputs data. When I open the form and immediately close it I get a runtime error 91: "Object Variable or With block variable not set". It stops on the Bulk.Close line below.

Private Sub Form_Unload(Cancel As Integer)
    Bulk.Close

I'm declaring Bulk as Public in the general declarations section then opening a recordset in Sub Form_Load. Is the error implying that I never created Bulk? What is the proper way to declare and open recordsets?

Option Compare Database

Public dabs As DAO.Database
Public Bulk As DAO.Recordset

Private Sub Form_Load()
    Set dabs = CurrentDb
    Set Bulk = dabs.OpenRecordset("Bulkpipe")
    Bulk.Index = "idxBP_ID"

 

[MajP]

It looks correct, but it does not matter. There really is no reason you have to close the RS. Once the form closes the RS goes out of scope anyways since it is a form level public variable.

 

[GNorman]

Embarrassing error on my part. I'm in test mode so I delete the Bulk table at the end of each evening to save disk space. Re-importing Bulk took care of the error but I think you've answered my underlying question: am I declaring and opening the recordset properly?

 

[HarleyQuinn]

Yeah, the way you declare/open the recordsets is fine.

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[MajP]

one thing. Do you need to use recordsets? I assume the form is unbound and you want to make updates to it. It normally is more efficient to make sql calls to the table: update, append, delete.

dim strSql as string
strSql = some action sql string
currentDb.execute strSql

 

[GNorman]

MajP: The user is scanning a barcode to fill a text box on the form. After the scan I seek the scanned value in the table/recordset and update a field in the table if it's found. Based on that scenario I assume recordset is the way to go but I'm always willing to learn.

 

[MajP]

you can find it with a simple dlookup. If it returns null then the record does not exist. If it does exist then run an update query. I assume if it is not found you run an append query. Something like

if not isnull(dlookup(fldname,tblName,"fldName = '" & txtBoxBarcode & "'") then
  strSql = "UPDATE tblName SET tblName.fldName = '" & someString & "' WHERE (tblName.fldName)= '" & txtBoxBarcode & "'"
else
  strSql = some append query
end if
currentdb.execute strSql

Not saying you can not use recordsets, but often it is simpler and cleaner to use simple action queries.

 

[GNorman]

The table can have tens of thousands of records. Is a dlookup as fast as a seek?

 

[MajP]

Probably not, but try a dlookup and see if you have any performance issues. I assume the field is indexed. I imagine we are talking seconds different. However the action sql will be faster then opening the recordset.

The faster way would be to build a query with the where statement something like

fldBarCode = [forms]![yourForm]![txtBoxBarCode]

Now you have a query that returns a single record if the record exists or none if no record exists.

if dcount(fldName,qryBarCodeExists) = 1 then
update action query string
else
append action query string
end if

 

[GNorman]

Great looking alternatives. I'll do some experimenting over the next couple of days. Many thanks for your input.

 

[GNorman]

So for dlookup I don't need any preparatory Dim statements for the table I'm using?

 

[GNorman]

In one of your messages you ask if the table is indexed. Yes it is, how would I activate that index for the dlookup? Do I need to?

 

[MajP]

1) The dlookup is a function. The parameters are the field name, the table or query name, and an optional where string. There are no variables to dimension
2) If you are doing searches on a field in a table and you order by this field it will speed up the search if indexed on that field. There is nothing to activate.

I really would use the second suggestion
1. build the query that returns the record that matches the barcode value on your form
[forms]![yourForm]![txtBoxBarCode]
Basically you need to drop the above into the criteria field in the query builder
2. use a dcount to see if a record is returned
3. if found run an update query, if not possibly run an append query.

This would be very, very fast and efficient. You are using sql to return one record and one field. You are using a dcount on a query that has one or zero records. You are doing a single record action query.