[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Copy record not working correctly

I have a command button on a form which when clicked is supposed to copy the current record.  But some of the code is not working correctly.  Specifically everything between   'Get username a initials   AND      ' end of get info   is not working at all.



Here's onclick code of the command button...

Private Sub cmdCopyEstimate_Click()
On Error GoTo Err_cmdCopyEstimate_Click

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste
   
    'Get username a initials
    Dim strInitial As String
    Dim strUsername As String
    Dim fldUsername As String

    strUsername = Environ("Username")

    strInitial = DLookup("strInitial", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldUsername = DLookup("fldusername", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldEmailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    Me.txtMailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))

    Me.fldUsername = strInitial
    Me.txtRealEstN = Me.txtEstimateID
    Me.txtSalesRep = fldUsername
    ' end of get info

   
Exit_cmdCopyEstimate_Click:
    Exit Sub

Err_cmdCopyEstimate_Click:
    MsgBox Err.Description
    Resume Exit_cmdCopyEstimate_Click
   
End Sub
0
SteveL13
Asked:
SteveL13
  • 14
  • 13
1 Solution
 
omgangCommented:
Are you sure it's not working?  Have you stepped through the code and looked at the values of the variables as the code executes?  Perhaps you simply need to refresh your form after the control updates.

    Me.fldUsername = strInitial
    Me.txtRealEstN = Me.txtEstimateID
    Me.txtSalesRep = fldUsername
    ' end of get info

    Me.Form.Requery

OM Gang
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I believe that   DoCmd.RunCommand acCmdCopy copies data to the Clipboard.  Is that your intent?

mx
0
 
SteveL13Author Commented:
No.  I want to copy the record to the same table as the record I'm copying.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I am sort of confused because you also have DoCmd.RunCommand acCmdPaste
0
 
SteveL13Author Commented:
All of that came from the button wizard to duplicate a record.  
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.  "is not working at all."
Can you be more specific?

Also, does the copy and paste actually happen ?  IE does the code work down to this line:

strUsername = Environ("Username"

?
IE ... do you see the new record on the Form?

mx
0
 
SteveL13Author Commented:
I actually got this to work.  I had put some bad code in an afterupdate event of the form which broke it.  Not I have a new problem...  The two field at the end of this code are not populating with the information.  The two fields are     "Me.txtRealEstN" and  "Me.txtSalesRep".  What am I doing wrong now?

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste
   
    'Get username a initials
    Dim strInitial As String
    Dim strUsername As String
    Dim fldUsername As String

    strUsername = Environ("Username")

    strInitial = DLookup("strInitial", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldUsername = DLookup("fldusername", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldEmailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    Me.txtMailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))

    Me.fldUsername = strInitial
   

    ' end of get info

    Me.txtRealEstN = [fldUsername] & "-" & [me.txtRecID]
    Me.txtSalesRep = fldUsername
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

seems there should not be an brackets around this:

Me.txtRealEstN = [fldUsername] & "-" & [me.txtRecID]

>>  Me.txtRealEstN = fldUsername & "-" & me.txtRecID

because that is a String variable you defined above.
0
 
SteveL13Author Commented:
That did not work.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What results are you getting?

Can you put a break point that first line ( Me.txtRealEstN = fldUsername & "-" & me.txtRecID ) see what the values of fldUsername and  me.txtRecID are ?

mx
0
 
SteveL13Author Commented:
I never really figured out how to do that.  So I put a message box....msgbox [fldUsername]
after the line of code but it never popped up.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, that would imply that it's not getting to that line of code,  but I don't see anything that would be stopping it either.

Can you upload this db ... explaining to how reproduce the problem ?

Is there any code you are not showing here ?

mx
0
 
SteveL13Author Commented:
This is in the onload event of the form...

    'Get username a initials
    Dim strInitial As String
    Dim strUsername As String
    Dim fldUsername As String

    strUsername = Environ("Username")

    strInitial = DLookup("strInitial", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldUsername = DLookup("fldusername", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    fldEmailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
    Me.txtMailTo = DLookup("MailEstsTo", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))

    Me.fldUsername = strInitial
    Me.txtRealEstN = Me.txtEstimateID
    Me.txtSalesRep = fldUsername
   
   
    ' end of get info
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Unless strInitial and/or fldUsername are blank, I don't see anything obvious.

Which of those three at the end do not get populated ?

mx
0
 
SteveL13Author Commented:
   Me.fldUsername DOES get populated
    Me.txtRealEstN DOES NOT get populated
    Me.txtSalesRep DOES NOT get populated
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.

What is in this field Me.txtEstimateID ?

And this does not look quite right:

fldUsername = DLookup("fldusername", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))

The syntax is fine, but fldUsername is appearing in three places.  I suspect that fldUsername is blank.

mx
0
 
SteveL13Author Commented:
Me.txtEstimateID is a concatenated field consisting of =[fldUsername] & "-" & [txtRecID]

Actually, on the form, fldUsername is not blank.

txtRealEstN is supposed to be a copy of the data in txtEstimateID.  This field is bound to a field named EstN in tblSalesRepInfo
0
 
SteveL13Author Commented:
By the way... all of the fields do populate correctly when the form is opend as a new record.  It only breaks when the copy record function is used.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually, on the form, fldUsername is not blank.

But in the code, that is also a String Variable

Dim fldUsername As String

fldUsername = DLookup("fldusername", "tblSalesRepInfo", "fldUsername=" & Chr(34) & strUsername & Chr(34))
 
That says ... Look up the value in Field fldusername  where the Field value in fldusername = strUsername
Not sure that makes sense ... it's the same field ...?

mx
0
 
SteveL13Author Commented:
I am attaching a stripped down version of the database becuase the original is very confidential.  Please do the following...

From the main menu, open a new estimate.  Note that all of the field at the top of the form are populated.  This works perfectly.

Then, from the Main Menu, click the button to edit existing estimate.   Select SCL-98 from the drop-down.  Then click the button at the bottom of the form to copy the record.  When the forms opens, the field labeled "Estimate #: is blank.  It should look just like "SCL-99".  That is what is not working.

Remote-User.zip
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I get this when I click on New Estimate ...

Can you fix that?

mx
Capture1.gif
0
 
SteveL13Author Commented:
I am not getting that.  The form opens fine for me.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Never mind ... I fixed that.  Need my UserName in that table.

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Next problem.  Existing Estimate.

When I select SCL-98 ... I get this error message.
Capture1.gif
0
 
SteveL13Author Commented:
That works for me too.  So sidestep the drop-down and go to the actual form itself... frmEstimateHeader in the database window.  Double-click it.  Since there is only one record in the table it should open to SCL-98.  When open, click the Copy button at the bottom.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Ok ... I got past that, but when I click Copy estimate I get this:

And there are several other compile errors due to missing items.  I really can't work with this db, sorry.

mx
Capture1.gif
0
 
SteveL13Author Commented:
Thanks for trying.  I can't give you the whole thing, sorry.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I mean ... I really don't have time to trouble shoot all the errors. ... just to get to the main event.

mx
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now