Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: Bye bye macros- VBA help plz!

    OK. I've been pursuaded to kiss goodbye to macros and I'm quite enjoying the liberated feel of endless possibility! That aside, I've found a gem of a site to get started http://www.functionx.com/vbaccess/index.htm but without some heavy reading (reserved for the weekend) I kind of need a quick fix/addition for the moment to the fairly basic examples I have managed to take on board so far. My code is quite obviously not working but I just need clarification on some (no doubt) basic points:

    1. Whether I'm using the repeated If and ElseIf statements correctly in conjunction with each other.
    2. Whether the placing of EndIf is correct or should be used after each condition.
    3. Whether use of 'And' is correct or actually needed- examples I've seen seem to just use commas(?)

    4. Also the SetValue function in a macro can act on a value or a control. If I've declared the StartDate and EndDate as a date values by referencing the Control name then does this screw things up when I need to perform an action on the Control?

    Code as follows. Don't laugh too hard...

    Private Sub Form_Current()

    Dim dtmStartDate As Date
    Dim dtmEndDate As Date
    Dim dtmFContactDate As Date
    Dim dtmRContactDate As Date
    Dim dtmNContactDate As Date

    dtmStartDate = [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate]
    dtmEndDate = [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![EndDate]
    dtmFContactDate = [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![sfrmDataFEA-frmDataEA]![ContactDate]
    dtmRContactDate = [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![sfrmDataREA-frmDataEA]![ContactDate]
    dtmNContactDate = [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![sfrmDataNEA-frmDataEA]![ContactDate]

    'This decides the enabling and locking of the Go button and date fields that are used to pre-populate the data fields _
    via the macro behind the button. These controls must be restricted to the user at certain points in order that the macro _
    cannot run without the necessary criteria and that it cannot run again once the action has been performed thus avoiding _
    the possibility of hundreds of primary key violations.

    'Disable the button if there is no StartDate parameter.

    If IsNull(dtmStartDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False

    'Disable the button if there is no EndDate parameter.
    ElseIf IsNull(dtmEndDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False

    'If there are StartDate and EndDate parameters and none of the three subforms are populated then _
    enable the button.

    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmFContactDate) And _
    Not IsNull(dtmRContactDate) And _
    Not IsNull(dtmNContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = True

    'If there are StartDate and EndDate parameters and the first subform has been populated by the macro run from the _
    button then disable the button.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmFContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False

    'If there are StartDate and EndDate parameters and the second subform has been populated by the macro run from the _
    button then disable the button.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmRContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False

    'If there are StartDate and EndDate parameters and the third subform has been populated by the macro run from the _
    button then disable the button.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmNContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False

    'If there are StartDate and EndDate parameters and the first subform has been populated by the macro run from the _
    button then Lock the StartDate and EndDate.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmFContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate].[Locked] = True And _
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![EndDate].[Locked] = True

    'If there are StartDate and EndDate parameters and the second subform has been populated by the macro run from the _
    button then Lock the StartDate and EndDate.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmRContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate].[Locked] = True And _
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![EndDate].[Locked] = True

    'If there are StartDate and EndDate parameters and the third subform has been populated by the macro run from the _
    button then Lock the StartDate and EndDate.
    ElseIf Not IsNull(dtmStartDate) And _
    Not IsNull(dtmEndDate) And _
    Not IsNull(dtmNContactDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate].[Locked] = True And _
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![EndDate].[Locked] = True
    End If
    End Sub

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It seems that you have many ElseIf statements when, at least in some cases, you could combine your scenarios by using "Or". For example, you could combine
    Code:
    'Disable the button if there is no StartDate parameter.
    
    If IsNull(dtmStartDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False
    
    'Disable the button if there is no EndDate parameter.
    ElseIf IsNull(dtmEndDate) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].[Enabled] = False
    into
    Code:
    'Disable the button if there is no Date parameter.
    
    If (IsNull(dtmStartDate)) Or (IsNull(dtmEndDate)) Then
    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![btnGo].Enabled] = False
    Besides that, though, you "violated" the basic forum rule, by not telling us what does/does not happen, what error message(s) you get, etc.

    Sam

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One suggestion I have is to avoid referencing the Forms collection. Is your code in prt of the frmDataEA form? If so, then it is easier to read if you use Me to refer to the current object (the frmDataEA form).

    So:

    [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate]

    Becomes:

    me.[sfrmMonthCommencingEA-frmDataEA]![StartDate]

    I believe this is faster as well.

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Thanks to you both for the suggestions. I am just making sure I'm not way off the mark at the minute with the basic structure of the code as it's the first bit I've ever written hence not posting any specific error- there may be many for simple reasons!

    The 'Me' reference is something I'd considered but hadn't come accross exactly how to use it correctly so will give that a go.

    I'm still a bit miffed on the 4th point though. Can I reference the declaration to lock the control when the declaration relates to the value in the control? Or should I avoid the declaration and stay with [Forms]![frmDataEA]![sfrmMonthCommencingEA-frmDataEA]![StartDate].[Locked]

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    There's nothing much to say. Try your program first, and see if you get results, error messages, or both. Your Locked statement should work as is. If you do get messages, be sure to quote them in any subsequent post.

    Sam

Posting Permissions

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