A bit of Access code required (2K)

Topic

New Reply

I have 3 update queries for one table. The table (MAR) has the following fields: TYPE, YEAR & AMT.

The upadate queries do the following.

TYPE = “006” & YEAR = “85”, then AMT * 21.3%
TYPE = “006” & YEAR = “86”, then AMT * 63.5%
TYPE = “006” & YEAR = “87”, then AMT * 72.5%

Can anyone provide a simple piece of VBA to do this.

Thanks in advance

Reply | Quote

Replies

It’s not clear to me what you want:

Do you want VBA code to run existing update queries?
or
Do you want to create the SQL statement for the update queries in code, and then execute these?

And do you want the result of AMT * n% to be stored in the AMT field itself?

Reply | Quote

Do you want VBA code to run existing update queries? YES

And do you want the result of AMT * n% to be stored in the AMT field itself? YES

Reply | Quote

To execute an existing query, use

DoCmd.OpenQuery “queryname”

(name between quotes) if you want to use the literal name

or

DoCmd.OpenQuery strQueryname

(variable name without quotes) if you put the name of the query in a string variable.

If the user has “Confirm action queries” checked in Tools/Options, he/she will be asked to confirm execution. If you want to prevent this, you can set SetWarnings to False before executing the queries, and back to True afterwards:

DoCmd.SetWarnings False
DoCmd.OpenQuery “qryFirst”
DoCmd.OpenQuery “qrySecond”
DoCmd.OpenQuery “qryThird”
DoCmd.SetWarnings True

Reply | Quote

Sorry, my mistake. I’m after VBA code to replace the update queries, not to actually run them.

Reply | Quote

OK, here are two methods of modifying values using code. Since I use Access 97, they are based on DAO. I suppose they can be modified to use ADO.

The first method creates SQL instructions and executes them.

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = “UPDATE MAR SET AMT = 0.213 * AMT WHERE TYPE = ‘006’ AND YEAR = ’85′”
dbs.Execute strSQL, dbFailOnError
‘ Repeat the above two instructions for the other cases
Set dbs = Nothing

Note the use of single and double quotes in the WHERE part:

WHERE TYPE = '006' AND YEAR = '85'"

The second method loops through the recordset. This is in general much slower than using SQL, but also more flexible.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(“MAR”)
Do While Not rst.EOF
If rst!TYPE = “006” Then
If rst!YEAR >= “85” AND rst!YEAR <= "87" Then
rst.Edit
Select Case rst!YEAR
Case "85"
rst!AMT = rst!AMT * 0.213
Case "86"
rst!AMT = rst!AMT * 0.635
Case "87"
rst!AMT = rst!AMT * 0.725
End Select
rst.Update
End If
End If
rst.MoveNext
Loop

Note: I have often used code like this, but I haven't really tested this.

Reply | Quote

I won’t move this thread now because of its length, but in the future you should post your Access questions to the Access forum, including those that involve VBA in Access. The Access object model is different enough from the other Office apps so that it may take longer to get an answer here, and your posts won’t be of as much benefit to others looking for Access answers.

PS/ You got lucky that Hans wandered in and saw your post.

Reply | Quote