custom copy button

[clueless]

i'm brand new to this message board and vba code for that
matter...please be patient.

i created a copy button on "frmDealsRework" that will copy an existing
record, but i want to customize the button such that the user can
update "txtLoanName" and/or "txtClientName" before access copies the
record.

i have created another form, "frmCopyLoanUpdate", that has two unbound
text boxes, "txtLoanNameChange" and "txtClientNameChange". the user
should update one of the text boxes or both. as stated previously, i
would like the copied record to include these updates.

in the properties section of the copy button, i've created an [Event
Procedure] in the "On Click" row (it doesn't work at all!). the intent
of the code is to open "frmCopyLoanUpdate", hide (or at least minimize)
"frmDealsRework", allow the user to enter the appropriate changes in
"txtLoanNameChange" and "txtClientNameChange", then finish copying the
record.

my knowledge of vba is very limited and trial and error has been
unsuccessful. can anyone get me started here?

thank you,
clueless

 

[Albert D.Kallal]

i have created another form, "frmCopyLoanUpdate", that has two unbound

text boxes, "txtLoanNameChange" and "txtClientNameChange". the user
should update one of the text boxes or both. as stated previously, i
would like the copied record to include these updates.

in the properties section of the copy button, i've created an [Event
Procedure] in the "On Click" row (it doesn't work at all!). the intent
of the code is to open "frmCopyLoanUpdate", hide (or at least minimize)
"frmDealsRework", allow the user to enter the appropriate changes in
"txtLoanNameChange" and "txtClientNameChange", then finish copying the
record.

No need to hide, or minimize the main form...just have your small form open
up...it will be on top of the other form, and the user will still have a
"visual que" as to what record they are about to copy.....

all in all, this will take about 10-15 lines of code...

Here is how the code will open up our copy form. Just a simply buttion on
the main form...and the code in the event click behind it will be

me.Refresh ' we need to save to disk before we copy!! right!!

docmd.OpenForm "frmCopyLoanUpdate"

ok, now, we have our form opened (by the way, when done testing..make sure
you set the "other" tab of this form to "model" so the user cannot get out
of the form!!!)

ok, in this form, we have the two un-bound boxes. Did you say the user MUST
fill out one these controls before the copy?

Anyway, you will have two button on this form

Cancel Copy

The code behind the Cancel is easy...you can use the wizard to build that
form...it just will close. Eg, the code for that button is

docmd.Close


now, the code behind our copy command will be

dim strFields as striing
dim lngID as long
dim strSql as string
dim lngNewID as long
dim f as form

if isnull(me.txtLoanNameChange) or isnull(me.txtClientNameChange) then

msgbox "please enter a name change"
exit sub
end if

' ok...copy....

' add to below the list of fields you want to copy (leave out the autonumber
primary key, and any other fields that
you might not want to copy

strFields = "FirstName,LastName,Company,WorkPhone"

of course, the above also assumes you did not have any spaces in your field
names (field names with spaces inflict a lot of pain!!).

set f = forms!frmDealsReWork

lngID = f!id ' get existing primary key id

ok, now, build a sql string to "copy" the record

strSql = "INSERT INTO tblLoans (" & strFields & ")" & _
" SELECT " & strFields & " from tblLoans" & _
" where id = " & lngID

currentdb.Execute strSql
strSql = "select @@identity from tblLoains"
lngNewid = currentdb.openRecordSet(strSql)(0)

' ok, no move the form to this new reocrd....

f.requery
f.FindFirst "id = " & lngNewID

if isnull(me.txtLoanNameChange) = false then

f!LoanName = me.txtLoanNameChange

end if

if isnull(me.txtClientNameChange) = false then

f!LoanName = me.txtClientNameChange

end if

docmd.close


The above is air code...but does lay out the steps....

 

[clueless]

Albert,

thanks for resonding to my post so quickly. the sample code you have
given me is a HUGE help, but i do have a few follow-up questions and/or
points of clarification:

1. just to confirm...the user MUST fill out at least one of the two
text fields in frmCopyLoanUpdate (or the user can fill out both). i'm
not sure if that changes your advice.

2. when setting the strFields string, there are 40 or 45 fields that
need to be copied into the new record (basically i want everything to
be copied except for the primary key LoanID. the new record should get
its own ID in the same table). do i need to type all 40 field names
into the string or do you know of a quicker technique?

3. for strSql, should i copy the notation that you have used exactly
as it appers? you typed - strSql = "INSERT INTO tblLoans (" &
strFields & ")" & _ " SELECT " & strFields & " from tblLoans" & _"
where id = " & lngID (i realize that "_" means a continuation of the
code line).

4. i don't understand this code at all:

currentdb.Execute strSql
strSql = "select @@identity from tblLoains"
lngNewid = currentdb.openRecordSet(strSql)(0)

i would appreciate any additional insight. thanks again for all your
help.

 

[Albert D.Kallal]

1. just to confirm...the user MUST fill out at least one of the two

text fields in frmCopyLoanUpdate (or the user can fill out both). i'm
not sure if that changes your advice.

No real change. So, you can sell see that the code simply tells the user if
one of them is blank...

2. when setting the strFields string, there are 40 or 45 fields that
need to be copied into the new record (basically i want everything to
be copied except for the primary key LoanID. the new record should get
its own ID in the same table). do i need to type all 40 field names
into the string or do you know of a quicker technique?

hum...yea...40 fields quite a bit. Ok, in this case, I likey would then
build a append query, and then drop in the 40 fields. this would mean that
at least you donn't have to type them in..but can select in the query
builder. you then grab the sql text from the querydef object. I often do
this.

I suppose another way would be to use a reocrdset. Can I assume the primary
key (id) is te first field...and that is the ONLY field we want to skip?
(lets assume this!!!).

3. for strSql, should i copy the notation that you have used exactly
as it appers? you typed - strSql = "INSERT INTO tblLoans (" &
strFields & ")" & _ " SELECT " & strFields & " from tblLoans" & _"
where id = " & lngID (i realize that "_" means a continuation of the
code line).

Yes, but of couse, your field names, and in the above, the table name of
tblLoans needs to be changed to what you used. However, since we do have a
lot of fields, and it is the same table, then lets dump the use of sql....

4. i don't understand this code at all:

currentdb.Execute strSql
strSql = "select @@identity from tblLoains"
lngNewid = currentdb.openRecordSet(strSql)(0)

The above code gets the new autonumber (primary key) of the record we just
added. After we add the record...we will have to move the form to that new
record. So, the above pulls out the primary key of the record just added.
You OFTEN have to do this in code. The above is traditionally how you do
this in oracle, or sql server, and it also works with JET 4

However, we just agreed to dump the use of sql, and we are going to use two
reocrdsets. The reason we will use two reocrdsets is that we don't want to
type in the field names. (as mentioned, you could also build a append
query---but lets go with the record set, as it will kill two birds here

bird #1 - it will copy our fields...and if your modify the table structure,
then the following code will include any new fields that you add over time

bird #2 - we can eliminate the @@idenity code to grab the primary key if we
use a 2nd reocrdset for the target data...

So, here how we will do this now....

ok....just got a phone call.....I have to go...but will continue this thread
later today..or tomorrow....

However, here is a sketch of how the code will go..

dim rstFrom as dao.reocrdset
dim rstTo as dao.RecordSet

rstFrom = set = forms!formname.reocrdset

rstTo.AddNew
for i = 1 to numFields
rstTo(i) = rstFrom(i)
next i
' code here to udpate comnay name (if to be changed).
lngNewID - rstTo(0) ' get primary key
rstTo.Close

So, we can replace all of the sql we used with two reocrdsets. Looking at
this now...it will not be much less code, but certainly less typing on your
part, as you don't have to enter the field names.....

 

[clueless]

awesome! i'll keep on lookout for your next post. a couple of things
below:

---- this is a safe assumption on your part. the first field is, in
fact, the LoanID. we want to skip this one and copy all of the other
fields (there are 48, excluding the Loan ID).

----- this line of code returns an error (compile error: expected
expression)

---- do i need to define this variable or did you simply want me to
use the total number of the fields in the table minus the LoanID?

---- i assume that you're referring to the "txtLoanName" and
"txtClientName" updates that the users enteres. is that correct? does
this code change at all since we bailed on SQL? where exactly does it
go?

i don't know if i should just post my messages or if i should email
them to you. until you tell me differently, i'll continue to do both.
thanks again for your attention.

 

[Albert D.Kallal]

i don't know if i should just post my messages or if i should email
them to you. until you tell me differently, i'll continue to do both.
thanks again for your attention.

no...keep in the newsgroup. Others can learn from this!!!

---- this is a safe assumption on your part. the first field is, in
fact, the LoanID. we want to skip this one and copy all of the other
fields (there are 48, excluding the Loan ID).
great...ok...

----- this line of code returns an error (compile error: expected
expression)


---- do i need to define this variable or did you simply want me to
use the total number of the fields in the table minus the LoanID?

no, we would set it in code...so, if you over time add more fields, it would
not have to be changed...

---- i assume that you're referring to the "txtLoanName" and
"txtClientName" updates that the users enteres. is that correct? does
this code change at all since we bailed on SQL? where exactly does it
go?

ok, so, here is our code so far....


Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim i As Integer

Dim lngNewID As Long


if isnull(me.txtLoanNameChange) or isnull(me.txtClientNameChange) then

msgbox "please enter a name change"
exit sub
end if

Set rstFrom = forms!frmDealsReWork.ReocrdSet

Set rstTo = forms!frmDealsReWork.RecordsetClone

rstTo.AddNew

For i = 0 To rstFrom.Fields.Count - 1

rstTo(i) = rstFrom(i)

Next i
lngNewID = rstTo(0) ' get the new loanID

if isnull(me.txtLoanNameChange) = false then
rst("LoanName") = me.txtLoanNameChange
end if

if isnull(me.txtClientNameChange) = false then
rst("tClientName") = me.txtClientNameChange
end if

rstTo.Update

' move form to new record...
rstFrom.Requery ' re-load recordset to show new record added
rstFrom.FindFirst "id = " & lngNewID

Set rstFrom = Nothing
Set rstTo = Nothing

docmd.close ' close our little form..

The above in a nut shell should work for us. And, by spending some time
thinking about this...we wound up with less code anyway....

 

[clueless]

i know you're tired of this scenario, but i'm still struggling to make
it work. the "copy" button on the main form works fine. it opens up
the small form ("frmCopyLoan"), but my "Continue Copy" button code is
not working. it breaks down immediately. the error occurs on the
first line: Dim rstFrom As DAO.Recordset. here is how the enitre code
looks for the button right now:

Private Sub ContinueCopy_Click()

Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim i As Integer
Dim IngNewID As Long

rstFrom = Forms!Deals_Rework.Recordset

If IsNull(Me.txtLoanNameChange) Or IsNull(Me.txtClientNameChange)
Then
MsgBox "Please update at least one of the two fields in the
form."
Exit Sub
End If

Set rstFrom = Forms!Deals_Rework.Recordset
Set rstTo = Forms!Deals_Rework.RecordsetClone

rstTo.AddNew

For i = 0 To rstFrom.Fields.Count - 1

rstTo(i) = rstFrom(i)

Next i

IngNewID = rstTo(0) 'get the new loan ID

If IsNull(Me.txtLoanNameChange) = False Then
rst(txtLoanName) = Me.txtLoanNameChange
End If

If IsNull(Me.txtClientNameChange) = False Then
rst(txtClientName) = Me.txtClientNameChange
End If

rstTo.Update 'move form to new record

rstFrom.Requery 're-load record set to show new record added
rstFrom.FindFirst "ID = " & IngNewID

Set rstFrom = Nothing
Set rstTo = Nothing

DoCmd.Close

End Sub


do you see where i'm going wrong? thanks for hanging in there with me.

 

[Albert D.Kallal]

Dim rstFrom As DAO.Recordset

If the above don't work, then you don't have a reference to the dao library.
(this means you are using a2000, or a2002), since a97, and a2003 have the
dao reference by default.....

You can see the step by step to set that reference here

http://www.members.shaw.ca/AlbertKallal/wordmerge/Details.htm

Ignore the comments about the code...but note how the reference is set....

So, set that reference, and then try a debug->compile. Get the compile to
work BEFORE you try and run the code....

 

[clueless]

albert, this is as close as i can get it. the DAO reference is working
now. that's good. i start at i=1 and that allows the code to compile
(at i=0 the code doesn't work). the record will copy but the updates
aren't working quite right. do you see anything strange in my code?
basically, i've gotten it setup so that the updates for
txtLoanNameChange and txtClientNameChange will update, but the new
record is listed at the beginning of the table instead of the end (i
would like the new record to show up last). is the code b/n the two
lines below correct? Also, the wrong form is closing on me. Any ideas
there?


Private Sub ContinueCopy_Click()

Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim i As Integer
Dim IngNewID As Long

If IsNull(Me.txtLoanNameChange) And IsNull(Me.txtClientNameChange)
Then
MsgBox "Please update at least one of the two fields in the
form."
Exit Sub
End If

Set rstFrom = Forms!Deals_Rework.Recordset
Set rstTo = Forms!Deals_Rework.RecordsetClone

rstTo.AddNew

For i = 1 To rstFrom.Fields.Count - 1

rstTo(i) = rstFrom(i)

Next i

IngNewID = rstTo(0) 'get the new loan ID
_____________________________________________________________
If IsNull(Me.txtLoanNameChange) = False Then
Forms!Deals_Rework.txtLoanName = Me.txtLoanNameChange
End If

If IsNull(Me.txtClientNameChange) = False Then
Forms!Deals_Rework.txtClientName = Me.txtClientNameChange
End If

rstTo.Update 'move form to new record

rstFrom.Requery 're-load record set to show new record added
rstFrom.FindFirst "LoanID = " & IngNewID

Set rstFrom = Nothing
Set rstTo = Nothing

DoCmd.Close
_____________________________________________________________
End Sub

again, thanks for all your help. we're almost there.