By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Not executing a IsNull scenario when writing a new record

kcdoell
100+
P: 230
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
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,599
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
100+
P: 230
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
100+
P: 230
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
Expert 5K+
P: 8,599
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
100+
P: 230
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
Expert 5K+
P: 8,599
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
Expert 5K+
P: 8,599
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
Expert Mod 15k+
P: 31,186
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
100+
P: 230
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
Expert Mod 15k+
P: 31,186
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
100+
P: 230
Yes, that was what I was thinking too...

Thanks ;-)
May 21 '08 #12

ADezii
Expert 5K+
P: 8,599
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
100+
P: 230
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
Expert 5K+
P: 8,599
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
Expert Mod 15k+
P: 31,186
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
100+
P: 230
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

Post your reply

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