Quick Warning To Users When A Field Is Left Empty (1 Viewer)

Local time
Today, 14:35
Joined
Feb 27, 2022
Messages
49
Hi all.

I've got a form with combo box the main form and also have a subform. The combo box stores a numerical "ID" of the control whilst displays the text value.

I was just wanting a simple messagebox when someone enters data in one of the fields in the subform and the user has forgotten to select something in the combobox on the main form.

I've tried this on the AfterUpdate of one of the controls in the subform:
Code:
Dim supplierblank As Integer
supplierblank = Forms!frmInvestigation.cboSelectSupplier
If supplierblank Is Null Then
MsgBox "Hey you forgot to add a Supplier!", vbExclamation
End If

I get "Compile Error: Type Mismatch" when trying to test the form with the code. I thought "supplierblank" would be "String" and not "Integer" as it's text in the Combo box and not a number that is displayed and tried that too, but that also fails.

Could someone please help me with this?

Thanking you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2013
Messages
14,740
your supplierblank type needs to be the same as the ID in your combo - ID's are usually long. also 'is null' works in queries, in vba you use the isNull function

you can simplify your code to one line

if isnull(Forms!frmInvestigation.cboSelectSupplier) then MsgBox "Hey you forgot to add a Supplier!", vbExclamation

also suggest the event you should be using is a control gotfocus event or even the mainform beforeupdate event
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:05
Joined
Sep 21, 2011
Messages
10,868
Why not disable the subform until an entry is in the combo?, then enable when combo is completed?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
16,874
you can also use the Subform's BeforeInsert event to show the message:
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = (Nz(Me!Parent!cboSelectSupplier, 0) = 0)
If Cancel
   Msgbox "Please select a supplier from the Main form."
End If
End Sub
 
Local time
Today, 14:35
Joined
Feb 27, 2022
Messages
49
your supplierblank type needs to be the same as the ID in your combo - ID's are usually long. also 'is null' works in queries, in vba you use the isNull function

you can simplify your code to one line

if isnull(Forms!frmInvestigation.cboSelectSupplier) then MsgBox "Hey you forgot to add a Supplier!", vbExclamation

also suggest the event you should be using is a control gotfocus event or even the mainform beforeupdate event
Thank you CJ_London for your tips and help too. I'll experiment with the gotfocus event- never had to use it before.
 
Local time
Today, 14:35
Joined
Feb 27, 2022
Messages
49
you can also use the Subform's BeforeInsert event to show the message:
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = (Nz(Me!Parent!cboSelectSupplier, 0) = 0)
If Cancel
   Msgbox "Please select a supplier from the Main form."
End If
End Sub
Awesome. Thank you arnelgp. Haven't used the BeforeInsert before either. I'll try that option too.
 

PaquettePaul

Member
Local time
Today, 01:05
Joined
Mar 28, 2022
Messages
72
@Armelgp
As soon as I enter the first character in the main form, the BeforeInsert process is invoked. So putting a validation there does not give the user time to go anywhere (unless he starts at the field in question) before the error condition is invoked. However, as soon as the user leaves the form to go to any other form, the automatic save kicks in and the BeforeUpdate process is invoked. If the validation occurs there with a Cancel = True statement on failure, then the user would not be able to leave the data entry form until all conditions have been satisfied. I only use the BeforeInsert process to fill in some default values if required. Do I understand your response correctly?

this way the sub form does not have to be enabled or disabled as was suggested as a course of action.

btw, I really liked your Cancel statement in the example.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:05
Joined
Feb 19, 2002
Messages
36,961
Paul,
Arnelgp is referring to the BeforeInsert event of the subform, not the main form. That is the easy way to prevent data entry in a subform when the parent record has not yet been created.
 

Users who are viewing this thread

Top Bottom