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

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Error in my code to check data on existing table (Access 2002 SP3)

    Author
    Topic
    WSrk_winston
    AskWoody Lounger
    April 21, 2005 at 3:56 pm #418571

    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
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      April 21, 2005 at 4:26 pm #942462

      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
      • WSrk_winston
        AskWoody Lounger
        April 21, 2005 at 6:47 pm #942495

        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
        • WSHansV
          AskWoody Lounger
          April 21, 2005 at 7:09 pm #942500

          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
    Viewing 0 reply threads
    Reply To: Error in my code to check data on existing table (Access 2002 SP3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.