Validating Subform Data

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
I have a form which is capturing contact details for clients. We need to record 3 forms of identification for clients and so I have a separate table to capture the identification information.

The main form captures the contact information and the subform is used to capture the identification information for the contact. The subform is bound to the main form using the ContactID. The other fields on the subform are: IdentificationType(combo box which looks up the ID types from tblIdentificationType), IdentificatioNumber, Date Issued and Date of Expiry.

I have a save button on the form which is used to save the contact's record.

I have added code in the BeforeUpdate event of the subform to ensure that the ID Type, ID number and Date Issued are entered but that does not work when I click the Save button on the form and I am able to save and exit without entering identification information for the contact.

So before saving the record, I want to make sure that identification information has been entered on the subform.

If it is possible, I would also like to get some code to make sure that 3 identification records are captured for the contact.

Thanks for any assistance.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Hi,

This link seems to be a good idea:
http://www.databasedev.co.uk/validate_textbox.html

Edit: note that in the link above only textboxes are validated, but the code could be edited for comboboxes too:
Code:
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then

------------

Alternatively, you can place your own script in the before update form event, such as:

Code:
myMsg = "Error: Record could not be saved: " 
msg = myMsg & vbnewline
[COLOR="Navy"]If[/COLOR] Trim(Me.ComboBox1.Value & "") = "" [COLOR="Navy"]Then[/COLOR]
    msg = msg &  "ComboBox1 cannot be left Blank"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] Trim(Me.TextBox1.Value & "") = "" [COLOR="Navy"]Then[/COLOR]
   msg = msg & "TextBox1 cannot be left Blank"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] Trim(Me.TextBox2.Value & "") = "" [COLOR="Navy"]Then[/COLOR]
   msg = msg & "TextBox2 cannot be left Blank"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] msg <> myMsg [COLOR="Navy"]Then[/COLOR]
    Cancel = True [COLOR="SeaGreen"]'//Cancel Save[/COLOR]
    MsgBox msg
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]


Not sure about the 3 forms being given - that seems to depend on the structure of your data entry. Are they entered in three separate locations on the same form, or entered one by one in turn?
 
Last edited:

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
Hi!

I have actually used the code from that article Validating Required Data in Microsoft Access Text boxes to validate my form.

I have a main form called frmContacts where contact information is entered. I have set up the main form and added the validation using the method from the article. The validation for the main form works perfectly.

I have a subform called fsubContactIdentification and I have also added the validation code to this form. I have the information for the contact identification in a separate table because each contact will have 3 identification records.

The subform fsubContctIDentification is on the main form frmContacts. When I enter a contact record, identification information also needs to be captured. My problem:

The code works to validate information on the main form, however the validation does not work for the subform.

For the other part of my initial post, I wanted to also add validation to ensure that users enter 3 contact identification records as 3 forms of ID needs to be presented for each contact.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
You may want to script the Before Update event of the subform, then (this event will fire before the subform updates) - that is, write your own validation code to check that all the fields are filled in. Or, somewhat simpler, set rules on the table that the subform updates (such as Allow Nulls = No, required = Yes, and possible other custom validation rules in the table definition).

The problem with the article's code (I guess) is that you'd need to access the controls of the form property of the subform controls - it gets a little confusing.

With regard to the 3 forms of indentification, its a bit of a sticky one. You will have only 1 or 2 forms of Identification at the point where 1 has been entered by not 2 or 3, and when 1 and 2 have been entered but not 3. Right now, I'm not sure how I'd approach this. Maybe someone else would like to chime in ... :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,555
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top