Simple form validation before record is added to table, help please!

[schurst44]

Hi,

I've no experience in VBA programming and little experience with MS Access, however I've built a very simple table with several fields to keep a record of approved sub-contractors my business use.

I've created a main splash screen that a user can press a button "Add Sub-Contractor" and it opens up a blank form, as below:



I want to be able to set this form up with the following conditions:

1) The user can enter data on it that DOESN'T automatically get entered into the table.
2) When the "Back to Main" button is pressed, it will send the user back to the main splash screen WITHOUT the table being updated if there is text in any of the fields.
3) When the "Add Sub-Contractor" button is pressed, a series of validation rules are checked whcih if satisfied the data is added as a new record to the table as follows:



- 'Discipline' must be selected;
- 'Sub Contractor' name field must contain text & cannot be the same as an existing record in the table;
- 'Contact Name' must contain text;
- 'Address Line 1' must contain text;
- 'Address Line 2' must contain text;
- 'City' must contain text;
- 'Post Code' must contain text;
- 'Telephone' must contain text;
- 'E-mail' must contain a valid email;
- 'Approved (Y/N)' must be selected;
- 'Approval Date' must contain a date, and it cannot be in the future;
- 'PI Expiry' must contain a date IF 'PI Cover' is selected;
- 'EL Expiry' must contain a date IF 'EL Cover' is selected;
- 'PL Expiry' must contain a date IF 'PL Cover' is selected;
- 'Approval Review Date' must contain a date which must be in the future;

I had assumed that all of the above would be fairly simple to do with the built in validation methods in Access but it seems that because there are multiple conditions it may be something that is needed to be done in VBA.

I would be really grateful if anybody can provide me some code that can be manipulated to accommodate the above, or any help at all would be much appreciated.

Than you so much.

Spencer.

[ranman256]

Each control has a property to prevent user from leaving the field null.
(Allow null)

[davegri]

I would be really grateful if anybody can provide me some code that can be manipulated to accommodate the above, or any help at all would be much appreciated.

Not possible to provide code (let alone tested code) without knowing table names, field names and form names. You need to post the DB here to provide that information.

[schurst44]

Not possible to provide code (let alone tested code) without knowing table names, field names and form names. You need to post the DB here to provide that information.

Hi There.

Thanks for your response.

I can't upload the DB file as it's above the 500kb limit for the forum.

There is a single table within the database which is called 'Approved Sub-Contractors', it contains fields labelled:

Discipline
SubContractor
ContactName
AddressLine1
AddressLine2
AddressLine3
AddressLine4
City
PostCode
TelephoneNumber
Email
Approved
ApprovalDate
PICover
PIExpiry
ELCover
ELExpiry
PLCover
PLExpiry
ApprovalReviewDate
Notes

The following is a screenshot of the form which is titled 'ApprovedSCForm' and the labels of each item in the form match up with the headings of the field within the table.



Does that help?

Thank you for taking the time to read and respond to my initial query.

Spencer

[davegri]

Does that help?

A bit. But to provide code I would need to duplicate your form and table, making a few assumptions that might be wrong and it's a lot of work.
If I try to provide code without the above, it would undoubtedly contain bugs and/or syntax errors. Plus you say you have no experience with VBA, so would you know how to incorporate any provided standalone code into your DB?

In other words, need DB. If you compact/repair your db and then zip it, the size will drop dramatically.

[schurst44]

Thanks - I've compacted and repaired and zipped as the attached.

Approved SC Database.zip

Hope this is everything you need.

In response to your other queries, I've been able to modify bits of code to suit the simple things I've needed in the attached as you will see but no, syntax errors and so on I'd be no good at.

I really would like to learn the code though having started doing all of this. Is there a decent online tutorial with working examples that you could recommend for me?

Thanks

Spencer

[davegri]

- 'PI Expiry' must contain a date IF 'PI Cover' is selected;
- 'EL Expiry' must contain a date IF 'EL Cover' is selected;
- 'PL Expiry' must contain a date IF 'PL Cover' is selected;

The dropdowns allow "NONE". Does that require a date?

[schurst44]

Hi - If "None" is selected, there can be no date entered.

[davegri]

Approved SC Database-davegri-v01.zip

Have a look at this. It uses pretty extensive data checking and will not allow table updates until all data passes validation.

[schurst44]

Wow!

That is impressive. Thank you so much.

I have field tested it and all appears to look and work as I wanted with the exception of:

1) Approval review date - even when this field is populated and is a future date, an error message still pops up?
2) Assuming we can get that issue sorted, once all the fields are populated and the "Add Sub-Contractor" button is pressed so that the record gets added to the database, can we add a confirmation alert to say "Sub-contractor successfully added to the database" and when the user presses OK the user is sent back to the splash screen?

I can't thank you enough for what you've done for me, I'm truly grateful! Thank you so much!!

[davegri]

Approved SC Database-davegri-v02.zip

To get the sequence of events you want, I had to do a bit more work. The form is now unbound. The "Add Sub-contractor" button will run the validation and if all is OK, save the contents of the form to the table with an SQL INSERT statement.

[schurst44]

Hey, apologies for the late response, I've been gardening whilst the weather is nice here in the UK and I'm Furloughed from work due to this dreaded virus!

Everything works perfectly now, I was able to follow the code through and alter the wording of some of the alerts on validation also.

I really cannot thank you enough for your help, it must have taken you some time to do all of this for me, such a genuinely kind gesture!

Thanks again.

[davegri]

You're welcome. It did take some time, but we do have time now to help a little here, help a little there. Let's keep on keeping on.