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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|