Duplicate existing records - with a change

[Judi]

I would like to be able to copy a record and then give the user a message box
to fill in one field on the form, specifically, revision number.

Can anyone help me with this? I have the button already which copies and
pastes a new record, but I want to make sure that the user will enter a
number in this revision field... if I don't make it default, they may forget
to do this key part.

Here is the code I have already, it was created with the command button
wizard:

Private Sub btnCopyForRev_Click()
On Error GoTo Err_btnCopyForRev_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_btnCopyForRev_Click:
Exit Sub

Err_btnCopyForRev_Click:
MsgBox Err.Description
Resume Exit_btnCopyForRev_Click

End Sub


If anyone can give me the information I need, I would be grateful. Please
don't just give me code, I want to know what I'm doing so I can learn, not
just blindly copy and paste and accept that it works.

Thank you all!!

 

[Klatuu]

First, the code you posted is obsolete. the DoMenuItem is only still
included for backward compatibility. Second, it is almost impossible to read
and see what exactly is happening.

A question or two, and I will be happy to show you how it should be done and
explain it in detail.

Is this a single record form view?
Is what you are doing is want to make a duplicate of the record, with the
exception of one field, and add that record the the table?

If not, can you give some details on the situation, please.

 

[Judi]

This is the code created by the button wizard, I would appreciate your help
in fixing it.

Yes, I am trying to duplicate a record, there will be more changes than just
the one, but I want to make that one change first and then when the new
record opens, I want it to go to the first tab stop on the form. Then, the
user will be able to change the fields necessary. My main reason for this is
to make the user create a new record rather than edit the existing one. We
need to have both available, if the old one is overwritten, we lose
information.

Single record form view.

I hope you can help me... I want to learn this for myself, not just copy and
paste.

Thank you.

 

[Klatuu]

Okay, now that I know what you want to do, I can explain how to do it. It is
a bit different than you are envisioning it, but it would be the correct way
to do it.

As an overview, what we will do is use a command button to start the
process. When the button is clicked, we will make the Default Value property
of each control where we want to duplicate the value the current value of the
control. For those fields we are going to enter, we will not put in a
default value. It will then be obvious the the user which controls need to
be filled in. Once we have set the default vales, we will go to a new record
and, as you require, go to the first tab stop.

Here is the code with comments. Since I don't know the named of your
controls, all the names will be made up, but you should be able to add the
controls and fill in the names of the controls you use.

First create a command button with the name cmdDupRec
This code will go in the Click Event. To get to where the code should go,
open the Properties Dialog with the button selected and select the Events
tab. Then find the On Click event and click the small command button to the
right of its text box and choose Code Builder from the dropdown. Now you are
ready to copy the code in and make the necessary modifications.

Private Sub cmdDupRec_Click() 'Don't include this line in the copy/paste

With Me 'A With block means all the following statemenst related to Me
'Which is a reference to the current form

'This Line assigns the current value of the control to the Default Value
property of the Control
.SomeControl.DefaultValue = SomeControl
.AnotherControl.DefaultValue = Another Control
'Do the above for all the controls that you want to duplicate.
End With

'Now move to a new record
Docmd.GoToRecord acDataForm, Me.Name, acNewRec
'acDataForm means we are doing this in a form
'Me.Name tells it the name of the current form
'acNewRec means go to a new record

'Set the Focus to the First control in the tab stop

Me.SomeControl.SetFocus
'SomeControl should be the name of the control you want to start with
End Sub

That is pretty much it. If you have more questions getting it going, please
post back

 

[Judi]

O.K. I'm getting the logic behind this... am I using the control name that is
in the properties box when I am setting the defaults?

Also, can you get me a message box to fill in the field "Rev" when the new
record is created? I want it to pop up when they click the command button. It
just needs to say something like "What number revision is this?" and then
they type in an answer and hit OK or they can cancel out of it. I think I can
figure out the part to make a message box, but not how to assign the answer
to the field when all is said and done... I'm thinking something similar to
the DefaultValue we set in the other part, but I'm not sure... Maybe also a
dim statement with MsgAns being an integer and then making the default for
"Rev" equal to MsgAns? Am I at least on the right track?

Thank you again, you're helping me so much, and the comments in the code are
wonderful and will stay in there for future reference...

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

O.K. I'm getting the logic behind this... am I using the control name that is
in the properties box when I am setting the defaults?

I don't understand the question. You are doing this in VBA, not in the
properties dialog.

Also, can you get me a message box to fill in the field "Rev" when the new
record is created? I want it to pop up when they click the command button. It
just needs to say something like "What number revision is this?" and then
they type in an answer and hit OK or they can cancel out of it. I think I can
figure out the part to make a message box, but not how to assign the answer
to the field when all is said and done... I'm thinking something similar to
the DefaultValue we set in the other part, but I'm not sure... Maybe also a
dim statement with MsgAns being an integer and then making the default for
"Rev" equal to MsgAns? Am I at least on the right track?

You can't enter anything in a message box, but you can use an input box.
You can assume that if they don't enter anything in the input box, they want
to cancel, so after you have set the defaults, you can add this:

Me.RevFieldControlName = InputBox("Enter Rev Number")

If Me.RevFieldControlName = vbNullString Then
Me.Undo
Exit Sub
End If

Where RevFieldControlName is the name of the control where you would enter
the rev number.
The input box will pop up. If the user hits enter without entering anything
or clicks cancel, the value of the control will be an empty string
(vbNullSring or "") In that case, the Undo will cancel creating the new
record.

 

[Judi]

Sorry for the confusion... I mean: when I look at the property box, am I
using the name or the control source for the name I put into the VBA code?

Basically, I'm asking where to find the control name to use in the code.

 

[Klatuu]

That is correct. It should be
Me. or Me! followed by that name.
It is important to use one of those two styles so Access wont get confused
about that you are doing.
The Me! is actually the technically correct style, but most everybody,
including me, uses Me. because you get intellisense with Me. but not with Me!
Intellisense is where when you type in Me. You will see a dropdown of all
the properties and methods of the object.

 

[Judi]

O.K. mostly there, but I have some controls that are not filling
automatically... they show up with #Name? in the box. One is a combobox, the
rest are just text boxes.

Also, two fields are just not doing anything at all. They are both comboboxes.

 

[Klatuu]

As to the combo box, is it a multi column combo?
The text boxes, I don't know. You may want to check your syntax to be sure
you are using the correct names.
Examine one that is working and compare it to one that is not to see if any
differences pop out at you.

If you want, you can post the code you have written so far and I will have a
look.

As to the combos doing nothing, are the bound to a field and if so, did they
have a value showing before you clicked the button?

 

[Judi]

See Below:

If you want, you can post the code you have written so far and I will have a
look.

Private Sub btnCopyForRev_Click()

With Me 'A With block means all the following statements related to Me
'Which is a reference to the current form

'This Line assigns the current value of the control to the Default Value
'property of the Control
.Estimator.DefaultValue = Estimator (*shows up blank
- cbo)
.DMID.DefaultValue = DMID
.SSM.DefaultValue = SSM
.QuoteNum.DefaultValue = QuoteNum
.SalesQNumb.DefaultValue = SalesQNumb (*shows up blank)
.cboCustomer.DefaultValue = cboCustomer (*blank - also a cbo)
.ProjectName.DefaultValue = ProjectName (*blank - also a cbo)
.EndUser.DefaultValue = EndUser (*blank)
.Class.DefaultValue = Class
.BldgType.DefaultValue = BldgType
.Bldgs.DefaultValue = Bldgs
.cboProjectStatus = cboProjectStatus
.BID.DefaultValue = BID
.Plans.DefaultValue = Plan
.Specs.DefaultValue = Specs
.Sketch.DefaultValue = Sketch
.ChkLongBay.DefaultValue = ChkLongBay
.ChkFrameRequest.DefaultValue = ChkFrameRequest
.Description.DefaultValue = Description (*Blank)

'Do the above for all the controls that you want to duplicate.
End With

'Now move to a new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'acDataForm means we are doing this in a form
'Me.Name tells it the name of the current form
'acNewRec means go to a new record

Me.Rev = InputBox("Enter Revision Number")

If Me.Rev = vbNullString Then
Me.Undo
Exit Sub
End If

'Set the Focus to the First control in the tab stop

Me.LogDate.SetFocus
'SomeControl should be the name of the control you want to start with

End Sub

As to the combos doing nothing, are the bound to a field and if so, did they
have a value showing before you clicked the button?

Yes, they are, and yes, they did.

 

[Klatuu]

Judi,
I found I was also having difficulty with that methods. Sorry to waste your
time. Here, on the other hand is a method I tested to be sure it would work.

First, here is a routine that will store the values in the controls in the
tag property, or pub the values of the tag property in the control values,
depending on whether you pass it a True of False. False will put the values
in the Tag and True will put the tag values in the control.

Note that in the line that starts with varCtlName, all you need to do is put
the name of the control in the list as show.

Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long

'Change the list of control names to yours
'Creates an array of control names you want to either save in the tag or
copy from the tag
varCtlNames = Array("chkIsCorporate", "txtMainName", "txtFirstName", _
"txtAddressL1", "txtEnteredOn", "cboCity")

'Loops through the list of names
For lngX = 0 To UBound(varCtlNames)
If GetTag Then
'Put the value of the tag in the control using the index of the controls
collection
Me.Controls(varCtlNames(lngX)) =
Me.Controls(varCtlNames(lngX)).Tag
Else
'Put the value of the control in the tag using the index of the controls
collection
Me.Controls(varCtlNames(lngX)).Tag = _
Nz(Me.Controls(varCtlNames(lngX)), vbNullString)
End If
Next lngX

End Sub


Private Sub cmdDup_Click()
'Call the TagValues Sub to save the tag names
TagValues (False)
'Create a New Record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'Populate the controls with the saved values
TagValues (True)
'Now use the inputbox to get the value for the Rev
Me.txtFirstName = InputBox("GimmieName")

Me.chkIsCorporate.SetFocus
End Sub

 

[Judi]

Thanks, I will try this... I did get the previous one working with a bit of
tweaking (I'm learning!) but it's not pretty or anything... I'll let you
know... I can't do it right now, but I will let you know when I have given it
a try...

Thank you!