Results 1 to 12 of 12

Append Query to copy data to more that one table

  1. #1
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23

    Append Query to copy data to more that one table

    Hi, I have created a database that is going to be used to create and store quotes. When the quote is given to the client they will come back and advise we have won the job. I then have a button under the quote details that will allow us to convert that quote to a job and add it to the job table.

    I have created an append query that activates when the 'Job Won' button is selected. It pulls the data from my tblQuote to my tblJob however i also need the Job_Number that gets created when the button is selected (Job_Number is an autonumber that generates when a new record is created(the append query adds the quote to tblJob)) to be added to the quote record that was just won. is this possible to do under the one query?

    Also when the 'job won' button is selected i need it to open another form that will link to the new record in tblJob and allow the user to add additional information about the job to that record that has just been created? below are some screenshots of what i have tried so far but have not been successful.
    Click image for larger version. 

Name:	Quotereg.jpg 
Views:	15 
Size:	161.2 KB 
ID:	30635


    Click image for larger version. 

Name:	Quoteregmacro.JPG 
Views:	15 
Size:	38.7 KB 
ID:	30633Click image for larger version. 

Name:	Jobadddetails.JPG 
Views:	15 
Size:	31.9 KB 
ID:	30634


    Many thanks for any help offered!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,492
    put 2 queries in a macro:
    openquery "qaAddTbl1"
    openquery "qaAddTbl2"

    run the macro.

  3. #3
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23
    Thanks, I'm having trouble on the criteria for the 2nd query, when i do it now it creates a new quote in tblquote and populates the job number however i need it to be added to the existing quote record that is showing when i click the job won button?

    I'm not sure what way to set up my 2nd append query. I have shown what i did for the first below and what i am trying for the 2nd. my issue is that because i am only creating a new record in tblJobReg i dont know how to use the Job_Number to get the quote number added to the tblquote?

    Click image for larger version. 

Name:	workingappend.JPG 
Views:	12 
Size:	51.3 KB 
ID:	30654Click image for larger version. 

Name:	not working append.JPG 
Views:	12 
Size:	45.4 KB 
ID:	30655
    Last edited by dinsey90; 10-05-2017 at 07:52 PM.

  4. #4
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,447
    The first thing to do is to convert the macros to VBA, then it will be easier to provide you with details.

    The sequence of events will be:
    - run append query to add a record to the job table
    - use dlookup to get the job number:
    Dim NewJobID As Long
    NewJobID=DLookup("Max(JobID)","JobsTable")

    - run update query to update the Quote record using NewJobID
    DoCmd.RunSQL "UPDATE QuotesTable SET JobID=" & NewJobID & " WHERE QuoteID=" & Me!QuoteID

    - open the form:
    DoCmd.OpenForm "formname",,,"JobID=" & NewJobID (this is put in the "where" parameter of the Open command)

  5. #5
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23
    That worked perfectly, thanks so much aytee!
    Would you be able to advise on how i could create another button on the Quote full details section that would add the quote to an existing job number instead of creating a new one? I was thinking about putting a drop down box with existing job numbers at the bottom of my form then an add to existing button beside it but not sure what code to put behind the button?

    Thanks
    James

  6. #6
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,447
    This depends on your table structure. The way you have explained it there is a one-to-one relationship between quotes and jobs - one job is matched up with one quote, you can't have multiple quotes for one job. So how do you see your process working? Such as, a combobox list of all job numbers that do not have a quote already attached to it, the user will select one of those job numbers which will automatically update the quote record, then you need an update query to add the quote number to the job record.

  7. #7
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23
    I had to change my structure a bit so that a job can have multiple quotes associated with it. There will be no jobs without a quote assigned to it as a job will only be created from an existing quote. Would you recommend changing my primary keys in my tblQuote and tblJobs to a number that gets incremented via code instead of the auto number field? I have read up on it a bit and people have been saying that it can cause a lot of problems? many thanks

  8. #8
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,447
    1) To select a job number for a quote, create a combobox of existing job numbers and let the user select one. This field will be bound to the quote table.

    2) The question I have for you is - do job and quote numbers have meaning, what is your company's existing policy on the numbering of them? If no meaning is associated with these numbers, such that a job number 1234 is no different to a job number 56789, then definitely an autonumber is the way to go. If there is a system whereby, for example, job numbers begin at 1 at the beginning of every year, then you will have to develop a method of manually incrementing the number.

  9. #9
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23
    The quote and job numbers dont really have much meaning, however we do quotes for different sections and have different prefixes for each such as Q,QB & W, then proceeded by a 4 digit number. I was thinking about just duplicating the database though and changing these as they are handled by different people in the company. Is there an easy way to reset and autonumber or get it to start at a certain number (as we already have alot of quotes but wont be putting them in to the database as we want to keep it fresh going forward). Thanks

  10. #10
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,447
    You can begin autonumbers from 1 by making a copy of the table into an empty table. You can begin an autonumber at a specific number by again making a copy of the table, then adding bogus records up to that number, then deleting the records.

    Autonumbers are not 4 digits long, they are many more than that.

  11. #11
    dinsey90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    23
    Thanks for all your help on this aytee! I've added in a combo box that is controlled by tblQuote and created a button to add to existing. I dont know what code to use to get the new quote added using the job number from the combo box?
    Click image for larger version. 

Name:	addtoexisting.JPG 
Views:	7 
Size:	143.6 KB 
ID:	30768

  12. #12
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,447
    I've added in a combo box that is controlled by tblQuote
    If this means that you have the combobox defined on the table, remove it immediately! Never use table design for formatting or calculating or lookups, they will always come back and bite you. Leave tables in their simplest form and use queries/forms/etc to handle data manipulation.

    I dont know what code to use to get the new quote added
    The form is handling adding a new quote record. The sequence of events for the user must be analyzed and the form must follow suit:
    - add new quote
    - some time later, job is won - select the quote record and enter the job number

    - In selecting the quote record, there needs to be a combo/listbox which gives the user a list of all quotes which don't have job numbers assigned to them. If the user will know the quote number then this list will be the quote number. You could also give the user the capability to search for quotes by client.
    - in assigning a job number to an existing quote the job number combobox must be bound to the quote table, then when a number is entered the quote record will automatically be updated

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

Similar Threads

  1. Replies: 2
    Last Post: 09-14-2017, 04:09 PM
  2. Replies: 3
    Last Post: 09-14-2017, 10:42 AM
  3. Copy current data and Append
    By Zafarm in forum Access
    Replies: 1
    Last Post: 07-10-2017, 06:48 PM
  4. Replies: 1
    Last Post: 07-05-2015, 10:11 PM
  5. Replies: 4
    Last Post: 01-20-2015, 11:55 AM

Tags for this Thread

Posting Permissions

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