VBA command to create new records in tables and copy value of one field (1 Viewer)

[sheckay]

Hello. I have a database that I'm working on. I've created a JOB table that is the centerpoint of the various other tables related to the different processes. The idea is that each of the other tables will be connected to the job by the jobID field (primary key of the JOB table). There's a foreign key in each of these tables to hold that value. And it works so far, but there's one part of it that needs to be automatic. Matching the JOB_ID primary key with the fk in each of the other tables. Right now that field has a drop down box and the correct JobID can be selected. But I can't rely on that happening. Especially as this db is used over the years. Is there a VB command that I can use to create a new record in each table (without opening up each table), and upon doing this populating the fk's in each record with the value of the JOB_ID primary key? This command would be run as the new job is being created, which in turn produces a JobID in the JOB table. Thanks in advance.

 

[The_Doc_Man]

I suppose you could create some sequence of SQL "INSERT INTO..." action queries that could be launched behind the scenes after creation of this Job_ID. However, that is not usually the way Access works. If you have only the FK and NOTHING ELSE in one of those tables, don't enter anything. Access is designed to be most efficient with "sparse" tables. What you would USUALLY do is if and when you open one of the child tables, at that point create the desired record.

 

[sheckay]

Hello Doc. Thank you. In the meantime I have tried something along those lines:
Private Sub Command18_Click()
DoCmd.RunSQL "INSERT INTO shipRECtbl(jobIDfk) VALUES ('" & Me.JOB_ID & "' );"
End Sub
I created an action button on the screen to create new jobs. And it doesn't do anything if I run it before clicking "Save record" for the job I started creating. After having clicked that, if I click the command for the DoCmd.RunSQL it leaves me with a record in that table that is exactly what I'm looking for. The only problem is that if it's clicked again, it creates another record with the duplicate info for a different job.
Is there a way for the SQL not to run if it detects a field holding the same value as the one it's going to enter?

 

[sheckay]

OK, I've found this info and it seems to have fixed that duplicate problem:

You can prevent duplicate values in a field in an Access table by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique.

There are two basic ways that you can create a unique index:

• Set the field's Indexed property to Yes (No duplicates) You can do this by opening the table in Design view. This method is easy and a good choice if you only want to change one field at a time.

 

[sheckay]

But, is there a way to prevent the error pop ups? So that the command just doesn't run if there are duplicates, and the user doesn't get confused by pop up error messages?

 

[The_Doc_Man]

The advice I gave you, to not bother to create the otherwise empty record until you need to put something else in it, would prevent that because you could test the child table. Do a DCount of records in the child table where the foreign key matches your new value.

However, something else now bothers me A LOT. What you asked STRONGLY sounds like you are making 1-to-1 relationships between this main table and the other tables, because otherwise you wouldn't care about duplicate entries in these secondary tables. While it is perfectly possible to build tables with a 1-to-1 relationship, it is ALMOST always wrong. Like 98%+ of the time, wrong. Among other things, if you don't have one of the two or three exceptional cases to consider, those records - as you described them so far - would violate a "purity of purpose" rule. You have talked about the FK but that INSERT you showed us didn't contain any primary key. So the only identifying value in that table after that insert isn't a PK, it's an FK, and that means that the record cannot be identified at all (by strict consideration of key usage conventions.) I'm sure it seems clear to you, but to me, at least, that record is a ghost with no substance.

The way I see it, these other tables EITHER shouldn't exist AT ALL, or they should not be of the kind where you care about duplicates. Further, if you actually TRIED to build a relationship between your Job table and a "related" table with a 1-to-1 relationship (as is implied by having a unique index), you would potentially run into a case where you could never create a new record in either table if you tried to maintain relational integrity. Do that more than once and you enter into what is called a "deadly embrace."

Before you go off building ANY tables, describe these tables for us in English so we can advise you of proper infrastructure.

And for the record, once you have decided that you actually NEED a record in the "related" table, the "INSERT" method you chose IS one possible starting point to add the record, though by my standards, you would not create that record until you had more than a foreign key to enter into it.

 

[sheckay]

Hello Doc. There are no 1 to 1 relationships. Although I did think about it briefly.

As far as checking for duplicates upon creation of new records, with that field getting populated, I just changed the index property of that very field to "yes, no duplicates". That's working great.

So, basically, I have a job main form, where jobs can be created. The form behind that mainly has company name, jobID. Upon clicking a button to save the record, a new record is created for the forms related to the job (by the jobid primary key being copied to the foreign key of each table's foreign key). While either browsing existing jobs, and clicking on the forms for the job or doing the same for new jobs, the only forms that open are the ones related for the job.

If a user creates a job and clicks the save record button more than once, this is the cmd that also runs the create records command, then the user gets error messages. And the additional record is not created due to the field that's being populated having an index (yes, no duplicates) property. Prior to this, a new job was not created, but a duplicate record was created in the related table, with no info except a foreign key with the identical value of the one just created (by clicking the cmd button the first time).

The advice I gave you, to not bother to create the otherwise empty record until you need to put something else in it, would prevent that because you could test the child table. Do a DCount of records in the child table where the foreign key matches your new value.

However, something else now bothers me A LOT. What you asked STRONGLY sounds like you are making 1-to-1 relationships between this main table and the other tables, because otherwise you wouldn't care about duplicate entries in these secondary tables. While it is perfectly possible to build tables with a 1-to-1 relationship, it is ALMOST always wrong. Like 98%+ of the time, wrong. Among other things, if you don't have one of the two or three exceptional cases to consider, those records - as you described them so far - would violate a "purity of purpose" rule. You have talked about the FK but that INSERT you showed us didn't contain any primary key. So the only identifying value in that table after that insert isn't a PK, it's an FK, and that means that the record cannot be identified at all (by strict consideration of key usage conventions.) I'm sure it seems clear to you, but to me, at least, that record is a ghost with no substance.

The way I see it, these other tables EITHER shouldn't exist AT ALL, or they should not be of the kind where you care about duplicates. Further, if you actually TRIED to build a relationship between your Job table and a "related" table with a 1-to-1 relationship (as is implied by having a unique index), you would potentially run into a case where you could never create a new record in either table if you tried to maintain relational integrity. Do that more than once and you enter into what is called a "deadly embrace."

Before you go off building ANY tables, describe these tables for us in English so we can advise you of proper infrastructure.

And for the record, once you have decided that you actually NEED a record in the "related" table, the "INSERT" method you chose IS one possible starting point to add the record, though by my standards, you would not create that record until you had more than a foreign key to enter into it.

 

[Pat Hartman]

Do NOT create records before you need them. There are two ways to solve the problem depending on whether you are using a subform or popup to add the child records.

In either case, go to the relationships window and create a relationship between the two tables and enforce Referential Integrity.

Now if you are using a subform to add the child records click on the subform control and on the data tab, choose the master/child links. This will connect the main form to the subform and tell Access how to populate the foreign key in the subform. Now, whenever you add a record to the subform, it is automatically connected to the Job. There is no need to use a combo to do it manually.

If you are using popup forms, then in the BeforeInsert event of the popup, you need to pull the value in yourself since Access can't do it for you.

Me.SomeID = Forms!yourcallingform!SomeID

Using the BeforeInsert event ensures that if multiple records are added, ALL of them will have the PK populated. It also means that your code isn't dirtying the popup so it won't cause empty records to be added.

 

[sheckay]

Do NOT create records before you need them. There are two ways to solve the problem depending on whether you are using a subform or popup to add the child records.

In either case, go to the relationships window and create a relationship between the two tables and enforce Referential Integrity.

Now if you are using a subform to add the child records click on the subform control and on the data tab, choose the master/child links. This will connect the main form to the subform and tell Access how to populate the foreign key in the subform. Now, whenever you add a record to the subform, it is automatically connected to the Job. There is no need to use a combo to do it manually.

If you are using popup forms, then in the BeforeInsert event of the popup, you need to pull the value in yourself since Access can't do it for you.

Me.SomeID = Forms!yourcallingform!SomeID

Using the BeforeInsert event ensures that if multiple records are added, ALL of them will have the PK populated. It also means that your code isn't dirtying the popup so it won't cause empty records to be added.

Hello Pat. Thank you for your warning and your recommendation. I will soon look into implementing it. I'll update with the progress made. Thanks again.

 

[The_Doc_Man]

First, let me say that it is your DB and you have to make it work. I'm an interactive spectator from a distance. Having said that, this statement leads me to a concern.

As far as checking for duplicates upon creation of new records, with that field getting populated, I just changed the index property of that very field to "yes, no duplicates".

I'm trying to make my head fit around this. I keep coming back to a "purity of purpose" problem. This Job ID that is a PK in one table and a unique FK in other tables violates some of the versions of the definition of a PK. If you have a record that depends only on a single value then that value should be the PK of the table unless you have another equally valid PK. But in your secondary tables, you have an FK value that cannot be duplicated, which means that the no-dup field should be the PK for that secondary table, again unless you have another equally valid PK. BUT in "pure" database design, things that depend on the same PK are part of the same things in database terms. They should be in the same table. I'll repeat that: If these things depend on the same key they should be in the same table.

OK, I'm a purist in some things and a pragmatist in others. So I fully understand that you should do what you think you have to do. But what you describe suggests to me that you may have a subtle design problem waiting to wake up and bite you somewhere. This problem would be in the category of "improper normalization." It sounds like it would be a work multiplier, i.e. requires extra work to be properly handled.

 

[sheckay]

First, let me say that it is your DB and you have to make it work. I'm an interactive spectator from a distance. Having said that, this statement leads me to a concern.



I'm trying to make my head fit around this. I keep coming back to a "purity of purpose" problem. This Job ID that is a PK in one table and a unique FK in other tables violates some of the versions of the definition of a PK. If you have a record that depends only on a single value then that value should be the PK of the table unless you have another equally valid PK. But in your secondary tables, you have an FK value that cannot be duplicated, which means that the no-dup field should be the PK for that secondary table, again unless you have another equally valid PK. BUT in "pure" database design, things that depend on the same PK are part of the same things in database terms. They should be in the same table. I'll repeat that: If these things depend on the same key they should be in the same table.

OK, I'm a purist in some things and a pragmatist in others. So I fully understand that you should do what you think you have to do. But what you describe suggests to me that you may have a subtle design problem waiting to wake up and bite you somewhere. This problem would be in the category of "improper normalization." It sounds like it would be a work multiplier, i.e. requires extra work to be properly handled.

Hello Doc. That is a very interesting analysis of this database. I'll have to experiment with the idea of the FK being the PK in those extra fields. Thank you for your help, and your balanced opinion. I'll keep you updated. Thanks again.

 

[arnelgp]

Private Sub Command18_Click()
DoCmd.RunSQL "INSERT INTO shipRECtbl(jobIDfk) VALUES ('" & Me.JOB_ID & "' );"
End Sub

if you use Currentdb instead of DoCmd.RunSQL, you will not get Any message:

Currentdb.Execute "INSERT INTO shipRECtbl(jobIDfk) VALUES ('" & Me.JOB_ID & "' );"

 

[sheckay]

if you use Currentdb instead of DoCmd.RunSQL, you will not get Any message:

Currentdb.Execute "INSERT INTO shipRECtbl(jobIDfk) VALUES ('" & Me.JOB_ID & "' );"

Thank you for that suggestion. When I entered/replaced the command with that one it's not working. Maybe because there hasn't been a record for the job created yet in the referenced tables?

 

[sheckay]

Do NOT create records before you need them. There are two ways to solve the problem depending on whether you are using a subform or popup to add the child records.

In either case, go to the relationships window and create a relationship between the two tables and enforce Referential Integrity.

Now if you are using a subform to add the child records click on the subform control and on the data tab, choose the master/child links. This will connect the main form to the subform and tell Access how to populate the foreign key in the subform. Now, whenever you add a record to the subform, it is automatically connected to the Job. There is no need to use a combo to do it manually.

If you are using popup forms, then in the BeforeInsert event of the popup, you need to pull the value in yourself since Access can't do it for you.

Me.SomeID = Forms!yourcallingform!SomeID

Using the BeforeInsert event ensures that if multiple records are added, ALL of them will have the PK populated. It also means that your code isn't dirtying the popup so it won't cause empty records to be added.

Hello Pat. Your recommendation is very much appreciated. It appears to be a much better solution than the one that I've concocted. However, I can't get it to run.
I've started with the first form, whose data source is the shipRECtbl table. And this table has the jobIDfk field that I need to be populated.
That data is coming from the JOB_ID field in the JOB_tbl table.
So I tried a few versions of the command, and I used the error messages to figure out what needed to be altered. And now I'm stuck. Here's what I entered:
shipRECtbl.jobIDfk = Forms!JOB_FRM!JOB_ID
I get a runtime error 424 Object required
I've looked that error up and I see that it may be:

The error is caused by a function being called on a reference you’ve either misspelled, or have not declared.
The solution is to ensure you have all the appropriate references declared within your code. The simplest ways to fix the problem is as follows

Am I missing something?

 

[Pat Hartman]

I take it you are not using a subform but you are opening a dependent form. Both forms must be open. You cannot reference a form unless it is open. If you have forms with related data that are opened by themselves, there is no option except to use a combo to select the job to make the relationship. Can you post a database and tell us which forms to look at

 

[sheckay]

I take it you are not using a subform but you are opening a dependent form. Both forms must be open. You cannot reference a form unless it is open. If you have forms with related data that are opened by themselves, there is no option except to use a combo to select the job to make the relationship. Can you post a database and tell us which forms to look at

OK. Here it is. If you open Job_FRM, create a new job within there. Then click on the Shipment Received form via the button (open ship received), then try to enter anything into that form, you'll get that error. The Shipment Received form is the one that I've entered the code you recommended. It's where I get the 424 error, even when the two forms referenced are open. Thank you for your help.

 

[sheckay]

I changed the code to: shipmentsReceivedFRM.jobIDfk = Forms!JOB_FRM!JOB_ID

Still no luck. Same error

 

[Pat Hartman]

I made a lot of changes but lots more are needed.
Tables:
1. I made JobID required and removed the 0 default
2. Removed the lookup from Job. There are lots of issues with table level lookups. They are a crutch. It is best to just not use them.

Forms:
1. I renamed the controls on the Job_FRM. When Access adds a control using the wizard, it gives the control the name of the field it is bound to. When you add controls manually, YOU must give the controls rational names. NEVER leave them as text 123 or combo45.
2. I converted the macros to VBA.
3. On each of the forms referenced on the Job_FRM:
3.1 Added Option Explicit - to force Access to raise certain errors at compile time instead of execution time
3.2 Modified the code of all the save buttons to actually save the record. You were getting away with this code which wasn't doing what you thought only because Access always automatically saves records.
3.3 In the button code on Job_FRM, I preceded the open with a save. You MUST save the current record manually before opening a popup form. Access doesn't do it for you. Do it before opening a report or exporting data also.
3.4 I added the jobID to every dependent form. You didn't have it on any of them. If you don't want to see it, just set the visible property to false but don't delete the control. MS has changed the rules. Now if you want to reference a field in code, you must bind it to a control.
3.5 I added the code to the BeforeInsert event of all these forms to ensure that the FK gets populated.

YOU need to review your schema. At a minimum, you have two tables which need to be redone. ShipOut and ShipRec. Both have repeating groups. These are obvious when you have a bunch of fields with identical names with suffixes to make the names unique. These suffixed fields need to be eliminated and moved to a child table with one instance per row instead of the bunch you have. When you have more than one instance of data, you have many and that means you need a separate table so you can have one row per shipment rather than a fixed number of shipments on a row.

 

[sheckay]

I made a lot of changes but lots more are needed.
Tables:
1. I made JobID required and removed the 0 default
2. Removed the lookup from Job. There are lots of issues with table level lookups. They are a crutch. It is best to just not use them.

Forms:
1. I renamed the controls on the Job_FRM. When Access adds a control using the wizard, it gives the control the name of the field it is bound to. When you add controls manually, YOU must give the controls rational names. NEVER leave them as text 123 or combo45.
2. I converted the macros to VBA.
3. On each of the forms referenced on the Job_FRM:
3.1 Added Option Explicit - to force Access to raise certain errors at compile time instead of execution time
3.2 Modified the code of all the save buttons to actually save the record. You were getting away with this code which wasn't doing what you thought only because Access always automatically saves records.
3.3 In the button code on Job_FRM, I preceded the open with a save. You MUST save the current record manually before opening a popup form. Access doesn't do it for you. Do it before opening a report or exporting data also.
3.4 I added the jobID to every dependent form. You didn't have it on any of them. If you don't want to see it, just set the visible property to false but don't delete the control. MS has changed the rules. Now if you want to reference a field in code, you must bind it to a control.
3.5 I added the code to the BeforeInsert event of all these forms to ensure that the FK gets populated.

YOU need to review your schema. At a minimum, you have two tables which need to be redone. ShipOut and ShipRec. Both have repeating groups. These are obvious when you have a bunch of fields with identical names with suffixes to make the names unique. These suffixed fields need to be eliminated and moved to a child table with one instance per row instead of the bunch you have. When you have more than one instance of data, you have many and that means you need a separate table so you can have one row per shipment rather than a fixed number of shipments on a row.

Wow! Thank you for your help! It must have taken a lot to go through it like that, and then to fix those issues on top of it. I have a lot to learn. I'm going to open it up, look into it, and carry thought with what you recommend right away. Thank you!

 

[Pat Hartman]

You're welcome. It probably took about a half hour. Making this type of change is tedious rather than difficult. I was only going to fix one of the buttons but when I converted the macro, all got converted and a couple converted incorrectly for some reason so I just fixed them all.

Your schema needs work though. Another interface option is to use a tab control with subforms instead of opening up separate forms.