Help with code to copy record

[Guest]

I have a main form (form Contracts TR, from table Contracts) w 2 subforms (both from table Contract Details). On main, I have Contract# (pk) and an AppMerchContract# for the corresponding contract. I want to copy all but 2 of the fields in main form along with its subform info into a new record, and switch the Contract# to become the AppMerchContract#, and take the AppMerchContract# and make it the Contract# of the new record, so I have the new record with all the similar data for a start.

I have this code which I've adapted, but I haven't done programming in years and am not too familiar w this, so please help. I'm getting compile error at the end of the WHERE, but I'm really not sure if I have this correct anyway, or backwards..

Private Sub Command98_Click(
Dim sSQL As Strin
Dim db As Acces
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.AppMerchContract 'Is this the correct way to swap
!AppMerchContract = Me.ContractNumbe
!AppMerch = Me.AppMerc
!AHEmail = Me.AHEmail
![4WeekPurchStart] = Me.[4WeekPurchStart] 'Will this [] work for name w a number
.... 'Blah, blah the rest of my fields in main for
.Updat
.Bookmark = .LastModifie
NewContNum = !ContractNumbe

If Me.[fContracts TR].Form.RecordsetClone.RecordCount > 0 Then 'Duplicate the related record
sSQL = "INSERT INTO [Contract Details](ContractNumber, " &
"StoreID, MerchSKU, ..., DelivInstr) &"

"SELECT " & NewContNum & " AS ContractNumber, " &
"[Contract Details].StoreID, [Contract Details].MerchSKU, " &
...................
"[Contract Details].SendPOP, [Contract Details].DelivInstr " &
"FROM [Contract Details]" &
"WHERE ([Contract Details].ContractNumber = " & Me.ContractNumber &");" 'Here was where the compile error showed u
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

Sorry to post so much of it, but I keep looking and am not too sure what for :
Thanks for all help; any explaination appreciated. Relearning...

 

[Dirk Goldgar]

I have a main form (form Contracts TR, from table Contracts) w 2
subforms (both from table Contract Details). On main, I have
Contract# (pk) and an AppMerchContract# for the corresponding
contract. I want to copy all but 2 of the fields in main form along
with its subform info into a new record, and switch the Contract# to
become the AppMerchContract#, and take the AppMerchContract# and make
it the Contract# of the new record, so I have the new record with all
the similar data for a start.

I have this code which I've adapted, but I haven't done programming
in years and am not too familiar w this, so please help. I'm getting
compile error at the end of the WHERE, but I'm really not sure if I
have this correct anyway, or backwards...

Private Sub Command98_Click()
Dim sSQL As String
Dim db As Access
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 'Is
this the correct way to swap? !AppMerchContract =
Me.ContractNumber !AppMerch = Me.AppMerch
!AHEmail = Me.AHEmail
![4WeekPurchStart] = Me.[4WeekPurchStart]
'Will this [] work for name w a number? ....
'Blah, blah the rest of my fields in main form .Update
.Bookmark = .LastModified
NewContNum = !ContractNumber

If Me.[fContracts TR].Form.RecordsetClone.RecordCount > 0
Then 'Duplicate the related records
sSQL = "INSERT INTO [Contract
Details](ContractNumber, " & _ "StoreID, MerchSKU, ..., DelivInstr)
&" _

"SELECT " & NewContNum & " AS ContractNumber, " & _
"[Contract Details].StoreID, [Contract
Details].MerchSKU, " & _ ....................
"[Contract Details].SendPOP, [Contract
Details].DelivInstr " & _ "FROM [Contract Details]" &
_ "WHERE ([Contract Details].ContractNumber = " &
Me.ContractNumber &");" 'Here was where
the compile error showed up 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

Sorry to post so much of it, but I keep looking and am not too sure
what for
Thanks for all help; any explaination appreciated. Relearning...

I haven't examined the code in detail, but it looks plausible except for
this mistake:

"StoreID, MerchSKU, ..., DelivInstr) &" _

You have the concatenating ampersand inside the quotes on the second
line. Try this:

"StoreID, MerchSKU, ..., DelivInstr) " & _

 

[Guest]

Dirk
Thanks, I changed the & placement. Although I did have to correct that, it was not the solution. I am now getting red from 'sSQL = "INSERT...' thru the end of WHERE. Also, I tried to compile to see if I could get a little better idea, and it stopped at the top where I have Dim db as Access, says 'Expected user-defined type, not project'; the info I was adapting had 'as DAO.Database', should that be what I have here? (These are some of the details of this language that I don't understand. Also the 'Me.' setup; is that the current record reference? Time for a class.) The setup and concept make sense, but the syntax eludes me. My Help was giving me script errors - no help there.

Thanks for help so far. Any other suggestions

cwinslow

 

[Dirk Goldgar]

Dirk,
Thanks, I changed the & placement. Although I did have to correct
that, it was not the solution. I am now getting red from 'sSQL =
"INSERT...' thru the end of WHERE. Also, I tried to compile to see
if I could get a little better idea, and it stopped at the top where
I have Dim db as Access, says 'Expected user-defined type, not
project'; the info I was adapting had 'as DAO.Database', should that
be what I have here? (These are some of the details of this language
that I don't understand. Also the 'Me.' setup; is that the current
record reference? Time for a class.) The setup and concept make
sense, but the syntax eludes me. My Help was giving me script errors
- no help there.

Thanks for help so far. Any other suggestions?

cwinslow

Silly me, I didn't notice the "Dim db As Access". No, that should
remain "Dim db As DAO.Database". The keyword "Me" refers to the object
in which the code is currently executing; in this case, the form.
Technically, "Me." refers to properties of the form (such as Dirty and
NewRecord) while "Me!" refers to the controls on the form, but in
practice you can usually use "Me." to refer to either.

As for the problem with the assignment to sSQL, it's hard to tell
exactly what's wrong because you didn't post the complete statement --
at least, I hope you didn't, because there are ellipses in there that
are plainly invalid. You also appear to have a blank line that
shouldn't be in there. If I take the statement you posted and modify it
somewhat, I come up with this, which is not valid SQL (because of the
ellipses) but which at least gives no compile error:

sSQL = _
"INSERT INTO [Contract Details](ContractNumber, " & _
"StoreID, MerchSKU, ..., DelivInstr) " & _
"SELECT " & NewContNum & " AS ContractNumber, " & _
"[Contract Details].StoreID, [Contract Details].MerchSKU, " & _
" .................... " & _
"[Contract Details].SendPOP, [Contract Details].DelivInstr " & _
"FROM [Contract Details]" & _
"WHERE ([Contract Details].ContractNumber = " & _
Me.ContractNumber & ");"

If that's not enough to show you how to fix the statement, you'll have
to post the whole statement exactly as it is in the procedure.

 

[Guest]

Dirk, Thanks for your time, and then aaaauuuugggghhh! It was that stupid blank line! I just assumed (!) that it would read over it, as some programs do, but nooooo. But no more red lines! I also changed to 'Dim db As DAO.Database' , though, and compile still tells me 'User-defined type not defined'? Here's the top...

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

Set db = DBEngine(0)(0)
etc...

What would be 'DAO.Database' and could I be using something different?

cwinslow

 

[Dirk Goldgar]

Dirk, Thanks for your time, and then aaaauuuugggghhh! It was that
stupid blank line! I just assumed (!) that it would read over it, as
some programs do, but nooooo. But no more red lines! I also changed
to 'Dim db As DAO.Database' , though, and compile still tells me
'User-defined type not defined'? Here's the top...

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

Set db = DBEngine(0)(0)
etc...

What would be 'DAO.Database' and could I be using something different?

DAO.Database is right, but I suspect you're missing the required
reference to the DAO object library. With your code module open, click
Tools -> References..., locate "Microsoft DAO 3.6 Object Library" in the
list, and put a check in the box next to it.

Be aware that you will almost certainly now have references set to both
DAO and ADO (that is, "ActiveX Data Objects"), and these two libraries
define some objects with the same names but incompatible types. The DAO
Database object is okay, but the following list of objects are defined
in both libraries:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Because of this, if you intend to keep both references in your
database -- if you are also using ADO, or think you might at some point
in the future -- you must be sure always to qualify the declarations of
these objects with the library identifier, either DAO or ADODB, when you
declare them. For example,

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim cn As ADODB.Connection
Dim rs2 As ADODB.Recordset

.... and so on. I'd recommend qualifying the objects like that even if
you decide to drop the ADO reference by unchecking it, just in case it
should ever be restored.

 

[Guest]

Thanks for the info; I can read help, etc, but nothing explains like a person who knows. The compile error went away when I added the DAO library, but when I clicked my Dupe button, I got error 2465, Can't find field '|' in my expression. Looks like a pipe or an L; tried Find for pipe, L, i. and 1 and found none where they should not be (in names, etc, all were really Ls and were in correct places). I remember from a past life it's not usually where the debug points, but it _says_ problem's on the line:

If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 Then

out of the sequence:

.Update
.Bookmark = .LastModified
NewContNum = !ContractNumber

'Duplicate the related records
If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO [Contract Details](ContractNumber, " & _
"StoreID, MerchSKU, Quantity, MAINCases....

Is it correct to be using [] around my table name and fields with spaces, numbers, etc? I forgot how frustratingly fun this can be...

cwinslow

 

[Dirk Goldgar]

Thanks for the info; I can read help, etc, but nothing explains like
a person who knows. The compile error went away when I added the DAO
library, but when I clicked my Dupe button, I got error 2465, Can't
find field '|' in my expression. Looks like a pipe or an L; tried
Find for pipe, L, i. and 1 and found none where they should not be
(in names, etc, all were really Ls and were in correct places). I
remember from a past life it's not usually where the debug points,
but it _says_ problem's on the line:

If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 Then

out of the sequence:

.Update
.Bookmark = .LastModified
NewContNum = !ContractNumber

'Duplicate the related records
If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0
Then sSQL = "INSERT INTO [Contract
Details](ContractNumber, " & _ "StoreID,
MerchSKU, Quantity, MAINCases....

I thought "Contacts TR", or was it "fContacts TR", was the name of the
main form. This implies that it's the name of one of the two subform
controls you mentioned. Is that the case? My understanding is that
you're trying to test whether the subform has any records. If that's
right, then the code you're using should work, provided you correctly
name the subform control in the expression.

Is it correct to be using [] around my table name and fields with
spaces, numbers, etc? I forgot how frustratingly fun this can be...

Yes, that's correct, as far as spaces are concerned -- numbers aren't a
problem and don't require bracketing. I suggest you avoid embedding
spaces or other impermissible characters in the names of objects.

I don't understand why you're getting the message with the pipe
character still in it. That's a placeholder for text that is normally
inserted in the message before it is displayed to you. In this case, I
would normally expect it to be replaced by the name of field in
question.

 

[Dirk Goldgar]

???

 

[Guest]

Dirk, Sorry about that last one, got carried away on the enter key. I think you have pointed me to the correct problem - which items to use

My main form is called 'Contracts TR', and its Record Source is the table 'Contracts'. My first subform is 'Merch Contracts Sub' (the subform name), on the page/tab 'MerchSub', the Record Source is a SELECT statement that pulls data from the table 'Contract Details'. Second subform is 'App Contract Sub' (subform name) on page/tab 'AppSub', the Record Source is a Form 'Contract Details Sub' that pulls data from the table 'Contract Details'. I realize this is a little convoluted; I did not have total control here. So, I guess the problem is which item do I use where? I was thinking that I was actually copying the data from the _tables_, therefore using the table names. Almost home..

cwinslow

 

[Dirk Goldgar]

Dirk, Sorry about that last one, got carried away on the enter key. I
think you have pointed me to the correct problem - which items to
use?

My main form is called 'Contracts TR', and its Record Source is the
table 'Contracts'. My first subform is 'Merch Contracts Sub' (the
subform name),

Is that the name of the subform control (on the main form) or the name
of the form object that it displays? These may be the same, or they may
be different. I can have a subform control named "Subform1" displaying
a form named "frmThisIsMyForm -- Really". What counts for these object
references is the name of the subform *control*.

on the page/tab 'MerchSub',

"Page/tab"? Are you referring to pages of a tab control, meaning that
each subform is on a different page? If so, it's not relevant to the
proper construction of the reference, but I want to make sure I
understand what you mean.

the Record Source is a
SELECT statement that pulls data from the table 'Contract Details'.

Presumably you mean that's the RecordSource of the form object displayed
in the subform control.

Second subform is 'App Contract Sub' (subform name) on page/tab
'AppSub', the Record Source is a Form 'Contract Details Sub' that
pulls data from the table 'Contract Details'.

Well, the RecordSource can't be a *form*, so I guess you mean the
SourceObject. In other words, I take that "App Contract Sub" is the
name of the subform control, and "Contact Details Sub" is the name of
its SourceObject form.

I realize this is a
little convoluted; I did not have total control here. So, I guess the
problem is which item do I use where? I was thinking that I was
actually copying the data from the _tables_, therefore using the
table names. Almost home...

I'm not sure which item you should use, because you spoke of copying
data from one dependent table but you have *two* subforms. Which one's
data do you want to copy? Or should it be both?

 

[Guest]

My main form is called 'Contracts TR', and its Record Source is the table 'Contracts'. My first subform is 'Merch Contracts Sub' (the subform name),

D: Is that the name of the subform control (on the main form) or the name of the form object that it displays? These may be the same, or they may be different. I can have a subform control named "Subform1" displaying a form named "frmThisIsMyForm -- Really". What counts for these object references is the name of the subform *control*.
C: When I click the little box in the upper left corner of the subform in design view, the property sheet lists Name as ‘Merch Contracts Sub’. The name of the subform it displays is ‘Merch Contracts Sub TR’ which uses the SQL SELECT statement I referred to.
------------------------------------------------------------------

on the page/tab 'MerchSub',

D: "Page/tab"? Are you referring to pages of a tab control, meaning that each subform is on a different page? If so, it's not relevant to the proper construction of the reference, but I want to make sure I understand what you mean.
C: Yes, both subforms are on different pages of a tab control.
----------------------------------------------------------------------

the Record Source is a SELECT statement that pulls data from the table 'Contract Details'.

D: Presumably you mean that's the RecordSource of the form object displayed in the subform control.
C: Yes.
-----------------------------------------------------

Second subform is 'App Contract Sub' (subform name) on page/tab 'AppSub', the Record Source is a Form 'Contract Details Sub' that pulls data from the table 'Contract Details'.

D: Well, the RecordSource can't be a *form*, so I guess you mean the SourceObject. In other words, I take that "App Contract Sub" is the name of the subform control, and "Contact Details Sub" is the name of its SourceObject form.
C: Sorry. Brain bubble; the Record Source “Contract Details Sub†is a *query*.
-----------------------------------------------------

I realize this is a little convoluted; I did not have total control here. So, I guess the problem is which item do I use where? I was thinking that I was actually copying the data from the _tables_, therefore using the table names. Almost home...

D: I'm not sure which item you should use, because you spoke of copying data from one dependent table but you have *two* subforms. Which one's data do you want to copy? Or should it be both?
C: It may help you to know that App and Merch contracts have different contract#’s, and are separate records in the “Contracts†table, but many of their fields are the same (dates and delivery locations). I want to copy the data in the main form, then the data in either subform, depending on which contract (app or merch) was entered first, so the person entering the second corresponding contract does not have to key in all the data in these fields again which will be the same for both. But *both* subforms store data in the same table “Contract Detailsâ€.

 

[Dirk Goldgar]

D: Is that the name of the subform control (on the main form) or the
name of the form object that it displays? These may be the same, or
they may be different. I can have a subform control named "Subform1"
displaying a form named "frmThisIsMyForm -- Really". What counts for
these object references is the name of the subform *control*.
C: When I click the little box in the upper left corner of the
subform in design view, the property sheet lists Name as 'Merch
Contracts Sub'. The name of the subform it displays is 'Merch
Contracts Sub TR' which uses the SQL SELECT statement I referred to.
------------------------------------------------------------------


D: "Page/tab"? Are you referring to pages of a tab control, meaning
that each subform is on a different page? If so, it's not relevant
to the proper construction of the reference, but I want to make sure
I understand what you mean.
C: Yes, both subforms are on different pages of a tab control.
----------------------------------------------------------------------


D: Presumably you mean that's the RecordSource of the form object
displayed in the subform control.
C: Yes.
-----------------------------------------------------


D: Well, the RecordSource can't be a *form*, so I guess you mean the
SourceObject. In other words, I take that "App Contract Sub" is the
name of the subform control, and "Contact Details Sub" is the name of
its SourceObject form.
C: Sorry. Brain bubble; the Record Source "Contract Details Sub" is a
*query*. -----------------------------------------------------


D: I'm not sure which item you should use, because you spoke of
copying data from one dependent table but you have *two* subforms.
Which one's data do you want to copy? Or should it be both?
C: It may help you to know that App and Merch contracts have
different contract#'s, and are separate records in the "Contracts"
table, but many of their fields are the same (dates and delivery
locations). I want to copy the data in the main form, then the data
in either subform, depending on which contract (app or merch) was
entered first, so the person entering the second corresponding
contract does not have to key in all the data in these fields again
which will be the same for both. But *both* subforms store data in
the same table "Contract Details".

This is getting a bit elaborate, and I'm not at all convinced that there
isn't a better way to set this up -- copying data other than key fields
from one table to another usually indicates a flaw in the table
design -- but setting that aside, you haven't actually answered the
essential question: which of these subforms is it that you want to test
for having no records? This line:

If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 Then

should probably be either this:

If Me.[Merch Contracts Sub].Form.RecordsetClone.RecordCount > 0 Then

or

If Me.[App Contract Sub].Form.RecordsetClone.RecordCount > 0 Then

-- if I've understood you correctly -- but I'm not sure which.

 

[Guest]

the essential question: which of these subforms is it that you want to tes
for having no records? This line

If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 The

should probably be either this

If Me.[Merch Contracts Sub].Form.RecordsetClone.RecordCount > 0 The

o

If Me.[App Contract Sub].Form.RecordsetClone.RecordCount > 0 The

-- if I've understood you correctly -- but I'm not sure which
--------------------------------------------------------------
It may help to know that there is always a separate Contract# for any contract, App or Merch, but very often they have to send items to the same locations on the same dates, so it helps the person entering the second contract to not have to enter all the locations and dates again. There is never App data on a Merch contract or v.v., but they use many of the same fields

When I first looked at the code that I tried to adapt, it looked as though the references were to the underlying tables. I guess that's what threw me off. But in this (way too long, I know) dialog with you, it seems as though I must use the _form_ references instead, so I would need to duplicate the main form info, then the info from whichever subform was already filled in, either App or Merch. I actually need to test both subforms, then, because either the App contract or the Merch contract may have been entered first

Does this help? Can I use table references, or do I have to use the 3 different forms? If I used the tables, I would probably need to add code to refresh the tables first, so the data would be in the tables from the form entry? Thank you, thank you, thank you... accollades and riotous appreciation on their way to you...

 

[Dirk Goldgar]

the essential question: which of these subforms is it that you want
to test
for having no records? This line:

If Me.[Contracts TR].Form.RecordsetClone.RecordCount > 0 Then

should probably be either this:

If Me.[Merch Contracts Sub].Form.RecordsetClone.RecordCount > 0
Then

or

If Me.[App Contract Sub].Form.RecordsetClone.RecordCount > 0 Then

-- if I've understood you correctly -- but I'm not sure which.
---------------------------------------------------------------
It may help to know that there is always a separate Contract# for any
contract, App or Merch, but very often they have to send items to the
same locations on the same dates, so it helps the person entering the
second contract to not have to enter all the locations and dates
again. There is never App data on a Merch contract or v.v., but they
use many of the same fields.

When I first looked at the code that I tried to adapt, it looked as
though the references were to the underlying tables. I guess that's
what threw me off. But in this (way too long, I know) dialog with
you, it seems as though I must use the _form_ references instead, so
I would need to duplicate the main form info, then the info from
whichever subform was already filled in, either App or Merch. I
actually need to test both subforms, then, because either the App
contract or the Merch contract may have been entered first.

Does this help? Can I use table references, or do I have to use the 3
different forms? If I used the tables, I would probably need to add
code to refresh the tables first, so the data would be in the tables
from the form entry? Thank you, thank you, thank you... accollades
and riotous appreciation on their way to you...

LOL Let's save those for when it all works the way you want.

You might use lookups of the tables instead of the forms; it might even
be safer, because forms can have filters applied that may hide existing
records (though it may not be very likely in this case). But changing
to table lookups at this point seems to be more work for relatively
small benefit, since the code we've been working on must be very close
to working at this point, and I wouldn't dare to advise you unless you
posted the complete structure and relationships of the tables, as well
as the full details of the forms and subforms, and a more detailed
statement of how the user is supposed to interact with them.

Did you try swapping in the lines I suggested above, to see if one of
them did the job for you?