Hello:
When I write new record I have the following code: -
Private Sub Form_BeforeUpdate(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_Percentage]) 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_Percentage]) 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........
-
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.
16 1945
Hello:
When I write new record I have the following code: -
Private Sub Form_BeforeUpdate(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_Percentage]) 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_Percentage]) 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........
-
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: -
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_Percentage]) 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
Try this approach, and see what happens: -
'allow code execution to fall through, the Cancel Argument will
-
'be set to False by Default, no need to explicitly state it
-
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.
ADezii:
I was looking for what is different about [ProductIDFK]
I do have its row source set to: - SELECT ProductID, ProductName, WrkRegIDFK
-
FROM tblProduct
-
INNER JOIN tblProdWrkRegMM ON tblProduct.ProductID=tblProdWrkRegMM.ProductIDFK
-
WHERE (((tblProdWrkRegMM.WrkRegIDFK)=forms.dataentry.cbowrkreg));
-
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:
I was looking for what is different about [ProductIDFK]
I do have its row source set to: - SELECT ProductID, ProductName, WrkRegIDFK
-
FROM tblProduct
-
INNER JOIN tblProdWrkRegMM ON tblProduct.ProductID=tblProdWrkRegMM.ProductIDFK
-
WHERE (((tblProdWrkRegMM.WrkRegIDFK)=forms.dataentry.cbowrkreg));
-
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 - 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.
- I'm assuming that [ProductID] is the Bound Column for the Combo Box ProductIDFK, is this correct?
- 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?
To the best of my knowledge, No- 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.
- I'm assuming that [ProductID] is the Bound Column for the Combo Box ProductIDFK, is this correct?
- 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.
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.
-
Private Sub cmdOpenForeC_Click()
-
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
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?
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 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 :)
Yes, that was what I was thinking too...
Thanks ;-)
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.
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:
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: -
If IsNull(Me![ProductIDFK]) Or Me![ProductIDFK] = 0 Then
-
Msgbox "Missing Blah, Blah, Blah..."
-
Cancel = True
-
Exit Sub
-
...
Let me know how you make out.
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. - If Nz(Me.ProductIDFK, 0) = 0 Then
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: -
If IsNull(Me![ProductIDFK]) Or Me![ProductIDFK] = 0 Then
-
Msgbox "Missing Blah, Blah, Blah..."
-
Cancel = True
-
Exit Sub
-
...
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |