Results 1 to 6 of 6
  1. #1
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26

    Duplicate Check code with Run-Time error '3079'

    Hello I have a felling that this is a rather simple fix but I am very new to VB coding and I have exhausted all that I can think of to fix this error.



    This Database refers to keeping track of some Ex-Offender information for my agency. I have a table that contains all the demographic information on the individual; they have a unique ID call a SOID number. This number field has to be typed in manually and cannot be a autonumber. So what I wanted to do is if an employee tries to enter a SOID number of an individual that already exists in the system I want to display a mesg box informing them of the duplicate, delete the duplicate information they entered then transfer them to the existing record with the matching SOID. I know the code worked on another database that I created but that particular one was much simpler and didn't involve relating the SOID number to multiple tables. How do I define which table the 'rsc.FindFirst' statement? Thanks in advance for the help, below is the error mesg that I get and the corresponding code that I am using. The text highlighted in red is what the debug function says there’s something wrong with.

    Run-Time error '3079'
    The specified field 'SOID_Number' could refer to more than one table listed in the FROM clause of your SQL Statement.

    Code:
     
    Private Sub SOID_Number_BeforeUpdate(Cancel As Integer)
     
       Dim SID As Long
       Dim stLinkCriteria As String
       Dim rsc As DAO.Recordset
       Set rsc = Me.RecordsetClone
       SID = Me![ExOffender_Info.SOID_Number]
       stLinkCriteria = "[SOID_Number]=" & SID
       'Check Ex Offender Table for duplicate
       If DCount("ExOffender_Info.SOID_Number", "ExOffender_Info", stLinkCriteria) > 0 Then
          'Undo duplicate entry
          Cancel = True
          Me.Undo
          'Message box warning of duplication
          MsgBox "WARNING!! the SOID Number " _
                 & SID & " already exists in the system." _
                 & vbCr & vbCr & "Your current entry will be deleted and you will be transfered to the matching SOID Number you entered.", _
                 vbInformation, "Duplicate Information"
          'Go to record of original SOID'
         rsc.FindFirst stLinkCriteria
          Me.Bookmark = rsc.Bookmark
       End If
       Set rsc = Nothing
    End Sub

  2. #2
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    It sounds like you need to enter a table name in this line:
    Code:
    stLinkCriteria = "[TABLENAME].[SOID_Number]=" & SID
    But I could be mistaken without more info about the recordsource being used.

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I am guessing that the recordSource of the form is not from table "ExOffender_Info" and doesn't have a field name [SOID].

    EDIT: sorry, there do be a field name [SOID] in the form.

  4. #4
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Yup your right that worked!! Thanks

    I thought that was where I needed to difine a table but when I entered the code I entered it like the following.

    Code:
     
    stLinkCriteria = "[ExOffender_Info.SOID_Number]=" & SID
    But as I'm sure you already know I would get a different kind of error. Just for futher information could you tell me why the way I wrote the code is wrong and yours is right in this case. This all helps me get a better understanding.

    Thanks again.

  5. #5
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    Quote Originally Posted by viper View Post
    Code:
     
    stLinkCriteria = "[ExOffender_Info.SOID_Number]=" & SID
    But as I'm sure you already know I would get a different kind of error. Just for futher information could you tell me why the way I wrote the code is wrong and yours is right in this case. This all helps me get a better understanding.
    The brackets should surround each individual object and field. In your version it is treating the table and field as a single object but Access cannot find a single item named ExOffender_Info.SOID_Number. At least that's how I understand it...

  6. #6
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Oh ok that makes sense, if I remember correctly the error stated something like "The object ExOffender_Info.SOID_Number does not exist". Thanks for clarifing that, it really helps me to understand why things are entered in one way or another.

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

Similar Threads

  1. Replies: 14
    Last Post: 08-04-2010, 07:34 AM
  2. Error check in queries
    By devesa in forum Access
    Replies: 14
    Last Post: 05-07-2010, 09:39 PM
  3. Replies: 0
    Last Post: 03-16-2010, 08:10 PM
  4. Check time Diff.
    By wes028 in forum Access
    Replies: 3
    Last Post: 03-08-2010, 10:05 AM
  5. Code to spell out check amount?
    By spkoest in forum Access
    Replies: 4
    Last Post: 06-16-2009, 07:44 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