Edit multiple tables with one form per record

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 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.

Ricky, this is not how you create a relational database.

You need a Store table, with one record for each store. Fields:
StoreID AutoNumber or unique code for the store. Primary key
StoreName Full name of this store.
Suburb Where the store is.

Then you need a Sale table that contains the sales info for all stores. It
will have fields:
SaleID AutoNumber (primary key)
StoreID Relates to Store.StoreID (which store this entry is)
SaleDate Date/Time The date for this entry.
Amount Currency The amount of sales for this store on this date.

You can now create a form bound to the Sale table. It will have a combo box
for selecting the store.

If you prefer, you could create a main form bound to the Store table, with a
subform bound to the Sale table. The subform would show the sales for that
store (one row for each date.)

BTW, don't call a field Date: that's a reserved word for the system date.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ricky.agrawal@gmail.com> wrote in message
news:1135962282.598137.192930@g14g2000cwa.googlegr oups.com...[color=blue]
> 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 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.[/color]

Perhaps I was breaking down the information too much.
If I combine all the store tables into one table, then I have gone
around the issue of not being able to edit multiple tables with a form
because I'll have my sales information in one table.

But that still brings me back to an original problem I could not figure
out how to solve.
In the form, it displays all entries in the table. I just want to add a
new record.

Perhaps something like this..

--- DATE ---
STORE ID^1 | SALES^1 | DEPOSIT^1
STORE ID^2 | SALES^2 | DEPOSIT^2
STORE ID^3 | SALES^3 | DEPOSIT^3
STORE ID^4 | SALES^4 | DEPOSIT^4

When I input data, then whatever I submit with DATE being 9/25/2005,
all the records submitted will have that date in Field1.

Am I looking at the entire aspect the wrong way?

Allen, thanks for your reply.
I do have a Store table, I have called it the Store Master Information
where I have the following fields.
Store Number | Store Address | Store Phone Number| etc,.

I understand what you said with the Sale table, however I do not
understand how I can create a form bound to the Sale table.
I don't need to show sales for more than one date at a time. I just
need to be able to enter the information for all the stores in one
screen/form to make it easy to enter information.

Once again, thank you for your help, I appreciate it and now I know
that going to a table per store solution was incorrect. I will have a
Store table which has all the stores and their corresponding
information, and then a Sales table which will have all the sales
information.

I will try to search in my Access book about bounding.

ricky.agrawal@gmail.com wrote:[color=blue]
> 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).[/color]

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.

[color=blue]
> 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.
>[/color]

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 sheet, 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.

Regardless of what interface you choose, you will need all the sales info in
the one table.

There is no problem with entering the same date on several rows, for several
stores.

If you want the date text box to default to today, just set its Default
Value property to:
=Date()

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ricky.agrawal@gmail.com> wrote in message
news:1135964219.597420.230140@o13g2000cwo.googlegr oups.com...[color=blue]
> Allen, thanks for your reply.
> I do have a Store table, I have called it the Store Master Information
> where I have the following fields.
> Store Number | Store Address | Store Phone Number| etc,.
>
> I understand what you said with the Sale table, however I do not
> understand how I can create a form bound to the Sale table.
> I don't need to show sales for more than one date at a time. I just
> need to be able to enter the information for all the stores in one
> screen/form to make it easy to enter information.
>
> Once again, thank you for your help, I appreciate it and now I know
> that going to a table per store solution was incorrect. I will have a
> Store table which has all the stores and their corresponding
> information, and then a Sales table which will have all the sales
> information.
>
> I will try to search in my Access book about bounding.[/color]

Setting the default value property to =Date() was a fantastic idea!
Thanks!!

I'm sorting through some help files and google to learn more about
bounding. I think I've gotten it down.

I've created a form with the following entry boxes.

STORE ID | SALESDATE | GROSS SALES | DEPOSIT

Now I have created a lookup so the STOREID is a drop down box.
SALESDATE is automatically entered in with todays date.
GROSSSALES and DEPOSIT are both currency.

What do I have to do to make it so that only those records with todays
date (or a specified date, I might try to figure out how to turn it
into a drop down box) are shown?
I just did a test and it shows ALL records that are in the Sales table.
That could get confusing.

Salad, thanks for your reply!
I have created one table rather than 30 or so for each store. It is
much simplier to have one table for multiple reasons.

I think I have figured out a way to have only specified dates appear.
To use the filters!

I can simply apply a filter of 9/25/2005 and only those records with
those corresponding dates will come up!

The only problem I can see with what I have created so far is skipping
a store. However I can run a query later on to display a list of the
stores and their latest data entry. I'll have to figure out how to do
that (I haven't even look yet).

Or should I create a form with all thirty stores information displayed
without the STOREID drop down box so that folks know which stores have
their information entered in or not?

Salad, thanks for your advice! I did move to having all the stores in
one table. It is truly much simpler.

Now that you bring up the issue of clones, is there a check I can
install that will prevent a duplicate record from existing using the
following criteria?

if((SalesDate && StoreID) exist)
DisplayErrorBox;

Or something like that to prevent someone from incorrectly entering in
data for a store.

I guess this brings me to my next step.

I understand that Access can store Number, Int, Currency and many more.
Can Access store Array's or perhaps CSV which I can tell Access to
GetChar until it hits a Comma and consider the order the characters
recieved as one type of object?

I think that would be really easy to use form wise, and report wise as
well as storage wise.

The problem I've encountered is I can't edit all 33 stores information
at one time and move to the next day easily.

ricky.agrawal@gmail.com wrote:
[color=blue]
> Salad, thanks for your advice! I did move to having all the stores in
> one table. It is truly much simpler.
>
> Now that you bring up the issue of clones, is there a check I can
> install that will prevent a duplicate record from existing using the
> following criteria?
>
> if((SalesDate && StoreID) exist)
> DisplayErrorBox;
>
> Or something like that to prevent someone from incorrectly entering in
> data for a store.
>[/color]
I, for the most part, ALWAYS have an autonumber field as my primary key.
It makes life less complicated. For example, in the BeforeUpdate
event I might want to check if something exists. Ex:
If Me.NewRecord then
Dim var As Variant
var = Dlookup("ID","TableName","StoreID = " & _
Me.StoreID & " And ID <> " & Me.ID)
If Not IsNull(var) Then
msgbox "This store record already exists"
Cancel = True
Endif
Endif

Now, the above is a bit unnecessary but sometimes you want to check for
a record that contains the same info but the AUTONUMBER does not match
the current autonumber field.

Your situation is a classic example for a form/subform. In this
instance I might have an unbound form...it really depends on how you set
up your tables. In the main form, I might have a combo box that lists
all of your stores with 2 columns; StoreID and StoreName. In a textbox
I'd have the date field. In the AfterUpdate event for the combo box,
and date text box, and perhaps the OnCurrent or OnLoad events I'd have
some code like
SetFilter

Next, I'd have a subform that would list the data for the store/date
selected in the main form. It would have no filter.

Now drop the subform into the main form.

Let's say the main form is called MainForm and the subform is called
SubForm. Your SetFilter code may look something like this...
Private Sub SetFilter
Forms!MainForm!SubForm.Form.Filter = _
"StoreID = " & Me.ComboStoreID & " And " & _
"StoreDate = #" & Me.InputDateField & "#"
Forms!MainForm!SubForm.Form.FilterOn = True

You can shorten the references like this
Me("SubForm").Form.FilterOn = True

Of course, if the main table was bound to a table that stored the
StoreID and Date and the subform contained date related to it, the
form/Subform could be linked with the Master/Child relationship.

ricky.agrawal@gmail.com wrote:
[color=blue]
> I guess this brings me to my next step.
>
> I understand that Access can store Number, Int, Currency and many more.
> Can Access store Array's or perhaps CSV which I can tell Access to
> GetChar until it hits a Comma and consider the order the characters
> recieved as one type of object?[/color]

If you have a text file that is command delimited, you can actually link
to it and make use of it similar to a table. To test it out, from the
menu select File/GetExternalData/Link. Select txt as file type and
select your text file. Once linked, open it like a table.

You can do this (link) programmatically if you know the filename. See
Connect in help. Also, at
http://groups.google.com/advanced_search?hl=en you can enter keywords to
search and enter *access* in the groups to search box. Here's a sample
link if it doesn't wordwrap.

http://groups.google.com/group/micro...c1e64f800b48c1[color=blue]
>
> I think that would be really easy to use form wise, and report wise as
> well as storage wise.
>
> The problem I've encountered is I can't edit all 33 stores information
> at one time and move to the next day easily.
>[/color]
See if my example regarding form/subform gives you a possibility.

Okay, sounds like you have now figured out how to set the default value for
the date, and also to filter on a store.

To prevent 2 records for the same store and date, create a unique index on
the combination of those 2 fields:
1. Open your table in Design view.
2. Open the Indexes box (View menu.)
3. In the first column, enter a name for the index, e.g. StoreIdSaleDate.
4. In the 2nd column, choose the StoreID field.
5. In the lower pane of the dialog, set Unique to Yes.
6. On the next line of the dialog, choose the 2nd field in the 2nd column.
Leave the index name blank: this is not another index, it's a 2-field index.

The dialog now looks like this:
StoreIdSaleDate StoreID
SaleDate
Save the changes, and you cannot accidentally enter the same store twice on
the same date.

You can use Filter By Form (Toolbar) to filter the form to one store, or one
date, or any combination. Or, you could use a subform to show the sales for
a particluar store (stores in the main form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ricky.agrawal@gmail.com> wrote in message
news:1135966530.127920.118900@g14g2000cwa.googlegr oups.com...[color=blue]
> Setting the default value property to =Date() was a fantastic idea!
> Thanks!!
>
> I'm sorting through some help files and google to learn more about
> bounding. I think I've gotten it down.
>
> I've created a form with the following entry boxes.
>
> STORE ID | SALESDATE | GROSS SALES | DEPOSIT
>
> Now I have created a lookup so the STOREID is a drop down box.
> SALESDATE is automatically entered in with todays date.
> GROSSSALES and DEPOSIT are both currency.
>
> What do I have to do to make it so that only those records with todays
> date (or a specified date, I might try to figure out how to turn it
> into a drop down box) are shown?
> I just did a test and it shows ALL records that are in the Sales table.
> That could get confusing.[/color]

ricky.agrawal@gmail.com wrote:[color=blue]
>Salad, thanks for your reply!
>I have created one table rather than 30 or so for each store. It is
>much simplier to have one table for multiple reasons.
>
>I think I have figured out a way to have only specified dates appear.
>To use the filters!
>
>I can simply apply a filter of 9/25/2005 and only those records with
>those corresponding dates will come up!
>
>The only problem I can see with what I have created so far is skipping
>a store. However I can run a query later on to display a list of the
>stores and their latest data entry. I'll have to figure out how to do
>that (I haven't even look yet).
>
>Or should I create a form with all thirty stores information displayed
>without the STOREID drop down box so that folks know which stores have
>their information entered in or not?[/color]


What salad said .. and a simple way of limiting to just the one date is to
create a form with no recordsource and on it put a control for entering the
date you want to view or edit. Then add a sub-form - the sub-form record
source would be the sales table. The sub-form control properties would be

Link Child Fields = SalesDate
Link Master Fields = controlDateOnMainForm

Now only the records with that date show, and any added records default to
that date. This form design is easy and assumes you really only want to
enter / show records for one date at a time ;-)

If you want you can add a command button that adds runs an append query to
add a new record for each store with the date set to the date on
controlDateOnMainForm - this way you can just fill in the values for each
store and won't have to wonder if you missed any stores. If a store had no
sales you would delete that stores sales record.

Roger

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1