Acc2003/XP: DLookup to compare before update

  • Thread starter Thread starter Oz Skull
  • Start date Start date
O

Oz Skull

G'day ppl,

I am looking for a code that will lookup a string value before it updates
the field eg.

= DLookup("txtMyTitles", "tblMyTitles", "Criteria = '" &
forms!frmMyTitlesInput!txtMyTitles & "'")
If Me.txtMyTitles = ("txtMyTitles", "tblMyTitles") Then
response = msgBox("Title already Exists", vbOk)
DoCmd.Cancel
Else
Me.txtMyTitles.Update
End If

Effectively, I want to check the table for the value I enter to see if it is
already there, if so, then cancel the event.

Now I know the Syntax structure is all wrong, but it is the best was I can
explain what I am trying to achieve.

Look forward to your thoughts

TIA

Mark.
 
G'day ppl,

I am looking for a code that will lookup a string value before it updates
the field eg.

= DLookup("txtMyTitles", "tblMyTitles", "Criteria = '" &
forms!frmMyTitlesInput!txtMyTitles & "'")
If Me.txtMyTitles = ("txtMyTitles", "tblMyTitles") Then
response = msgBox("Title already Exists", vbOk)
DoCmd.Cancel
Else
Me.txtMyTitles.Update
End If

Effectively, I want to check the table for the value I enter to see if it is
already there, if so, then cancel the event.

Now I know the Syntax structure is all wrong, but it is the best was I can
explain what I am trying to achieve.
Click to expand...

DLookUp is a function which returns a value; you want to compare that
value with the value which is in your textbox. But there's actually a
simpler way: just see if the DLookUp returns nothing at all. Try:

Private Sub txtMyTitles_BeforeUpdate(Cancel as Integer)
Dim response As Integer
If Not IsNull(DLookUp("txtMyTitles","tblMyTitles","txtMyTitles = '" _
& Me!txtMyTitles & "'") Then
response = MsgBox("Title already exists", vbOKOnly)
Cancel= True ' the BeforeUpdate event's Cancel property
' cancels the update if it's true
End If
' if Cancel is left False it goes ahead and does the update, no code
needed
End Sub

One tweak: if your title might contain an apostrophe ("Lady
Chatterly's Lover" for example) you can't use ' to delimit it. Safer
is to use ", ASCII value 34, as a delimiter:

If Not IsNull(DLookUp("txtMyTitles", "tblMyTitles",
"txtMyTitles = " & Chr(34) & Me!txtMyTitles & Chr(34)) Then

This all assumes that you have a Field named txtMyTitles in your table
(the first argument to dlookup) and that the table is named
tblMyTitles. If it were me, I'd use a form control named txtMyTitle
bound to a table field named MyTitle (singular not plural and without
a prefix), but whatever naming conventions work for you are fine.

John W. Vinson[MVP]
 
Thank you John

You have helped immensely; with regard to the naming, they actually are as
you suggest, I put them in this context to avoid any confusion.

Thx again

reg's

Mark
 
One snag John, if the procedure is true, then a blank record is left at the
end of the event. What do you think I should add to the If statement to
delete the record in this event.

Thx again

Mark.
 
One snag John, if the procedure is true, then a blank record is left at the
end of the event. What do you think I should add to the If statement to
delete the record in this event.

Thx again

Mark.
Click to expand...

Move the code to the Form's BeforeUpdate event instead of the
control's. Setting Cancel to true will now cancel the entire record,
not just the one textbox.

John W. Vinson[MVP]
 
Thx again John,

your a legend.

Regs

Mark.


John Vinson said:
Move the code to the Form's BeforeUpdate event instead of the
control's. Setting Cancel to true will now cancel the entire record,
not just the one textbox.

John W. Vinson[MVP]
Click to expand...
 
Back
Top