If Checkbox is checked show msgbox

[ADB_Seeker]

On my form's field (DRAWING_NUMBER) I need Access to look to see if a
checkbox (RFP_Issued) is checked in the table (ER TABLE). If it is, I need a
message box to popup. I've coded it many ways and receive run-time error
2465 or 13. Following is my current code:

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If ([ER TABLE].[RFP Issued]) = 0 Then
MsgBox "A RFP has been issued on this drawing." & vbLf & _
" Nofify Engineering Manager or Administrator before making any
changes."
End If
End Sub

Thank you in advance for your help.
Linda

 

[David]

I think you have to use True or False and not 0
Thats what I have always done and it seems to work.

 

[Maurice]

Try adding cancel = true to your code and see if that eliminates the problem.

If ([ER TABLE].[RFP Issued]) = 0 Then
MsgBox "A RFP has been issued on this drawing." & vbLf & _
" Nofify Engineering Manager or Administrator before making any
changes."
cancel=true
End If
End Sub

also can't you check the fieldvalue in the form?
Could look something like this:

If [RFP Issued] = 0 Then '-assuming there is a field [RFP Issued] on your
form


hth

 

[Jon Lewis]

You can't just reference a table like this. You could use the DLookup
function for this. Look it up in Help for example usage.

HTH

 

[ADB_Seeker]

Thanks to everyone for the quick responses. I put in = True (instead of 0)
and it didn't work. I will add Cancel = True to see if that works, and I will
explore DLookup in help as Jon suggested.

 

[John W. Vinson]

On my form's field (DRAWING_NUMBER) I need Access to look to see if a
checkbox (RFP_Issued) is checked in the table (ER TABLE). If it is, I need a
message box to popup. I've coded it many ways and receive run-time error
2465 or 13. Following is my current code:

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If ([ER TABLE].[RFP Issued]) = 0 Then
MsgBox "A RFP has been issued on this drawing." & vbLf & _
" Nofify Engineering Manager or Administrator before making any
changes."
End If
End Sub

Thank you in advance for your help.
Linda

You do indeed need DLookUp as Jon suggests - as written, you're referring to
the entire ER table, with no indication of *which row* in that table to look
at, so even if the syntax were to work, you'ld get the wrong answer!

Try

If Not IsNull(DLookUp("[RFP Issued]", "[ER Table]", _
"[DRAWING_NUMBER] = '" & Me![DRAWING_NUMBER] & "'") Then

This assumes that the form control and table field are in fact named
DRAWING_NUMBER (with an underscore not a blank) and that the fields are of
Text datatype. Omit the ' and "'" if it's actually a Number type field.

 

[ADB_Seeker]

Actually the field Drawing Number has a blank, not an underscore so I added
quotation marks before/after the square brackets for these. I entered the
following code in the BeforeUpdate event and received a compile error
"Expected: Expression". The first single quote is highlighted.

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookUp("[RFPIssued]", "[ER Table]", _
"[DRAWING NUMBER]" = '" & Me!("[DRAWING NUMBER]", & "'") Then

MsgBox "ATTENTION! A RFP has been issued on this drawing." & vbLf & _
" Notify Engineering Manager or Administrator if you make any
changes."
End If
End Sub

On my form's field (DRAWING_NUMBER) I need Access to look to see if a
checkbox (RFP_Issued) is checked in the table (ER TABLE). If it is, I need a
message box to popup. I've coded it many ways and receive run-time error
2465 or 13. Following is my current code:

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If ([ER TABLE].[RFP Issued]) = 0 Then
MsgBox "A RFP has been issued on this drawing." & vbLf & _
" Nofify Engineering Manager or Administrator before making any
changes."
End If
End Sub

Thank you in advance for your help.
Linda

You do indeed need DLookUp as Jon suggests - as written, you're referring to
the entire ER table, with no indication of *which row* in that table to look
at, so even if the syntax were to work, you'ld get the wrong answer!

Try

If Not IsNull(DLookUp("[RFP Issued]", "[ER Table]", _
"[DRAWING_NUMBER] = '" & Me![DRAWING_NUMBER] & "'") Then

This assumes that the form control and table field are in fact named
DRAWING_NUMBER (with an underscore not a blank) and that the fields are of
Text datatype. Omit the ' and "'" if it's actually a Number type field.

 

[John W. Vinson]

Actually the field Drawing Number has a blank, not an underscore so I added
quotation marks before/after the square brackets for these. I entered the
following code in the BeforeUpdate event and received a compile error
"Expected: Expression". The first single quote is highlighted.

Well, all the complexity is because you fell for Microsoft's willingness to
let you use blanks and special characters in fieldnames. It's much simpler if
you don't!

If you use brackets though, you don't need (those) quotes - only the quotes
delimiting the string constants and the criteria. Try

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookUp("[RFPIssued]", "[ER Table]", _
"[DRAWING NUMBER] = '" & Me![DRAWING NUMBER] & "'") Then

MsgBox "ATTENTION! A RFP has been issued on this drawing." & vbLf & _
" Notify Engineering Manager or Administrator if you make any
changes."
End If
End Sub

If the value of the DRAWING NUMBER control on the form is X123, this will
construct a string

[DRAWING NUMBER] = 'X123'

which should give you the result you want.

 

[ADB_Seeker]

Darn Microsoft.... Your code worked perfectly. No more spaces or special
characters in fieldnames for me.

Thank you.

Actually the field Drawing Number has a blank, not an underscore so I added
quotation marks before/after the square brackets for these. I entered the
following code in the BeforeUpdate event and received a compile error
"Expected: Expression". The first single quote is highlighted.

Well, all the complexity is because you fell for Microsoft's willingness to
let you use blanks and special characters in fieldnames. It's much simpler if
you don't!

If you use brackets though, you don't need (those) quotes - only the quotes
delimiting the string constants and the criteria. Try

Private Sub DRAWING_NUMBER_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookUp("[RFPIssued]", "[ER Table]", _
"[DRAWING NUMBER] = '" & Me![DRAWING NUMBER] & "'") Then

MsgBox "ATTENTION! A RFP has been issued on this drawing." & vbLf & _
" Notify Engineering Manager or Administrator if you make any
changes."
End If
End Sub

If the value of the DRAWING NUMBER control on the form is X123, this will
construct a string

[DRAWING NUMBER] = 'X123'

which should give you the result you want.