Error in my code to check data on existing table (Access 2002 SP3)

Topic

New Reply

I am trying to see if the current record already exists on a table before inserting it again from a form. Data entry forms every once and a while are entered twice by mistake. I am trying to look up the data before an update event to see if it has been posted already or not. Here is what I have and I am getting a

Reply | Quote

Replies

You should expplicitly declare rst as a DAO recordset:

Dim rst As DAO.Recordset
Dim db As DAO.Database

But you don’t seem to do anything with the recordset except checking whether its RecordCount is 0. You might as well use a DCount instead.

What is Form_frm_1_prod_line_input? If it refers to the form running the code, you might as well use Me

Reply | Quote

Yes, the Form_frm_1_prod_line_input is the form running the code. I was not sure if DCount would use multiple sets of criteria for looking up the information. I had made those changes plus a couple more and I still get “Object variable or With block variable not set (Error 91).” Here is what I changed as well as adding the DAO in your suggetion.

Set rst = db.OpenRecordset(“SELECT prod_line, prod_line_date, prod_line_shift FROM 1_tb_prod_line_input WHERE 1_tb_prod_line_input.prod_line = ” & Me.c_prod_line.Value & ” AND 1_tb_prod_line_input.prod_line_date = #” & Me.prod_line_date.Value & “# AND 1_tb_prod_line_input.prod_line_shift = ” & Me.prod_line_shift.Value)

Reply | Quote

The 3rd argument to DCount is the WHERE clause of a query without the word WHERE itself. You can make it as simple or complicated as you like, with ANDs and ORs. I still think a DCount would be easier here than opening a recordset, although that should work too.

Without seeing the database, I have no idea where the error comes from.

Reply | Quote

Thank You for your help using DCount did the trick.

Reply | Quote