Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162

    1 form two tables

    Hi Guy

    I have two tables with the all the same fields in them one is 2013 the other 2014

    I want to have them use the same form, with a drop down combo box "2013" or "2014" and loads that table into the form, also these forms have subforms that are comments and there are 2 2014 and 2013

    Whats the best way to do this?



    I currently have a unbound combo box that has "2013 and 2014" to select from but am unsure how to code this to load the table according to year selected

  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
    Is it too late to correct the design? The normalized design would have the data in one table with an extra field for year.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Best way is one table with a field to identify the year. Don't these records already have a date field?

    Then the two related tables should also be one table.
    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
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    I believe but could be wrong, that 2 tables are needed as the tables relate to work in the 2 two years that are on going in other words I might be working on accounts for a record in 2013 and also working on accounts in 2014. So while the static data is the same there are many variables including yearend dates which effect all other field information within that table that relate only to that year.

    Hope that makes sense.

    I could simply have two forms 1 for each table but that seems messy as all the fields in the form have the same data type (but the data in each table is different - ie:- (Account deadline Date in 2013 table it says 24/12/2013 in the 2014 its 24/12/2014) and so on, also the subforms relate only to that year

  5. #5
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Click image for larger version. 

Name:	Screen2013.jpg 
Views:	13 
Size:	110.1 KB 
ID:	15194

    This is a screen shot so you can see the issue there is a huge amount of information just for 1 year

  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 don't see anything that changes my mind, but if want to keep the design you can change the record source property of the form in the after update event of the combo. An application shouldn't require structural changes when a new year arrives; yours does.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    okay well what would be the code for that?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Figured you'd go that way. Basically

    Me.RecordSource = "TableName"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Sorry a little lost on this

    so on the form property i enter this on the afterupdate

    not sure how that works these are the table names:- "
    2013 Accounts tacker main data" and "2014 Accounts tacker main data"


  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    In the combobox AfterUpdate even select [Event Procedure]. Click the ellipsis to open the VBA editor. Type code into procedure:

    Me.RecordSource = Me.comboboxname & " Accounts tacker main data"

    Should that be 'tracker'?

    Each year have to edit design - modify/create tables, queries, forms, reports, code (although with the suggested syntax code edit might not be necessary). A lot of headaches and frustration. I expect you will come to regret this approach or your successor will curse you.

    I agree with Paul. One table better.
    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.

  11. #11
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    but i don't understand how you would put all this stuff in one table and then display only 2013 or 2014, becuase part from say 8 of the 50 odd fields i would have to have it repeat every field by that factor

  12. #12
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    This is what I have
    Option Compare Database




    Private Sub Combo371_AfterUpdate(Me.RecordSource = Me.comboboxname & " Accounts tracker main data")


    End Sub






    Private Sub Combo371_AfterUpdate()


    End Sub


    Private Sub Combo371_Click()


    End Sub

    Dont seem to work

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Private Sub Combo371_AfterUpdate()
    Me.RecordSource = Me.Combo371 & " Accounts tracker main data"
    End Sub

    Suggest you give controls more meaningful names.

    With one table, apply filter criteria. Here is one method using a parameterized query as form RecordSource http://www.datapigtechnologies.com/f...tomfilter.html

    Another method could just have table as the RecordSource and use code to set the form Filter property.

    Don't understand "becuase part from say 8 of the 50 odd fields i would have to have it repeat every field by that factor".
    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.

  14. #14
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    Thank you for that, works perfectly, one more question though how about the subforms, as they are displaying the same table in both years, and other related tables are error'ing out

  15. #15
    keiath is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    162
    "becuase part from say 8 of the 50 odd fields i would have to have it repeat every field by that factor". What I meant by this is that the information in 2013 will not be the same as 2014 that data is different

    so for examaple in "account deadline Date" 2013 it would say 31/12/2013 in 2014 it would say 31/12/2014

    So in the table i would have to have 2 data fields "
    account deadline Date2013" and account deadline Date2014" as the data is not the same from 1 year to the next, which is why i think i need 2 tables 1 form

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Two Tables, One Form
    By imintrouble in forum Forms
    Replies: 7
    Last Post: 02-03-2012, 01:23 PM
  2. Replies: 14
    Last Post: 01-26-2012, 02:20 AM
  3. 1 Form ~ 2 Tables
    By cvansickle in forum Forms
    Replies: 0
    Last Post: 03-01-2011, 04:59 PM
  4. Two tables, one form
    By Remster in forum Forms
    Replies: 26
    Last Post: 11-10-2010, 05:24 AM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

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