Results 1 to 9 of 9
  1. #1
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71

    Adding multiple records to a table using a single button

    Ok, so I have a form that processes traffic counts by user input. For new locations, a user has to put in a Unique GeoID and then some other location data.
    Then the user needs to enter in the data in a table format (which is linked to a table called TBIVOL) based on that GeoID that was manually entered.

    Afterwards a process button is pressed and the information from that TBIVOL is appended to various other tables and then the count data is cleared from the TBIVOL table other than the GeoID and Hours

    Currently right now the TBIVOL table just has the hours of the day, and traffic count data that is manually imputed (Hour, NB/EB, SB/WB and Combined).

    For every new GeoID created, the user has to fill in this table starting with adding a new record for each hour (1-24) so there are 24 records for that GeoID. Then the other data can be entered.

    What I would like is to have a button that could be pressed, that will automatically populate the TBIVOL table with the 24 hours. That way all the user would have to do is input the actual data (NB/EB, SB/WB, etc).




    Currently I have tried to make a table with a blank GeoID and the numbered hours from 1-24 and then have tried to do an append query based on the GeoID (using Forms!24-Hour Counts By Direction!Geo), but the button keeps erroring out. So was hoping there was something I could just use in VB that I could just put there rather than making a separate table and query that would just auto-populate the hours for me.


    Example 1 is what the user sees if entering in a new GeoID location.

    Click image for larger version. 

Name:	Ex1 New Location.JPG 
Views:	8 
Size:	82.2 KB 
ID:	28270

    Example 2 is what the user sees if using an existing GeoID location.
    Click image for larger version. 

Name:	Ex2 Existing Location.JPG 
Views:	8 
Size:	92.2 KB 
ID:	28271

    I would like the Add Hours button on the right to populate the hours automatically.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a tHours table with only hrs 1 to 24.
    next make an append query, use the data table the form is using and the tHours table
    this will append 24 records for every record on the form.

  3. #3
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    I tried that but I kept running into a error stating there's a "Null due to a type conversion failure, and didn't add 24 records(s) to the table due to key violations" despite the HOURS TYPE being the exact same in both tables.

    The hours table I only have 1 field and that's HOURS and that's it (no primary key as when I had that it didn't work either)

  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 would assume the destination table would need the GeoID field; was that included in your append query? Along with any other required fields?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    So my form has the GeoID in it (as seen in the top image).

    My FORM_Hours table had a GeoID field in there, but I took it out when ranman256 mentioned to only have the hours and that's it in that table.

    The issue is trying to link the FORM_Hours table to the TBIVOL table using the GeoID from the form and that's where I am running into issues.

    The Query currently does nothing as I can't figure out how to link it to the GeoID from the form.

    The destination table (TBIVOL) has a GeoID field.

    Currently if I manually enter in data into the Form (hours and data) the TBIVOL creates that GeoID which is at the top of the form and adds the data. So that part has always worked. It's just I can't figure out how to link the FORM_Hours table to that form's GeoID. I have other SELECT queries that uses the format [Forms]![24-Hour Counts By Direction]![GeoID] which I place in the GeoID field under Criteria and that works fine.

    Table: FORM_Hours
    Click image for larger version. 

Name:	FORM_Hours.JPG 
Views:	7 
Size:	23.0 KB 
ID:	28275

    When my FORM's button executes this append query, it just returns 0 entries
    Click image for larger version. 

Name:	Append_Query.JPG 
Views:	7 
Size:	30.2 KB 
ID:	28274

  6. #6
    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 believe ranman meant to have a "reference" table with an hours field only, with records for 1-24. Your transaction table needs that field in it, so it can relate. In your query, the form reference should be in the field row, not the criteria. If I have the fields right:

    INSERT INTO TBIVOL(GeoID, Hour)
    SELECT Forms!FormName.GeoID, Hour
    FROM
    FORM_Hours
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And by the way, "hour" is a bad choice of field name, as Access can confuse it with the Hour() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Yes unfortunately I didn't create those tables (just the FROM_Hours) so I used the same name "HOURS" that was in the original table and linked to a bunch of other tables so I just had to deal with that name.

    And yes that has worked and solved the issue. Thank you so much. I did have to put a me.refresh in the button, but that was it. So looks like it's now working as I wanted it to.

    Thanks again

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    We were happy to help.
    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. Replies: 11
    Last Post: 02-02-2017, 10:31 AM
  2. Replies: 1
    Last Post: 06-24-2016, 02:34 PM
  3. Replies: 3
    Last Post: 11-19-2013, 11:30 AM
  4. Replies: 5
    Last Post: 12-04-2011, 10:52 PM
  5. Replies: 3
    Last Post: 11-16-2011, 11:53 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