Not executing a IsNull scenario when writing a new record

[kcdoell]

Hello:

When I write new record I have the following code:

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

Me!RowIsActive = False

'When a user is creating a new record the following code inserts the MonthID, YearID and
'The LocationsID. It does a Dlookup for the Locations ID when the control cboLocation is
'blank.

'Make sure required fields are filled out first

Dim frm As Form
Set frm = Forms!Forecast

If IsNull(frm![Policy_Type]) Or IsNull(frm![Insured_Name]) _
Or IsNull(frm![ProductIDFK]) Or IsNull(frm![Binding_Percent age]) Then

If IsNull(frm![Policy_Type]) Then MsgBox "Please select a Policy Type from" & _
" the list, thank you", 32, "Select a Policy Type"

If IsNull(frm![Insured_Name]) Then MsgBox "Please indicate who the Insured" & _
" is, thank you", 32, "Select an Insured"

If IsNull(frm![ProductIDFK]) Then MsgBox "Please choose a LOB from the" & _
" list, thank you", 32, "Select an LOB"

If IsNull(frm![Binding_Percent age]) Then MsgBox "Please choose a Binding Percentage from" & _
" the list, thank you", 32, "Select a Binding Percentage"

Cancel = True
Else: Cancel = False
End If

'If all req fields are populated then proceed to write the record to the forecast table.

If Me.NewRecord Then
'etc........
[/code]

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_Percent age]

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.

[ADezii]

Hello:

When I write new record I have the following code:

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

Me!RowIsActive = False

'When a user is creating a new record the following code inserts the MonthID, YearID and
'The LocationsID. It does a Dlookup for the Locations ID when the control cboLocation is
'blank.

'Make sure required fields are filled out first

Dim frm As Form
Set frm = Forms!Forecast

If IsNull(frm![Policy_Type]) Or IsNull(frm![Insured_Name]) _
Or IsNull(frm![ProductIDFK]) Or IsNull(frm![Binding_Percent age]) Then

If IsNull(frm![Policy_Type]) Then MsgBox "Please select a Policy Type from" & _
" the list, thank you", 32, "Select a Policy Type"

If IsNull(frm![Insured_Name]) Then MsgBox "Please indicate who the Insured" & _
" is, thank you", 32, "Select an Insured"

If IsNull(frm![ProductIDFK]) Then MsgBox "Please choose a LOB from the" & _
" list, thank you", 32, "Select an LOB"

If IsNull(frm![Binding_Percent age]) Then MsgBox "Please choose a Binding Percentage from" & _
" the list, thank you", 32, "Select a Binding Percentage"

Cancel = True
Else: Cancel = False
End If

'If all req fields are populated then proceed to write the record to the forecast table.

If Me.NewRecord Then
'etc........
[/code]

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_Percent age]

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:
[CODE=vb]
Dim frm As Form
Set frm = Forms!Forecast

If IsNull(frm![Policy_Type]) Then
MsgBox "Please select a Policy Type from" & _
" the list, thank you", 32, "Select a Policy Type"
Cancel = True
Exit Sub
ElseIf IsNull(frm![Insured_Name]) Then
MsgBox "Please indicate who the Insured" & _
" is, thank you", 32, "Select an Insured"
Cancel = True
Exit Sub
ElseIf IsNull(frm![ProductIDFK]) Then
MsgBox "Please choose a LOB from the" & _
" list, thank you", 32, "Select an LOB"
Cancel = True
Exit Sub
ElseIf IsNull(frm![Binding_Percent age]) Then
MsgBox "Please choose a Binding Percentage from" & _
" the list, thank you", 32, "Select a Binding Percentage"
Cancel = True
Exit Sub
Else
'allow code execution to fall through, the Cancel Argument will
'be set to False by Default, no need to explicitly state it
End If[/CODE]

[kcdoell]

Try this approach, and see what happens:
[CODE=vb]
'allow code execution to fall through, the Cancel Argument will
'be set to False by Default, no need to explicitly state it
End If[/CODE]

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.

[kcdoell]

ADezii:

I was looking for what is different about [ProductIDFK]

I do have its row source set to:

[code=sql]SELECT ProductID, ProductName, WrkRegIDFK
FROM tblProduct
INNER JOIN tblProdWrkRegMM ON tblProduct.Prod uctID=tblProdWr kRegMM.ProductI DFK
WHERE (((tblProdWrkRe gMM.WrkRegIDFK) =forms.dataentr y.cbowrkreg));
[/code]

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.

[ADezii]

ADezii:

I was looking for what is different about [ProductIDFK]

I do have its row source set to:

[code=sql]SELECT ProductID, ProductName, WrkRegIDFK
FROM tblProduct
INNER JOIN tblProdWrkRegMM ON tblProduct.Prod uctID=tblProdWr kRegMM.ProductI DFK
WHERE (((tblProdWrkRe gMM.WrkRegIDFK) =forms.dataentr y.cbowrkreg));
[/code]

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?

[kcdoell]

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.

[ADezii]

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.

[ADezii]

[CODE=vb]
Private Sub cmdOpenForeC_Cl ick()
If IsNull(Me![cboDivision]) Then
MsgBox "Please select the Division from the" & _
" drop down menu", 32, "Select the Division"
Me![cboDivision].SetFocus
Me![cboDivision].Dropdown
Exit Sub
ElseIf IsNull(Me![cboWrkReg]) Then
MsgBox "Please select the Working Region from" & _
" the drop down menu", 32, "Select Working Region"
Me![cboWrkReg].SetFocus
Me![cboWrkReg].Dropdown
Exit Sub
ElseIf IsNull(Me![cboCreditReg]) Then
MsgBox "Please select the Credit Region from" & _
" the drop down menu", 32, "Select Credit Region"
Me![cboCreditReg].SetFocus
Me![cboCreditReg].Dropdown
Exit Sub
ElseIf IsNull(Me![CboYear]) Then
MsgBox "Please select the Forecast Year from the" & _
" drop down menu", 32, "Select Forecast Year"
Me![CboYear].SetFocus
Me![CboYear].Dropdown
Exit Sub
ElseIf IsNull(Me![CboMonth]) Then
MsgBox "Please select the Forecast Month from the" & _
" drop down menu", 32, "Select Forecast Month"
Me![CboMonth].SetFocus
Me![CboMonth].Dropdown
Exit Sub
Else
'If no null values, opens the Forecast Subform and set
'the DataEntry Menu to invisible
DoCmd.OpenForm "Forecast", acNormal
Forms!DataEntry .Visible = False
End If
End Sub[/CODE]

[NeoPa]

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?

[kcdoell]

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.

[NeoPa]

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 / misunderstandin g. 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 :)

[kcdoell]

Yes, that was what I was thinking too...

Thanks ;-)

[ADezii]

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.

[kcdoell]

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.

[ADezii]

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:
[CODE=vb]
If IsNull(Me![ProductIDFK]) Or Me![ProductIDFK] = 0 Then
Msgbox "Missing Blah, Blah, Blah..."
Cancel = True
Exit Sub
...[/CODE]

Let me know how you make out.