473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Not executing a IsNull scenario when writing a new record

kcdoell
230 100+
Hello:

When I write new record I have the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. Me!RowIsActive = False
  4.  
  5. 'When a user is creating a new record the following code inserts the MonthID, YearID and
  6. 'The LocationsID.  It does a Dlookup for the Locations ID when the control cboLocation is
  7. 'blank.
  8.  
  9. 'Make sure required fields are filled out first
  10.  
  11. Dim frm As Form
  12. Set frm = Forms!Forecast
  13.  
  14. If IsNull(frm![Policy_Type]) Or IsNull(frm![Insured_Name]) _
  15. Or IsNull(frm![ProductIDFK]) Or IsNull(frm![Binding_Percentage]) Then
  16.  
  17. If IsNull(frm![Policy_Type]) Then MsgBox "Please select a Policy Type from" & _
  18.     " the list, thank you", 32, "Select a Policy Type"
  19.  
  20. If IsNull(frm![Insured_Name]) Then MsgBox "Please indicate who the Insured" & _
  21.     " is, thank you", 32, "Select an Insured"
  22.  
  23. If IsNull(frm![ProductIDFK]) Then MsgBox "Please choose a LOB from the" & _
  24.     " list, thank you", 32, "Select an LOB"
  25.  
  26. If IsNull(frm![Binding_Percentage]) Then MsgBox "Please choose a Binding Percentage from" & _
  27.     " the list, thank you", 32, "Select a Binding Percentage"
  28.  
  29. Cancel = True
  30. Else: Cancel = False
  31. End If
  32.  
  33. 'If all req fields are populated then proceed to write the record to the forecast table.
  34.  
  35. If Me.NewRecord Then
  36. 'etc........
  37.  
My problem is when [ProductIDFK] is blank the record still writes. That is to say I never get a warning up front like I do for [Policy_Type], [Insured_Name] or [Binding_Percentage]

I have looked at the name of the control on the form via its properties and it is called [ProductIDFK]. What could it be??

Any help would be great....

Keith.
May 19 '08 #1
16 1945
ADezii
8,834 Expert 8TB
Hello:

When I write new record I have the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. Me!RowIsActive = False
  4.  
  5. 'When a user is creating a new record the following code inserts the MonthID, YearID and
  6. 'The LocationsID.  It does a Dlookup for the Locations ID when the control cboLocation is
  7. 'blank.
  8.  
  9. 'Make sure required fields are filled out first
  10.  
  11. Dim frm As Form
  12. Set frm = Forms!Forecast
  13.  
  14. If IsNull(frm![Policy_Type]) Or IsNull(frm![Insured_Name]) _
  15. Or IsNull(frm![ProductIDFK]) Or IsNull(frm![Binding_Percentage]) Then
  16.  
  17. If IsNull(frm![Policy_Type]) Then MsgBox "Please select a Policy Type from" & _
  18.     " the list, thank you", 32, "Select a Policy Type"
  19.  
  20. If IsNull(frm![Insured_Name]) Then MsgBox "Please indicate who the Insured" & _
  21.     " is, thank you", 32, "Select an Insured"
  22.  
  23. If IsNull(frm![ProductIDFK]) Then MsgBox "Please choose a LOB from the" & _
  24.     " list, thank you", 32, "Select an LOB"
  25.  
  26. If IsNull(frm![Binding_Percentage]) Then MsgBox "Please choose a Binding Percentage from" & _
  27.     " the list, thank you", 32, "Select a Binding Percentage"
  28.  
  29. Cancel = True
  30. Else: Cancel = False
  31. End If
  32.  
  33. 'If all req fields are populated then proceed to write the record to the forecast table.
  34.  
  35. If Me.NewRecord Then
  36. 'etc........
  37.  
My problem is when [ProductIDFK] is blank the record still writes. That is to say I never get a warning up front like I do for [Policy_Type], [Insured_Name] or [Binding_Percentage]

I have looked at the name of the control on the form via its properties and it is called [ProductIDFK]. What could it be??

Any help would be great....

Keith.
Try this approach, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2. Set frm = Forms!Forecast
  3.  
  4. If IsNull(frm![Policy_Type]) Then
  5.   MsgBox "Please select a Policy Type from" & _
  6.          " the list, thank you", 32, "Select a Policy Type"
  7.     Cancel = True
  8.       Exit Sub
  9. ElseIf IsNull(frm![Insured_Name]) Then
  10.   MsgBox "Please indicate who the Insured" & _
  11.          " is, thank you", 32, "Select an Insured"
  12.     Cancel = True
  13.       Exit Sub
  14. ElseIf IsNull(frm![ProductIDFK]) Then
  15.   MsgBox "Please choose a LOB from the" & _
  16.          " list, thank you", 32, "Select an LOB"
  17.     Cancel = True
  18.       Exit Sub
  19. ElseIf IsNull(frm![Binding_Percentage]) Then
  20.   MsgBox "Please choose a Binding Percentage from" & _
  21.          " the list, thank you", 32, "Select a Binding Percentage"
  22.     Cancel = True
  23.       Exit Sub
  24. Else
  25.   'allow code execution to fall through, the Cancel Argument will
  26.   'be set to False by Default, no need to explicitly state it
  27. End If
May 20 '08 #2
kcdoell
230 100+
Try this approach, and see what happens:
Expand|Select|Wrap|Line Numbers
  1.   'allow code execution to fall through, the Cancel Argument will
  2.   'be set to False by Default, no need to explicitly state it
  3. End If
ADezii:

Your method is a lot cleaner than mine was, I made the change but still have the problem. I also set the properties of the validation rule in the control itself to Is Not Null. The interesting thing is that it will allow me to write the record with the [ProductIDFK] blank even though it is a required field with the above code etc.. but once it is written and I was to select a product from [ProductIDFK] and then blank it out, I get the warning message that it can not be null.....

What is that all about....

Thanks for getting back to me.

Keith.
May 20 '08 #3
kcdoell
230 100+
ADezii:

I was looking for what is different about [ProductIDFK]

I do have its row source set to:

Expand|Select|Wrap|Line Numbers
  1. SELECT ProductID, ProductName, WrkRegIDFK 
  2. FROM tblProduct 
  3. INNER JOIN tblProdWrkRegMM ON tblProduct.ProductID=tblProdWrkRegMM.ProductIDFK 
  4. WHERE (((tblProdWrkRegMM.WrkRegIDFK)=forms.dataentry.cbowrkreg));
  5.  
So that it will only give me a drop down list of the [ProductIDFK] that are tied to the Working Region. This should not make a difference, correct??

Thanks,

Keith.
May 20 '08 #4
ADezii
8,834 Expert 8TB
ADezii:

I was looking for what is different about [ProductIDFK]

I do have its row source set to:

Expand|Select|Wrap|Line Numbers
  1. SELECT ProductID, ProductName, WrkRegIDFK 
  2. FROM tblProduct 
  3. INNER JOIN tblProdWrkRegMM ON tblProduct.ProductID=tblProdWrkRegMM.ProductIDFK 
  4. WHERE (((tblProdWrkRegMM.WrkRegIDFK)=forms.dataentry.cbowrkreg));
  5.  
So that it will only give me a drop down list of the [ProductIDFK] that are tied to the Working Region. This should not make a difference, correct??

Thanks,

Keith.
So that it will only give me a drop down list of the [ProductIDFK] that are tied to the Working Region. This should not make a difference, correct??.
To the best of my knowledge, No
  1. I'm assuming that the [ProductIDFK] Field is a LONG INTEGER, is this correct? If it is a STRING, and you allow the Allow Zero Length String Property to Yes, I imagine that this could be the source of the problem.
  2. I'm assuming that [ProductID] is the Bound Column for the Combo Box ProductIDFK, is this correct?
  3. If all else fails, would you be willing to E-Mail me a subset of the Database, and I would be glad to have a look at it?
May 20 '08 #5
kcdoell
230 100+
To the best of my knowledge, No
  1. I'm assuming that the [ProductIDFK] Field is a LONG INTEGER, is this correct? If it is a STRING, and you allow the Allow Zero Length String Property to Yes, I imagine that this could be the source of the problem.
  2. I'm assuming that [ProductID] is the Bound Column for the Combo Box ProductIDFK, is this correct?
  3. If all else fails, would you be willing to E-Mail me a subset of the Database, and I would be glad to have a look at it?
Yes, [ProductIDFK] Field is a LONG INTEGER in which I have it bound to [ProductID] for the combo box on the table itself.

When I placed it on the form, I went into the properties of [ProductIDFK] and applied the above mentioned filter to select only Products that are tied to that particular Working Region displayed on the form.

Does that additional info help?

Thanks,

Keith.
May 20 '08 #6
ADezii
8,834 Expert 8TB
Yes, [ProductIDFK] Field is a LONG INTEGER in which I have it bound to [ProductID] for the combo box on the table itself.

When I placed it on the form, I went into the properties of [ProductIDFK] and applied the above mentioned filter to select only Products that are tied to that particular Working Region displayed on the form.

Does that additional info help?

Thanks,

Keith.
I'm sort of at a Dead End, Keith. Would you be willing to send me the Database, or a subset of it, as an E-Mail Attachment? If so, I would look at it as soon as possible and I can guarantee complete confidentiality. I believe the problem has a very simple solution, I just don't know what that solution is at this time (LOL). With the actual DB in front of me, thinks may become quite clearer.
May 20 '08 #7
ADezii
8,834 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenForeC_Click()
  2. If IsNull(Me![cboDivision]) Then
  3.   MsgBox "Please select the Division from the" & _
  4.          " drop down menu", 32, "Select the Division"
  5.            Me![cboDivision].SetFocus
  6.            Me![cboDivision].Dropdown
  7.              Exit Sub
  8. ElseIf IsNull(Me![cboWrkReg]) Then
  9.   MsgBox "Please select the Working Region from" & _
  10.          " the drop down menu", 32, "Select Working Region"
  11.            Me![cboWrkReg].SetFocus
  12.            Me![cboWrkReg].Dropdown
  13.              Exit Sub
  14. ElseIf IsNull(Me![cboCreditReg]) Then
  15.   MsgBox "Please select the Credit Region from" & _
  16.          " the drop down menu", 32, "Select Credit Region"
  17.            Me![cboCreditReg].SetFocus
  18.            Me![cboCreditReg].Dropdown
  19.              Exit Sub
  20. ElseIf IsNull(Me![CboYear]) Then
  21.   MsgBox "Please select the Forecast Year from the" & _
  22.          " drop down menu", 32, "Select Forecast Year"
  23.            Me![CboYear].SetFocus
  24.            Me![CboYear].Dropdown
  25.              Exit Sub
  26. ElseIf IsNull(Me![CboMonth]) Then
  27.   MsgBox "Please select the Forecast Month from the" & _
  28.          " drop down menu", 32, "Select Forecast Month"
  29.            Me![CboMonth].SetFocus
  30.            Me![CboMonth].Dropdown
  31.              Exit Sub
  32. Else
  33.   'If no null values, opens the Forecast Subform and set
  34.   'the DataEntry Menu to invisible
  35.   DoCmd.OpenForm "Forecast", acNormal
  36.   Forms!DataEntry.Visible = False
  37. End If
  38. End Sub
May 21 '08 #8
NeoPa
32,556 Expert Mod 16PB
I presume this file is not sensitive or private ADezii? I know the OP often has issues with privacy of data. Let me know if this needs removing.

PS. I didn't get what the problem turned out to be but I would guess it's to do with Allowing Null values in the field setup?
May 21 '08 #9
kcdoell
230 100+
I presume this file is not sensitive or private ADezii? I know the OP often has issues with privacy of data. Let me know if this needs removing.

PS. I didn't get what the problem turned out to be but I would guess it's to do with Allowing Null values in the field setup?

I sent ADezii an e-mail to remove. I don't mind the code but I do mind the DB since I am the creator and I sent it to him privately. Hopefully this can be resolved sooner than later.

Keith.
May 21 '08 #10
NeoPa
32,556 Expert Mod 16PB
No worries Keith. You can always PM a moderator or admin if this sort of issue arises again.

I'm sure this was a simple oversight / misunderstanding. ADezii often goes to extraordinary lengths to help people out. I'm sure he'll be upset with himself when he realises what he did.

Anyway, it's sorted now :)
May 21 '08 #11
kcdoell
230 100+
Yes, that was what I was thinking too...

Thanks ;-)
May 21 '08 #12
ADezii
8,834 Expert 8TB
At this time, I wish to make a public apology to both NeoPa and kcdoell for my inappropriate posting of a problematic Database. I was totally under the wrong assumption, and posted some results which should have remained private, even after I assured kcdoell that confidentially would be guaranteed. My actions, although not intentional, were totally inexcusable. Kindly accept my apology.

P.S, - Thanks NeoPa for covering my back and removing the DB.
May 21 '08 #13
kcdoell
230 100+
At this time, I wish to make a public apology to both NeoPa and kcdoell ........
ADezii:

Apology accepted and so the saying goes “Things happen” well, you know what I mean…..

Now back to the issue at hand. My problem is still gnawing at me. I believe it is in the before update event on my Form called “Forecast”. The problem only happens when a new record is created. That is to say if I was to blank out the [ProductIDFK] field on an existing record being displayed the msgbox in my code displays and indicates that [ProductIDFK] can not be Null but if I write a new record via my Form “Forecast” it allows it to be created with the [ProductIDFK] blank. I tried creating a new [ProductIDFK] on my table, reinserting it into my form and the same thing happens.

Let me know if you see something.

Thanks,


Keith.
May 21 '08 #14
ADezii
8,834 Expert 8TB
ADezii:

Apology accepted and so the saying goes “Things happen” well, you know what I mean…..

Now back to the issue at hand. My problem is still gnawing at me. I believe it is in the before update event on my Form called “Forecast”. The problem only happens when a new record is created. That is to say if I was to blank out the [ProductIDFK] field on an existing record being displayed the msgbox in my code displays and indicates that [ProductIDFK] can not be Null but if I write a new record via my Form “Forecast” it allows it to be created with the [ProductIDFK] blank. I tried creating a new [ProductIDFK] on my table, reinserting it into my form and the same thing happens.

Let me know if you see something.

Thanks,


Keith.
Here is a stretch! Since [ProductIDFK] is a LONG INTEGER Field bound to [ProductID], by Default (unless otherwise changed by you), it will have a Default Value of 0 for any given New Record and will pass the IsNull() validation. Either check the Default Value Property of the [ProductIDFK] Field to make sure it is not set to 0, or add the following Validation Code:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me![ProductIDFK]) Or Me![ProductIDFK] = 0 Then
  2.   Msgbox "Missing Blah, Blah, Blah..."
  3.     Cancel = True
  4.       Exit Sub
  5. ...
Let me know how you make out.
May 21 '08 #15
NeoPa
32,556 Expert Mod 16PB
I wouldn't even entertain the idea that it was done for any reason other than your temporarily losing the plot in an inattentive moment. This happens to us all from time-to-time.

I would just offer an alternative for your first line. Some don't like using the Nz() function, but otherwise it's a more compact version.
Expand|Select|Wrap|Line Numbers
  1. If Nz(Me.ProductIDFK, 0) = 0 Then
May 22 '08 #16
kcdoell
230 100+
Here is a stretch! Since [ProductIDFK] is a LONG INTEGER Field bound to [ProductID], by Default (unless otherwise changed by you), it will have a Default Value of 0 for any given New Record and will pass the IsNull() validation. Either check the Default Value Property of the [ProductIDFK] Field to make sure it is not set to 0, or add the following Validation Code:
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me![ProductIDFK]) Or Me![ProductIDFK] = 0 Then
  2.   Msgbox "Missing Blah, Blah, Blah..."
  3.     Cancel = True
  4.       Exit Sub
  5. ...
Let me know how you make out.
The reason why it was happening makes complete sense to me now. In fact, Yesterday I said to myself that that somehow the DB did believe that there was a value being slected in [ProductIDFK]; it was the only thing that made sense to why it was happening.

Both solutions solved the problem.

Thanks a lot!!!

Keith.
May 22 '08 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Jay Bienvenu | last post by:
I'm having a very strange problem that currently defies explanation. I use the code below to send a status report to a client. The intent is to send the user the report via fax using WinFax and/or...
17
by: jdph40 | last post by:
Question 1: In Access 97, I have a form (frmVacationWeeks) with a subform (sbfrmPostVacDates). The subform can have up to 33 records and each record has 2 checkboxes, one for approved and one for...
0
by: NOSPAM | last post by:
Guys, I get the 'There was an error executing the command' error message. I an using win xp & Access 2002. I created a database using the MS Access template 'Order Entry' I have entered...
2
by: Dave | last post by:
I have an Access2K table in which several records have nulls in some fields. In code that iterates through this table the statement If Not IsNull() Then ... (or If IsNull() Then ...) is...
1
by: Peter Neumaier | last post by:
Hi! I'm wondering whether it's possible to set up the MS SQL function ISNULL() as a default value to avoid NULL entries when importing data into a table?! For example, I want the column1, to...
7
by: tshad | last post by:
I thought I understood how the SaveViewState is working and was trying to use this (as per some code I found) to detect refreshes. It seemed to be working but I found that the SaveViewState was...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
29
by: =?Utf-8?B?SGVybWF3aWg=?= | last post by:
Hello, Please anybody help me. I have only a little experience with web development. I created simple project using ASP NET 2.0 (VS 2005) It works fine on local computer. When I tried to run...
0
Frinavale
by: Frinavale | last post by:
I have a peculiar problem... Background: I have a function that I don't want the user to execute more than once while they are waiting for it to process; therefore, I disable all of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.