Autonumber and checking for duplicate values in db

[Thurman]

Hello,

I have a form where a user enters names into my database.
The names may be new or they may already exist in the
database. Names are entered in tbxFirstName and
tbxLastName on the form and the ID for the record is
autonumber. If a name is new, a popup form is supposed to
open so that the user can assign the new person to an
apartment. If the name is already there, the user needs to
be reminded of this fact and then choose between leaving
the record as it is or assigning the person to a different
apartment.

So far I have been using the DLookup function and Is
NotNull to determine which commands to follow. It works if
the name IS already there. But if the name is new, it
doesn't work. Because the autonumber field automatically
updates when a character is entered in either tbxFirstName
or tbxLastName, the DLookup rightly returns that the record
already exists. Following that, however, it falls apart
because, in fact, the data has not yet been committed to
the database. Can someone help me out with this? My code is:

Private Sub btnAssignApartment_Click()

Dim stDocName, Msg, CR As String

CR = Chr$(13)
strFirstName = Me.tbxFirstName 'global variable
strLastName = Me.tbxLastName ' global variable
stDocName = "frmPopUpTenantApartment"
Msg = "'" & strFirstName & " " & strLastName & "' already
exists in the database." & CR & CR
Msg = Msg & "Would you like to assign this tenant to a
different apartment?" & CR
Response = MsgBox(Msg, vbYesNo, "Duplicate Name")

If Not IsNull(DLookup("TenantID", tblTenants", "FirstName =
'" & Me.tbxFirstName & "' And LastName = '" &
Me.tbxLastName & :;) Then

If Response = vbNo Then 'tenant not new, apt the same
Me.Undo
DoCmd.Close acForm, "frmPopUpAddTenants"
Elseif Response = vbYes Then 'tenant not new, apt is new
DoCmd.Close acForm, "frmPopUpAddTenants"
DoCmd.OpenForm stDocName
End If

Else 'tenant is new, need to assign apt
DoCmd.OpenForm stDocName
End If

End Sub

THANKS IN ADVANCE!
Th.

 

[Tim Ferguson]

But if the name is new, it
doesn't work. Because the autonumber field automatically
updates when a character is entered in either tbxFirstName
or tbxLastName, the DLookup rightly returns that the record
already exists.

Leave the search form text boxes unbound -- i.e. leave the ControlSource
properties blank. (You could even make the whole form unbound).

Then if you want to create the record, you can use a couple of lines of vba
to create it and point the form (or another proper one) at it; or if not
the table has not been touched.

Hope that helps


Tim F

 

[Thurman]

-----Original Message-----


Leave the search form text boxes unbound -- i.e. leave the ControlSource
properties blank. (You could even make the whole form unbound).

Then if you want to create the record, you can use a couple of lines of vba
to create it and point the form (or another proper one) at it; or if not
the table has not been touched.

Hope that helps


Tim F

.

Thanks Tim. I tried that last week at some point. Problem
is I can't recall why it didn't work. May have been an
invalid use of null message or run-time error 13? I'll go
back and try it again and, if need be, repost. Thanks
again. Have a happy holiday. - th.

 

[Tim Ferguson]

Thanks Tim. I tried that last week at some point. Problem
is I can't recall why it didn't work. May have been an
invalid use of null message or run-time error 13?

If DCount("*","MyTable", _
"SomeName = """ & me!txtSomeName.Value & """") > 0 Then

' there's a record already
SetMyFormTo me!txtSomeName.Value

Else
' no there isn't, so create it new
strSQL = "INSERT INTO MyTable (SomeName) " & _
"VALUES (""" & me!txtSomeName.Value & """)"

SetMyFormTo me!txtSomeName.Value

End If

Have a happy holiday. - th.

Not over here... but have a good one yourself.

Tim F