If Exists enters data in table 2 (1 Viewer)

Pavczech

Registered User.
Local time
Today, 15:39
Joined
Jul 11, 2012
Messages
41
Hi Guys

I have code whch is working fine to the point I need to close this form.
It checks if the record exist in "Visitors Book - Personal" and if it doesn't it just enter it with the visit detail to "Visitors Book - Visits" and closes. Now is there a way that if the record exists it does only records the visits details to "Visitors Book - Visits" and closes. ( By the way I think the DoCmd.CancelEvent doesn't do anything )

here is the module

Private Sub CommandENTER_Click()
Me.PersonalID = DLookup("[ID]", "[Visitors Book - Personal]", "[First Name] ='" & Forms![VisitorsBookIn]![First Name] & "' AND [Surname] ='" & Forms![VisitorsBookIn]![Surname] & "' AND [Month Of Birth] ='" & Forms![VisitorsBookIn]![Month Of Birth] & "' ")
Dim stExist As String
Dim stNoExist As String
Dim stLinkCriteria As String

Dim I As Integer

I = DCount("[First Name]", "[Visitors Book - Personal]", "[First Name] ='" & Forms![VisitorsBookIn]![First Name] & "'")
Y = DCount("[Surname]", "[Visitors Book - Personal]", "[Surname] ='" & Forms![VisitorsBookIn]![Surname] & "'")
Y = DCount("[Month Of Birth]", "[Visitors Book - Personal]", "[Month Of Birth] ='" & Forms![VisitorsBookIn]![Month Of Birth] & "'")
If I > 0 And Y > 0 And X > 0 Then
DoCmd.CancelEvent
DoCmd.Close
Else
DoCmd.Close
End If
End Sub


Thanks again
 

Pavczech

Registered User.
Local time
Today, 15:39
Joined
Jul 11, 2012
Messages
41
yep CancelEvent is bit uselless there.
Quite right I don't want to duplicate the data that is why I have 2 tables.
First Visitors detail- Name, Surname, DOB etc
Second Visits details- Date, Time In, Out etc,
On the form there are fields from bough tables and therefore if the visitor exists it doesn't do anything to the table 1, but inputs the visits details in to the table 2. Does this make sence now.
Thanks
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
Note that DLookup() can return Null so you need to code for that using Nz()

CancelEvent only works on events that have a Cancel parameter, e.g. Before Insert event. So it won't take any effect on the click event. You can write your IF like this:
Code:
If DCount("*", "[COLOR=Red]TableName[/COLOR]", "[COLOR=Red]Your three criteria[/COLOR]") > 0 Then
    DoCmd.Close "FormName"
End If
Amend the bits in red
 

Users who are viewing this thread

Top Bottom