Quick Warning To Users When A Field Is Left Empty

[greensleeves149]

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:

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]

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]

Why not disable the subform until an entry is in the combo?, then enable when combo is completed?

 

[arnelgp]

you can also use the Subform's BeforeInsert event to show the message:

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

 

[greensleeves149]

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.

 

[greensleeves149]

Why not disable the subform until an entry is in the combo?, then enable when combo is completed?

Thanks Gasman. I didn't think of going about it that way. That's a nice clean way of doing it.

 

[greensleeves149]

you can also use the Subform's BeforeInsert event to show the message:

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]

@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]

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.

 

[PaquettePaul]

Ah, thanks