Results 1 to 4 of 4
  1. #1
    chromachem is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    49

    store table value in a variable

    We have a table (tblLabels) open in datasheet view.
    One of the columns in the table is LABID.

    We are trying to store the value of LABID in the first rec of the table in the variable strID.
    Code:
    DoCmd.OpenTable "tblLabels"
    DoCmd.GoToRecord acTable, "tblLabels", acFirst
    
    
    strID = LABID




    The value of LABID is empty in the VBA code.
    How can we store the value of a field in a table in a variable in VBA code?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd use a DLookup() without a criteria, would should return the first record. You understand a table can't/shouldn't be counted on to be in any particular order?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    chromachem is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    49
    Dlookup() worked great to get info for first record.
    How would I go about moving through the table one record at a time. We plann(ed) on using a for next loop until LABID="" or EOF if it works for a table.

    We want to print labels for

    LabID NumLabels
    602002 2
    602003 1
    602004 2

    2 labels for the first ID, 1 label for the 2nd ID and 2 labels for the 3rd ID. The idea was to take the table above, and make it into this table

    LabID NumLabels
    602002
    602002
    602003
    602004
    602004


    Then print the labels.

    The plan is to use an array strLabID(inta) to store the Lab ID numbers.
    strLabID(0) = 602002
    strLabID(1) = 602002
    strLabID(2) = 602003
    strLabID(3) = 602004
    strLabID(4) = 602004

    then clear out the table and write the ID's back in the table as above.
    Finally - print the ids from the table.

    The table is populated automatically from Custody Log form

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd probably go a different way. I'd open a recordset based on the first table. Within a loop of that recordset, I'd have a For/Next loop using the NumLabels value. Within that loop, I'd use the AddNew method of a recordset based on the second table to add a record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Store logged in username in a variable
    By Hammilton in forum Forms
    Replies: 11
    Last Post: 12-17-2015, 04:53 PM
  2. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  3. Date variable Store
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 07-01-2013, 05:57 AM
  4. How can i store expressions in table?
    By smahdih in forum Access
    Replies: 9
    Last Post: 10-28-2011, 05:32 AM
  5. How to store data many row in one table
    By dododo in forum Access
    Replies: 4
    Last Post: 06-25-2011, 12:42 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