Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank You for your help using DCount did the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •