Inserting Data to Same Table

[mike60smart]

Hi Everyone

I have a Main Form named "frmQuotes"
With a subform named "frmQuotePartNoSubForm" whose Record Source is "tblQuotationPartNo" which displays as follows:-

The fields in the table are :-
QuotePartNoID - PK
QuoteID - FK
PartNo - Text

I have some VB on a Command Button which generates a New Quote with a Revision letter.

This works just fine and creates a new record with the new revision.

What I am trying to do is append the Part No's to tblQuotationPartNo but with a new QuoteID
that has been obtained by using the following Reference in an Unbound Textbox named txtQuoteID:-
=[Forms]![frmQuotes]![frmQuotationPartNosubform].[Form]![LastOfQuoteID]

The Record Source of the Form "frmQuotationPartNosubform" is as follows:-

SELECT TOP 1 Last(tblQuotation.QuoteID) AS LastOfQuoteID FROM tblQuotation ORDER BY Last(tblQuotation.QuoteID) DESC;

I have tried to make this process as clear as possible and hope you are following.
How can I do this by using a Command Button on either the Main Form or Subform ?
Any help appreciated

 

[bastanu]

If QuoteID is an autonumber (which it should) use Max instead of Last to get the newly created one. And you don't really need the form, just use DMax in the insert SQL statement. You can create a new insert query in design view and add a calculated field using dMax to get the new quote id and append that to the FK field but also have the QuoteID field from the table in the query because that is where you need to add the criteria (=the original QuoteID that hopefully you still have it loaded somewhere on your form); don't append this field. Once the query is OK you can save it and call it from your button or copy its SQL and call it using CurrentDB.Execute (you will need to change it a bit to isolate the variable for the original ID).

Cheers,

 

[ebs17]

Jet and ACE, unlike SQL Server have NO certain way of obtaining the last autonumber inserted by "you".

This statement is incorrect.

db.Execute strSQL, dbFailOnError
lngArtIDNew = db.OpenRecordset("Select @@Identity")(0)

 

[The_Doc_Man]

I believe that a comma is missing for Pat's statement, and that it is correct if you supply the comma.

Jet and Ace, unlike SQL Server, have NO certain way of obtaining the last autonumber inserted by "you".

You could execute a query if your record contains your ID, in which case you COULD find the record with the highest Autonumber ID AND your ID. But neither Jet nor ACE has the "@@Identity" construct. You have to actually ask the right question of SQL (not of Jet or ACE).

 

[ebs17]

The instruction shown works on an access file as a backend. Try it.
What SQL other than Jet-SQL would be involved, and what would Jet/ACE understand other than Jet-SQL?

 

[mike60smart]

Jet and ACE, unlike SQL Server have NO certain way of obtaining the last autonumber inserted by "you". In a multi-user environment, multiple people are all adding records and timing is everything. If you both hit insert at the same time, one record will be saved and then the next. Even if your next instruction is a dMax() to obtain the "last" autonumber ID, it might not be your record. THEREFORE, if you are using Jet/ACE, the safest method is to copy the parent record using DAO. That allows you to capture the specific RecordID that "you" just entered. Then to copy the child records, you can use an append query that takes TWO argument's. It takes the OLD OrderID to select the records to be copied and it uses the NEW OrderID in the append clause as the FK rather than the FK from the old record.

In the rare cases where you have more than two levels, the process is:
1. copy the parent level using DAO so you can obtain the new ID
2. Using two recordsets, create a loop that reads the second level table using the OldID to select the records from the first recordset. Then use .AddNew on the second recordset for each record using the NewID as the FK
3. the third or "bottom" level can always be copied using an append query with two arguments.

I've never had four levels but they would be handled by repeating step 2 for the 3rd table. So all the levels between 1 and bottom use a DAO loop to copy so they can append the correct FK value.

Hi Pat

Would it be possible for you to give me an example of both of the following?
1. copy the parent level using DAO so you can obtain the new ID
2. Using two recordsets, create a loop that reads the second level table using the OldID to select the records from the first recordset. Then use .AddNew on the second recordset for each record using the NewID as the FK