Learning if a record exists in a table

G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

BeforeUpdate or AfterUpdate Events 4
Testing if record exists 18
Method or date member not found 1
From a form check if a name exists on a different table 2
Select Case Statements & OldValue Property 8
How to code/perform a search 14
Options Group, AfterUpdate Event, OldValue Property and ControlSou 6
Undo if same record exists 4

Top