Update Query in Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning!

I appreciate your assistance on the following scenerio. I need to know:

1.) Is this possible?
2.) What is the syntax or where may I find it?

1.) I have a table of statistics and a form that displays them. When the
statistics are modified a series of update queries runs.
The update queries contain tables in this format:

Table 1

Statistics
Employee Number
Month and so on...
///////

Table 2

Stat
January
February
March
April
May and so on...
////////

Table 3

Rating
January
February
March
April
May and so on...
////////

Table 1 Statistic joins N:1 with Table 2 (Statistic N:1 Stat), and Table 2
joins N:1 with Table 3 (i.e. January N:1 Rating). The values that update are
the rating from (i.e. January) field in Table 2 and the value from the (i.e.
January) field in Table 3.

I would like to create a query in VBA that looks at the month on the form
(Table 1) and based on it, assigns the correct month Rating (Table 2) and
correct month Value (Table 3) for the month matching Table 1.

This way I do not have to go into each update query every month and change
the inner joins to the month I want since the ratings and values may differ
from month to month.


Can I eliminate my update queries and simply write a query in VBA that will
look at the month on the form and based on that month, update the ratings and
weights on the statistics table accordingly?

2.) What is the syntax.. just need a start. I understand how to open tables
and add new records but have never written an update query in code and do not
know how to declare what fields in the table to use based on the form.

Your assistance is much needed and appreciated!

Thank you in advance,

Sandy Skaar
Database Coordinator
 
Method 1.
Open an existing action query (update append maketable, delete) in design /
SQL mode. Copy the text

In the click event of a button, insert the code

currentdb().execute "<paste the text here>"

This will run the query in code

The next step is to put the text into a string variable

strTemp = "<paste the text here>"

then you can use

currentdb().execute strTemp

Then you can modify the string to include your month

If strTemp was "Delete * from tblData where [Month] = 1;"

you can change this to

strTemp = "Delete * from tblData where [Month] = " & txtMonth.value & ";"
to use the setting from your form
 
ChrisJ,

Thank you, this will come in handy when I need to specify criteria as a
variable from a form.

However, for this situation I need to specify the field from the linked
tables to use based on the month from the form object.

Do you have any suggestions for this? The only thing I can think of is
creating SQL string expressions for each month.. just trying to figure out a
more compact way of doing it.

Thanks!

Sandy

ChrisJ said:
Method 1.
Open an existing action query (update append maketable, delete) in design /
SQL mode. Copy the text

In the click event of a button, insert the code

currentdb().execute "<paste the text here>"

This will run the query in code

The next step is to put the text into a string variable

strTemp = "<paste the text here>"

then you can use

currentdb().execute strTemp

Then you can modify the string to include your month

If strTemp was "Delete * from tblData where [Month] = 1;"

you can change this to

strTemp = "Delete * from tblData where [Month] = " & txtMonth.value & ";"
to use the setting from your form


Sandy said:
Good morning!

I appreciate your assistance on the following scenerio. I need to know:

1.) Is this possible?
2.) What is the syntax or where may I find it?

1.) I have a table of statistics and a form that displays them. When the
statistics are modified a series of update queries runs.
The update queries contain tables in this format:

Table 1

Statistics
Employee Number
Month and so on...
///////

Table 2

Stat
January
February
March
April
May and so on...
////////

Table 3

Rating
January
February
March
April
May and so on...
////////

Table 1 Statistic joins N:1 with Table 2 (Statistic N:1 Stat), and Table 2
joins N:1 with Table 3 (i.e. January N:1 Rating). The values that update are
the rating from (i.e. January) field in Table 2 and the value from the (i.e.
January) field in Table 3.

I would like to create a query in VBA that looks at the month on the form
(Table 1) and based on it, assigns the correct month Rating (Table 2) and
correct month Value (Table 3) for the month matching Table 1.

This way I do not have to go into each update query every month and change
the inner joins to the month I want since the ratings and values may differ
from month to month.


Can I eliminate my update queries and simply write a query in VBA that will
look at the month on the form and based on that month, update the ratings and
weights on the statistics table accordingly?

2.) What is the syntax.. just need a start. I understand how to open tables
and add new records but have never written an update query in code and do not
know how to declare what fields in the table to use based on the form.

Your assistance is much needed and appreciated!

Thank you in advance,

Sandy Skaar
Database Coordinator
Click to expand...
Click to expand...
 
So if your base query has

"From table1 inner join table2 on table1.field1 = table 2 .field6 ...."

you can change this in code to

"From table1 inner join table2 on table1." & strTemp1 & " = table 2 ." &
strTemp2 & " ...."

Is this not what you are wanting??


Sandy said:
ChrisJ,

Thank you, this will come in handy when I need to specify criteria as a
variable from a form.

However, for this situation I need to specify the field from the linked
tables to use based on the month from the form object.

Do you have any suggestions for this? The only thing I can think of is
creating SQL string expressions for each month.. just trying to figure out a
more compact way of doing it.

Thanks!

Sandy

ChrisJ said:
Method 1.
Open an existing action query (update append maketable, delete) in design /
SQL mode. Copy the text

In the click event of a button, insert the code

currentdb().execute "<paste the text here>"

This will run the query in code

The next step is to put the text into a string variable

strTemp = "<paste the text here>"

then you can use

currentdb().execute strTemp

Then you can modify the string to include your month

If strTemp was "Delete * from tblData where [Month] = 1;"

you can change this to

strTemp = "Delete * from tblData where [Month] = " & txtMonth.value & ";"
to use the setting from your form


Sandy said:
Good morning!

I appreciate your assistance on the following scenerio. I need to know:

1.) Is this possible?
2.) What is the syntax or where may I find it?

1.) I have a table of statistics and a form that displays them. When the
statistics are modified a series of update queries runs.
The update queries contain tables in this format:

Table 1

Statistics
Employee Number
Month and so on...
///////

Table 2

Stat
January
February
March
April
May and so on...
////////

Table 3

Rating
January
February
March
April
May and so on...
////////

Table 1 Statistic joins N:1 with Table 2 (Statistic N:1 Stat), and Table 2
joins N:1 with Table 3 (i.e. January N:1 Rating). The values that update are
the rating from (i.e. January) field in Table 2 and the value from the (i.e.
January) field in Table 3.

I would like to create a query in VBA that looks at the month on the form
(Table 1) and based on it, assigns the correct month Rating (Table 2) and
correct month Value (Table 3) for the month matching Table 1.

This way I do not have to go into each update query every month and change
the inner joins to the month I want since the ratings and values may differ
from month to month.


Can I eliminate my update queries and simply write a query in VBA that will
look at the month on the form and based on that month, update the ratings and
weights on the statistics table accordingly?

2.) What is the syntax.. just need a start. I understand how to open tables
and add new records but have never written an update query in code and do not
know how to declare what fields in the table to use based on the form.

Your assistance is much needed and appreciated!

Thank you in advance,

Sandy Skaar
Database Coordinator
Click to expand...
Click to expand...
Click to expand...
 
Chris,

Ahh! I see! Thank you VERY much! You have saved me hours. This will work
splendidly. I am new to writing SQL in VBA so I am not 100% sure of all
syntax rules.

Thanks again!

Sandy

ChrisJ said:
So if your base query has

"From table1 inner join table2 on table1.field1 = table 2 .field6 ...."

you can change this in code to

"From table1 inner join table2 on table1." & strTemp1 & " = table 2 ." &
strTemp2 & " ...."

Is this not what you are wanting??


Sandy said:
ChrisJ,

Thank you, this will come in handy when I need to specify criteria as a
variable from a form.

However, for this situation I need to specify the field from the linked
tables to use based on the month from the form object.

Do you have any suggestions for this? The only thing I can think of is
creating SQL string expressions for each month.. just trying to figure out a
more compact way of doing it.

Thanks!

Sandy

ChrisJ said:
Method 1.
Open an existing action query (update append maketable, delete) in design /
SQL mode. Copy the text

In the click event of a button, insert the code

currentdb().execute "<paste the text here>"

This will run the query in code

The next step is to put the text into a string variable

strTemp = "<paste the text here>"

then you can use

currentdb().execute strTemp

Then you can modify the string to include your month

If strTemp was "Delete * from tblData where [Month] = 1;"

you can change this to

strTemp = "Delete * from tblData where [Month] = " & txtMonth.value & ";"
to use the setting from your form


:

Good morning!

I appreciate your assistance on the following scenerio. I need to know:

1.) Is this possible?
2.) What is the syntax or where may I find it?

1.) I have a table of statistics and a form that displays them. When the
statistics are modified a series of update queries runs.
The update queries contain tables in this format:

Table 1

Statistics
Employee Number
Month and so on...
///////

Table 2

Stat
January
February
March
April
May and so on...
////////

Table 3

Rating
January
February
March
April
May and so on...
////////

Table 1 Statistic joins N:1 with Table 2 (Statistic N:1 Stat), and Table 2
joins N:1 with Table 3 (i.e. January N:1 Rating). The values that update are
the rating from (i.e. January) field in Table 2 and the value from the (i.e.
January) field in Table 3.

I would like to create a query in VBA that looks at the month on the form
(Table 1) and based on it, assigns the correct month Rating (Table 2) and
correct month Value (Table 3) for the month matching Table 1.

This way I do not have to go into each update query every month and change
the inner joins to the month I want since the ratings and values may differ
from month to month.


Can I eliminate my update queries and simply write a query in VBA that will
look at the month on the form and based on that month, update the ratings and
weights on the statistics table accordingly?

2.) What is the syntax.. just need a start. I understand how to open tables
and add new records but have never written an update query in code and do not
know how to declare what fields in the table to use based on the form.

Your assistance is much needed and appreciated!

Thank you in advance,

Sandy Skaar
Database Coordinator
Click to expand...
Click to expand...
Click to expand...
 
Back
Top