Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24

    Code:
    Private Sub welchesJAHR_DblClick(Cancel As Integer)
    Dim Jahr As String
    Dim db As Database
    Dim quChange As QueryDef
    Dim str As String
    
    Jahr = Me.Jahr '(2021)
    
    Set db = CurrentDb
    
    str = "SELECT * FROM FJ" & Jahr '(FJ2021)
    Set quChange = db.QueryDefs("ABFRAGEFJ") '(this is the name of the query in which I want the change)
    quChange.SQL = str
    
    ' repeat for the other four tables & queries
    str = "SELECT * FROM Table2" & Jahr
    Set quChange = db.QueryDefs("Query2")
    quChange.SQL = str
    
    str = "SELECT * FROM Table3" & Jahr
    Set quChange = db.QueryDefs("Query3")
    quChange.SQL = str
    
    str = "SELECT * FROM Table4" & Jahr
    Set quChange = db.QueryDefs("Query4")
    quChange.SQL = str
    
    str = "SELECT * FROM Table5" & Jahr
    Set quChange = db.QueryDefs("Query5")
    quChange.SQL = str
    
    Set quChange = Nothing
    Set db = Nothing
    End Sub

  2. #17
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61

    Smile

    Quote Originally Posted by wvmitchell View Post
    Code:
    Private Sub welchesJAHR_DblClick(Cancel As Integer)
    Dim Jahr As String
    Dim db As Database
    Dim quChange As QueryDef
    Dim str As String
    
    Jahr = Me.Jahr '(2021)
    
    Set db = CurrentDb
    
    str = "SELECT * FROM FJ" & Jahr '(FJ2021)
    Set quChange = db.QueryDefs("ABFRAGEFJ") '(this is the name of the query in which I want the change)
    quChange.SQL = str
    
    ' repeat for the other four tables & queries
    str = "SELECT * FROM Table2" & Jahr
    Set quChange = db.QueryDefs("Query2")
    quChange.SQL = str
    
    str = "SELECT * FROM Table3" & Jahr
    Set quChange = db.QueryDefs("Query3")
    quChange.SQL = str
    
    str = "SELECT * FROM Table4" & Jahr
    Set quChange = db.QueryDefs("Query4")
    quChange.SQL = str
    
    str = "SELECT * FROM Table5" & Jahr
    Set quChange = db.QueryDefs("Query5")
    quChange.SQL = str
    
    Set quChange = Nothing
    Set db = Nothing
    End Sub
    thank you so much again!!!! this is exactly what I needed :-) It works and I'm now already adapting all my forms and reports:-)

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #19
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad it worked out for you. Make friends with Google. When someone mentions 'alias the table', "ms access table alias" will tell you what you want to know - at least in the results.

    Sorry to be picky about the code tags thing, but hopefully you can see how much easier it makes to read and (for us) troubleshoot code. It also prevents the insertion of spaces every 50 contiguous characters, which is what the forum will do. That just raises objections to things that aren't really there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-17-2016, 05:57 AM
  2. Replies: 0
    Last Post: 12-05-2013, 01:54 PM
  3. How to show Forms\reports\queries
    By stenman in forum Programming
    Replies: 3
    Last Post: 09-17-2013, 04:56 AM
  4. reports to forms to queries
    By aaa1 in forum Forms
    Replies: 9
    Last Post: 08-10-2011, 07:29 PM
  5. Queries, Forms, Reports..
    By groundhog in forum Access
    Replies: 3
    Last Post: 07-07-2010, 12:30 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