Results 1 to 13 of 13
  1. #1
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7

    Question Copy existing record fields to new record & display on form

    Access Newbie here, so please bear with me....



    I have a Jobs form. If the user wants to capture a repeat of a previous job, I have a button which opens a Search form (without closing the Jobs Form). When the user has selected the job they want to repeat, I want to add a new record to the Jobs table using the Job No selected, close the Search form and display the new record in the Jobs form. All of this happens when the user selects a client and then a job on the Search form and clicks on a Select button (btnCustSelect).

    The following code is in the On Click event of the btnCustSelect.

    Code:
    'User is entering a repeat of a previous job
        intRepeatJobNo = lstJobName
        DoCmd.GoToRecord ObjectType:=acDataTable, ObjectName:="Jobs", record:=acNewRec
        
        'Copy Values from existing record to new record
        JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)
        CustCode = DLookup("CustCode", "Jobs", "JobNo=" & intRepeatJobNo)
        PFNo = DLookup("PFNo", "Jobs", "JobNo=" & intRepeatJobNo)
            
        'Get the new Job number
        intRepeatJobNo = Jobs.JobNo
        Forms!frmJobs.RecordSource = "SELECT Jobs.*, Customer.CustName, Factory.FactoryName FROM Factory INNER JOIN (Customer INNER JOIN Jobs ON Customer.CustCode = Jobs.CustCode) ON Factory.FactoryCode = Jobs.FactoryCode WHERE Jobs.JobNo = intRepeatJobNumber"
        Forms!frmJobs.Requery
        SetJobDetailTrue
        'Close the search form
        DoCmd.Close acForm, Me.Name
    When I run this, I get "Compile error: Variable not defined" on "JobName" (a field in the Jobs table) on this line:

    JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)

    I have tried it as Jobs.JobName - same error.

    When I comment out this code

    Code:
    JobName = DLookup("JobName", "Jobs", "JobNo=" & intRepeatJobNo)
    CustCode = DLookup("CustCode", "Jobs", "JobNo=" & intRepeatJobNo)
    PFNo = DLookup("PFNo", "Jobs", "JobNo=" & intRepeatJobNo)
    the error changes to "Error 2489 - The object 'Jobs' isn't open.

    Surely if I haven't closed the Jobs form, the Jobs table should still be open? Or do I need to open it before I can add the new record? If so, how?

    TIA

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    You are getting confused between fields and variables.
    Define JobName etc as Public in a module and set and read where needed.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run an append query using selected source key
    get the new key via Dmax()
    then open the new record: docmd.openform "fMyForm",,,"[id]=" & vNewKey

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Could be any of a few things.
    I would not use a variable name that is the same as anything else (JobName = ). Access thinks you are trying to set a variable to the DLookup value.

    I have never tried to modify or create a record using that method and would not think it's possible. Instead, I'd either run an action sql (append a record) or use .AddNew to a recordset object and requery the form. You shouldn't need to requery anyway if setting the form recordsource - one or the other will do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7
    Quote Originally Posted by Welshgasman View Post
    You are getting confused between fields and variables.
    Define JobName etc as Public in a module and set and read where needed.
    JobName etc are the names of the fields in the Jobs table?... The code I found online created a new record and then set the values that are the same between the old and the new records through DLookup - which seemed to be exactly what I wanted to do....

    Will look at the suggestions made, thanks!

  6. #6
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    I would not use a variable name that is the same as anything else (JobName = ). Access thinks you are trying to set a variable to the DLookup value.
    JobName isn't supposed to be a variable It's a field name in the Jobs table......

    Quote Originally Posted by Micron View Post
    I'd either run an action sql (append a record) or use .AddNew to a recordset object and requery the form.
    I'll give that a try in the morning, thanks - my brain hurts now.....

    My issue is that of the 40 + fields in the existing record, about 35 will be duplicated in the new record..... and I'm not sure how to duplicate the record....

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wondering why you bother to open a form to search for a record that you already have displayed somewhere (in a listbox, I presume). Why not just a method that creates a new job from the one showing on the form?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by estelle1966 View Post
    JobName isn't supposed to be a variable It's a field name in the Jobs table......
    Not the way you are using it?
    I would also prefix with type so I would call that strJobName. Then I would know which one I was working with.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    Wondering why you bother to open a form to search for a record that you already have displayed somewhere (in a listbox, I presume). Why not just a method that creates a new job from the one showing on the form?
    The record isn't displayed anywhere until I search for it - the Customers and their jobs are displayed in listboxes on the Search form - select a client, then select which of their jobs you want to work with. The Search form populates the Jobs form with a certain record (in theory, of course - as it isn't yet working!) - either an existing record the user wants to edit or a new record with 90% of the fields already populated from the record selected on the Search form.

    Not sure if I'm clear on this - in English, the user would on the Jobs form select one of 3 options:

    1) New Customer, New Job
    2) Existing customer, New job
    3) Existing Customer, Repeat of existing job

    It's option 3 I'm trying to deal with :-) So the new Job would appear on the Jobs form, pre-populated with the data from the previous record for said order and allow the user to edit the pre-populated data.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ah, OK, I thought the jobs list was on the 1st form. Using the sql method it might look something like this:
    Code:
    Dim strSql As String, strJobName As String, lngCustCode As Long, lngPFNo As Long
    
    'User is entering a repeat of a previous job
      strJobName = DLookup("JobName", "Jobs", "JobNo=" & Me.lstJobName)
      lngCustCode = DLookup("CustCode", "Jobs", "JobNo=" & Me.lstJobName)
      lngPFNo = DLookup("PFNo", "Jobs", "JobNo=" & Me.lstJobName)
      strSql = "INSERT INTO 'Jobs'('JobName','CustCode','PFNo') VALUES('" & strJobName & "'," & lngCustCode & "," & lngPFNo & "')"
      CurrentDb.Execute strSql
      Me.Filter = "JobNo='" & Me.lstJobName & "'"" '<<probably not correct. Don't know what lstJobName represents
      Me.FilterOn = True
    
      SetJobDetailTrue
    I've made assumptions about the data types of fields so if what I think is a number isn't, then the above needs modification if it is to work at all. I also assumed you'd agree that the form recordsource ought to be set in design view - unless you need to modify it for other procedures. Anyway, what has been said a couple of times is that your code is written as if you're using a variable, not a table name - hence the compile error. I have to think that's not how it is done wherever you got the code from. Maybe post a link to where you got it from if you remain stuck.

    Of course, there are several ways to handle most things, this situation being one of those things. You could .AddNew to a recordset as I mentioned. You could also GoToRecord rather than apply a filter. In either case, I haven't been able to decipher what the PK field is so I probably haven't correctly coded to apply a filter. Something tells me it's not lstJobName.

    If you research 'ms access vba copy record to same table' you'll get plenty of code samples. Here's one that proposes the recordset method
    https://stackoverflow.com/questions/...-to-same-table
    Last edited by Micron; 08-03-2021 at 01:29 PM. Reason: code correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7
    Thanks - I will look at the link you posted and do some more online searching :-)

    I did get the method I was trying to use to work this morning - the problem was that in the sample code, the textbox on the form and the field name in the table are the same, so where I was assuming it was setting a table field, it was actually setting the value of a textbox on the form. I'm not sure if it's going to be an elegant solution in the long term though, as it is based on there being a button on the form to create a copy record, whereas in my system it is always going to come from another form (the reason I was getting the error that the table Jobs was not open).

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the textbox on the form and the field name in the table are the same,
    That's from using the wizard to build the form? I'll use that too but will rename every control using a (more or less) standard naming convention. Perhaps something has changed in Access over the years but the trigger for me in not having field and object names being the same was the "Ambiguous Name Detected" error message. As soon as I changed the control name, problem was solved. IMO it also makes sense to differentiate - if not for my own sake when reviewing months later, but also for anyone else who has to follow.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    estelle1966 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2021
    Posts
    7
    Quote Originally Posted by Micron View Post
    IMO it also makes sense to differentiate - if not for my own sake when reviewing months later, but also for anyone else who has to follow.
    The names were the same in the code I found online :-) I always use naming conventions I learnt way back in the day, exactly to avoid the confusion. In my system, a textbox would be prefaced by "txt", so when the name was not prefixed by anything, I (foolishly) assumed it was a table field name.

    Thanks for all the pointers everyone -I knew it wasn't a variable, but it got me to go and do a run through of the online code.

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

Similar Threads

  1. Replies: 21
    Last Post: 08-28-2018, 02:53 PM
  2. Replies: 8
    Last Post: 05-22-2018, 11:16 AM
  3. Replies: 12
    Last Post: 05-14-2015, 03:48 PM
  4. Replies: 10
    Last Post: 03-05-2013, 03:10 PM
  5. Replies: 0
    Last Post: 02-25-2011, 09:40 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