If Checkbox is checked show msgbox

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
0
Utf
3/24/2010 3:36:06 PM
access.formscoding 7493 articles. 0 followers. Follow

8 Replies
1819 Views

Similar Articles

[PageSpeed] 10

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

0
Utf
3/24/2010 4:12:10 PM
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
-- 
Maurice Ausum


"ADB_Seeker" wrote:

> 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
0
Utf
3/24/2010 4:14:02 PM
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" <ADB_Seeker@discussions.microsoft.com> wrote in message 
news:960CC715-DEB4-4C6E-8EAC-C6A55E1B30E2@microsoft.com...
> 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 


0
Jon
3/24/2010 4:16:42 PM
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.

"ADB_Seeker" wrote:

> 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
0
Utf
3/24/2010 5:06:01 PM
On Wed, 24 Mar 2010 08:36:06 -0700, ADB_Seeker
<ADB_Seeker@discussions.microsoft.com> wrote:

>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 [MVP]
0
John
3/24/2010 5:26:03 PM
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

"John W. Vinson" wrote:

> On Wed, 24 Mar 2010 08:36:06 -0700, ADB_Seeker
> <ADB_Seeker@discussions.microsoft.com> wrote:
> 
> >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 [MVP]
> .
> 
0
Utf
3/24/2010 6:45:01 PM
On Wed, 24 Mar 2010 11:45:01 -0700, ADB_Seeker
<ADB_Seeker@discussions.microsoft.com> wrote:

>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.
-- 

             John W. Vinson [MVP]
0
John
3/24/2010 7:55:44 PM
Darn Microsoft.... :-) Your code worked perfectly. No more spaces or special 
characters in fieldnames for me.

Thank you.


"John W. Vinson" wrote:

> On Wed, 24 Mar 2010 11:45:01 -0700, ADB_Seeker
> <ADB_Seeker@discussions.microsoft.com> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/24/2010 10:01:02 PM
Reply:

Similar Artilces:

Last number in a column shows in the total/summay line cell
I am looking for a formula that would have the last number listed in a column listed in the total or summary row of the spreadsheet. Column A lists the month of the year Column B lists # of files pending Jan 10 Feb 12 Mar 7 Summary -- I want this to show the last number in Column B -- in this example 7. Thank you. hi in a cell of your choosing, enter.. =OFFS...

Outlook slow showing messages.
Any advise on , when viewing messages through outlook 2002, When you highlight the message it takes about 15 - 30 seconds to show the message in the Preview plane. The longer the outlook is open the longer it gets between messages. Any help would be great on this... Brad Mc ...

i want excel to show the first tab upon opening, not the second
when i open my spreadsheet, it always opens with the second sheet prominent, rather than the first. how can i change this? Hi, You will need VB for that. Alt+F11 to open VB editor. Double click 'ThisWorkbook' and paste the code below in on the right. For it to work you must enable macros on opening Private Sub Workbook_Open() Application.Goto Sheets(2).Range("A1") End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "robs...

Checking for duplication on rows
Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

A formula that only shows the number of ex: "A" in a column and
Hi! Can i make a formula that only shows the number of ex: "A" in a column and ignore any hidden rows that result from filtering. Maybe i ca combine =SUBTOTAL(3;P:P) and =COUNTIF($P:$P;"=A") in some way? Regards Gunnar Gunnar, You basically need to reproduce your filtering: =SUMPRODUCT((P1:P10="A")*(B1:B10="B")*(C1:C10="C")*1) HTH, Bernie MS Excel MVP "Gunnar Sandstr´┐Żm" <gunnar.sandstrom@ericsson.com> wrote in message news:c9n09c$hh2$1@newstree.wise.edt.ericsson.se... > Hi! > Can i make a formula that only shows ...

Showing Percent
I have a column chart showing count on the Y axis and Categories on the X axis. Is there a way to show the percent for each category above each column? I looked at Chart Options - Data Labels and the Show Percent is grayed out. Thanks! Steve Debra, Thank you for the help - it works great! The nice thing about it is that once the add-in is applied to a worksheet, you can copy the workbook to another computer that doesn't have the add-in and the labels will also work there. Steve "Debra Dalgleish" <dsd@contextures.com> wrote in message news:3F19C51C.4060403@contextur...

Mobile adevices not showing up in mobil admin
Several people have used there devices to sync to our exchange 2003 server. The problem is when I use the mobile admin web page I never see any activity under the transaction log. I also get an error when I go to the remote wipe page and try to lookup a user. Any ideas of what might be going on? TIA Josh "Josh" <J o S H@computers.com> wrote: >Several people have used there devices to sync to our exchange 2003 server. >The problem is when I use the mobile admin web page I never see any activity >under the transaction log. Unless you told it to "wipe...

Print not showing on paper when printed
-- To be myself is the most important thing!!! Interesting! Using white ink? No print area assigned? Please provide some details. Gord Dibben MS Excel MVP On Wed, 24 Jun 2009 16:26:01 -0700, Citripio <citripio@discussion.microsoft.com> wrote: A couple more.............. Printer cartridge(s) out of ink? Font is colored white and you are printing in color? Gord On Wed, 24 Jun 2009 16:36:54 -0700, Gord Dibben <gorddibbATshawDOTca> wrote: >Interesting! > >Using white ink? > >No print area assigned? > >Please provide some details. > > >...

existing accounts not showing up on net worth reports
I have existing accounts set up, Cd's, that i have transfered to new accounts, cd's at another institution. the report for net worth over time fails to show the original cd's after the transfer. How should I have done this diferently? -- DR In microsoft.public.money, DR wrote: >I have existing accounts set up, Cd's, that i have transfered to new >accounts, cd's at another institution. the report for net worth over time >fails to show the original cd's after the transfer. How should I have done >this diferently? Forgetting for the moment how you...

How to check continuous form fields
I have a tabbed form, when I switch from one tab to another, it checks to make sure that none of the mandatory fields are missing. However the code that checks the records on tab #1 (which is the code below after the elseif), the code only checks the first record. I can not figure out how to modify the code to get it to look at other records on the form that may be present. Any suggestions on what modification I would need to make here would be helpful. thanks! If TabCtl4 = 1 Then Dim CkCtls As New Collection CkCtls.Add "DRNo" ...

Sent email show up in Inbox
Hi Why do all my sent emails from Outlook show up in my Inbox. That is reflected in OE and in Windows Live Mail. Thanks Don What does OE have to do with Outlook's Inbox? Where are you sending the mail from (seems like Outlook) but which Inbox is it showing up in? "Don S." <dis933@hotmail.com> wrote in message news:ujBcXaWRKHA.4692@TK2MSFTNGP06.phx.gbl... > Hi > > Why do all my sent emails from Outlook show up in my Inbox. That is > reflected in OE and in Windows Live Mail. Using a Gmail account via POP3? Perfectly normal then; http://mail.googl...

Check to see if a formula is correct
I want to do something like this Sub Macro1() If Range("A1") =(=B1 + B2) Then ' This is a formula Exit Sub Else Range("C1").Select 'Has correct formula Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas End If End Sub How do I do this? Hi Try Sub Macro1() If Range("A1").HasFormula Then Exit Sub Else Range("C1").Copy Range("A1").PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False ...

Problem With Deferral Check Links
Hi.. When i Do Deferral Check Links in File Maintenance. I am getting an error message "A remove range operation on table 'PP_File_Maintenance_Error_Log' cannot find the table. On clicking the more Info button. These messages are displayed " [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'TEST.dbo.zDP_PP400004L_1'. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TEST.dbo.PP400004'. " In the database these two objects are not present. Can You Pl. tell me what to do about it. Thanks These do not...

Urgent !!!!!
I tape all data and add the font colour, but when i preview file & print , it can't show the colour, why? Could it be that you are previewing a print for a black-and-white printer? Kind regards, Marcel Kreijne Quandan - Steunpunt voor spreadsheetgebruikers (Quandan - dutch supportsite for spreadsheetusers) www.quandan.nl "Eva" <anonymous@discussions.microsoft.com> schreef in bericht news:5812EE04-51AB-4AB7-8A80-C9F6994C8B97@microsoft.com... > I tape all data and add the font colour, but when i preview file & print , it can't show the colour, why? See ...

Need help with showing Totals
I have a clustered cylinder chart. Along the x-axis, I have the average test score per exam per class. For example, in the first cluster I have the average test score for five exams in the Science class. In the second cluster, I have the average test score for five exams in the Mathematics class, etc. For each average test score, I would like to display the total number of students that took the test above each score. In looking through several of the previous answers in this discussion group, I think I need to add a dummy series and plot the total number of students aga...

Area Chart showing percentage?
I'm a newbie that created an Area Chart using the Graph Wizard. It's almost what I need, except the Y-axis shows an accumulated count of the values. What I need is to normalized the values so that they represent a percentage. E.g., the total across the X-Axis is always 100% and the chart shows the percentage contribution of each value being plotted. 100--------------------------------- 80___ _________ _ 60 \/ /\ \___/ 40 /\______/ \ __ 20/ \_____/ 0---------------------------------- The SQL generated by the Wizard: TRANSFO...

Re: Account Won't Show Up on Home Page as a Favorite
For some reason this message appears to have been deleted from the server. I'm reposting it. Kevin "Kevin Campbell" <kcampbel@nospam.midmaine.com> wrote in message news:... > I have a Home Equity Line of Credit account that, for some reason, will not > show up on my list of favorite accounts on the Home page in Money 2003. In > the account details I have the Add to Favorites checkbox checked and under > the Favorite Accounts menu item it does show up. Also, when I am in the > account register view and I click on the account name to drop the list of > ac...

Contacts Permissions Problem
Hi- I am running OL2003 on WinXP & 2k with Exchange 2k. I have a large public folder of contacts. I can edit all of them. I created a new user, gave her owner permissions on the folder, but she cannot edit many of the contacts. When she opens them, they show as <read only>. Today I was researching this and I found something interesting - some of the records show Outlook Version 10, and others show Outlook Version 11. It turns out that the recs that are version 10 can be edited by the new user, but those that are version 11 can only be opened as read only. I tried to change the ve...

Check out http://hackityourself.com
Guys, *Check out http://hackityourself.com* They hack email and myspace accounts for like $50, i used um before. Looks like their price even went down a few bucks. ...

Spell Check in Outlook Express
Running a nDell Dimension 2400. When I spell check e- mail in OE I get,"An error occurred whiole the spelling was being checked.". When I set the option to always spell check before send, I get the message, "The spell check on the document was halted. Do you want to send anyway?" Can someone solve this for me. I will be grateful. This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x m...

P/R adjustments after Calculate Checks
Hi, After Transactions>>Payroll>>Calculate Checks, is there an "easy" way to change/adjust the deduction amounts (ie: Federal W/H) prior to printing the checks. We are trying to do a parallel (catch-up) P/R run and changing the amounts prior to posting. Or.. should we post then use the Payroll Manual Check - Adjustment, to change it after posting. Thanks There is no way to update the information after you calculate paychecks unless you remove the build; make the adjustments; and rebuild/calculate. If you needed to determine a garnishment amount, for example, th...

Checking on two values
To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes Hei Johannes COUNTIF takes only one criteria. You can do this wit...

Get space between a checkbox and text in a CButton with BS_AUTOCHECKBOX
Hi, I have created a CButton with BS_AUTOCHECKBOX style and I would like to be able to resize my control to fit the text. So I know how to calculate the text width but how can I know the space taken by the checkbox + the space bewteen the checkbox and my text ? THe way I solved this was to create, on my dialog, a button which had no characters in it, just the space. This was a hidden, disabled control. Then I used this to get both the height and the width I needed for the basic button and added to the width the GetTextExtent value. This is the safest way to create buttons, because you c...

show pivot table detail in same cell
I would like to create a report using following sample data in excel ticket , monthly release , application 111-fix login bug , 2006-06 , ABC 112-improve logging , 2006-06 , ABC 113-enhance blah blah , 2006-07 , XYZ 114-fix performance issue , 2006-08 , ABC 115-implement cash app , 2006-08 , XYZ using this data I would like to create following report (application roadmap) i.e. which ticket would be implemented in which monthly release...

Show stdev errorbars on pivot bar chart
I have made a pivot bar chart using excel 2003, and I would like to show error bars on this bar chart. The values for the bars are averages of a certain quantity. The values for the errorbars should be the stdev of this quantity. It is possible to add error bars in the existing chart using "Format data series", but that only works for one particular chart. If I update the underlying data or plot other quantities, then the errorbars are gone. Does anyone know how to solve this problem? Like all custom formatting in pivot charts, refreshing the data clears the formatting. Microsof...