Solved How to insert data at one go in both the parent table and child table in Microsoft Access (1 Viewer)

[nector]

Please understand me on this one very carefully, I did not want to continue from the previous question that I asked before. The code listed below is working Okay after struggling to make it work. However, many thanks to your suggestions they helped a lot.

The only issue I have is that the foreign Key after inserting the record remain hidden in a combo box, so my question is that, is there a way to have this foreign key be visible as soon as the record is inserted. I do not need to do it manually otherwise there will be a lot of human errors.

Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Set db = CurrentDb
sSQL = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT [tblJobQuotation].[QTRDate], [tblJobQuotation].[JobNumber], [tblJobQuotation].[CustomerName]" & _
"FROM [tblJobQuotation] WHERE [tblJobQuotation].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQL, dbFailOnError
sSQLOne = "INSERT INTO [tblJobworksDetails] ( Descriptions, QTY )" & _
"SELECT [tblJobQtDetails].[Description], [tblJobQtDetails].[QTY] " & _
"FROM [tblJobQtDetails] WHERE [tblJobQtDetails].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQLOne, dbFailOnError

The brackets are not an issue here , removing brackets access VBA throughs an error. So forget about brackets.

 

[Uncle Gizmo]

The only issue I have is that the foreign Key after inserting the record remain hidden in a combo box,

Have a look at my blog below, where I demonstrate how you can display values from a combo box in a separate text box(es)....

Display Data from a Combo Box in a Text Box​

Display Data from a Combo Box in a Text Box - Nifty Access

Display Data from a Combo Box Combo boxes are one of the most valuable controls you can have on a form, they are so versatile and give you such an extra range of functions for your form that you should really consider understanding how to use them. Also with a combobox as your foundation, you

www.niftyaccess.com

I'm not sure this will work, because I don't know how your combo box is set up.

 

[Minty]

The brackets are not an issue here , removing brackets access VBA throughs an error. So forget about brackets.

Good to see you took on board the suggestion to debug.print your sql strings, to see why you have to use all those unnecessary brackets.

 

[CJ_London]

The only issue I have is that the foreign Key after inserting the record remain hidden in a combo box,

I don't see where you are inserting a foreign key (I assume the second insert) - you are only inserting a description and quantity

 

[ebs17]

I'm not entirely clear what you mean either.
A ComboBox has a RowSource. If you want to use the value of a column other than the bound column, you must specify that column explicitly.

Me.CboWorksOrder.Columns(3)        ' for the 4th column

If the value is only created by appending it to a table, the RowSource of the ComboBox would have to be updated.

Me.CboWorksOrder.Requery

 

[Pat Hartman]

The only issue I have is that the foreign Key after inserting the record remain hidden in a combo box, so my question is that, is there a way to have this foreign key be visible as soon as the record is inserted. I do not need to do it manually otherwise there will be a lot of human errors.

You need code after the first insert to obtain the PK of the new record so you can use it as the FK when you insert the child record. Look for posts with @@Identity

 

[LarryE]

Please understand me on this one very carefully, I did not want to continue from the previous question that I asked before. The code listed below is working Okay after struggling to make it work. However, many thanks to your suggestions they helped a lot.

The only issue I have is that the foreign Key after inserting the record remain hidden in a combo box, so my question is that, is there a way to have this foreign key be visible as soon as the record is inserted. I do not need to do it manually otherwise there will be a lot of human errors.

Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Set db = CurrentDb
sSQL = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT [tblJobQuotation].[QTRDate], [tblJobQuotation].[JobNumber], [tblJobQuotation].[CustomerName]" & _
"FROM [tblJobQuotation] WHERE [tblJobQuotation].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQL, dbFailOnError
sSQLOne = "INSERT INTO [tblJobworksDetails] ( Descriptions, QTY )" & _
"SELECT [tblJobQtDetails].[Description], [tblJobQtDetails].[QTY] " & _
"FROM [tblJobQtDetails] WHERE [tblJobQtDetails].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQLOne, dbFailOnError

The brackets are not an issue here , removing brackets access VBA throughs an error. So forget about brackets.

There is no reason whatever to have a foreign key visible. If fact, you never want it visible because you never want the user to change it accidently. Leave it visible property to No. If for some reason it is necessary to change the foreign key, then change it with VBA code but leave it not visible.

 

[nector]

Well, I think there is just too much misunderstanding here, so I have decided to use a small database to demonstration the problem here, now you can see properly that the foreign key is hidden which is not supposed to be the case. I want this key to visible always no matter what?

Private Sub CmdJobsworks_Click()
Dim db As DAO.Database
Dim sSQL As String
Dim sSQLsnd As String
Set db = CurrentDb
sSQL = "INSERT INTO tblJobcosting ( WorkDate, WorKorder, NotesDetails, CompleteJob, JobPrice )" & _
"SELECT [tblJobWorks].[WorkDate],[tblJobWorks].[WorKorder],[tblJobWorks].[NotesDetails],[tblJobWorks].[CompleteJob],[tblJobWorks].[JobPrice]" & _
"FROM [tblJobWorks] WHERE [tblJobWorks].[ID] = " & Me.CboJobWorks
db.Execute sSQL, dbFailOnError
sSQLsnd = "INSERT INTO tblJobcostingDetails ( Qty, Descriptions, VatRate )" & _
"SELECT [tblJobworksDetails].[Qty], [tblJobworksDetails].[Descriptions], [tblJobworksDetails].[VatRate]" & _
"FROM [tblJobworksDetails] WHERE [tblJobworksDetails].[ID] = " & Me.CboJobWorks
db.Execute sSQLsnd, dbFailOnError
End Sub

 

[ebs17]

the foreign key is hidden

Before one do anything else, you should mention the name of the key field and the two tables involved.
Unless you offer a case of good whiskey for a successful guess.

 

[nector]

Okay my apologies the foreign key field is called ID in a table called tblJobcostingDetails

SELECT [tblJobcosting].[ID] FROM tblJobcosting;

 

[ebs17]

The ID field in tblJobcosting is a primary key, contrary to your statement.
Do you know what you're doing?
The DB lab was free (a waste of time).

 

[nector]

The ID field in tblJobcosting is a primary key, contrary to your statement.
Do you know what you're doing?
The DB lab was free (a waste of time).

Please try to understand me here a primary in the parent table becomes a foreign key in the child table as simple as that, but that foreign key (ID) is the one being hidden and as long as it remains hidden then that data remains orphans.

That is where my problem is, it's not about understanding DB lab here, that is incorrect judgement.

 

[bob fitz]

Nector

Perhaps you just need to change the "Data Entry" property of the main form from Yes to No

 

[LarryE]

but that foreign key (ID) is the one being hidden and as long as it remains hidden then that data remains orphans.

A textbox that holds a foreign key and is hidden or not hidden has absolutley nothing to do with orphaned records. As long as a foreign key is entered in the foreign key textbox on the form at the time a new child record is made, that is all that is required. In fact, you should hide foreign keys on forms so users do not accidently change them. And their Tab Stop property should always be set to No.

 

[Pat Hartman]

The problem is exactly what I told you in #6. When you copy the parent record to the other table, a new autonumber ID is generated. In order to keep the child records linked to that parent, you need to obtain the ID from the newly inserted record and include it as the FK value in the second insert.

 

[nector]

Okay my knowledge ends here now if I run the code below everything is working according to my requirements, However, my worry is DLast function can it work against SQL server database especially Passthrough Query?????????

That is why if it was possible to run the queries below in VBA I will highly appreciate

INSERT INTO tblJobcosting ( WorkDate, WorKorder, NotesDetails, CompleteJob, JobPrice )
SELECT tblJobWorks.WorkDate, tblJobWorks.WorKorder, tblJobWorks.NotesDetails, tblJobWorks.CompleteJob, tblJobWorks.JobPrice
FROM tblJobWorks
WHERE (((tblJobWorks.ID)=[Forms]![FRMJobWorks]![CboJobWorks]))

INSERT INTO tblJobcostingDetails ( Qty, Descriptions, VatRate, ID )
SELECT tblJobworksDetails.Qty, tblJobworksDetails.Descriptions, tblJobworksDetails.VatRate, DLast("ID","tblJobcosting") AS FPKeys
FROM tblJobWorks INNER JOIN tblJobworksDetails ON tblJobWorks.ID = tblJobworksDetails.ID
WHERE (((tblJobWorks.ID)=[Forms]![FRMJobWorks]![CboJobWorks]));

 

[Minty]

That is very unreliable in a multiuser situation. (The Last function in particular is extremely flaky)
If you want to do this on the server simply pass all the parameters into a stored procedure and let it do the hard work.

Or as previously suggested do it in Access and get the inserted PK ID using the @@Identity process.

If you need help with the stored procedure we can Assist.

 

[nector]

Finally after working so hard the code below is now working from VBA this way I'm now able to avoids the trouble with SQL Server.

Private Sub CmdAudit_Click()
Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Dim NewID As Integer
Set db = CurrentDb
sSQL = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT [tblJobQuotation].[QTRDate], [tblJobQuotation].[JobNumber], [tblJobQuotation].[CustomerName]" & _
"FROM [tblJobQuotation] WHERE [tblJobQuotation].[JobQID] = " & Me.CboJobWorks
db.Execute sSQL, dbFailOnError
NewID = DLast("ID", "tblJobcosting")
sSQLOne = "INSERT INTO tblJobcostingDetails ( Qty, Descriptions, VatRate, ID )" & _
"SELECT tblJobworksDetails.Qty, tblJobworksDetails.Descriptions, tblJobworksDetails.VatRate, NewID" & _
"FROM FROM tblJobWorks INNER JOIN tblJobworksDetails ON tblJobWorks.ID = tblJobworksDetails.ID WHERE [tblJobQtDetails].[JobQID] = " & Me.CboJobWorks
db.Execute sSQLOne, dbFailOnError
End Sub

 

[CJ_London]

quite frankly your code does not make sense as written. You have a number of errors in your created sql code (missing spaces, which will create a runtime error) and if it is working as intended then the implication is what you have shown is not what you have.

You finally 'got it' about getting the newly created ID - although using the unreliable DLast rather than the @@Identity. But then you don't do anything with it.

If it is supposed to be in this line

"SELECT tblJobworksDetails.Qty, tblJobworksDetails.Descriptions, tblJobworksDetails.VatRate, NewID" & _

that won't work because as constructed NewID needs to be a field in either tblJobWorks or tblJobworksDetails - if that does not exist you will get a runtime error and if does exist, it won't have the same value as your generated NewID

it should be

"SELECT tblJobworksDetails.Qty, tblJobworksDetails.Descriptions, tblJobworksDetails.VatRate, " & NewID & _

 

[Minty]

I'm not sure how to respond to that, but you completely ignored my advice about using DLast, and could have googled very quickly to find the reliable method to get the last inserted ID.

Insert... ; Select @@identity

I work with project access (*.adp) I want to know if it possible to take the last ID after the sql INSERT QUERY my code: strSQL = "INSERT INTO missionFather ....." DoCmd.RunSQL strSQL thanks!

www.access-programmers.co.uk

Which in your case would mean adding

Dim rs as DAORecordset

   Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    NewID = rs!LastID
    rs.Close

To the appropriate place in your code.