Learning if a record exists in a table

[Ted]

using a2k if it makes any difference...

when a certain condition in my before update event code (below) is met, i
want to update a record using a query in a certain table in my database. the
thing is that it's important to test whether that matching record is already
in that table. is there some way to test (using appropriate criteria which
restrict the search to the matching values of "IRB Number" and "MR Number"
found on the current form ("Screening Log") if a matching record is locatable
in that table ("Patient Follow-Up")?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
' Me.[Visit #] = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = " &
[Me]![Last Name] & "[First Name] = " & [Me]![First Name] & "[MI] = " &
[Me]![MI] & "[MR_Number] = " & [Me]![MR_Number] & "[IRB Number] = " &
[Me]![IRB Number]), 0) + 1
End If

Dim Flag As Integer

Flag = 0

Select Case Outcome_

Case 5, 6, 7

If Me.Last_Name <> Me.Last_Name.OldValue Then
Flag = Flag + 1
ElseIf Me.First_Name <> Me.First_Name.OldValue Then
Flag = Flag + 1
ElseIf Me.MR_Number <> Me.MR_Number.OldValue Then
Flag = Flag + 1
ElseIf Me.SequenceNum <> Me.SequenceNum.OldValue Then
Flag = Flag + 1
ElseIf Me.SponsorIDNumber <> Me.SponsorIDNumber.OldValue Then
Flag = Flag + 1
ElseIf Me.IRB_Number <> Me.IRB_Number.OldValue Then
Flag = Flag + 1
ElseIf Me.OffStudyDate <> Me.OffStudyDate.OldValue Then
Flag = Flag + 1
ElseIf Me.Campus <> Me.Campus.OldValue Then
Flag = Flag + 1
End If

Case Else

End Select

If Flag > 0 Then DoCmd.RunMacro "Update Screening Log (Edit Only) Record"
<-- this gets done but ONLY if a record is in there


End Sub

 

[Ofer Cohen]

Hi Ted
To check if a record exist in a table you can use the Dcount

If DCount("*","[TableName]","[TextFieldNameInTable] = '" &
Me![TextFieldNameInForm] & "' And [NumberFieldNameInTable] = " &
Me.[NumberFieldNameInForm) = 0 Then
' Record not found
Else
' REcord Found
End If

To filter on a text field you need to add a single quote before and after
the variant, as you can see in the example