Validating data

Question

I have an data entry form from a table in one database that is linked to a table in a second database.  The link is on a date field that is set for month and year.  In my data entry form, if the data has not been entered yet into the table in the other database, when the person leaves the field, they should get a warning that the data is not yet in the other table so therefore the record cannot be saved.  I can't figure out the best way to do that.

Any suggestions?

Answer 1

Hi. In you data entry form, is the user supposed to enter that other information for the other table? Data validation can be done in the form's BeforeUpdate event.

Answer 2

Yes - the data should have been entered into the main table first.  This data entry form would be the second piece.  I figured it would be on the before update event but am not sure how to either write the VBA or if it can it be done in a macro.

Answer 3

You can use either VBA or macro, but my question was "is the user supposed to enter the data for the other table in the same form you're trying to validate?" If so, then you can simply check if that control is empty or not. Hope that makes sense...

Answer 4

"I have an data entry form from a table in one database that is linked to a table in a second database.  The link is on a date field that is set for month and year."

A couple of design-related questions spring to mind here.

1. Are you really using two databases, and is the table in the second database a linked table in the first? If so, I assume this is by necessity? (Otherwise it adds some complexity.)
   
2. A date field with "month and year" is an unusual way to relate tables. Are you referring to a foreign key relationship between the two tables, or do you mean that the date field is on a subform of your main form, and the forms are linked by date?

Answer 5

1.  Yes - I have an data entry table set up in a form that needs to be compared to a date field in a separate table from another database that is linked into the one collecting the database entry.  The information is simple categorizing the record by month which is why the fields are formatted to year-month.  Neither of the tables have a key (foreign or primary) set to this field.  The reason for the validation is for reporting.  If my users enters info into the data entry form but the record it needs to link to in the separate table/database (primary reporting database) - then my primary database will not see that record, therefore missing it in the reporting.  So I am trying to make sure if they are doing the data entry - that I can somehow alert the user that the main table has not been updated with that date information yet.  I know its kind of odd.

Answer 6

Hi. I am not sure if you've answered my question yet. Can you post a screenshot of your form and point out where this date is? Thanks.

Answer 7

Hi seebert,

>> I figured it would be on the before update event but am not sure how to either write the VBA or if it can it be done in a macro

If you want to check the value before update the table, you could use Before Change events on the table.
Here is a simple demo. “Hyper” is the field you want to check.

You could refer the link below for more information.

#Validating User Input With the Before Change Data Macro Event in Access 2010

https://msdn.microsoft.com/en-us/library/office/gg549169(v=office.14).aspx

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 8

Thank you Edward - that is a really helpful resource.  I don't think it solves my problem.  When a record is entered into table one - I want it to look and see if a field in a second table already contains that same info.  So for example, if my user enters in 2015-09, then I want it to check a field in the second table to make sure a record with the date field (which will be linked to the data entry field for an append later on) exists.  I know it sounds a little backward but it is a necessary validiation because if the date is entered into the data entry form but a record using that date has not been created in the main table, then when it comes time append the data, the data from the data entry table will not upload because it needs that connection.  Does that make sense?

Answer 9

Hi. You know one other option to validate data without using code is to use a Combobox for data entry. That way, the user can only enter valid information. Just a thought...