Re: Edit multiple tables with one form per record

ricky.agrawal@xxxxxxxxx wrote: 
I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.

The tables are named after key points such as the store number and the
store ID. The fields of those tables are generic fields such as sales
per day, bank deposit and what not. The first field for each store
table is the date and I've set that as the primary key as one store
cannot have more than one sales data per day.

The problem I have encountered is that I cannot seem to create a form
that will have a data entry box called DATE in which I enter in the
date.
Along those lines, I cannot seem to have some thirty tables or so have
strictly the records come up which correspond to that selected date.
That way I can enter in information corresponding to 9/26/2005 for all
thirty stores (or however many store tables there are).

I'm sure others will point out that DATE is a reserved word and should not be used. Can you not name the field StoreDate or something similar?

I don't know why you don't have 1 table instead of 30 stores. It would make things so much simpler. What happens if one of those stores closes? What happens if you you add another store? Then your system becomes less usable.

You should be able to select a store, a date, and filter records that match that store id and date.


I tried to start simple and create a form that will let me edit
multiple tables, but when I copy over the boxes from one form to
another form, it seems to revert all the entry boxes to the first
table. I am confused on how to proceed and complete this form in MS
Access.


A form can be unbound and bound. If unbound, no table is associated with the form. You would update the information when a Save button is pressed. You would fill in the record's data when you go to a new record. This is more work than a bound form.

A bound form is associated with a table. Let's say your form's RecordSource (property ***, data tab for the form) is
Store1Table
or
Select * from Store1Table

When you move to another store, you need to switch the RecordSource. Let's say it opens to Store1Table. You now select Store2Table from a dropdown. You would then do something like
Me.RecordSource = Store2Table
or
Select * from Store2Table

Your life can be complicated or uncomplicated. You can make a mountain out of a molehill or climb real mountains. You might want to consider changing direction to making your life easier so you can tackle the real problems that will confront you.
.

Quantcast