Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A bit of Access code required (2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A bit of Access code required (2K)

    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?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A bit of Access code required (2K)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A bit of Access code required (2K)

    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

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A bit of Access code required (2K)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A bit of Access code required (2K)

    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:
    <pre>WHERE TYPE = '006' AND YEAR = '85'"</pre>

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A bit of Access code required (2K)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •