Allen Browne, can you help me with your code?

[Guest]

Hi, Allen. I used your suggested code (from your answer to 'Command Button Duplicate for Forms and Subforms' on 12/17/03) to duplicate some recods entered into a form along with the subform records. It will compile, and dupe the main form data, but not the subform data. Here is my code so far (it's long, but the only way I know to show you)

Private Sub DupeAppMerch_Click(
Dim sSQL As Strin
Dim db As DAO.Databas
Dim NewContNum As Strin

Set db = DBEngine(0)(0

If Me.Dirty Then 'Save firs
Me.Dirty = Fals
End I
If Me.NewRecord The
MsgBox "Select the record to duplicate.
Else 'Duplicate the main recor
With Me.RecordsetClon
.AddNe
!ContractNumber = Me.AppMerchContrac
!AppMerchContract = Me.ContractNumbe
!AppMerch = "M
!CoProgContract = Me.CoProgContrac
!DateEntered = Me.DateEntere
!DateOrdered = Me.DateOrdere
.Updat
.Bookmark = .LastModifie
NewContNum = !ContractNumbe
'Duplicate the related records from form App Contract Sub - Do I need another AddNew
If Me.[App Contract Sub].Form.RecordsetClone.RecordCount > 0 The
sSQL = "INSERT INTO [Contract Details](ContractNumber, " &
"StoreID, ArrivalDate) " &
"SELECT " & NewContNum & " AS ContractNumber, " &
"[Contract Details].StoreID, [Contract Details].ArrivalDate " &
"FROM [Contract Details]" &
"WHERE ([Contract Details].ContractNumber = " & Me.ContractNumber & ");
'Duplicate the related records from form Merch Contracts Sub - Do I need another AddNew
ElseIf Me.[Merch Contracts Sub].Form.RecordsetClone.RecordCount > 0 The
sSQL = "INSERT INTO [Contract Details](ContractNumber, " &
"StoreID) " &
"SELECT " & NewContNum & " AS ContractNumber, [Contract Details].StoreID " &
"FROM [Contract Details]" &
"WHERE ([Contract Details].ContractNumber = " & Me.ContractNumber & ");
db.Execute sSQL, dbFailOnErro
Els
MsgBox "Worksheet information duplicated, but there were no details for Appearances or Merchandise.
End I
'Display the duplicat
Me.Bookmark = .LastModifie
End Wit
End I
Set db = Nothin
End Su

 

[Allen Browne]

Hi Cat

Do you get an error message? What is it? Which line?

To try to debug your SQL statement, try adding:
Debug.Print sSQL
just above the line:
db.Execute sSQL, dbFailOnError
Then copy the SQL statement into SQL View of a query, and it may help you to
see what's wrong. You could also work the other way around: mock up an
Append query statement in the query design view (Append is on the Query
menu) with any literal value you choose, and then switch to SQL View (View
menu) to see the string you need to create in your code.

Couple of suggestions:

It may help to add a space between the table name and field list in the
INSERT clause, i.e.:
sSQL = "INSERT INTO [Contract Details] (ContractNumber, " & _

Presumably ContractNumber is the foreign key field in Contract Details.
Presumably this is a Text field (not a Number field), since you Dim'd
NewContNum as a string. If so, you will need additional quote marks around
the literal text value in the SQL string, i.e.:
"SELECT """ & NewContNum & """ AS ContractNumber, " & _

You are correct in omitting the AutoNumber from the SQL statement: Access
will assign that automatically.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen. I used your suggested code (from your answer to 'Command Button

Duplicate for Forms and Subforms' on 12/17/03) to duplicate some recods
entered into a form along with the subform records. It will compile, and
dupe the main form data, but not the subform data. Here is my code so far
(it's long, but the only way I know to show you):

Private Sub DupeAppMerch_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim NewContNum As String

Set db = DBEngine(0)(0)

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else 'Duplicate the main record
With Me.RecordsetClone
.AddNew
!ContractNumber = Me.AppMerchContract
!AppMerchContract = Me.ContractNumber
!AppMerch = "M"
!CoProgContract = Me.CoProgContract
!DateEntered = Me.DateEntered
!DateOrdered = Me.DateOrdered
.Update
.Bookmark = .LastModified
NewContNum = !ContractNumber
'Duplicate the related records from form App Contract Sub - Do I need another AddNew?
If Me.[App Contract Sub].Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO [Contract Details](ContractNumber, " & _
"StoreID, ArrivalDate) " & _
"SELECT " & NewContNum & " AS ContractNumber, " & _
"[Contract Details].StoreID, [Contract Details].ArrivalDate " & _
"FROM [Contract Details]" & _
"WHERE ([Contract Details].ContractNumber = " & Me.ContractNumber & ");"
'Duplicate the related records from form Merch Contracts Sub - Do I need another AddNew?
ElseIf Me.[Merch Contracts

Sub].Form.RecordsetClone.RecordCount > 0 Then

sSQL = "INSERT INTO [Contract Details](ContractNumber, " & _
"StoreID) " & _
"SELECT " & NewContNum & " AS ContractNumber,

[Contract Details].StoreID " & _

"FROM [Contract Details]" & _
"WHERE ([Contract Details].ContractNumber = " & Me.ContractNumber & ");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Worksheet information duplicated, but there were

no details for Appearances or Merchandise."

End If
'Display the duplicate
Me.Bookmark = .LastModified
End With
End If
Set db = Nothing
End Sub

have not accounted for in this code. I thought it would increment just as if
I had entered the data directly, or copied and pasted...

 

[Guest]

Do you get an error message? What is it? Which line?
No error msgs, but only the main form data is copied, not the data from the subform.

To try to debug your SQL statement, try adding:
Debug.Print sSQL
just above the line:
db.Execute sSQL, dbFailOnError

Then do I need to print this? I added it to the code, but it did not run any differently; I don't know if it was supposed to.

copy the SQL statement into SQL View of a query

I tried that, but I think one of my main problems with not knowing the language is that I don't know which things to keep
(", &, [fields]) and how to determine the 'coming from' side vs the 'going to' side. I do have an append query that works (only for the main form), but I could not adapt it to the subforms...

It may help to add a space between the table name and field list in the
INSERT clause, i.e.:
sSQL = "INSERT INTO [Contract Details] (ContractNumber, " & _

Thanks, I did that. No change, but good to know.

Presumably ContractNumber is the foreign key field in Contract Details. *YES
Presumably this is a Text field (not a Number field), since you Dim'd *YES
NewContNum as a string. If so, you will need additional quote marks around
the literal text value in the SQL string, i.e.:
"SELECT """ & NewContNum & """ AS ContractNumber, " & _

I put those in. (No change in the outcome, tho.) What do they do?

It seems like it should be so simple. I understand the concept, but I'm surely missing the boat on the details.
Your help is greatly appreciated!

Cat (ps... got... to ... sleeep........I'll check back in am. Thanks!)

 

[Allen Browne]

Is the line executing?
Try pressing F9 in the db.Execute line to see if it is running. That will
cause a break point; at least you can see what is happening.

If it is executing, but nothing is happening, examine db.RecordsAffected to
see if any records are being written.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

No error msgs, but only the main form data is copied, not the data from the subform.

Then do I need to print this? I added it to the code, but it did not run

any differently; I don't know if it was supposed to.

I tried that, but I think one of my main problems with not knowing the

language is that I don't know which things to keep

(", &, [fields]) and how to determine the 'coming from' side vs the 'going

to' side. I do have an append query that works (only for the main form), but
I could not adapt it to the subforms...

It may help to add a space between the table name and field list in the
INSERT clause, i.e.:
sSQL = "INSERT INTO [Contract Details] (ContractNumber, " & _

Thanks, I did that. No change, but good to know.

Presumably ContractNumber is the foreign key field in Contract Details. *YES
Presumably this is a Text field (not a Number field), since you Dim'd *YES
NewContNum as a string. If so, you will need additional quote marks around
the literal text value in the SQL string, i.e.:
"SELECT """ & NewContNum & """ AS ContractNumber, " & _

I put those in. (No change in the outcome, tho.) What do they do?

It seems like it should be so simple. I understand the concept, but I'm

surely missing the boat on the details.

 

[SteveS]

Is the line executing?
Try pressing F9 in the db.Execute line to see if it is running. That will
cause a break point; at least you can see what is happening.

If it is executing, but nothing is happening, examine db.RecordsAffected to
see if any records are being written.

PMFJI, but it looks like there should be another

db.Execute sSQL, dbFailOnError

line after the first sSQL = "Insert into....." statement (a total of 2
db.Execute statements within the If..ElseIf..End If statement)


Q: Can the 'App Contract Sub' have records that need to be duplicated at
the same time that the 'Merch Contracts Sub' has records that need to be
duplicated?

If so, then the IF..ElseIf..Else..End IF will need to be changed to two
If..End If statements.

 

[Guest]

I added the breakpoint, but I don't know how to watch it step through. I tried to run it, and same results, main record copies, but no sub-records

I now get an error 'Too few parameters.Expected 2.' So I I then added the extra "" you suggested for string,
"SELECT """ & NewContNum & """ AS ContractNumber,
And I'm getting "Too few parameters. Expected 1.

Also, SteveS asked
Q: Can the 'App Contract Sub' have records that need to be duplicated at
the same time that the 'Merch Contracts Sub' has records that need to be
duplicated
There will only be App *or* Merch on a given contract; the App and Merch contracts are totally separate records, they just have a lot of data in common. So, no, but I see your logic

I _really_ need this to work; I've been fighting it a long time..

 

[Guest]

I must be asleep! I also put the "" ''" around """ & Me.ContractNumber & """ and the error is gone, and SUB RECORDS COPY!!!! My only problem now is the AppDate and ArrivalDate, which _only_ come from AppContracts, but I want them to show up on Merch contracts. Do I have to restructure so that both subforms use the same underlying query? (noooo...)

Also, I'm sure it's possible to have my field AppMerch, which I have here hardcoded to 'M', pick up the 3rd character of the AppMerchContract, which is assigned into ContractNumber. IOW, the AppMerchContract format is like 5432A... or 5432M... and I want to pick up the 'A' or 'M', not just assign it the most likely, which I did here. I tried:
!AppMerch = Mid ([Me].[AppMerchContract],3,1) (I also tried = Mid(([Me.AppMerchContract]),3,1) to no avail...)
but that was not allowed.

Getting close! Thanks!!!

 

[Allen Browne]

Glad you have progress.

I'm sure you can solve it from here.

I must be asleep! I also put the "" ''" around """ & Me.ContractNumber &

""" and the error is gone, and SUB RECORDS COPY!!!! My only problem now is
the AppDate and ArrivalDate, which _only_ come from AppContracts, but I want
them to show up on Merch contracts. Do I have to restructure so that both
subforms use the same underlying query? (noooo...)

Also, I'm sure it's possible to have my field AppMerch, which I have here

hardcoded to 'M', pick up the 3rd character of the AppMerchContract, which
is assigned into ContractNumber. IOW, the AppMerchContract format is like
5432A... or 5432M... and I want to pick up the 'A' or 'M', not just assign
it the most likely, which I did here. I tried:

!AppMerch = Mid ([Me].[AppMerchContract],3,1) (I also tried =

Mid(([Me.AppMerchContract]),3,1) to no avail...)

 

[Guest]

Thank you thank you thank you... Yes, it finally copies the subform records. Yeah! We newbies soooo appreciate your help!