How can I validate multiple values (copy+paste) against available selection in a drop

[waltera]

Hi,

I am new to using forms in Access, but my objective is to gather a bunch of information centrally from different users. So I have a table A that comprises information about client, data unit and employee entering the data and another one (table B) that will actually contain all the total values the client holds in a certain category.

So for example if I was looking at grocery business across a region then Table A would comprise the grocers name, region (like greater Sydney), the employee name of who is entering inventory amounts (so I know who is responsible), and the unit i.e. whether amounts are entered in ones / thousands, etc. Table B would hold postcodes and inventory items (Tomatoes, Carrots, Apples, etc) in its columns.

Both tables are filled through a form with Table B being a Subform to the one of Table A. (working with bound forms). In the subform postcodes are available through a drop down list (combo box) that changes its content depending on which region was selected on the main form (i.e. only greater Sydney postcodes). Now the idea is, that an employee could use copy+paste from excel to enter multiple postcodes and amounts at the same time, rather than selecting a postcode for each row and then filling in the amounts per item.

The problem:


If a postcode is copied that is not part of the selection list, Access won't give a warning and jsut save it all to Table B anyway, while I would like it to pop up a message box so the user can correct it.

I also haven't yet figured out how to only commit the "save records" to both tables through a button click rather than automatically. Currently, if the Main Form is filled and the subform isn't I get an unrelated entry in table A which has no counterpart in table B.

Any suggestions and pointers appreciated.

Thanks

PS: I'm using ACCESS 2007.

[Dal Jeanis]

First, I'd suggest you go over to Roger's Access library at http://www.rogersaccesslibrary.com (run by Access MVP Roger Carlson) and review his tutorials on database design and application design. You're lumping things together in each table that probably need to be separated out, or you will have massive headaches later. For instance, the employee who is entering data doesn't generally belong in the same table as the company - that's probably two or three tables.

Second, if you are just trying to validate postal codes against regions, you can do that easily enough with code in the before-update event of the postal code text box.

Third, the last item sounds odd. If someone enters an A record and no B records, then what you describe is how a database is supposed to work. Presumably, you are entering records for the same company at different dates, so one A record will have at first none, then one, then multiple B records. (Or, more accurately, will have multiple groups or clusters of records that are currently called a B record.)