Results 1 to 11 of 11
  1. #1
    jeraldkearney is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4

    Updating a variable Access Form text box from VBA.

    I have VBA code that reads each table in an access database. It parses the table names and computes a total for a integer field in each table. It does this in a for each tbl in tbldef. As the total is developed I want to display this in a text box in a single Access form. The name of the text box is in the form of a variable. There are over 200 text boxes to update. The vba code works properly until I try to update the text box.



    If the I have a strName="Forms.frmTotalBirdCount"+strTextBoxName. How do I make something like strName=intTotal

    The Form is current. I have tried several approaches and I am looking for advice on the the best or better way.

    Thank you,

    Jerry

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of updating 200 boxes, post the totals to a table,
    then a form can show you all the totals.
    No assigning values to boxes,
    no reprogramming to add boxes.

    Code:
    DIM lTot as long
    dim sSql as string
    dim tbl as tabledef
    
    docmd.setwarning false
    for each tbl in tabledefs
      lTot = Dsum("[amt]",tbl.name)
      ssql = "insert into tDefTotals (tableName, Total) values ('" & tbl.name & "'," & lTot & ")
      docmd.runSql sSql
    next
    docmd.setwarning true

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    200 textboxes means 200 tables? Why? What is the data structure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Without a description of the purpose of the dB, 200+ tables is an indication of a bad dB design.

    Quote Originally Posted by jeraldkearney View Post
    The vba code works properly until I try to update the text box. If the I have a strName="Forms.frmTotalBirdCount"+strTextBoxName. How do I make something like strName=intTotal
    The reference to the text box is the wrong syntax.

    Consider posting the dB for analysis .......

  5. #5
    jeraldkearney is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4

    The database structure and its purpose.

    Quote Originally Posted by June7 View Post
    200 textboxes means 200 tables? Why? What is the data structure?
    Thank you for the question. I am a bird watched. There are about 900 species in the what is called the ABA region. This is basically Canada and the US. On a yearly basis you report your counts to the American Birders Association. So you keep a list for each state or providence for your life list and year list. Since my wife is also a bird watcher, I also keep her lists. So in the US that means you can manage up to 200 lists a year. Keep in mind that not only do you track the bird sighting date, you track the location. You also have to realize that there are over 96,000 recognized and indexed birding location in the US and CA. These locations are grouped down to the county or providence.

    The first screen is to identify the birds seen and their locations. A record for each sighting, a record in build into a day table. After processing the day table, it is appended to the year table. As I process the day table, I check to see if I have a Life table and Year table for the state I was birding. If not, I create the table using a standard ABA table. I then update the tables with the current data.

    We are started to travel outside the ABA area and I need a greater level of flexibility. So I am faced with a table with infinite fields or inifinite tables.

    Since my first birding db was something I wrote in Pascal on the original Mac. After that I moved through the xbases including db3, db4, and foxpro. I tend to use VBA over the SQL.

    Right now I am planning to follow ranman256 advise and use VBA to maintain a table with the different totals.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Should not be separate tables for 'life' and 'year' lists, should be one table for sightings and then queries and/or reports calculate totals.

    I still don't understand why there would be 200 list tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sounds interesting....

    Quote Originally Posted by jeraldkearney View Post
    <snip> The first screen is to identify the birds seen and their locations. A record for each sighting, a record in build into a day table. After processing the day table, it is appended to the year table. As I process the day table, I check to see if I have a Life table and Year table for the state I was birding. If not, I create the table using a standard ABA table. I then update the tables with the current data.
    What is the difference between the day table, the year table and the life table?
    Are the structures of the 3 tables the same?

    Quote Originally Posted by jeraldkearney View Post
    <snip>Since my first birding db was something I wrote in Pascal on the original Mac. After that I moved through the xbases including db3, db4, and foxpro. I tend to use VBA over the SQL.<snip>
    I started with FileMaker on the Mac. Took me a year to create a complicated report with varying totals.
    I also went through dB3 /dB4 and started on dB5. Taught myself Turbo Pascal (Borland) and wrote a ballistics dB program.
    Then Access 95 on; I'm better in VBA than SQL, but doing more (and learning more) in SQL.

    While you could leave the structure in a flat file structure (FileMaker/xBase) and use a lot fo VBA, it is easier/better(?) in a relational dB. So that is why the all the questions.....

  8. #8
    jeraldkearney is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4

    Files and the mechanics of the process.

    Quote Originally Posted by ssanfu View Post
    Sounds interesting....


    What is the difference between the day table, the year table and the life table?
    Are the structures of the 3 tables the same?


    I started with FileMaker on the Mac. Took me a year to create a complicated report with varying totals.
    I also went through dB3 /dB4 and started on dB5. Taught myself Turbo Pascal (Borland) and wrote a ballistics dB program.
    Then Access 95 on; I'm better in VBA than SQL, but doing more (and learning more) in SQL.

    While you could leave the structure in a flat file structure (FileMaker/xBase) and use a lot fo VBA, it is easier/better(?) in a relational dB. So that is why the all the questions.....
    The issues that I am working with. I currently have over 500K sightings records. Each year it grows by 10K to 25K a year. Each day I record my sightings for the day. This is my dayfile. After I process the dayfile, it goes to my yearfile and clears the dayfile. At the end of the year my yearfile is sent to Cornell University. There are over 4000 species in the world. My life list is based on these 4000+ species. My ABA list is based on less than 1000 species. All of the states and providences in the US and CA are covered by the ABA list. Remember that the location file that I use has over 96K locations in the US and CA. The location file's granulation is down to counties for future use. I can download the historical sighting for each of the 96K sites with the probablity of seeing each bird by month. My hope is to be able to predict what is the best chance to fill my lifelist and yearlist for each state, country, region and world. I am looking at a process that update daily all the tables instead of processing all of the 500k records every day.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Didn't answer any questions...

    1) How many tables do you have?
    2) What is a day table?
    3) What is a year table?
    4) What is a life table?
    5) Are the structures of the 3 tables the same?
    6) Are your wife's sightings in the same tables as yours or are hers in different tables?

    Without knowing the dB design, it is very hard to give a precise answer.
    Maybe you could post a copy of the dB with just a few records? (Do a "Compact & Repair" , then Zip it.)

  10. #10
    jeraldkearney is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    4
    I would like to thank all for their responses. I am following ranman256's advice to write the count results to a table.

    Thank you, Jerry

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Jerry,

    You might want to review/scan this material to get more focused info about a database to support bird watching.
    I recommend you re-consider your database structure before making final decision on next steps.

    I'm sure you're familiar with the terminology, but I found this with a reference to bird watching.

    Good luck with your project.

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

Similar Threads

  1. Text Box In Form Not Updating Table Field
    By dgarber in forum Forms
    Replies: 8
    Last Post: 11-22-2017, 03:15 AM
  2. Text Box Not Updating on Form
    By mmpboca in forum Forms
    Replies: 6
    Last Post: 01-16-2017, 07:35 AM
  3. Updating text box in form used as query criteria
    By jmwebster91 in forum Forms
    Replies: 6
    Last Post: 06-20-2016, 09:37 AM
  4. Replies: 2
    Last Post: 07-01-2015, 11:39 AM
  5. Replies: 5
    Last Post: 04-27-2015, 02:40 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