Results 1 to 14 of 14
  1. #1
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40

    DLookup Tied to Form Error

    Before updating a form I want to prevent duplicate records from being created by cross referencing the associate name on reporting month entered in the form to the master table. The code I'm using below keep on telling me that my DCount function does not work. I'm not sure where the error is but I'm sure it's pretty simple... Please Help

    Private Sub Reporting_Month_BeforeUpdate(Cancel As Integer)
    Dim AssocName As Integer
    Dim RptMonth As Integer

    AssocName = DCount("*", "Master Scores Table", "Associate Name = '" & Me.Associate_Name & "'")
    RptMonth = DCount("*", "Master Score Table", "Reporting Month = '" & Me.Reporting_Month & "'")

    If (AssocName >= 1 And RptMonth >= 1) Then
    MsgBox "This associate already has a record for this month"
    Exit Sub


    End If
    End Sub

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First, you need to find out exactly where the error is occurring. Add a breakpoint to the code and step thru it, find out truly what is going on.

    You don't need a DCount, you don't care what the count is, correct? Use one dlookup with criteria of both fields at the same time.

  3. #3
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    I tried this code first but seem to be missing some syntax

    DLookup("Associate Name","Master Score Table","[Associate Name]=" & Me.Associate_Name & " AND [Reporting Month]='" & Me.Reporting_Month &"'")

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think you have a logical flaw. I suspect you want a single DCount() with both fields in the criteria. If I have records in the table for other months but not this month, but others have records for this month, your code will report me as already entered.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You missed the single quotes around the name value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Basically I only want a single record per associate per month and if it had already been entered the msgbox to pop up

  7. #7
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    I added those single quotes in but now get an error "Type mismatch"

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is Reporting_Month numeric? If so, lose its quotes.

  9. #9
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    No reporting month is a text field

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post your whole code. It looks like you are defining an integer but looking up a text (Associate Name).

  11. #11
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Private Sub Reporting_Month_BeforeUpdate(Cancel As Integer)
    If DLookup("Associate Name", "Master Score Table", "[Associate Name]='" & Me.Associate_Name & "'AND [Reporting Month]='" & Me.Reporting_Month & "'") Then
    MsgBox "This associate already has a record for this month"
    Exit Sub
    End If
    End Sub

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If what? Missing operator. Not IsNull or <>"" or whatever. DLookup returns a value, the left side of the IF statement only.

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Missing space : "'AND

    When I struggle with string concatenation I will put a Debug.Print just before it and copy/paste the concatenation, that way assuring that it looks right before looking elsewhere for the problem.

  14. #14
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Eureka that did the trick

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

Similar Threads

  1. Replies: 2
    Last Post: 01-04-2016, 09:40 AM
  2. DLookup text box on form shows error
    By Abacus1234 in forum Forms
    Replies: 8
    Last Post: 04-28-2015, 03:01 PM
  3. Replies: 1
    Last Post: 09-30-2013, 11:47 AM
  4. Subform tied to dropdown box?
    By BillC in forum Forms
    Replies: 13
    Last Post: 08-24-2012, 01:53 PM
  5. Dlookup in form returns #error
    By RickM in forum Access
    Replies: 1
    Last Post: 03-29-2010, 07:59 PM

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