Form Coding Issue, Help Please

  • Thread starter Thread starter Kevin D.
  • Start date Start date
K

Kevin D.

I have a form, frmDonorsNew, which keeps track of Auction Donors. On this
form is field "AuctionDonor" which holds the name of the Auction Donor.

When entering a new Auction Donor (new record) I have the following code in
After Update for field AuctionDonor. The intent of the code is to check for
a duplicate database entry in the AuctionDonor field and give the user the
option of navigating to the original entry or deleting the duplicate entry
and entering another entry.

The code is as follows:

Private Sub AuctionDonor_AfterUpdate()

Dim varDonorID As Variant

If Me.NewRecord Then

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Me!AuctionDonor & "'")

If Not IsNull(varDonorID) Then

Msg = "You entered an Auction Donor already in the database. Click OK
to go to the previous entry." & Chr(13) _
& "Click Cancel to enter another Auction Donor."
Style = vbOKCancel + vbExclamation + vbDefaultButton1 ' Define buttons.
Title = "Duplicate Auction Donor Detected" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbOK Then ' User chose Yes.
Cancel = True
Me.Undo
Me.Recordset.FindFirst "DonorID = " & varDonorID
Me.Refresh
Exit Sub
Else ' User chose No.
Me.Undo
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acNext

End If
Exit Sub
End If

End If

End Sub

This works perfectly in all instances but one, where there is an apostrophe
in the Auction Donor's name, such as "Kevin's Store". In these situations I
get the following visual basic error message after the AuctionDonor field is
updated:

Syntax error (missing operator) in query expression '[Donor] = 'Kevin's
Store".

When I hit the debug button it points to the following line in the code"

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Me!AuctionDonor & "'")

How can I adjust this code so it works in these situations.

Thanks all for your replies.

Kevin D.
 
You need to double-up the ' character in the string (Kevin''s Store) so that
ACCESS reads the '' as '. This is easily done using the Replace function:

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Replace(Me!AuctionDonor, "'", "''", 1, -1, 1) & "'")
 
Thanks so much Ken, that did the trick.

Kevin D.

Ken Snell said:
You need to double-up the ' character in the string (Kevin''s Store) so that
ACCESS reads the '' as '. This is easily done using the Replace function:

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Replace(Me!AuctionDonor, "'", "''", 1, -1, 1) & "'")

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Kevin D. said:
I have a form, frmDonorsNew, which keeps track of Auction Donors. On this
form is field "AuctionDonor" which holds the name of the Auction Donor.

When entering a new Auction Donor (new record) I have the following code
in
After Update for field AuctionDonor. The intent of the code is to check
for
a duplicate database entry in the AuctionDonor field and give the user the
option of navigating to the original entry or deleting the duplicate entry
and entering another entry.

The code is as follows:

Private Sub AuctionDonor_AfterUpdate()

Dim varDonorID As Variant

If Me.NewRecord Then

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Me!AuctionDonor & "'")

If Not IsNull(varDonorID) Then

Msg = "You entered an Auction Donor already in the database. Click OK
to go to the previous entry." & Chr(13) _
& "Click Cancel to enter another Auction Donor."
Style = vbOKCancel + vbExclamation + vbDefaultButton1 ' Define
buttons.
Title = "Duplicate Auction Donor Detected" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbOK Then ' User chose Yes.
Cancel = True
Me.Undo
Me.Recordset.FindFirst "DonorID = " & varDonorID
Me.Refresh
Exit Sub
Else ' User chose No.
Me.Undo
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acNext

End If
Exit Sub
End If

End If

End Sub

This works perfectly in all instances but one, where there is an
apostrophe
in the Auction Donor's name, such as "Kevin's Store". In these situations
I
get the following visual basic error message after the AuctionDonor field
is
updated:

Syntax error (missing operator) in query expression '[Donor] = 'Kevin's
Store".

When I hit the debug button it points to the following line in the code"

varDonorID = DLookup("[DonorID]", "tblDonorsNew", "[Donor] = '" &
Me!AuctionDonor & "'")

How can I adjust this code so it works in these situations.

Thanks all for your replies.

Kevin D.
Click to expand...
Click to expand...
 
Back
Top