Add new records to a subform (A2K SR-1 Win2KPro)

Topic

New Reply

I have a main form for this db and a tabbed control with 7 subforms, one for each day of the week, on the main form. The subforms are built from a query in order to get some calculation fields to show. I have the calculated fields showing on the main form using the DLookup function. The subforms are linked to the main page with [ClientID]=[WeekID].

I need to be able to add a new record to the subform(s) without copying the record for the main form and be able to display the DLookup values based on each additional Weekly Activities record inserted.

This morning it occurred to me that I need to add another keyfield, CaseID and set up the following relationships: CaseID->ClientID, One-to-One; CaseID->WeekID, One to Many (One CaseID for many WeekIDs). Otherwise, I am linking the Weekly Activities information to the Client Information and if you want to add a new record to the Weekly Avticities, the Cleint Information (name, address, etc.) will also be copied. This is obviously bad design. However, I can’t get the CaseID to do anything: I can define a table called CaseID with one field, autonumbered, with the relationships designed as specified and it doesn’t change when I do stuff to the main form. Any advice on how to setup the db structure? Again, I need to tie the weekly activities info to a client but a single client may have more than one week entered. If i implement the standard wizard-based Add a Record to the main table, both records get copied and a new, blank Week Activities record displays. Close but no cigar.

So my questions are: How does one set up PK/FK structures to group multiple weekly activities to a particular client and, How do I add a new Weekly Activities record to the subform/query.

Also, since the subforms are based on a single query, it would seem to me that one could add a record to the query or underlying table and have each of the subforms show that new record or goto it using GotoRecords,,acLast

Once I get this problem solved I need to start generating reports and the main report is ClientName + WeeklyActivities by Month (date range). Then this project is looking complete!

TIA

Reply | Quote

Replies

If you are using subforms to display the weekly activity, then you don’t need to include the client information in the data source for the subform. Subforms do that automatically based on the parent child links you setup for the subform container object on the main form. By the way, how are you limiting the data displayed to a particular day if you are only using one query?

If you need to have a CaseID, and it has a one to many relationship with the Client table, and you also need to associate a CaseID with the activity table, then you may need to consider nested subforms, or you will need to limit the Main form display to a single Client and CaseID. Hope this makes sense and triggers some ideas. If it doesn’t or I’ve misunderstood things, please post back.

Reply | Quote

thanks for the quick response – on a sunday even!

I haven’t heard of nested subforms, however. By the name, not sure if that applies. Here’s how the db is set up:

1) Client Info table – name, address, etc.
2) qry WeeklyActivites – this is built from a table with each day, a time range on the day, three categories of activities by hour and a narrative (memo) field. The tab control on the main screen has sub-queries based on the main query for the specific day for each page on the tab control. Currently, the child/master settings are based on WeekID/ClientID. I am using a query based on a table because I have three fields that do sums based on the activity hours. In essence, the tab control parses out the base query into the 7 days of the week, but it all gets written to one record in the main query. I wouldn’t call these nested subforms but parrallel subforms.

The whole difficulty I am having is adding a new record to just the qryWeeklyActivities without duplicating the Client Information data. I thought that adding an additional level of abstraction, viz. the CaseID with the one-to-one relationship to ClientID and one-to-many with WeekID, would give me a grouping on Weekly Activities based on CaseID rather than, as is now the case, Weekly Activities tied to ClientID. I tried implementing this by creating a new table, CaseID, with a single autonumber field and setting the relations to the other two tables as specified but nothing happens to the CaseID table – no records are added. That’s probably because nothing is being inputted into the CaseID table, but that’s not suprising because I am not directly interacting with this CaseID table anywhere in the main form/subform. But the whole idea may be wrong to begin with.

I keep thinking the key to whole problem lies with the fact that the subforms all derive from a single query and all i need to do is add a new record to the table the query is based on and then, in each subform, goto that new record. There’s no patricular reason that the ClientID/WeekID can’t be one-to-many relationship itself. Perhaps. Also, another detail: the subforms in tabcontrol are set up as single form, cycle thru current record. It might be easier to do this if the subform was in a datasheet, but I want to give space for the memo field as that is a major part of the weekly activities data collection and having it set as a form rather than datasheet is very user-friendly.

Hope this helps – thanks again for getting back to me.

Reply | Quote

Steve, if I understand the situation correctly, I would do it as follows:

• Create a subform with qryWeeklyActivities as record source in the main form, linked by ClientID. Records in this subform correspond to weeks (WeekID).
• Put a tab control on the subform (not on the main form!) with tab pages for each day of the week.
• Place controls bound to the fields relating to a specific day of the week directly on the corresponding tab page, not in a subform on the tab page.
  [/list]This way, the controls in the tab pages are all synchronized – they automatically belong to the same WeekID. When you create a new record in the subform, it is visible in all tab pages.
  See the attached screenshot for a rough idea (without details, and without aesthetics taken into consideration).

Reply | Quote

Ok, I’m trying to do what you suggested – it makes sense somehow: Placing the main subform on the main form with the basic query as the source would make the main subform a container for the sub-subforms. The basic problem I am having, after all, is simply adding a new record to the tabcontrol. However, I am having many obsticles:

1) the source query has too many fields for it to used as a subform. There are 7 fields for each day of the week plus the WeekID field gives 36 fields and Access chokes when I try to plop the whole query in as the source for a subform. So, I thought, why not just use the WeekID from the source query and have the sub-subforms display the rest of the fields? This doesn’t really seem to work

2) When I place a sub-subform on a tab control, strange things happen. I can set the master/child field on the main subform to ClientID/WeekID; the sub-subform master/child settings look strange in the Wizard: Show for each record in using WeekID. If I ok thru all that, I can’t edit the Linkage parameters – I get an ‘Object variable or With block not set’. I don’t think the nested forms are working correctly, but i’ve never worked with them so i may certainly be missing something.

This is all a little frustrating, because I’d think it’s not a real difficult problem: Access is supposed to allow you to parse out and relate tables. That’s what I’m trying to do with these two tables (Client and Weekly Activities) — but, since the Weekly Activities has a lot of fields, it looks best to use the tab control and, because there’s a memo field for each day, I don’t want to use a datasheet. All I really want to do is add a record to the Weekly Activities as needed and have that new record relate to an existing Client ID as a one-to-many relationship. It’s not hard to make a new record and have the tab-control formatted subforms hook up with a record in Client Info, but it seems devilishly difficult to make this thing add new Weekly Activities in the format I have set up. If I could do this in Datasheet, I think I would have been well on my way to other aspects of the database.

Perhaps your ‘rough and dirty’ example would help – if it can add a new record in the sub-subform that gives me blank fields in the tab control and keeps the same WeekID, that’s exactly what I need: a one-to-many relation between ClientID/WeekID with tab-formatted sub-subforms build from parts of the source query. Could you post it, perhaps?

Also, do you think there’s an issue with the sheer number of fields in the source query?

thanks

Reply | Quote

Tables and queries can contain up to 255 fields, so 36 fields in itself shouldn’t be a problem – I have used queries with about 100 fields without problems. There is also a 2000 bytes limit to the size of a record; each long integer contributes 4 bytes, each text field the length of the actual text in it + 1, and memo fields contribute 4 bytes (their content is not stored in the record itself).

For what it’s worth, I have attached my rough demo as a zipped Access 97 database.

Reply | Quote

thanks for the demo. turns out i have hacked out a solution of sorts. I have some simple code that visits each page on the tab control and adds a new record. This gives me a new record in the base query with the same WeekID, so I have the one-to-many relationship with ClientID. The basic nut has been cracked. I can now go on…hopefully! now I need to do some additional work on the tab control to help the user know what ‘page’ they’re on if there’s more than one week activity set for a client. been having some problems getting events to trigger when clicking on the tabs on the tabcontrol, but that’s alright. just another day in the life.

thanks for you assistance, all!

Reply | Quote

Well it worked and looks much better than before — I implemented your suggestion of adding the tabcontrol to a subform rather than directly on the main form. Now, with the record navigation bar at the bottom of the tabcontrol/subform, I get to look at any additional Weekly Activities info where ClientID=WeekID. No need to add hidden fields on the main form.

Curiously, Access couldn’t build the subform using the source query (which has 62 fields) if I double-click on the query window to select all the fields and drag into the query design grid — Access complained the SQL was too long/contained errors. It did work if I clicked on the asterisk and dragged that down to one column (basically SELECT * FROM). So, the resulting db has a much cleaner structure: Client Information table, Week Activities query (with some additional calculation fields based on the Week Activities table), the ClientInformation main form, the qryWeeklyActivities subform, a Monthly Report and that’s it. There’s still some minor cleaning up and field testing but it definately looks very close to completion. a very handsome db, if i say so myself.

Thanks VERY MUCH for your help and patience in getting the kinks ironed out. I had a somewhat ugly, kinda useable system but now it looks and feels much more solid, simple and robust, and easier to understand. Kudos!!

Reply | Quote

An alternative is to create separate records for each day of the week. So the WeeklyActivities table would have a primary key consisting of ClientID, WeekID and DayID (or ClientID and Date, if you combine week and day into a date).
This makes aggregating data over time easier. On the other hand, you would have to create separate queries and subforms for the days of the week. When the user starts a new week, you’d have to create the day records in code.
This would lead to the nested subform approach WendellB mentioned:

Main form : clients.
Subform : weeks.
Sub-subforms in pages of tab control on subform: days of the week.

Reply | Quote

I think you still have a structural problem with your table design that is getting mixed up with your form design. It seems to me that a given client could have one or more Case IDs over time, and that you could have activities that occur related to two or more cases in one week, or even in one day. In addition, the use of week in displaying data appears to be a convienent way to display the data, but doesn’t need to be a part of the structure. Thus I would envision the activities table to be a fairly simple design, with six fields – an autonumber for indexing and primary key purposes, the CaseID foreign key, a Date/Time field with start value, either and end DateT/ime field or a Duration field, a category ID, and the narrative field.

From that information, you can calculate the week and day involved, and through the CaseID you can link back to the client. Have I missed something important?

Reply | Quote