MS Access Forum / General 1 / December 2005
Edit multiple tables with one form per record
|
|
Thread rating:  |
ricky.agrawal@gmail.com - 30 Dec 2005 17:04 GMT 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.
Allen Browne - 30 Dec 2005 17:27 GMT 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.
 Signature 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.
> 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 [quoted text clipped - 19 lines] > table. I am confused on how to proceed and complete this form in MS > Access. ricky.agrawal@gmail.com - 30 Dec 2005 17:36 GMT 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.
Allen Browne - 30 Dec 2005 17:46 GMT 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()
 Signature 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.
> Allen, thanks for your reply. > I do have a Store table, I have called it the Store Master Information [quoted text clipped - 14 lines] > > I will try to search in my Access book about bounding. ricky.agrawal@gmail.com - 30 Dec 2005 18:15 GMT 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.
Allen Browne - 31 Dec 2005 02:40 GMT 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.)
 Signature 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.
> Setting the default value property to =Date() was a fantastic idea! > Thanks!! [quoted text clipped - 15 lines] > I just did a test and it shows ALL records that are in the Sales table. > That could get confusing. ricky.agrawal@gmail.com - 30 Dec 2005 17:31 GMT 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?
salad - 30 Dec 2005 17:44 GMT > 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 [quoted text clipped - 13 lines] > 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 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.
ricky.agrawal@gmail.com - 30 Dec 2005 18:31 GMT 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?
darkroomdevil - 31 Dec 2005 02:57 GMT >Salad, thanks for your reply! >I have created one table rather than 30 or so for each store. It is [quoted text clipped - 14 lines] >without the STOREID drop down box so that folks know which stores have >their information entered in or not? 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
ricky.agrawal@gmail.com - 30 Dec 2005 19:01 GMT 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.
salad - 30 Dec 2005 20:18 GMT > Salad, thanks for your advice! I did move to having all the stores in > one table. It is truly much simpler. [quoted text clipped - 8 lines] > Or something like that to prevent someone from incorrectly entering in > data for a store. 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 - 30 Dec 2005 19:53 GMT 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.
salad - 30 Dec 2005 20:32 GMT > 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? 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/microsoft.public.access/browse_frm/thread/1d317d1 9c2ba5b89/b9c1e64f800b48c1?lnk=st&q=connect+link+text+group%3A*access*&rnum=10&h l=en#b9c1e64f800b48c1
> 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. See if my example regarding form/subform gives you a possibility.
|
|
|