Results 1 to 10 of 10
  1. #1
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Check if record exists, Check Number

    I need to check if a record has been entered into a table from my form using the OnCurrentEvent.(Really, I am looking to see if a check number has already been entered into the table TReg) My Form name is frmEvent and the field I am looking for is named ChkNo which is on my form. I want to see if that number is on another form FReg. Table (frmEvent) is using is tblEvent and field name is ChkNo and table to compare to is TReg and field is ChkNo on form FReg.



    Since I am using a scheduler and it resets the date entered, I have no way of knowing if the event has already been entered unless it happens to be on that day!

    Example: I enter a scheduled event and post it to check register and the next day comes around, the form will not show if the event has been posted or not since the event date has expired. I have to go and manually see if the event is in the table/form.

    One more thing.Here is the sql code for check dupes but how can I use this on my form? I need this as vba so I can set criteria!

    SELECT TReg.ChkNo, TReg.Bank
    FROM TReg
    WHERE (((TReg.ChkNo) In (SELECT [ChkNo] FROM [TReg] As Tmp GROUP BY [ChkNo] HAVING Count(*)>1 )))
    ORDER BY TReg.ChkNo;
    Last edited by burrina; 01-06-2013 at 11:03 AM. Reason: sql code for dupes.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would use a DLookup or DCount if I were you. http://access.mvps.org/access/general/gen0018.htm

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    So, here is what I came up with: Will it work?

    'Purpose: Check to see if event was entered into Check Register. Uses ChkNo as Criteria.

    If DLookup("ChkNo", "TReg", "Criteria = " & Forms!FReg!ChkNo) = ([ChkNo]) Then
    Me.Label125.Caption = "Event Was Successfully Entered into Check Register"
    Else
    Me.Label125.Caption = "Event Was NOT Successfully Entered into Check Register"
    End If

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I thought you were trying to find out if the value in the control "Me.ChkNo" is already in the ChkNo field of the TReg table? What's with the Criteria name in the WhereCondition argument?

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I don't know, don't know this syntax! My next attempt... If DLookup("ChkNo", "TReg", ([ChkNo])) = Me.ChkNo Then

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're going to want the Control OUTSIDE of the quotes:
    If DLookup("ChkNo", "TReg", "[ChkNo] = " Me.ChkNo ) Then
    All arguments to the Domain functions are strings.
    What does a DLookup return when there is NO match?

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sorry Burrina, did I through you a curve there?

  8. #8
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I am getting nowhere with this. I will try a different approach.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your choice of course but you probably need to master then Domain Functions anyway. Now is as good a time as any. Your call.

  10. #10
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    NOT Solved but moving on.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-19-2012, 04:30 PM
  2. How to check if Table already exists
    By riaarora in forum Access
    Replies: 1
    Last Post: 08-12-2012, 09:48 AM
  3. Replies: 1
    Last Post: 03-06-2012, 06:45 PM
  4. Trouble with check if Exists before Edit or Add Record
    By mrfixit1170 in forum Programming
    Replies: 4
    Last Post: 02-10-2012, 10:38 AM
  5. Check to see if record id exists in joined table
    By csoseman in forum Programming
    Replies: 1
    Last Post: 08-18-2011, 01:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums