Insert Into Heeelllllllp!!!!

  • Thread starter Thread starter Derek
  • Start date Start date
D

Derek

How do I insert records into two tables one table is the
main form and the second is a subform table as in orders
and items. I want to be able to automatically place a
repeat order without having to do all the detail work

I am assuming that if I choose the main form record first
and append the record to its underlying table, that the
new record will generate a new number in the counter field
of the main form table. As this is also the referencial
integrity linking field to the subform table when I append
records from the subform it needs the new linking field
number in the main table to allow the appending of new
records to the subform table. how will it know the number
of the linking field? Am I missing something simple?
 
Derek said:
How do I insert records into two tables one table is the
main form and the second is a subform table as in orders
and items. I want to be able to automatically place a
repeat order without having to do all the detail work

I am assuming that if I choose the main form record first
and append the record to its underlying table, that the
new record will generate a new number in the counter field
of the main form table. As this is also the referencial
integrity linking field to the subform table when I append
records from the subform it needs the new linking field
number in the main table to allow the appending of new
records to the subform table. how will it know the number
of the linking field? Am I missing something simple?
Click to expand...

Add the parent record via a recordset so that you can get the autonumber
ID for the added record, then use an append query to copy the records
from the child table, specifying the new number as a literal value.
Here's a fairly flexible routine for the purpose:

'----- start of code -----
Private Sub cmdDupAll_Click()

Dim varOldMainKey As Variant
Dim varNewMainKey As Variant
Dim fld As DAO.Field
Dim strSQL As String
Dim strFieldList As String
Dim strLinkMaster As String
Dim strLinkChild As String

strLinkMaster = Me.tSub.LinkMasterFields
If Left(strLinkMaster, 1) = "[" Then
strLinkMaster = Mid(strLinkMaster, 2, Len(strLinkMaster) - 2)
End If
varOldMainKey = Me.Controls(strLinkMaster).Value
strLinkMaster = Me.Controls(strLinkMaster).ControlSource

strLinkChild = Me.tSub.LinkChildFields
If Left(strLinkChild, 1) = "[" Then
strLinkChild = Mid(strLinkChild, 2, Len(strLinkChild) - 2)
End If

' Build a list of the subform fields to be copied.
' This list must exclude the LinkChildField and the
' table's autonumber key.
For Each fld In Me!tSub.Form.Recordset.Fields
If fld.Name = strLinkChild _
Or (fld.Attributes And dbAutoIncrField) <> 0 Then
' skip this field
Else
strFieldList = strFieldList & ", [" & fld.Name & "]"
End If
Next fld

' Copy the main form's record, noting the new ID
With Me.RecordsetClone
.AddNew
varNewMainKey = .Fields(strLinkMaster).Value
For Each fld In Me.Recordset.Fields
If fld.Name <> strLinkMaster Then
.Fields(fld.Name) = fld.Value
End If
Next fld
.Update
End With

' Build a SQL statement to copy the related records.

Select Case VarType(varNewMainKey)
Case vbString
varOldMainKey = _
"""" & _
Replace(varOldMainKey, """", """""", , , _
vbBinaryCompare) & _
""""
varNewMainKey = _
"""" & _
Replace(varNewMainKey, """", """""", , , _
vbBinaryCompare) & _
""""
Case vbDate
varOldMainKey = Format(varOldMainKey, "\#mm/dd/yyyy\#")
varNewMainKey = Format(varNewMainKey, "\#mm/dd/yyyy\#")
Case Else
varOldMainKey = Str(varOldMainKey)
varNewMainKey = Str(varNewMainKey)
End Select

strSQL = _
"INSERT INTO " & Me.tSub.Form.RecordSource & " " & _
"([" & strLinkChild & "]" & strFieldList & ") " & _
"SELECT " & varNewMainKey & strFieldList & _
" FROM " & Me.tSub.Form.RecordSource & _
" WHERE [" & strLinkChild & "] = " & varOldMainKey

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery
Me.Recordset.FindFirst "[" & strLinkMaster & "] = " & varNewMainKey

End Sub
'----- end of code -----

You don't have to be quite this flexible if you don't want to; you can
hard-code field and table names if you want.
 
Good God Dirk where did you learn all that?

I will certainly try it, fingers crossed

Thank you.
-----Original Message-----
How do I insert records into two tables one table is the
main form and the second is a subform table as in orders
and items. I want to be able to automatically place a
repeat order without having to do all the detail work

I am assuming that if I choose the main form record first
and append the record to its underlying table, that the
new record will generate a new number in the counter field
of the main form table. As this is also the referencial
integrity linking field to the subform table when I append
records from the subform it needs the new linking field
number in the main table to allow the appending of new
records to the subform table. how will it know the number
of the linking field? Am I missing something simple?
Click to expand...

Add the parent record via a recordset so that you can get the autonumber
ID for the added record, then use an append query to copy the records
from the child table, specifying the new number as a literal value.
Here's a fairly flexible routine for the purpose:

'----- start of code -----
Private Sub cmdDupAll_Click()

Dim varOldMainKey As Variant
Dim varNewMainKey As Variant
Dim fld As DAO.Field
Dim strSQL As String
Dim strFieldList As String
Dim strLinkMaster As String
Dim strLinkChild As String

strLinkMaster = Me.tSub.LinkMasterFields
If Left(strLinkMaster, 1) = "[" Then
strLinkMaster = Mid(strLinkMaster, 2, Len (strLinkMaster) - 2)
End If
varOldMainKey = Me.Controls(strLinkMaster).Value
strLinkMaster = Me.Controls (strLinkMaster).ControlSource

strLinkChild = Me.tSub.LinkChildFields
If Left(strLinkChild, 1) = "[" Then
strLinkChild = Mid(strLinkChild, 2, Len (strLinkChild) - 2)
End If

' Build a list of the subform fields to be copied.
' This list must exclude the LinkChildField and the
' table's autonumber key.
For Each fld In Me!tSub.Form.Recordset.Fields
If fld.Name = strLinkChild _
Or (fld.Attributes And dbAutoIncrField) <> 0 Then
' skip this field
Else
strFieldList = strFieldList & ", [" & fld.Name & "]"
End If
Next fld

' Copy the main form's record, noting the new ID
With Me.RecordsetClone
.AddNew
varNewMainKey = .Fields(strLinkMaster).Value
For Each fld In Me.Recordset.Fields
If fld.Name <> strLinkMaster Then
.Fields(fld.Name) = fld.Value
End If
Next fld
.Update
End With

' Build a SQL statement to copy the related records.

Select Case VarType(varNewMainKey)
Case vbString
varOldMainKey = _
"""" & _
Replace(varOldMainKey, """", """""", , , _
vbBinaryCompare) & _
""""
varNewMainKey = _
"""" & _
Replace(varNewMainKey, """", """""", , , _
vbBinaryCompare) & _
""""
Case vbDate
varOldMainKey = Format
Click to expand...
(varOldMainKey, "\#mm/dd/yyyy\#")
varNewMainKey = Format
Click to expand...
(varNewMainKey, "\#mm/dd/yyyy\#")
Case Else
varOldMainKey = Str(varOldMainKey)
varNewMainKey = Str(varNewMainKey)
End Select

strSQL = _
"INSERT INTO " & Me.tSub.Form.RecordSource & " " & _
"([" & strLinkChild & "]" & strFieldList & ") " & _
"SELECT " & varNewMainKey & strFieldList & _
" FROM " & Me.tSub.Form.RecordSource & _
" WHERE [" & strLinkChild & "] = " & varOldMainKey

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery
Me.Recordset.FindFirst "[" & strLinkMaster & "] = " & varNewMainKey

End Sub
'----- end of code -----

You don't have to be quite this flexible if you don't want to; you can
hard-code field and table names if you want.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Derek said:
Good God Dirk where did you learn all that?
Click to expand...
Dirk, you will allow me to answer?

People learn by reading manuals and help files, by having to develop
something, by trying out, but above all *by helping others*.
 
Derek said:
Good God Dirk where did you learn all that?
Click to expand...

LOL Here and there, Derek.
I will certainly try it, fingers crossed
Click to expand...

I just noticed that the proc isn't as flexible as it could be.
Specifically, it hard-codes the name of the subform control (on the main
form). You'll see a bunch of references to "tSub" -- that's the name of
the subform control. You should replace this wherever it occurs with
the name of your subform control (which may or may not be the same as
the name of the form object that it displays).
 
Bas Cost Budde said:
Dirk, you will allow me to answer?

People learn by reading manuals and help files, by having to develop
something, by trying out, but above all *by helping others*.
Click to expand...

Well said, Bas.
 
Thanks Dirk.

Sorry for not getting back I'm holding down two jobs and
trying to sort this database. Is it too late to reply on
this issue? Should I now start a complete new question or
will you get this. If you do I dont know how, it takes me
hours to find my old questions so how you follow all yours
I do not know!!

I have tried and studied your code. I get an error when I
try to compile it at line
Dim fld As DAO.Field

When I asked the question originally I got no responce at
all so I simplified the question so that some one would
give me a clue. I did not expect to get the complete code
but thanks. The problem is ,and this could be the cause of
the error, that the linking field is in the subform but
the many side of the records that I want to copy are in
another form that is a poup form with it's own
table/query. So Here is preciseley what I am trying to do.
The program is for ordering doors the type you would see
in an office block. The main form has the customers name
and details. The subform holds all the info on what type
of door is ordered (size, colour, fire rating, etc.)and
then a pop up form lists all of the ironmongery (door
handles, hinges, locks etc.)and i need a way of copying
the subform record and the pop up form records and
appending it to the underlying tables. we can then simply
change only the information that makes this record unique
(ie. door colour or door size) and we have added an item
to the order in seconds. Currently this take over two
minutes and is very prone to error. Hope you can help,
again!!.

Many thanks
 
Answers inline.

Derek said:
Thanks Dirk.

Sorry for not getting back I'm holding down two jobs and
trying to sort this database. Is it too late to reply on
this issue? Should I now start a complete new question or
will you get this. If you do I dont know how, it takes me
hours to find my old questions so how you follow all yours
I do not know!!
Click to expand...

If you reply to this message, we can continue from here. Since I'm
using Outlook Express as my newsreader, I can ask it to show me threads
I'm participating in. That's how I normally keep track. The web
interface you're using doesn't support that, as far as I know.

In this case, though, this message of yours that I'm replying to now
doesn't show up as a reply to our earlier discussions. Either you
posted it as a new message rather than as a reply, or the originals have
"aged off the server". I just happened to look at the message and
realize it was part of an earlier discussion we were having.
I have tried and studied your code. I get an error when I
try to compile it at line
Dim fld As DAO.Field
Click to expand...

You probably need to add a reference to the Microsoft DAO 3.6 Object
Library. You can do this while editing any code module, by clicking
Tools -> References, locating the afore-mentioned reference in the list,
and putting a check in the box next to it.
When I asked the question originally I got no responce at
all so I simplified the question so that some one would
give me a clue. I did not expect to get the complete code
but thanks. The problem is ,and this could be the cause of
the error, that the linking field is in the subform but
the many side of the records that I want to copy are in
another form that is a poup form with it's own
table/query.
Click to expand...

Tsk! That's a completely different situation from what you asked, and
the code I posted won't help. Ah, well, maybe someone else will be
looking to do what you were asking, and will find it useful.
So Here is preciseley what I am trying to do.
The program is for ordering doors the type you would see
in an office block. The main form has the customers name
and details. The subform holds all the info on what type
of door is ordered (size, colour, fire rating, etc.)and
then a pop up form lists all of the ironmongery (door
handles, hinges, locks etc.)and i need a way of copying
the subform record and the pop up form records and
appending it to the underlying tables. we can then simply
change only the information that makes this record unique
(ie. door colour or door size) and we have added an item
to the order in seconds. Currently this take over two
minutes and is very prone to error. Hope you can help,
again!!.
Click to expand...

Hmm, I'm going to need to investigate this a bit further before giving
advice. Besides, it's late and I'm going to bed now. <g> So please
post a reply giving the details of the tables involved here and their
relationships to each other. Also tell what forms are based on what
tables (or queries), and what are the linking fields on the main form
and subform. If there are queries involved, please give the SQL of each
query.

That should keep you busy for a while!
 
Thanks Dirk

Main form is called "ProjectForm" The forms table is
called "Project". Primary and Link Field is AccountCode
Subform is called "Details" The subforms query is also
called "Details" and is based on a table called "Item"
Link field AccountCode (Many Side)
The subforms Link and Primary field to the pop up
IronmongeryForm is "ItemNumber"
The Pop up form is called IronmongeryForm and is based on
a query called "IronMongery Query"

ProjectForm holds customer details or more precisely the
project details for the customer.
The subform holds details about each individual product,
in this case office doors
The pop up holds info on the various items of ironmongery
for each door

So if the customer wants to add another door similar to
door number D6, to the project Number 7 but this time a
red door instead of blue but with everything else the
same, we click on the dropdown on the main form to find
project Number 7, we click on a dropdown on the subform to
find door number D6, now we need to copy all the details
from the subform (door number 6) and all of the items of
ironmongery in the pop up form to the same underlying
tables and move to the new record in the subform. We
change the colour of the new door from blue to red and
three minutes of hard typing would be done in 15 seconds.

Just to clarify things I see no reason to include anything
from the ProjectForm or table. All the information
requiring copying is held in the query "Details" and the
query "Ironmongery_Query" and the lik field between them
is ItemNumber

The Table for the query "Details is "Item"
The Table for the query "IronMongery_Query"
is "IronMongery"

Thanks Dirk!!!!!
 
Derek said:
Thanks Dirk

Main form is called "ProjectForm" The forms table is
called "Project". Primary and Link Field is AccountCode
Subform is called "Details" The subforms query is also
called "Details" and is based on a table called "Item"
Link field AccountCode (Many Side)
The subforms Link and Primary field to the pop up
IronmongeryForm is "ItemNumber"
The Pop up form is called IronmongeryForm and is based on
a query called "IronMongery Query"

ProjectForm holds customer details or more precisely the
project details for the customer.
The subform holds details about each individual product,
in this case office doors
The pop up holds info on the various items of ironmongery
for each door

So if the customer wants to add another door similar to
door number D6, to the project Number 7 but this time a
red door instead of blue but with everything else the
same, we click on the dropdown on the main form to find
project Number 7, we click on a dropdown on the subform to
find door number D6, now we need to copy all the details
from the subform (door number 6) and all of the items of
ironmongery in the pop up form to the same underlying
tables and move to the new record in the subform. We
change the colour of the new door from blue to red and
three minutes of hard typing would be done in 15 seconds.

Just to clarify things I see no reason to include anything
from the ProjectForm or table. All the information
requiring copying is held in the query "Details" and the
query "Ironmongery_Query" and the lik field between them
is ItemNumber

The Table for the query "Details is "Item"
The Table for the query "IronMongery_Query"
is "IronMongery"

Thanks Dirk!!!!!
Click to expand...

So, let me see. We have this data structure:

table: Project
field: AccountCode (primary key)
... other fields ...

table: Details
field: ItemNumber (primary key)
field: AccountCode (foreign key to Project)
... other fields ...

table: Ironmongery
field: IronmongeryID?? (primary key)
field: ItemNumber (foreign key to Details)
... other fields ...

Please verify the above and correct it as needed. I need to know the
data types of the various fields. For table Ironmongery (and ideally
for table Details), I need to know the names and types of all other
fields in the table as well. Also, which of these fields, if any, are
autonumber fields?
 
Hi Dirk

THe table "Details" has many fields (80 in fact) Yes I
know that's a lot but all are required to correctly
specify one door set. Most are Number fields or text But
the Primary Key is ItemNumber which is also the only
Autonumber

The ironmongery has
ItemNumber (Number)(Foreing key to ItemNumber in Details
Table.
Product (Text)
Description (Text)
Per (Text) Indicates if goods are purchased in pairs
Quantity (Number (Single))
Discount (Number (Single))
Price (Currency)

There is no Primary Key or autonumber in this table

Thanks Dirk if you require every name of every field
in "Details" I can give you them but if you supply the
basics I should be able to work it out.
 
Derek said:
Hi Dirk

THe table "Details" has many fields (80 in fact) Yes I
know that's a lot but all are required to correctly
specify one door set. Most are Number fields or text But
the Primary Key is ItemNumber which is also the only
Autonumber

The ironmongery has
ItemNumber (Number)(Foreing key to ItemNumber in Details
Table.
Product (Text)
Description (Text)
Per (Text) Indicates if goods are purchased in pairs
Quantity (Number (Single))
Discount (Number (Single))
Price (Currency)

There is no Primary Key or autonumber in this table

Thanks Dirk if you require every name of every field
in "Details" I can give you them but if you supply the
basics I should be able to work it out.
Click to expand...

I think we should be able to go from there. Assuming that your button
to duplicate the item is on the subform, not on the main form, code
along these lines would be a place to start:

'------- start of code ------

Dim lngOldID As Long
Dim lngNewID As Long
Dim strSQL As String

' Hang onto current ID.
lngOldID = Me.ItemNumber

' Duplicate the record on this form into a new record.
RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

' Get the ID of the new record.
lngNewID = Me.ItemNumber

' Copy the related information from the old record
' to the new record.
strSQL = _
"INSERT INTO Ironmongery (" & _
"ItemNumber, Product, Description, " & _
"Per, Quantity, Discount, Price) " & _
"SELECT " & lngNewID & " AS ItemNumber, " & _
"Product, Description, " & _
"Per, Quantity, Discount, Price " & _
"FROM Ironmongery " & _
"WHERE ItemNumber=" & lngOldID

CurrentDb.Execute strSQL, dbFailOnError

'------- end of code ------
 
Thank you so much Dirk

I will try this and hopefuly I won't have to "Bug" you
again. Such a simple solution (When you know what you are
doing!!

Cheers!!
 
Back
Top