Copy Record with Autonumber & Relationships

I hope someone can help me...I haven't got a clue how to do this.

I have a table with several one-to-many relationships. One of the
related tables has it's own one-to many relationships as well.

I would like to copy a record when I am in a form and append it to the
last record. To make it more complicated, I have an auto-number
(primary key) on the main table and some of the related tables also.

I can't do what I want to do using the command button wizard.
Apparently, I need to do this by code. I've read a bunch of postings
and can't find the code I need to use. My head is spinning with trying
to find code that will work. I've never coded with VB before so I'm
really lost!

Can anyone help??

Swinky,
I've used the following to copy records. It's not pretty, but seems to work
OK.

db.Execute "INSERT INTO tblName SELECT " _
& FieldNames(tblName, "AutoNumberField", "", "", "") _
& " FROM tblName" _
& "WHERE fldname= " & somevalue, dbFailOnError

where FieldNames is a function which returns the names of the fields you
wish to copy, and excludes those you don't. The arguments are the fields (up
to 4) that you wish to exclude. My linked table names all start with "t" and
queries with "q".

Public Function FieldNames(tblName As String, exclFld1 As String, _
exclFld2 As String, exclFld3 As String, exclFld4
As String) As String
On Error GoTo ErrorFieldNames
Dim fldNames As String, td As TableDef, qd As QueryDef, db As Database, fld
As Field
Set db = CurrentDb
If Left(tblName, 1) = "t" Then
Set td = db.TableDefs(tblName)
For Each fld In td.Fields
If fld.Name <exclFld1 And fld.Name <exclFld2 And fld.Name <>
exclFld3 And fld.Name <exclFld4 Then
If fldNames <"" Then
fldNames = fldNames & ", " & fld.Name
Else
fldNames = fld.Name
End If
End If
Next
Else
Set qd = db.QueryDefs(tblName)
For Each fld In qd.Fields
If fld.Name <exclFld1 And fld.Name <exclFld2 And fld.Name <>
exclFld3 And fld.Name <exclFld4 Then
If fldNames <"" Then
fldNames = fldNames & ", [" & fld.Name & "]"
Else
fldNames = "[" & fld.Name & "]"
End If
End If
Next
End If
FieldNames = fldNames

Exit Function
ErrorFieldNames:
MsgBox Error$
Exit Function
End Function



Hope this helps


--
Bob Darlington
Brisbane
"Swinky" <swinkelman@lunt.comwrote in message
news:1165873092.641615.296030@j72g2000cwa.googlegr oups.com...

I'm running into errors with this code. I get a "ByRef argument type
mismatch" compile error on the code:

& FieldNames(tblName, "OANo", "BudgetID", "ToolID", "RouteID") _

"tblName" is the highlighted word in the error.


Bob Darlington wrote:

tblname is a string variable which should be set to the name of the relevant
table. Or just insert the name of the table in quotes.

--
Bob Darlington
Brisbane
"Swinky" <swinkelman@lunt.comwrote in message
news:1165945166.440430.136380@l12g2000cwl.googlegr oups.com...