Results 1 to 5 of 5
  1. #1
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25

    Question VBA to only append X number of records

    I've got some code that appends records from TABLE1 to TABLE2 through a series of append queries. TABLE2 needs to be limited in record count. That number varies, and is set in an unbound text field (let's call it txtMAXRECORDS) on a form.

    I've gotten to the point where it only runs each append query if there is SOME space left in TABLE2...



    Code:
    'Append first recordset
    If DCount("*", "qry_count_all_records_in_TABLE2") < Me.txtMAXRECORDS.Value Then
    DoCmd.OpenQuery "qry_append_TABLE1_to_TABLE2_part1"
    End If
    
    'append second recordset
    If DCount("*", "qry_count_all_records_in_TABLE2") < Me.txtMAXRECORDS.Value Then
    DoCmd.OpenQuery "qry_append_TABLE1_to_TABLE2_part2"
    End If
    
    'append third recordset
    If DCount("*", "qry_count_all_records_in_TABLE2") < Me.txtMAXRECORDS.Value Then
    DoCmd.OpenQuery "qry_append_TABLE1_to_TABLE2_part3"
    End If

    ...but let's say I only have space for 10 more records, but my third query would drop in 100. Is there a way that I can make instruct query to only append the number of records available?

  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
    Create a SELECT query: SELECT TOP N.... where N is the number of records to be appended.
    Now create an APPEND query based on that
    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
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I might as well ask because I know if I don't someone else will. Why would you restrict a table to n records? What do you do when you reach your limit, create a new table? Something seems very wrong.
    Anyway, you would have to ensure that the append only adds one record (no idea how at this point) and loop

    For i = 1 To Me.txtMAXRECORDS

    if that control contains a value that represents the max minus the current count. If it represents the table max, then you'd still have to append only one record at a time, but after each loop, get the DCount of the table.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25
    Hi Micron- TABLE2 is a workflow table. Each record represents a piece of work to be done that day. The max amount of work that can be completed in a day is driven by the number of workers that show up, which varies due to illness, vacation, etc.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Still makes no sense. You don't create multiple tables for the same basic thing, you use foreign keys or composite indexes or attribute fields to distinguish one group of records from another in the same table. One day this will trip you up somehow if you continue long enough.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Query a textbox and append a number to it
    By shabbaranks in forum Programming
    Replies: 5
    Last Post: 12-05-2018, 09:39 AM
  2. Replies: 3
    Last Post: 10-30-2018, 09:41 AM
  3. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  4. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  5. Append Query and Increment Number
    By burrina in forum Queries
    Replies: 1
    Last Post: 02-02-2013, 12: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