Results 1 to 7 of 7
  1. #1
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185

    Write Records On A Report To A Table - Use Duplicated AutoNumbering

    I have a continuous form that uses a parameter query in order for the end user to build a report. The form has a button next to each record that when pushed add the system date into one field and the system time into another. The report will contain anywhere from 5 to 20 records and is created by choosing a date (another parameter query) and then is sort ascended by system time.


    The data comes from a table called tblMasterData.

    I would like to add a button in the header of that report (rptList) that will write all of the records on the report to a new table. Lets call that table tblListData.
    I would also like in that table, when the records are written for there to be an auto number generated but I would like the number for all records to be the same.
    Example: If 15 records are on the report and you push the button, all 15 records are auto numbered "1" then the next time any records are written to the table by means of the report button, they would all be auto numbered "2" etc...

    Is this possible?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In answer to some of this:

    1. I would like to add a button in the header of that report (rptList) that will write all of the records on the report to a new table. Lets call that table tblListData.
    Fine - use an APPEND query or the SQL equivalent in VBA
    Much easier to do this from your form. Adding buttons to reports (in REPORT view) is possible but forms are designed for that purpose

    2. I would also like in that table, when the records are written for there to be an auto number generated but I would like the number for all records to be the same.
    Example: If 15 records are on the report and you push the button, all 15 records are auto numbered "1" then the next time any records are written to the table by means of the report button, they would all be auto numbered "2" etc...
    Then its not an autonumber - use a number field
    In your 'append query', set the field value to DMax(FieldName)+1

    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    Wow.... I can't believe this is the first time I am learning about append queries!! That is awesome. I have made the append query and it works like a charm!

    I am a little stuck on the last part though. So in the table I am appending to, there is a field named ID - I have set that to a number field. However when I type into the criteria DMax(ID)+1 I get an error message pop up stating The expression you entered has a function containing the wrong number of arguments.

    I get the same error when I try to use it in the default value of the field in the table. Where am I supposed to type DMax function?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think you can eliminate one of the DMax function's required parameters, and as far as I know, you must provide the field AND the domain. Criteria is optional. Use the syntax
    DMax ( expression, domain, [criteria] )
    You will find most of the similar functions here - suggest you bookmark the page or go to the parent site page and see what else is there that might interest you. It will save you time waiting for replies that you could easily discover the answer to.
    Good luck.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    That is a great site.... I will use it for sure!!

    I tried DMax(FieldName,TableAppendingTo)+1 That did not work.
    I tried DMax(FieldName,QueryName)+1 That did not work either.

    So here is what I have...

    qryTodaysChosenRecords - Queries the main table for records that were chosen today
    qryAppendToListTable - This is pulling from the above query and appending to tblList

    Both the queries and the table have a field titled "ID"

    Where specifically am I supposed to type the DMax function I tried in the Field Name in the append query....

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DMax(FieldName,TableAppendingTo)
    If that is pseudo code, then my suggestion would be to always post what you tried - down to the last apostrophe, which is what you're example is missing. If that's not what you really tried, then it's misleading, as I would say your attempt doesn't match the example(s) posted at that site. It would be
    DMax("FieldName","TableAppendingTo")+1. Unless your field and domain names contained spaces or characters other than underscore, in which case you'd need [brackets]. So perhaps you get the drift - 2 possible corrections I could give you, of which neither might be applicable. Plus, "doesn't work" doesn't help much, and I'm sure you want to help us help you.

    What remains would be to correct where you placed the expression - again, if that's applicable. In that case, in an empty query design grid field, in the Field row (at the top), enter
    Expr1: DMax("FieldName","TableAppendingTo")+1. Choose or enter the FieldName field in the Append To row.

    You might want to test this on a table copy first because it seems to me you're expecting this to append to several records. You should confirm that your criteria is correct lest you mess things up.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Apologies for omitting the table name part of the DMax syntax by mistake.
    Micron has already given you clear and detailed advice which I can't improve upon
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Duplicated Records Showing
    By angie in forum Queries
    Replies: 4
    Last Post: 07-26-2016, 08:31 AM
  2. Combine duplicated data in a report
    By padfoot in forum Reports
    Replies: 3
    Last Post: 03-24-2012, 08:41 AM
  3. How to delete the duplicated records in a field?
    By jamal numan in forum Access
    Replies: 7
    Last Post: 10-28-2011, 01:39 PM
  4. Records duplicated based on field
    By dskulman in forum Queries
    Replies: 3
    Last Post: 03-03-2011, 06:26 PM

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
  •  
Other Forums: Microsoft Office Forums