Results 1 to 3 of 3
  1. #1
    craig1988 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2014
    Posts
    82

    Find First record and append to another table

    Hi All

    I have a table "tblGlobeCode" that has 999 records ranging obviously from 1 to 999 (The field is called "GlobeNo)". each of these numbers identifies an Acc number along with a note regarding the acc number.



    Currently there are acc numbers assigned to 500 of these records, the rest are unassigned. ALSO they assigned numbers are intermitant so we are trying to "fill the gaps".

    I have used a select query to find all the unassigned GlobeNo's and sorted them in ascending order.

    WHAT I NEED!!
    When a new account number is being entered into the DB it will need to be assigned a GlobeNo. I am going to use a button. What I need the button to do is Find the first available GlobeNo and append the Acc Number to that record in the "tblGlobeCode". Also, directly afterwards if a MsgBox could appear to tell the user what GlobeNo was assigned to it.

    Hope this can be sorted!!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,602
    Why do you have unassigned numbers?
    Why must the numbers be assigned such that there are no gaps?
    Why are there gaps present now?
    Is the purpose of the number to uniquely identify an ACC record?

  3. #3
    craig1988 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2014
    Posts
    82
    Hi Orange

    The numbers 001 - 999 make up part of a bigger serial number and it is limited to 001 - 999. I have unassigned numbers because we have a defined number of allowable records (999). I cant use an autonumber as the records used to be added on an excel file manually and numbers appeared to be plucked from the air (dont ask me why, hence the gaps.

    As I said above, the GlobeNo identifies the start of a bigger serial number (to be used in another process). One Acc number can have multiple GlobeNo's assigned to it. Its the GlobeNo that is unique NOT the Acc No in this case.

    Thanks

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

Similar Threads

  1. Replies: 4
    Last Post: 06-27-2014, 04:34 PM
  2. Replies: 21
    Last Post: 07-04-2013, 10:46 AM
  3. Find the right record to append from
    By gwboolean in forum Access
    Replies: 8
    Last Post: 04-26-2012, 08:12 AM
  4. VBA: Find Record from in table from form
    By shimmy84 in forum Programming
    Replies: 6
    Last Post: 03-19-2012, 10:51 PM
  5. best way to find a record in a linked table
    By BRV in forum Programming
    Replies: 14
    Last Post: 11-10-2011, 09:31 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
  •  
Tech Forums: Microsoft Office Forums