VB to Check Duplicates then update two Linked Tables

[Tezcatlipoca]

I'm sure the answer to this is straightforward, but I've been hunting around on Access' help, these forums and elsewhere, and can't find any examples or solutions that fit with what I need.

Basically, my user is sitting on a form, frmSearchOnline. On this form are unbound text fields for MemberName and MemberNumber, allowing the user to freely enter whatever they like into these.

On the same form is a Save button (labelled btnAddMember), which has the following currently attached to it:

Private Sub btnAddMember_Click()
On Error GoTo Err_btnAddMember_Click
    Dim stDocName As String
    Dim strUserName As String
    strUserName = Me.LoginName 'Ignore this bit.  It's not relevant to my issues, and is used to pass a hidden username from one form to another
    stDocName = "frmView"
    If IsNull(MemberName) Then
        MsgBox "You must enter a Name", vbExclamation, "Invalid Function"
        Me.MemberName.SetFocus
        Exit Sub
    End If
    If IsNull(MemberNumber) Then
        MsgBox "You must enter a Number", vbExclamation, "Invalid Function"
        Me.MemberNumber.SetFocus
        Exit Sub
    End If
    If [COLOR="Red"]Me.MemberNumber.Value = DLookup("MemberNumber", "tblMembers")[/COLOR] Then
    Select Case MsgBox("This number exists.  Go to Record?" & vbCrLf, vbYesNo, "Record Select")
        Case vbYes:
            DoCmd.OpenForm stDocName, , , "[MemberNumber] = " & Me.MemberNumber, , acNone, strUserName
            DoCmd.Close acForm, "frmSearchOnline", acSaveN
        Case vbNo:
            Exit Sub
        Case Else:
            Exit Sub
    End Select
    End If
    'INSERT CODE WILL GO HERE
Exit_btnAddMember_Click:
    Exit Sub
Err_btnAddMember_Click:
    MsgBox Err.Description
    Resume Exit_btnAddMember_Click
End Sub

The validation check to ensure the Name and Number boxes have been filled works just fine, as does the code for my 'Move to Record?' query for the user. It is my validation check for duplicates (the bit in red text) that's failing.

When both boxes have been filled and the user clicks the Save button, the VB should hunt through the field MemberNumber in the linked table tblMembers for a match. If it finds an exact match with whatever the user has entered into the MemberNumber txt field, the user should receive a message that the number already exists and an offer to go to the existing record.

My first problem is with my DLookup not functioning properly, but I can't see why. I could instead go into the table tblMembers and simply make the MemberNumber field unique, but that's too sloppy for my needs, plus I need to retain control over what happens if a match is found.

My second problem is in actually creating the record if the match is clean. I think I need to use the SQL INSERT statement, but am not too sure of the syntax. In addition to tblMembers I have the linked table tblLogs. When a member is added with the Save button on the form, tblMembers should get a new row added with the contents of the Name and Number fields, and tblLogs should get a new row added with the contents of the Number field.

I'd naturally prefer to get direction on how I can teach myself where I'm going wrong, rather than have somebody supply the code for me, but my attempts to get this function working so far are proving fruitless.

Can anybody please advise?

 

[pbaldy]

Well, a DLookup without a criteria will simply return the first record in the table. I'd use DCount with the value from the form in the criteria:

If DCount(...) > 0 Then
'value already exists

Here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[Tezcatlipoca]

Well, a DLookup without a criteria will simply return the first record in the table. I'd use DCount with the value from the form in the criteria:

If DCount(...) > 0 Then
'value already exists

Here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

Thanks for the help, but I'm still getting nowhere. I'ver tried including code in the OnClick of my button:

    If DCount("*", "tblMembers", "[MemberNumber] = '" & Me![MemberNumber] & "'") > 0 Then
        MsgBox "This Number is already in use.", vbExclamation, "Error"
    End If

but this returns a Criteria error regardless of the value typed into the MemberNumber text field.

I've also tried playing with the inclusion of code into the forms Before_Update event (as referenced in your link and elsewhere), but this produces no effect whatsoever, regardless of what is entered into the MemberNumber field.

I know it's not my form, since it is opened to fully allow Edits, Additions, Deletions, etc., but just cannot see why it is refusing to work for me.

 

[pbaldy]

Presuming your MemberNumber field is a text data type in the tblMembers table, that should work. Is it a numeric data type? If not, can you post a sample db?

 

[Tezcatlipoca]

Heh. Simultaneous post. As you were typing the above, I had just spotted my mistake of using rogue apostrophes. Apostrophes removed, now the check works! Thanks for pointing me in the right direction, pbaldy.

 

[pbaldy]

No problem, glad we got it sorted out.