vba-syntax for source change in queries, forms and reports depending on the year I'm working in

[brigitteAT]

I want to change the underlying table of a query - according to the year I want to work with - with VBA
from
SELECT FJ2020.*
FROM FJ2020;
to
SELECT FJ2021.*
FROM FJ2021;
and tried:

Private Sub welchesJAHR_DblClick(Cancel As Integer)
Dim Jahr As String
Dim fjjahr As String

Jahr = Me.Jahr '(2021)
fjjahr = "FJ" & Jahr '(FJ2021)

Dim str As String
str = "SELECT " & fjjahr
str = str & ".*"


str = str & "FROM " & fjjahr
str = str & ";"


DoCmd.RunSQL (str)

but the str with this only shows: "SELECT FJ2021.* FROM FJ2021;" and doesnt work
(I have about 5 Tables per year but lots of depending queries, forms and reports, thus the easiest way to me looks like to create 5 basic queries first, change their sources and use those then for all further forms, queries and reports ...but any other solutions are also welcome... but I do want to have separat tables for my accounting each year...)

[Welshgasman]

Firstly you should not have tables for each year.?
If you typed all that in, then look at the code again.?

in your code you have & ".*" yet you say it produces "*." ?

[moke123]

but I do want to have separat tables for my accounting each year

While I strongly agree with Gasman about your table design, a simpler solution is to use seperate backends for each year.
You can simply use something like JStreet relinker to change backends when needed.

[Micron]

I don't agree with multiple tables or multiple databases. If this was designed properly (all records in one table with a date field) then you'd only need one query. It looks to me like the sentence showing what it produces is typed here incorrectly. It has to be
SELECT FJ2021.*FROM FJ2021; but I think the lack of a space between * and FROM won't cause it to fail, which means "doesn't work" doesn't help much. I think in this case, the wanting of a table for each year is the bigger issue unless there is a good reason for it. Anyway, there is an opportunity for a vba teaching moment I suppose:
Maybe
Dim fjjar As String, strSql As String

fjjar = "FJ" & Year(Date)

strSql = "SELECT " & fjjar & ".* FROM " & fjjar & ";"

The trailing semicolon is not needed with vba sql. I suppose if you run that on January 1 you won't get much in the way of records.

[brigitteAT]

I don't agree with multiple tables or multiple databases. If this was designed properly (all records in one table with a date field) then you'd only need one query. It looks to me like the sentence showing what it produces is typed here incorrectly. It has to be
SELECT FJ2021.*FROM FJ2021; but I think the lack of a space between * and FROM won't cause it to fail, which means "doesn't work" doesn't help much. I think in this case, the wanting of a table for each year is the bigger issue unless there is a good reason for it. Anyway, there is an opportunity for a vba teaching moment I suppose:
Maybe
Dim fjjar As String, strSql As String

fjjar = "FJ" & Year(Date)

strSql = "SELECT " & fjjar & ".* FROM " & fjjar & ";"

The trailing semicolon is not needed with vba sql. I suppose if you run that on January 1 you won't get much in the way of records.

the vba-code itself so far seems ok - at least does not turn to yellow immediately - but: when I only try with: DoCmd.RunSQL (str)
it tells me that it expects something like " DELETE, INSERT, SELECT...."
how do I say that I want the query "AbfrageFJ" to run with the new year as basis - until I change Year again - , without having to open it right now?!?
do I have to "INSERT" the code to the Query !?!?!

[Micron]

RunSql is only for data definition or action queries. Your example is a simple SELECT, which doesn't make sense to run in code anyway.

run with the new year as basis - until I change Year again - , without having to open it right now?

What does that mean? You want to modify the query sql but not open it? Then you need to *modify the query definition (QueryDef). If that doesn't help, I suggest you post the rest of the procedure so we can try to figure out what's going on there. Please use code tags for your code (# on forum toolbar).

*if only doing this once per year, why not just put the query in design mode and change it there? Any solution that doesn't involve any input from you has to make assumptions. If that means using the current year, then as I said, this change will take effect on January 1. When you get back to work, you don't get to see anything from last December. Maybe you should just have a parameter prompt for the year if not just doing a manual one time change.

[brigitteAT]

It is not a matter of once a year!!!!
At the moment I permanently switch between 2020 and 2021 (2020 is sort of "test", to see - in comparsion to my old programme - if I get the same results in my new one), while 2021 is my first "regular" year (when it works and I can throw away the old programme) but between Jan and March I will always switch doing the final reports of the old year while already working in the new year...
so modify the query definition of my 5 queries (I have 5 tables per year) with one "cklick" sounds great but I have no clue how to do so
from the examples in the help-function of querydef I tried this:

Private Sub welchesJAHR_DblClick(Cancel As Integer)
Dim Jahr As String
Dim FJJAHR As String
Dim db As Database
Dim quChange As QueryDef
Dim str As String

Jahr = Me.Jahr '(2021)
FJJAHR = "FJ" & Jahr '(FJ2021)
str = "SELECT " & FJJAHR & ".* FROM " & FJJAHR & ";"

Set db = CurrentDb
Set quChange = db.QueryDefs("ABFRAGEFJ") '(this is the name of the query in which I want the change)
quChange.Execute (str) 'here it stops telling me 3421-error data-types
'dbFailOnError - goes yellow...
quChange.Close
Set quChange = Nothing
' Set db = Nothing - do I really want/need that?
End Sub
I have really no Idea of how to use QueryDef, but have to post this now, befor I look for better examples...


I want to start this procedure from a button in a form ("YEAR") where I list all years with accounting data, with the Idea to simply doublecklick on the year (= me.Jahr) I want to work in...

[orange]

??? (I have 5 tables per year)

Can you tell us more about your application and these tables in simple English?

Do you use Google or Bing?
Try Ms access querydefs as search term

[brigitteAT]

??? (I have 5 tables per year)

Can you tell us more about your application and these tables in simple English?

Do you use Google or Bing?
Try Ms access querydefs as search term

My "Project":
what I'm working on is sort of a "book-keeping"-feature in my mdb, where I have stored all links, and data for my daily work
I have 5 table "per year" where I store the "book-keeping-data"
1) "FL" in this one I enter the data of Income/Expenses amont, date,VAT, and decide on which accouts (credit+debit) they shall show up (so I get ONE FL-ID which relates to 2 ore more lines in the next table (as soon as I finish an entry-Line, I doublecklick and thus copy/calculate/split the data via rs to the next table)
2) "FJ" in this table the data show up on either credit or debit side (and if I delete one line in FL all related lines in FJ are also deleted!!)
then I have 3 more Tables where I store data for certain years only

as I always try to make things easier, and permanently try out new features, ... but have no knowledge of access/VBA/SQL-programming !!!! ....
I don't want to risk to mess up old daty by accident, so I decided to have these 5 tables separate for each year , so I copied them and added the year to the name
FL2020, FL2021 ...
FJ2020, FJ2021 ...

I though it would be the easiest way to "generally" decide somewhere*) in which year I want to work and simply change the "source" of the first underlying query (on which all other queries, forms, and reports depend on)
for each table there is a "first" query "FLAbfrage", "FJAbfrage".... and all other forms, queries, reports base on these "first" query (so I don't have to change anything in them, as soon as the Source is changed in the first query :-)
although it is only 5 Tables per year and 5 "first" queries, I want to do it in VBA with ONE cklick, because I switch between the years I work in (or look up something) much more often than just once a year only

for somewhere *) thus I created a form called "JAHR" (Year) which lists the years which contain book-keeping-data, and tried to create a VBA-Code to change with one double-clickt the source of the (5) first Query (Queries)

for example one of the fist queries is:
"AbfrageFJ"
actually it does:
SELECT FJ2020.*
FROM FJ2020;

and when I doublecklick the year 2021 it shall do:
SELECT FJ2021.*
FROM FJ2021;

so far I learnt that the solution has something to do with querydef... but I have no clue how to use that ...
what I tried so far is what is quoted in #7
(don't know if it is impolite to just quote #7, but don't want to blow up the whole thread by repeating this here either ....!?!?!?)

[moke123]

Why wouldn't you just use a where clause?

Select * from myTable where year(SomeDateField) = SomeYear.

If you're using saved queries you could use a tempvar for the year value.

I don't want to risk to mess up old daty by accident

I hope you're using a copy of the data.

When doing development it is not uncommon to have "Live data" and "Test data" in 2 seperate backend files and switch between them for testing and validation.

[orange]

Hmmmm?

as I always try to make things easier, and permanently try out new features, ... but have no knowledge of access/VBA/SQL-programming !!!! ....

Perhaps it's time to learn about database, Access, vba, SQL....
There are many tutorials and articles in the Database Planning and Design link in my signature.

[Welshgasman]

I would probably set a placeholder in that query and use the Replace() function
Something along the lines of

Set quChange = db.QueryDefs("ABFRAGEFJ")
quChange.SQL = Replace(quChange.SQL,"YearTable",Me.Jahr) ' where YearTable is the placeholder
quChange.Execute

You could also alias the table, so only one replace would be needed?

HTH

[Welshgasman]

Why wouldn't you just use a where clause?

Select * from myTable where year(SomeDateField) = SomeYear.

If you're using saved queries you could use a tempvar for the year value.


I hope you're using a copy of the data.

When doing development it is not uncommon to have "Live data" and "Test data" in 2 seperate backend files and switch between them for testing and validation.

This is the crux of the O/P's problem @moke123, having separate tables and has just said 5 tables per year!!!

[Micron]

Well, I'm not going to continue try to change your mind about your methods.
You cannot .Execute a SELECT query either. Maybe (untested and lacks error trapping)

Private Sub welchesJAHR_DblClick(Cancel As Integer)
Dim db As DAO.Database '<suggest you specify DAO rather than rely on the defaults
Dim qDef As DAO.QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs("ABFRAGEFJ")
qdef.Sql = "SELECT FJ" & Me.Jahr & ".* FROM FJ" & Me.Jahr & ";"

Debug.Print qdf.Sql '<<check output in immediate window; remove line when OK

DoCmd.OpenQuery "ABFRAGEFJ" '< don't know if you actually want to open the query or not

Set db = Nothing
Set qdef = Nothing

End Sub

Last request from me for you to post your code within code tags.

[brigitteAT]

thank you so much Micron !!! your code works perfectly!!!! (without debug.print and openquery) have already tried out several reports based on the "AbfrageFJ" and get the correct results!!!!! :-):-):-) I'm so happy!!!!
sorry didn't know what "code tags" is .... but now finally found it in the task list above!

also many thanks to moke123, Orange and Welshgasman!
.. it's not that I don't also appreciate you thoughts about my mdb-design, (...yes I do have a copy of my data, make this every day in this stadium of experiments...) but I dont understand so much of your "vocabulary" (saved query?, tempvar?, set a placeholer?, alias the table? ...) and sure, more tutorials would be helpful to really learn it :-) but actually I'm only a simple User, trying to find a way - just for myself!!! - to make access do things "automatically" for me, which otherwise would be quite time-consuming ... about 150 booking records each month in which only the date changes whereas the amounts usually doesn't change during the year...and sum up VAT, net income and expenses for the finance authority....I dont have to use a programme and can't write one, but I strongly dislike repetitve tasks and always think these are jobs the pc should do for me .. somehow :-)))
so sorry for not beeing able to state my questions "simple / correctly"