help with copying record with subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease No],[Gross
Bonus],[Net
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consent],[Comments],[Remarks],[JOA #],[Term],[Last_Changed_Date]) "
SELECT
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventory_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inventory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Lease_Inventory_Table.[Acquisition],
Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Table.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Comments],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA
#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FROM Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
 
What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma before the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



emily said:
What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consent],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventory_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inventory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Lease_Inventory_Table.[Acquisition],
Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Table.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Comments],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA
#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FROM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
 
I'm sorry, I got so frustrated that I just deleted the whole thing. I don't
know anything about VBA and I tried to copy some code from a prior post and
alter it to fit my situation. I am trying to copy a record (a copy of the
subform in addition to the main form contents) and I don't have any primary
keys to worry about.

My main form is Lease_Inventory_Form and my subform is Partner_WI_Subform.

Could you help me?

Douglas J. Steele said:
What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma before the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



emily said:
What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consent],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventory_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inventory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Lease_Inventory_Table.[Acquisition],
Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Table.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Comments],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA
#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FROM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
Click to expand...
 
You must have "primary keys to worry about". If you didn't, how would you be
able to uniquely identify which record you want to copy?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


emily said:
I'm sorry, I got so frustrated that I just deleted the whole thing. I don't
know anything about VBA and I tried to copy some code from a prior post and
alter it to fit my situation. I am trying to copy a record (a copy of the
subform in addition to the main form contents) and I don't have any primary
keys to worry about.

My main form is Lease_Inventory_Form and my subform is Partner_WI_Subform.

Could you help me?

Douglas J. Steele said:
What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma before the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



emily said:
What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net
Click to expand...
Click to expand...
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consen
t],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT
Click to expand...
Click to expand...
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventor
y_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Click to expand...
Click to expand...
Click to expand...
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inv
entory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Click to expand...
Click to expand...
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Le
ase_Inventory_Table.[Acquisition],Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Ta
ble.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Commen
ts],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FR
OM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Click to expand...
Click to expand...
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subta
ble.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Aha! So it is impossible within the Access framework to simply copy the
"current" record (subforms and all)?

Douglas J Steele said:
You must have "primary keys to worry about". If you didn't, how would you be
able to uniquely identify which record you want to copy?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


emily said:
I'm sorry, I got so frustrated that I just deleted the whole thing. I don't
know anything about VBA and I tried to copy some code from a prior post and
alter it to fit my situation. I am trying to copy a record (a copy of the
subform in addition to the main form contents) and I don't have any primary
keys to worry about.

My main form is Lease_Inventory_Form and my subform is Partner_WI_Subform.

Could you help me?

Douglas J. Steele said:
What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma before the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net
Click to expand...
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consen
t],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT
Click to expand...
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventor
y_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Click to expand...
Click to expand...
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inv
entory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Click to expand...
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Le
ase_Inventory_Table.[Acquisition],Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Ta
ble.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Commen
ts],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FR
OM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Click to expand...
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subta
ble.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
The concept of copying a record and "subforms" isn't really part of Access.
First of all, records don't have "subforms", and forms get their data from
tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


emily said:
Aha! So it is impossible within the Access framework to simply copy the
"current" record (subforms and all)?

Douglas J Steele said:
You must have "primary keys to worry about". If you didn't, how would you be
able to uniquely identify which record you want to copy?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


emily said:
I'm sorry, I got so frustrated that I just deleted the whole thing. I don't
know anything about VBA and I tried to copy some code from a prior
Click to expand...
post
and
alter it to fit my situation. I am trying to copy a record (a copy of the
subform in addition to the main form contents) and I don't have any primary
keys to worry about.

My main form is Lease_Inventory_Form and my subform is Partner_WI_Subform.

Could you help me?

:

What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma
Click to expand...
before
the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table ([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net
Click to expand...
Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consen
t],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT
Click to expand...
Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventor
y_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area
Click to expand...
Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inv
entory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp
Click to expand...
Date],Lease_Inventory_Table.[Water
Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net
Click to expand...
Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Le
ase_Inventory_Table.[Acquisition], Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Ta
ble.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Commen
ts],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA #],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FR
OM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT
Click to expand...
Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subta
ble.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & " FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Is there any way to copy a line from a table and copy the lines from the
linked subtable and make that copied 'record' the current record so I can
make a few changes without reentering everything in the record/table?

Douglas J Steele said:
The concept of copying a record and "subforms" isn't really part of Access.
First of all, records don't have "subforms", and forms get their data from
tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


emily said:
Aha! So it is impossible within the Access framework to simply copy the
"current" record (subforms and all)?

Douglas J Steele said:
You must have "primary keys to worry about". If you didn't, how would you be
able to uniquely identify which record you want to copy?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm sorry, I got so frustrated that I just deleted the whole thing. I
don't
know anything about VBA and I tried to copy some code from a prior post
and
alter it to fit my situation. I am trying to copy a record (a copy of the
subform in addition to the main form contents) and I don't have any
primary
keys to worry about.

My main form is Lease_Inventory_Form and my subform is Partner_WI_Subform.

Could you help me?

:

What error are you getting?

If instead of DoCmd.RunSQL "INSERT INTO...", you use

CurrentDb.Execute "INSERT INTO...", dbFailOnError

you'll be able to trap any errors that might occur.

One thing I did notice is that your first SQL statement has
Lease_Inventory_Table.[Last_Changed_Date],FROM in it. The comma before
the
FROM keyword is definitely wrong.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



What am I doing wrong??

Private Sub COPY_RECORD_Click()
Dim MaxField As Double
DoCmd.RunSQL "INSERT INTO Lease_Inventory_Table
([Company],[State],[Status
I],[Status II],[Field],[Area Name],[Area/Blk],[OCS],[SL],[Gross
Acs],[Net
Acs],[Lse Date],[Exp Date],[Water Depth (Ft)],[OR RT],[Apache
WI],[Depth
Limits],[Operator],[Op's WI],[NRI BPO],[NRI APO],[Prospect],[Lease
No],[Gross
Bonus],[Net

Bonus],[Royalty],[Royalty_CD],[Acquisition],[Notice],[TO],[Vote],[Non-Consen
t],[Comments],[Remarks],[JOA
#],[Term],[Last_Changed_Date]) "
SELECT

Lease_Inventory_Table.[Company],Lease_Inventory_Table.[State],Lease_Inventor
y_Table.[Status
I],Lease_Inventory_Table.[Status
II],Lease_Inventory_Table.[Field],Lease_Inventory_Table.[Area

Name],Lease_Inventory_Table.[Area/Blk],Lease_Inventory_Table.[OCS],Lease_Inv
entory_Table.[SL],Lease_Inventory_Table.[Gross
Acs],Lease_Inventory_Table.[Net Acs],Lease_Inventory_Table.[Lse
Date],Lease_Inventory_Table.[Exp Date],Lease_Inventory_Table.[Water
Depth
(Ft)],Lease_Inventory_Table.[OR RT],Lease_Inventory_Table.[Apache
WI],Lease_Inventory_Table.[Depth
Limits],Lease_Inventory_Table.[Operator],tblLease_Inventory.[Op's
WI],Lease_Inventory_Table.[NRI BPO],Lease_Inventory_Table.[NRI
APO],Lease_Inventory_Table.[Prospect],Lease_Inventory_Table.[Lease
No],Lease_Inventory_Table.[Gross Bonus],Lease_Inventory_Table.[Net

Bonus],Lease_Inventory_Table.[Royalty],Lease_Inventory_Table.[Royalty_CD],Le
ase_Inventory_Table.[Acquisition],

Lease_Inventory_Table.[Notice],Lease_Inventory_Table.[TO],Lease_Inventory_Ta
ble.[Vote],Lease_Inventory_Table.[Non-Consent],Lease_Inventory_Table.[Commen
ts],Lease_Inventory_Table.[Remarks],Lease_Inventory_Table.[JOA

#],Lease_Inventory_Table.[Term],Lease_Inventory_Table.[Last_Changed_Date],FR
OM
Lease_Inventory_Table WHERE [Field] = " & Me.Field
' Look for the new key
MaxField = DMax("Field", "Lease_Inventory_Table")
' Insert the new records in the second table based on the old key, but
with
the new key above
DoCmd.RunSQL "INSERT INTO Partner_WI_Subtable ( [Field], [Company]
,[Interest],[Type Interest],[Depth Limitation] ) SELECT

Partner_WI_Subtable.[Company],Partner_WI_Subtable.[Company],Partner_WI_Subta
ble.[Interest],Partner_WI_Subtable.[Type
Interest],Partner_WI_Subtable.[Depth Limitation], " & MaxField & "
FROM
Partner_WI_Subtable WHERE [Field] = " & Me.Field
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[Field] = " & MaxField
If .NoMatch Then
MsgBox "Field not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top