A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Before-update reference on a field



 
 
Thread Tools Display Modes
  #1  
Old July 7th, 2009, 03:08 PM posted to microsoft.public.access
Pwyd
external usenet poster
 
Posts: 114
Default Before-update reference on a field

Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]", "Not
IsNull([V_InvoiceNumber]=[Forms]![Main Record]![V_InvoiceNumber]) ") Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub


this is currently sitting in the "before update" field, happily provided to
me by some other access database helpers here in the community. However
after testing it, it ALWAYS asks whether i want to cancel, regardless of
wehther the invoice number is actually a duplicate. I've read the reference
on IsNull and what it resolves to, and i'm not sure wehther this is what i
want, however i'm not sure what i should replace it with. Nz() perhaps? i
was only trying to prevent it from trying to do a comparison of "null" to a
string "null" which i'm told is invalid.

  #2  
Old July 7th, 2009, 03:21 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Before-update reference on a field

The Where portion of that doesn't look right to me. Try:

Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If IsNull(DLookup("[V_InvoiceNumber]", _
"[VendorRecordsTable]", _
"V_InvoiceNumber = " & [Forms]![Main Record]![V_InvoiceNumber])) Then
Else
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Pwyd" wrote in message
...
Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]", "Not
IsNull([V_InvoiceNumber]=[Forms]![Main Record]![V_InvoiceNumber]) ") Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub


this is currently sitting in the "before update" field, happily provided
to
me by some other access database helpers here in the community. However
after testing it, it ALWAYS asks whether i want to cancel, regardless of
wehther the invoice number is actually a duplicate. I've read the
reference
on IsNull and what it resolves to, and i'm not sure wehther this is what i
want, however i'm not sure what i should replace it with. Nz() perhaps?
i
was only trying to prevent it from trying to do a comparison of "null" to
a
string "null" which i'm told is invalid.



  #3  
Old July 7th, 2009, 08:17 PM posted to microsoft.public.access
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default Before-update reference on a field

Hi,

If you look at what you put in for the third parameter it will always
evaluate to true and so as long as you have any records in the table, you
will get an invoice number back from the DLookup() function; which in itself
will be interpreted as true unless it is zero. Try this instead:

If IsNull([V_InvoiceNumber]) Then
MsgBox "Please enter an invoice number."
Cancel = True
Else
If Not IsNull(DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]",
"[V_InvoiceNumber] = " & [V_Invoice_Number])) Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If
End If

Clifford Bass

"Pwyd" wrote:

Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]", "Not
IsNull([V_InvoiceNumber]=[Forms]![Main Record]![V_InvoiceNumber]) ") Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub


this is currently sitting in the "before update" field, happily provided to
me by some other access database helpers here in the community. However
after testing it, it ALWAYS asks whether i want to cancel, regardless of
wehther the invoice number is actually a duplicate. I've read the reference
on IsNull and what it resolves to, and i'm not sure wehther this is what i
want, however i'm not sure what i should replace it with. Nz() perhaps? i
was only trying to prevent it from trying to do a comparison of "null" to a
string "null" which i'm told is invalid.

  #4  
Old July 8th, 2009, 02:26 PM posted to microsoft.public.access
Pwyd
external usenet poster
 
Posts: 114
Default Before-update reference on a field

Great thanks cliff, i'll give that a try.



"Clifford Bass" wrote:

Hi,

If you look at what you put in for the third parameter it will always
evaluate to true and so as long as you have any records in the table, you
will get an invoice number back from the DLookup() function; which in itself
will be interpreted as true unless it is zero. Try this instead:

If IsNull([V_InvoiceNumber]) Then
MsgBox "Please enter an invoice number."
Cancel = True
Else
If Not IsNull(DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]",
"[V_InvoiceNumber] = " & [V_Invoice_Number])) Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If
End If

Clifford Bass

"Pwyd" wrote:

Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]", "Not
IsNull([V_InvoiceNumber]=[Forms]![Main Record]![V_InvoiceNumber]) ") Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub


this is currently sitting in the "before update" field, happily provided to
me by some other access database helpers here in the community. However
after testing it, it ALWAYS asks whether i want to cancel, regardless of
wehther the invoice number is actually a duplicate. I've read the reference
on IsNull and what it resolves to, and i'm not sure wehther this is what i
want, however i'm not sure what i should replace it with. Nz() perhaps? i
was only trying to prevent it from trying to do a comparison of "null" to a
string "null" which i'm told is invalid.

  #5  
Old July 8th, 2009, 03:41 PM posted to microsoft.public.access
Pwyd
external usenet poster
 
Posts: 114
Default Before-update reference on a field

i removed the extra _ you put in InvoiceNumber in the criteria part of the
expression, now its giving me a data type mismatch?


Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If IsNull([V_InvoiceNumber]) Then
MsgBox "Please enter an invoice number."
Cancel = True
Else
If Not IsNull(DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]",
"[V_InvoiceNumber] = " & [V_InvoiceNumber])) Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If
End If


End Sub

"Clifford Bass" wrote:

Hi,

If you look at what you put in for the third parameter it will always
evaluate to true and so as long as you have any records in the table, you
will get an invoice number back from the DLookup() function; which in itself
will be interpreted as true unless it is zero. Try this instead:

If IsNull([V_InvoiceNumber]) Then
MsgBox "Please enter an invoice number."
Cancel = True
Else
If Not IsNull(DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]",
"[V_InvoiceNumber] = " & [V_Invoice_Number])) Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If
End If

Clifford Bass

"Pwyd" wrote:

Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]", "Not
IsNull([V_InvoiceNumber]=[Forms]![Main Record]![V_InvoiceNumber]) ") Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If

End Sub


this is currently sitting in the "before update" field, happily provided to
me by some other access database helpers here in the community. However
after testing it, it ALWAYS asks whether i want to cancel, regardless of
wehther the invoice number is actually a duplicate. I've read the reference
on IsNull and what it resolves to, and i'm not sure wehther this is what i
want, however i'm not sure what i should replace it with. Nz() perhaps? i
was only trying to prevent it from trying to do a comparison of "null" to a
string "null" which i'm told is invalid.

  #6  
Old July 8th, 2009, 06:01 PM posted to microsoft.public.access
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default Before-update reference on a field

Hi,

Ah, an erroneous typo. Is V_InvoiceNumber defined as an actual number
or is it defined as text? If text, chante this:

= " & [V_InvoiceNumber]))

to:

= """ & Replace([V_InvoiceNumber], """", """""") & """"))

Note that those are all quotes (") not apostrophes ('). The Replace()
function makes sure that you do construct an invalid invoice number clause,
in the odd event that someone has entered a quote symbol into the invoice
number.

If that is not the issue, post back.

Clifford Bass

"Pwyd" wrote:

i removed the extra _ you put in InvoiceNumber in the criteria part of the
expression, now its giving me a data type mismatch?


Private Sub V_InvoiceNumber_BeforeUpdate(Cancel As Integer)

If IsNull([V_InvoiceNumber]) Then
MsgBox "Please enter an invoice number."
Cancel = True
Else
If Not IsNull(DLookup("[V_InvoiceNumber]", "[VendorRecordsTable]",
"[V_InvoiceNumber] = " & [V_InvoiceNumber])) Then
If MsgBox("Invoice number already exists. Continue?", vbYesNo, _
"Invoice Number Duplicate") = vbNo Then
Cancel = True
End If
End If
End If


End Sub

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:39 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.