Results 1 to 13 of 13
  1. #1
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73

    Copy data from one form to another

    After spending a day on this (pathetic) I am at my wits end.
    The task in my mind is simple:
    Open up a form/subform with multiple records on the subform
    Open up another form/subform
    Copy the records from the orginal subform to the other subform using a Do While Loop

    I suspect that the easiest way to do this is to use recordsets but cannot seem to get them to work so I tried this way (excuse the simplicity of the coding idea - I am not really a programmer):

    'Find out how many equipment records we need to create
    Reccount = DCount([Quote Number], "[Order Details]", "[Quote Number] = " & Forms!Quotation![Quote Number])

    'Move to subform and make sure that we are on the first record
    Forms!Quotation![Quote Sub].SetFocus
    DoCmd.GoToRecord , , acFirst

    'Set up do while loop
    Do While Reccount > 0
    ' Fill in the new record on the new form with the details of the first record of the original form
    Forms![PO Request]![PO Request Details]!Quantity = Forms!Quotation![Quote Sub]![Quantity]
    Forms![PO Request]![PO Request Details]![Quoted Price] = DLookup("PRICE", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    Forms![PO Request]![PO Request Details]![Quoted Modelnum] = DLookup("MODELNUM", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    Forms![PO Request]![PO Request Details]![Quoted Description] = DLookup("DESCRIPT", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    Forms![PO Request]![PO Request Details]![Euip Id] = Forms!Quotation![Quote Sub]![Euip Id]
    Forms![PO Request]![PO Request Details]![Ext Price] = Forms!Quotation![Quote Sub]![Quantity] * Forms!Quotation![Quote Sub]![Quoted Price]
    'Make sure that the new form has the focus
    Forms![PO Request]![PO Request Details].SetFocus
    'Save the record just created
    RunCommand acCmdSaveRecord
    'Start a new record on the new form
    DoCmd.GoToRecord , , acNewRec


    'Move to the original form and then to the subform
    Forms![Quotation].SetFocus
    Forms![Quotation]![Quote Sub].SetFocus
    'Move to the next record on the orginal form
    DoCmd.GoToRecord , , acNext
    ' Reduce the loop count by one.
    Reccount = Reccount - 1
    'Start loop again
    Loop

    So although the procedure seems pretty clear to me in my head for some reason Access doesn't like it. The current error is on the DoCmd.GoToRecord command at the end (where I am moving to the next original record). Access returns a "You can't go to specified record" error.


    What is baffling is that the code has filled in the first AND second record of the new form/subform.

    I suspect there are far better more elegant ways to do this but no amount of searching the help files has shown it to me!

    Any help would be appreciated!
    Thanks
    RCRobman

  2. #2
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    If you don't mind me asking...Why do you want to copy data from one form to another? Why not write the data to a table and just reference the table from both forms?

  3. #3
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    The reason that I am doing this is that the data refers to two separate tables - one for quotations - one for sales orders. The intent is to turn the data in the quotaion into a sales order.

  4. #4
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Is there not a common value in each field? I would think if there was you would just run an update query. I apologize if you have already been through this, difficult to picture!

  5. #5
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Actually the problem is that the two tables are actually detail tables of two parent tables Quotation and Sales Order. So the problem is that the user has to determine that they wish to create an order from a quote and then we create the order. So I cannot do an update as I would have to update the parent table (Sales Order) in order to create the primary key (Sales Order Number) that the detail table is tracking against i.e. the detail table uses creates each record for the sales order against the sales order number.

  6. #6
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    I would use an append query.

  7. #7
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    I can't see how I can use an append query when since the primary key has not been created for the main form (table) - until I have the primary key I can't create the records in the subtable?

  8. #8
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    I gave it some thought and decided to try your suggestion of using an Append query to add the data. To do so what I did was amend the code so that I opened up the Sales Req data form and created a record so that I had a primary key (PO Request Number) to work with. I then created a SQL string to use to do the insert with but when I run it I get a "Incomplete Query Clause" error.
    I have checked the code over and over and can find nothing wrong with it (to my understanding) - perhaps one of you might see what is wrong?

    newsalesreq = Forms![PO Request]![PORequest Number]
    DoCmd.Close

    'Find out how many equipment records we need to create
    Reccount = DCount([Quote Number], "[Order Details]", "[Quote Number] = " & Forms!Quotation![Quote Number])

    'Move to subform and make sure that we are on the first record
    Forms!Quotation![Quote Sub].SetFocus
    DoCmd.GoToRecord , , acFirst

    'Set up do while loop
    Do While Reccount > 0
    ' Define the new values to be inserted from the orignal form
    newqty = Forms!Quotation![Quote Sub]![Quantity]
    newqtedprice = DLookup("PRICE", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    newqtemodel = DLookup("MODELNUM", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    newqtedesc = DLookup("DESCRIPT", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
    neweqpid = Forms!Quotation![Quote Sub]![Euip Id]
    newextprice = Forms!Quotation![Quote Sub]![Quantity] * Forms!Quotation![Quote Sub]![Quoted Price]
    'Define SQLStatement to execute
    Dim SQLStatement As String

    SQLStatement = "Insert Into 'PO Request Details' ('PORequest Number',Quantity,'Quoted Price','Quoted Modelnum','Quoted Description','Euip ID','Ext Price') VALUES (newsalesreq,newqty,newqtedprice,newqtemodel,newqt edesc,neweqpid,newextprice);"
    DoCmd.RunSQL SQLStatement, -1

  9. #9
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    I don't know why but when I copied the statement "newqt edesc" came out wrong in the real code it is "newqtedesc" as it should be.

  10. #10
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Try the below syntax. If you are using variables in your SQL you need to surround them with "&" and double quotes ("). IF it is a date variable you should use #" & variable & "# and if it is a string variable you should use '" & variable & "'. Numeric variables you just use " & variable & ".

    Code:
    "INSERT INTO [PO Request Details] ([PORequest Number],[Quantity],[Quoted Price],[Quoted Modelnum],[Quoted Description],[Euip ID],[Ext Price]) VALUES (" & newsalesreq & "," & newqty & "," & newqtedprice & "," & newqtemodel & "," & newqtedesc & "," & neweqpid & "," & newextprice & ");"

  11. #11
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Thanks to both of you for your help.
    I finally got it working by creating a Append Query and using the Criteria field to strip the Sales Requistion number and Quote Number from the open forms and then doing a Docmd.OpenQuery to actually append the records.

    I neve did figure out why the original code with the DoWhile Loop didn't work - very strange in that it always created the first two records (indicating that the code was correct) and then would seem to lose it self and error out.

    Anyway, thanks to both of you for your help!

  12. #12
    Mortaza is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    1

    Complete Code and instruction about how to use it for copying data from one form to..

    Hi,

    I'm in the same situation, I have a Form/subform by the name of Rquisition Form. and another Form/Subform by the name of Purchase Order. What I want is to put a botton on the Requisition Form (Create Purchase Order) that when I click on it, it will open the Purchase Order Form with the numbers of records in the subform which is copied from the Requisition Form.

    I think this code will also work for my case but, I don't how to use it? should I put it in a module? please explain the A to Z steps for me.

    Thank you,

  13. #13
    rcrobman is offline Not Expert Yet!
    Windows XP Access 2007
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Below is the code that I used to generate the equipment needed to turn a Qutoation into a Sales Requisition (Purchase Order in your case).


    'Determine if a requisition is required
    Response = MsgBox("Do you wish to generate a sales requisition for this quotation?" & (Chr(13)) & (Chr(10)) & "NOTE: You must only do this once as a new requisition will be created each time you answer yes!" & (Chr(13)) & (Chr(10)) & "Please make sure that you have edited the quote to match the customer purchase order!!!", vbYesNo, "Generate Sales Requisition?")
    'On yes response do the following
    If Response = vbYes Then
    'Define and open the quotation form

    docname = "PO Request" This code is used to open the form in hidden mode and populate the top of it with fields from the already open form.
    DoCmd.OpenForm docname, acNormal, , , acFormAdd, acHidden

    'Fill in the values on the sales requisition form
    Forms![PO Request]!EmployeeID = Forms!Quotation!EmployeeID
    Forms![PO Request]!SoldBy = DLookup("Employee", "Employees", "EmployeeID =" & Forms!Quotation!EmployeeID)
    Forms![PO Request]!CustPONum = "TBA - From Quotation # " & Forms!Quotation![Quote Number]
    Forms![PO Request]!CustomerID = Forms!Quotation!CustomerID
    Forms![PO Request]![Company Name] = DLookup("COMPANYNM", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!Address = DLookup("ADDRESS1", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!CITY = DLookup("CITY", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!PROVINCE = DLookup("Province", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!FIRSTNAME = DLookup("FIRSTNAME", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!LASTNAME = DLookup("LASTNAME", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!SoldPhone = DLookup("PHONE", "Customers", "CUSTID= " & Me.CUSTID)
    Forms![PO Request]!Reqstatus = "NOTE: Ship To, Register To, Customer PO need to be completed - generated from quotation # " & Forms!Quotation![Quote Number]
    Forms![PO Request]!Identifier = POrequestident & Forms![PO Request]![Request Number]
    Forms!Quotation![Quote Sub].SetFocus
    'Save the record so that we have the primary key to add equipment against
    RunCommand acCmdSaveRecord

    'Get the number of the sales requisition that we just created for later use. This number will be used to append the records to the table for the subform.
    salesreqnum = Forms![PO Request]![PORequest Number]
    'Run the query that will append the equipment records to the newly created sales requisition - the query itself started by creating a select query and then just making the criteria fields equal to the Quote Number (see where clause)
    DoCmd.OpenQuery "PORequest Add Quotation", acViewNormal
    'Make sure we are on the quotation form and close it.
    Forms!Quotation.SetFocus
    DoCmd.Close
    'Move to the PO Request form - close it and then open it so it reflects the equipment added to it.
    Forms![PO Request].SetFocus
    DoCmd.Close
    'Open the newly created sales requisition for editing and provide necessary warnings about what must be edited before the sales req will process
    docname = "PO Request"
    Linkcriteria = "[PORequest Number] = " & salesreqnum
    DoCmd.OpenForm docname, acNormal, , Linkcriteria, acFormEdit
    Response = MsgBox("Please make sure that you edit the form to add in the Customer PO, Ship To, Register To" & (Chr(13)) & (Chr(10)) & "Be sure to edit the current status above to reflect that order is complete or it will not be processed", vbCritical, "Order Created - YOU MUST EDIT IT BEFORE IT WILL BE PROCESSED!!!")


    Code for the select query in SQL Format - I didn't write it in SQL but couldn't find a way to show you the grid:
    INSERT INTO [PO Request Details] ( [PORequest Number], [Euip Id], [Quoted Modelnum], [Quoted Description], [Quoted Details], Quantity, [Quoted Price], [Ext Price] )
    SELECT [Forms]![PO Request]![PORequest Number] AS Expr1, [Order Details].[Euip Id], [Order Details].[Quoted Modelnum], [Order Details].[Quoted Description], [Order Details].[Quoted Details], [Order Details].Quantity, [Order Details].[Quoted Price], [Order Details].[Ext Price]
    FROM [Order Details]
    WHERE ((([Order Details].[Quote Number])=[Forms]![Quotation]![Quote Number]));


    You may have to play around a bit but that is essentially it - grab the fields that you can from the open form - populate the new form with what you can and then do an append to it's subform table using the records out of the quotation subform.

    Good Luck!
    RCRobman

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy data from Tables in VBA
    By MattyT in forum Programming
    Replies: 4
    Last Post: 12-24-2010, 10:07 AM
  2. String to Copy a Field of Data to Another Table
    By aquarius in forum Programming
    Replies: 1
    Last Post: 09-17-2010, 09:02 AM
  3. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 03:02 PM
  4. COPY DATA and TRIM
    By Ed H in forum Access
    Replies: 1
    Last Post: 01-01-2009, 07:01 AM
  5. Replies: 0
    Last Post: 09-21-2008, 11:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums