By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

MS Access populate table fields on new record

P: 8
Please bear with me. I don't do much vba development, so my knowledge of process and syntax is lacking.

I have a form with six subforms. To print my required reports, I need to have certain fields in each subform populated. I am using combo boxes in the forms. What would be the best method to accomplish this? I have the defaults for each combobox set, but it is not populating the data field in the proper table until I physically choose the default with the mouse.

My present thought is to insert VB code in the "Add Record" button on the main form. Is this a possibility? Should I directly fill in the fields, or write code that would choose each combobox? What code would I put in the "Add Record" button. What would the code look like?
Oct 26 '18 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Hi BigEd.

It seems clear that you've put some effort into expressing your question fully, which is important in itself. However, as a reader, I must say that I come away without a clear idea of what you're asking for.

Perhaps you could think through the process as you see it and try to describe that to us step by step. An idea of what's on the form(s) and what on the report whould lead us in the right direction and enable us to understand the rest of the explanation more clearly.

In case it helps, and my understanding at this stage is very fuzzy so it may be way off target, Cascaded Form Filtering may be what you're after, or at least part of it (or maybe not at all of course).
Oct 26 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 3,055
BigEd1977,

Keep in mind that the default value for a Field/Control only affects the underlying table when you move to a new record. If you are opening your form on an existing record, the underlying values will be shown.

I think I kind of understand what you are trying to do, but if you give us a more concrete example of what you want to happen, that might help us understand your problem a bit more clearly. I know why you are trying to do this from your previous thread, but the what is still a bit fuzzy.

Thanks!
Oct 26 '18 #3

P: 8
Thanks guys.

Let me try again.

I have a form that includes six subforms. I have a report that takes the data from all of the forms, then summarizes it. That report has six subreports, one for each form. I have found that if there is no data in a subform that the subreport will not print. There are key controls/fields that need to have data in them for the report to print correctly.

Each subform that has the required controls/fields have comboboxes as the controls. I have the comboboxes set to default data in the properties, and they show the default when a new record in the main form is generated. Unless I go to the subform, and click a selection in the combobox, the subreport does not print. I choose the default in the combobox, even though it is already being displayed, and the subreport prints.

My goal is to have the comboboxes in each subform populate the proper fields in the subform table when a new record is generated.

Main record is: Order With a table named tblOrder
Sub record is: Burgers With a linked table named tblBurgers There can be multiple burger records per Order record.

There are fields in the burger record/table that need populated at the creation of the "Order" record for the reports to print.

Other tables linked to tblOrder are: tblSausage, tblStuffedSausage, tblHunterSticks, tblJerky. There are fields, flavor, seasoning, premiumseasoning, in each of these tables that need populated with the create of the Order record.

Does that make sense?
Oct 26 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,055
I think I see the issue here:

Let's assume for the moment that the following is true:
  1. tblOrder has a Primary Key
  2. tblBurger uses tblOrder.PrimaryKey as a foreign key
  3. All relationships are established properly

Even if this is the case, a new record will not be added to tblBurgers until you create a record in the subform.

So, if I understand you correctly, every time you create a new record in tblOrder, you want to automatically create a new record in tblBurgers (as well as the other related tables)?

If that is the case, this can be done, but it can be tricky, because you can run the risk of creating unnecessary, empty records in your child tables.

NeoPa may have some better, more "seasoned" advice for this, but my initial thought may be that once you begin creating a record in on your main form (perhaps in the AfterUpdate event of the first field you update) that the VBA checks for the number of associated child records in each associated table. If there are none, then create a record with the default value assigned for the required field.

Not sure if this makes much sense (to you or to me), but it may be one approach. This would be a highly irregular approach (from a standard DB design standpoint), but considering your requirement, it is possible. I have a few tables that are populated in a somewhat similar way, so your concept is not unfathomable.
Oct 26 '18 #5

NeoPa
Expert Mod 15k+
P: 31,186
Hi Big Ed.

I would strongly recommend that, before you proceed with the design of the interface, you review the design of your tables. Database Normalisation and Table Structures is a resource you should read and digest (almost ingest) before trying to do that.

Tables are containers of similar items that may have different attributes. Sausages are ingredients with a name attribute of "Sausage". Stuffed sausages are similarly ingredients but with a different Name attribute. These generally belong together in a table for ingredients. NOT in their own separate tables. That reflects a misunderstanding of the design and of how databases can help you do what you need to do.

The very first step of any project is the design. This includes the table structures as the first and most important element. Get this wrong and everything you try to do will be impaired by this one mistake.
Oct 26 '18 #6

P: 8
NeoPa.

I'm fairly sure the table structure is pretty good. Maybe not perfect. So you know, Sausage and Stuffed Sausages are different products, and have different attributes, just like sausage is different from burgers, and different from Hunter Sticks and Jerky.

Here is an example of the table structure

tblOrder
----Order Number (Autonum)
----Customer Name
----Customer Address
----Date

tblSausage
----Sausage Flavor
----Sausage Customer Seasoning
----Sausage Premium Seasoning
----Link to Order Number field in tblOrder

tblStuffedSausage
----Sausage Flavor
----Sausage Customer Seasoning
----Sausage Premium Seasoning
----Link Ratio
----Link to Order Number field in tblOrder

tblFlavor
----Flavor Type
----Flavor Cost
----Link to tblSausage

tblCustomSeasoning
----Seasoning Type
----Seasoning Cost
----Link to tblSausage

I would love to be able to sent you a screenshot of my table relationships so you can get a better idea of how they are set up. I have 28 tables in this database.

Attached Images
File Type: jpg Access Database Table Relationships.jpg (151.6 KB, 52 views)
Oct 26 '18 #7

P: 8
Actually. I found out how to get the relationship screenshot uploaded.

Thanks,
Oct 26 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 3,055
Ed,

I won’t say that your relationships are “wrong” at this point, but they are a bit irregular. Typically, an “Order” has “Items” in a one to many relationship. There may be different types of Items that are all described by various fields in the Items Table. What is more curious about your description (as I understand it) is that for every Order, there will be a corresponding record in tblBurgers—even if there are no burgers on the order. However, this requirement is, for lack of a better term, a “self-imposed” requirement, just so that you can get your report to work the way you want it.

To be honest, it looks like you have a bit of DB structure to work on, as this convoluted structure will only continue to increase the headache factor. A simple example can show you how untenable this structure is: Let’s say you begin selling venison. Now, you must create a new table for venison, and, by the way, add another sub report to capture the venison orders. Not to mention all the recoding required to accommodate those changes. But, what if you now include 12 new items? This would require 12 new tables, 12 sub reports, 100’s of lines of new code—the headache factor increases exponentially.

Please don’t be offended if we are frank here on this forum. I think we all have been exactly where you are—at least I have. The best time to restructure your DB is right now. Don’t wait until it is too late.

We would be glad to offer additional guidance and advice on where to start and how to do that. Initially, it looks like you have a reasonable grasp of things. So, hopefully you will understand the importance of our advice.

Others on this forum will concur with this assessment.
Oct 27 '18 #9

P: 8
twinnyfo.

No offense taken at all. I'm just trying to learn, and thank you for your advice. Let me consider your advice for a while as I may need to redo a lot of work. Thanks.
Oct 27 '18 #10

NeoPa
Expert Mod 15k+
P: 31,186
TwinnyFo:
Others on this forum will concur with this assessment.
I guess I've already said pretty much the same thing already so my concurring would come as no surprise.

I can say that Twinny has drilled further into the problems I was alluding to earlier. Hopefully this has illustrated to you in a more clearly understandable way the fundamental concepts of Normalisation that, I'm sorry to say so bluntly, your database doesn't currently comply with. I want to be entirely unambiguous here. It certainly doesn't comply with normalisation concepts. It is not compatible even with 1NF as explained within the linked article.

Believe me when I say that if this were left as it is then the problems would simply get more and more serious the more you tried to do.

Let me juxtapose an earlier comment from me and your response :
NeoPa:
Tables are containers of similar items that may have different attributes. Sausages are ingredients with a name attribute of "Sausage". Stuffed sausages are similarly ingredients but with a different Name attribute. These generally belong together in a table for ingredients. NOT in their own separate tables. That reflects a misunderstanding of the design and of how databases can help you do what you need to do.
BigEd:
So you know, Sausage and Stuffed Sausages are different products, and have different attributes, just like sausage is different from burgers, and different from Hunter Sticks and Jerky.
While your statement is probably true in the real world, as far as database work is concerned it is not correct at all. You want to do the same type of things to Sausages as you do to Stuffed Sausages. As far as the database is concerned they're interchangeable. You could have an order with one, the other, or even both. As far as that order's concerned they are simply items with different attributes.

Believe me - many of those who have programming experience struggle when they first come across database work. It takes a different approach and it takes a different way of thinking. Many still do it without that, but they are definitely not the better ones.

Good luck with your rethink. I hope we've said enough and clearly to enable you to understand as you need to to go forward reliably.
Oct 27 '18 #11

Post your reply

Sign in to post your reply or Sign up for a free account.