Need to create a new record in multiple tables simultaneously, using one form

[davemarco]

Hi all. I currently have a pharmaceutical lot database set up in the following format:

MFGData (table w/Manufacturing Info)
QAData (table w/ Quality Assurance Info)
QCData (table w/ Quality Control Info)
PASData (table w/ Process & Analytical Science Info)
SCData (table w/ Supply Chain Info)

frmMFGData (form for entering in Manufacturing Info)
frmQAData (form for entering in Quality Assurance Info)
frmQCData (form for entering in Quality Control Info)
frmPASData (form for entering in Process & Analytical Science Info)
frmSCData (form for entering in Supply Chain Info)

Each table has a corresponding form for data entry in each area. The tables were subdivided in this way in order to limit each department's ability to edit the data of other departments. The only field common to each table is the drug lot number, or "Lot #" (which is the primary key of each table).

I wanted to make it so that when Manufacturing enters a new lot number on frmMFGData, it automatically creates that lot number in the other 4 tables. This process mirrors our actual real world business process, where drugs are manufactured and assigned new lot numbers by our manufacturing team, and then other departments simply reference those numbers when doing their part. To accomplish this, I went ahead and set up 1 to 1 relationships between the various tables using their "Lot #" fields, establishing referential integrity and enabling cascading updates. However, when I attempted to enter a new lot number into frmMFGData (the manufacturing form), it didn't seem to appear in any of the other tables. If I edit an existing lot number and change it to something else, the change does carry over to the other tables, so I know that the cascading updates are working in some capacity.

If cascading updates cannot "cascade" new records, then is there any other way to accomplish this?

 

[llkhoutx]

On an appropriate event, use ADOor DAO code to create those records, e.g.

sub YourEvent
dim db as dao.database
dim rs(5) as dao.recordset
dim i as long

set db=currentdb

for i = 1 to 5
set rs(i) = db.openrecordset("YourTaableiName",doopendynaset)
rs(i).addnew
rs(i).FieldnamI = yournewvalue
....
rs(i).fieldnameJ = anothernewvalue
next i

rs.close
db.close
set rs=nothing
set db=nothing

end sub

 

[CJ_London]

If the only reason for separate tables is to prevent departments from editing other departments data then you don't need separate tables. Since each department has its own form, simply limit the recordsource for that form to the fields they are allowed to edit/view.

If you still want extra tables, then cascading updates cannot "cascade" new records but if you are using 2010 (may also be in 2007), investigate using a data macro to create your records in the other tables - go into table design and click on Create Data Macro's in the ribbon. I don't use them myself so cannot advise further but would be interested to know how you get on.

The alternative is to have some vba code to insert records in the form after update event of the manufacturing form

 

[davemarco]

Hi llkhoutx,

Thanks for the reply. I took a brief course on VBA, but I am not currently very skilled in that arena. Is this a problem that can only be solved using VBA?

Could you provide a small explanation of what your code does, or maybe some developer's comments for the various sections? I'm not clear on what does what here.

Thanks!

 

[llkhoutx]

Look it up what my code does. It's elementary.

There's always numerous way to solve a problem.

 

[davemarco]

If the only reason for separate tables is to prevent departments from editing other departments data then you don't need separate tables. Since each department has its own form, simply limit the recordsource for that form to the fields they are allowed to edit/view.

If you still want extra tables, then cascading updates cannot "cascade" new records but if you are using 2010 (may also be in 2007), investigate using a data macro to create your records in the other tables - go into table design and click on Create Data Macro's in the ribbon. I don't use them myself so cannot advise further but would be interested to know how you get on.

The alternative is to have some vba code to insert records in the form after update event of the manufacturing form

Thanks CJ. I had considered consolidating all of the tables, and may have to do that if it comes to it. Unfortunately, I've written a bunch of rather extensive SQL queries that reference the current table format, and it would be a major headache to change them all. I'll keep that possibility on the back burner as Plan B.

As for the vba code and/or macros, we're definitely thinking along the same lines. I had originally tried to add an After Update macro that creates a new record in each of the other tables, but was unable to find a "Create New Record" command in the macro list. With regards to VBA, I was thinking of using something in conjunction with an "After Insert()" event, but I'm not sure if those work with forms.

 

[davemarco]

Look it up what my code does. It's elementary.

There's always numerous way to solve a problem.

Thanks for the reply. I can certainly understand how it might seem elementary to someone who understands it. Unfortunately, as I stated, I am not VBA proficient. I found pages explaining what several of the key words do, but I'm not understanding how they fit together. It looks like you're temporarily creating a new database, and I see that you have a loop that goes around 5 times, but I'm not following your syntax or intent. Could you possibly explain your code logic a little?

Thanks again.

 

[spikepl]

Depending on how the UI is constructed, but in reality you do not need to create all those recrds at the same time, becasue no one looks a tthem all at the same time. You need to have them available when needed - and that is a different thing. When you open a form for a given type of data, you could just check if the record for the batch exists, and make it if it doesnt.

 

[davemarco]

Depending on how the UI is constructed, but in reality you do not need to create all those recrds at the same time, becasue no one looks a tthem all at the same time. You need to have them available when needed - and that is a different thing. When you open a form for a given type of data, you could just check if the record for the batch exists, and make it if it doesnt.

I am definitely following your train of thought. Unfortunately, I have several reports that our end users can generate that are based on queries. These queries use the "Lot #" field for their INNER JOIN's, which requires the lot numbers to exist in all tables at the time that the queries are run. I would think that creating all 5 of the necessary records at once would be less messy than attempting to manage so many "just in time" solutions, no?

 

[CJ_London]

These queries use the "Lot #" field for their INNER JOIN's

Make them LEFT JOINS.

Presumably the manufacture table has the 'master' records from which all the other records are created so you would left join from this table to all the other tables. This will show all the manufacture records plus all the others where they exist and nulls if they don't (which if they have only been created and not completed would be the case anyway)

I don't advise using # in field names. Although 'legal', you can end up with unexpected errors if your code in not exactly right. Given your level of expertise at the moment, I would do anything to reduce the risk of errors - which includes having spaces and/or non alpha numeric characters in field and table names

You say Job# is the primary key in your other tables - I hope this is not an autonumber - if it is there is no guarantee you will be able to keep the tables linked

 

[davemarco]

Make them LEFT JOINS.

Presumably the manufacture table has the 'master' records from which all the other records are created so you would left join from this table to all the other tables. This will show all the manufacture records plus all the others where they exist and nulls if they don't (which if they have only been created and not completed would be the case anyway)

I don't advise using # in field names. Although 'legal', you can end up with unexpected errors if your code in not exactly right. Given your level of expertise at the moment, I would do anything to reduce the risk of errors - which includes having spaces and/or non alpha numeric characters in field and table names

You say Job# is the primary key in your other tables - I hope this is not an autonumber - if it is there is no guarantee you will be able to keep the tables linked

Thankfully no, "Lot #" is manually entered.

 

[davemarco]

Ok, I've been studying this, and I think that I'm starting to get some of it. I was hoping that you might be able to fill in some of the blanks for me.

sub YourEvent //Defining the event. I'm think that I might want AfterInsert() for this?
dim db as dao.database //declaring database object
dim rs(5) as dao.recordset //declaring a 5 record recordset array?
dim i as long //declaring loop variable (assuming than an int would work as well)

set db=currentdb //copying over the actual database to the temp variable

for i = 1 to 5 //loop
set rs(i) = db.openrecordset("YourTaableiName",doopendynaset)

//I'm a little confused here. Does this copy the contents of an entire table ("YourTableName") to the recordset variable (in this case "rs")? If so, how do I get it to zero in on the "Lot #" that was just typed in by the user on the frmMFGData form? Also, can you explain why we'd use a dynaset type here?

rs(i).addnew //adding a new record to temporary record set "rs"? How would this help me add the new records to all of my tables?
rs(i).FieldnamI = yournewvalue //Not sure how to convert this to be equal to the lot # that was just typed into the frmMFGData form
.... //not sure what this does
rs(i).fieldnameJ = anothernewvalue //not sure what new values I'm putting here
next i //sends loop to next cycle

rs.close //close temp recordset
db.close //close temp database
set rs=nothing //delete temp database
set db=nothing //delete temp recordset

end sub

Overall, I'm starting to understand, but there are certain aspects of the code logic that are unclear to me. Could you possibly explain some of these finer points? Thanks.

 

[CJ_London]

you are mixing up dao and ado types of process. And there is no real need to loop for what you are trying to achieve.

Open your frmMFGData in Design view and paste the following into the form afterupdate event

Private Sub Form_AfterUpdate()
    
    'add records to each table    
    CurrentDb.Execute "INSERT INTO QAData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO QCData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO PASData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO SCData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    
End Sub

This has used the table names you supplied, the only thing I have changed is Lot # to LotNo. If you insist on using Lot # then you will need to surround each instance with square brackets like this [Lot #]

I've assumed the LotNo is numeric and not text and that this has been added manually by a user in the frmMFGData form and the control name is the same as the field name.

You will also need to think about how to ensure a user enters a lot number before updating any records.

 

[davemarco]

you are mixing up dao and ado types of process. And there is no real need to loop for what you are trying to achieve.

Open your frmMFGData in Design view and paste the following into the form afterupdate event

Private Sub Form_AfterUpdate()
    
    'add records to each table    
    CurrentDb.Execute "INSERT INTO QAData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO QCData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO PASData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO SCData (LotNo) VALUES(" & Me.LotNo & ")", dbFailOnError
    
End Sub

This has used the table names you supplied, the only thing I have changed is Lot # to LotNo. If you insist on using Lot # then you will need to surround each instance with square brackets like this [Lot #]

I've assumed the LotNo is numeric and not text and that this has been added manually by a user in the frmMFGData form and the control name is the same as the field name.

You will also need to think about how to ensure a user enters a lot number before updating any records.

Simple and elegant. This works perfectly, thanks!