Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    32

    Unanswered: MS Access Macro Syntax

    I need to write an MS Access macro that compares consecutive records in an open database, and I am not sure what syntax I need to use in the macro.

    What is the MS Access macro syntax for:
    1) referring to the current open database?
    2) referring to a current record in the open database?
    3) referring to a field in the current record?
    4) deleting the current record?

    Also, do I need to use database open and close commands in the macro if the current database is open on screen when the macro is run?

    Thanks

    Tim

  2. #2
    Join Date
    Oct 2002
    Posts
    32
    This is a follow-up to my previous post. Here is the VB Code for what I want to be able to do in an Access macro. I have looked at the ObjectBrowser in Access, but I can't tell which codes I need. I would appreciate any assistance you can provide. Thank you!

    Private Sub cmdDoIt_Click()
    'Dimension product number, product code, and product freight variables.
    'Add boolean to identify end of file.
    Dim strPN1 As String
    Dim strPN2 As String
    Dim strPC1 As String
    Dim strPC2 As String
    Dim strPF1 As String
    Dim strPF2 As String
    Dim blnEOF As Boolean

    datValueTest.Refresh

    mRS.MoveFirst

    blnEOF = False

    'Remove duplicate lines, and subtract freight from product total to
    'arrive at product cost.
    Do Until blnEOF = True
    strPN1 = mRS.Fields("ProductNumber").Value
    strPC1 = mRS.Fields("ProductTotal").Value
    strPF1 = mRS.Fields("ProductFreight").Value
    mRS.MoveNext
    If mRS.EOF Then
    blnEOF = True
    Else
    strPN2 = mRS.Fields("ProductNumber").Value
    strPC2 = mRS.Fields("ProductTotal").Value
    strPF2 = mRS.Fields("ProductFreight").Value

    'Set empty variables to zero for calculations.
    If strPC1 = "" Then
    strPC1 = "0"
    End If
    If strPC2 = "" Then
    strPC2 = "0"
    End If
    If strPF1 = "" Then
    strPF1 = "0"
    End If
    If strPF2 = "" Then
    strPF2 = "0"
    End If

    mRS.MovePrevious
    If strPN1 <> strPN2 Then
    mRS.Edit
    mRS.Fields("ProductTotal").Value = _
    mRS.Fields("ProductTotal").Value - _
    mRS.Fields("ProductFreight").Value
    mRS.Update
    mRS.MoveNext
    ElseIf strPC1 <> strPC2 Then
    mRS.MoveNext
    ElseIf strPF1 = "0" And strPF2 > "0" Then
    mRS.Delete
    mRS.MoveNext
    ElseIf strPF1 > "0" And strPF2 = "0" Then
    mRS.MoveNext
    mRS.Delete
    mRS.MovePrevious
    ElseIf strPF1 = "0" And strPF2 = "0" Then
    mRS.Delete
    mRS.MoveNext
    Else
    mRS.MoveNext
    End If
    End If
    Loop

    'Complete calculation for last line of table.
    mRS.MoveLast
    If mRS.Fields("ProductFreight").Value <> "" Then
    mRS.Edit
    mRS.Fields("ProductTotal").Value = _
    mRS.Fields("ProductTotal").Value - _
    mRS.Fields("ProductFreight").Value
    mRS.Update
    End If
    mRS.MoveFirst

    'Display data (unnecessary if using an Access Macro).
    'LoadDisplay code not included here.
    Call LoadDisplay

    End Sub

    Again, thanks!

    Timm

Posting Permissions

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