Best way to add new records to table B when active form bound to table A (1 Viewer)

[adam.arca]

Hope the title makes sense. I'm really a beginner to all this so please do accept my apologies in advance if it's all a bit basic and / or just plain wrong.

So, I have a main form which is bound to tblCustomers (table "A" in the title). There are various sub forms pulling in data from related tables. So far, so easy. Going forwards, what I would really prefer to have is a situation where this is the only form on display to prevent users (me, mainly) from having to flick between forms.

What I want to do next is have an electronic time card and add hours spent working on customer projects. These time records will be posted to tblHours (table "B").

I can think of 3 ways to approach this but am really not sure which is the "best."

Option 1 is to have unbound controls on the main form in order to be consistent with the design imperative of one form only on display. But this would involve the most programming as I would need code to a) query tblHours for the underlying data (the time card for the week to date) upon form_load and b) add new time records to tblHours as created and then re-query for the time card data.

Option 2 is to ignore the design imperative and have a separate, bound form for the time card. Presumably, in this case, I could code the main form and the time card form to open in pre-set and separate parts of the screen so that their combined footprint would be the same as the one, larger main form under option 1?

And option 3 is the compromise one: stick with the one main form but insert the time card form as a sub form of the main form. If the child and master fields are left blank, then presumably the time card sub form would still pull through its own bound data?

Does any of that make any sense to anyone? Are there other options? What would people who've done this before recommend?

Many thanks in advance for all and any responses.

 

[Ranman256]

youd make a Parent/child form. The parent form would be Customers.
insert a subform to Timecard table,
link the 2 on the CustID field.

then data entered/shown for the 1 customer will be only for that customer.

 

[adam.arca]

Many thanks for taking the time to reply. Unfortunately, that hasn't answered my query.

At some point I will need a subform to see all hours posted to particular customers and, then, I will need to do what you have suggested.

But what I'm trying to do right now is find the best way to post in as a batch all the hours for the day for customers ABC, DEF, XYZ etc etc even if I happen to be on the customer record for MNO on my main form.

 

[plog]

Actually it doesn't make sense when you add this:

What I want to do next is have an electronic time card and add hours spent working on customer projects

A subform on your Customers form wouldn't really work because tblHours isn't in a direct relationship with tblCustomers-->it goes through tblProjects. That means if a subform for tblHours is to exist, it should be on a from whose control source is tblProjects.

1. What does tblHours look like exactly? Name & datatypes please, possibly some sample data.

2. How many and what inputs do you expect the user to fill in to populate tblHours from this form you envision?

 

[jdraw]

Adam,

Start with a description of what you are trying to support with a database. Use simple, plain English with no database jargon and no "business jargon". Keep it simple.

Get you proposed tables identified. From your description identify the relationships between proposed tables. Create a draft data model using pencil and paper. Get some test data and test scenarios.
Test your model; reconcile every issue and adjust the model. When it's working as intended you will have a blueprint for your database.

I would not recommend that you start with forms.

 

[adam.arca]

Plog, jdraw, many thanks for taking the time to read my post and respond.

Point taken about lack of info but, in my defence, I'll just say that it's difficult to know exactly how much info is the right amount without running the risk of boring you all silly. That said, I've clearly not been able to explain myself very well at all.

What I'm actually doing is keeping an existing back end with a load of tables, data and relationships and creating a brand new front end (it seemed simpler to start with a clean slate rather than develop what has become a bit of a mish mash).

Attached (hopefully) please find a screenshot of the main form as currently drafted, which is divisible into 3 areas, viz:
1. Buttons on the left for actions
2. Tab strip in the middle for all customer fields and data drawn from related tables.
3. Time card on the right hand side in the rather garish shade of mauve (which, despite the way it looks, is actually part of the main form). The time card records all time for the week booked by fee earners to customer projects (so could be several different projects over several different customers).

The way it looks at the moment is based on my option 1 from the original post where the timecard would be unbound fields (that is unbound to the data source for the main form which is tblCustomers) and would be populated by a query. This is because my distinct preference would be to have only one form visible on the screen which does everything.

Now I've got to that stage, I've started thinking about whether that's the best way to handle things (you can tell I'm an amateur!). My option 2 would be to have the timecard as a separate form (which is what I currently do) but I'm assuming I could code both the main form and the timecard form to occupy pre-set parts of the screen so that it would still look pretty much like the screenshot with the two forms laid programmatically side-by-side. Is that do-able and, if it is, is it the way to go?

And the compromise option 3 was to have the timecard form as a subform on the main form but not linked in any way (no parent or child fields): again, is that do-able and / or advisable?

The two tables in question here are tblCustomers and tblHours, the latter being related to the former in a 1 to many relationship using [CustRef] as the key. In total, though, tblHours has half a dozen or so separate fields, all of which would need to be added programmatically to the table as a new record if I go with my option 1. As a completely separate issue, I will eventually have a subform which presents all hours worked for a particular customer but that's not what I'm trying to achieve at the moment.

Hopefully that's enough information to give you something to work with, if you want to of course, and not so much info that you've lost the will to live! Any answers and / or steer would be gratefully received.

Many thanks.

 

[plog]

I really wanted succinct answers to my 2 questions, so I just skimmed your last post and looked at your screenshot. It runs counter to your first post.

Initially you mentioned only wanting one form. Wny? I mean that screenshot you posted has 11 non-intuitive buttons, at least 8 different sections, and the main area has 5 tabs . How's having only 1 form making this easy on the users?

Also, I'm always a table skeptic. You mentioned Projects, Hours and Customers. That sounds like it requires at least 3 tables, but you keep mentioning just Customers and Hours. Could you post a screenshot of your relationship tool all filled out?

 

[adam.arca]

Plog, many thanks for bearing with me so far. I have attached a snapshot of the table relationships as requested: this is actually from the current back end but I don't see this changing much as part of the spring clean (which isn't to say it couldn't be improved).

You queried whether I was really getting everything on one form as I wanted and the truthful answer is no. I would guesstimate that 50% of time spent whilst the current database ia active is interacting with the client data, 40% is adding new time records and managing time generally and 10% is other stuff. The buttons you questioned relate to that 10% which I'm happy to keep away from the main form, but what I'm trying to do is have one form where I can deal with the other 90% of stuff rather than (as presently) one form for the client data and another for entering the time records.

With regard to your 2 questions:
1. I have also attached a snapshot of the hours table for sample data (and the fields are shown on the relationship chart also). I don't have a projects table but use a freeform "Tasks" field in the time table to differentiate these (I appreciate this isn't normalised but this way just works better for us).
2. There are only 2 users of this database and we currently add something like 50-75 time records each pw (so very low volumes really).

I hope that's answered your questions but I wouldn't be surprised to find I'm still omitting something vital. Many thanks again for taking the time.

 

[plog]

Why did your initial post reference tblHours and tblCustomers? When you refer to objects with 3 and 4 names you only confuse me about which object we are actually talking about.

To be honest I'm sort of confused as to what you are trying to accomplish and don't want to re-reread the wall of text above to figure it out . Something to do with forms.

So here's advice in general--a one-to-many table relationship is accomplished via a sub-form. The one side of the relationship is the main form and the many side becomes the subform. Since tblMainRecords is at the top of the food chain your main form should be based on it. Then you can put 4 subforms on it, for each of the tables in a one-to-many relationship with tblMainRecords (tblAgenda, tblJobs, tblTime, tblNotes
).

 

[adam.arca]

OK, plog, thank you for your time.

I understand where you're coming from: I did try to keep it succinct, then I was told by jdraw to give chapter and verse, but that "wall of text" is, I agree, a bit overwhelming. It seems you can't win some times.

I was hoping to get a steer on which of my 3 options might work best on the (reasonable?) assumption that someone before might have had my issue. In the absence of that, I'll just try all 3 and see which works best for me.