Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    5

    Unanswered: Help with VBA in Access

    I working in an accounting office and am trying to creating a database to keep track of all of our contract invoices. I am trying to create VBA code that will reference two other controls within the form to calculate the payment due date. I am a complete rookie with Access and VBL (most of my programming experience is in C++). I am going to post a screenshot of the form to show the control names as well as the VBL code that I have come up with to this point through 2 days of research. As you can see, I have two subs because I want the "Due Date" field to update when either of the dependant controls are updated. Again, if I am going about this incorrectly please feel free to tell me so. I am very new at this and have very thick skin (thank you to the US Army for that). Any and all help with this will be greatly appreciated. Thank you

    Click image for larger version. 

Name:	Access DB Snip.PNG 
Views:	10 
Size:	173.1 KB 
ID:	16128

    Code:
    Private Sub Date_Inv_Received_AfterUpdate()
    
        'Create variables'
        Dim RRDate As Date
        Dim DateInvReceived As Date
        Dim blineDate As Date
        
        'Assign the date the invoice was received to the dateInvReceived variable.'
        DateInvReceived = Form!Contract!DateInvReceived.Value
        
        'Set the focus and assign rrDate variable'
        Form!Contract!RRDate.SetFocus
        RRDate = Form!Contract!RRDate.Value
    
        If RRDate = 0 Then
            blineDate = "Not Due"
        ElseIf DateInvReceived + 7 < RRDate Then
            blineDate = DateInvReceived + 7
        ElseIf RRDate < DateInvReceived Then
            blineDate = DateInvReceived
        Else
            blineDate = RRDate
        End If
        
        'Set the focus to the output'
        Form!Contract!baselineDate.SetFocus
        Form!Contract!baselineDate = blineDate
    
    End Sub
    
    
    Private Sub RR_Date_AfterUpdate()
    
        'Create variables'
        Dim RRDate As Date
        Dim DateInvReceived As Date
        Dim blineDate As Date
        
        'Assign the date the invoice was received to the dateInvReceived variable.'
        DateInvReceived = Forms!Contract!DateInvReceived.Value
        
        'Set the focus and assign rrDate variable'
        Forms!Contract!RRDate.SetFocus
        RRDate = Forms!Contract!RRDate.Value
    
        If RRDate = 0 Then
            blineDate = "Not Due"
        ElseIf DateInvReceived + 7 < RRDate Then
            blineDate = DateInvReceived + 7
        ElseIf RRDate < DateInvReceived Then
            blineDate = DateInvReceived
        Else
            blineDate = RRDate
        End If
        
        'Set the focus to the output'
        'Forms!Contract!baselineDate.SetFocus
        'Forms!Contract!baselineDate = blineDate
    
    End Sub
    I was thinking about this last night and realized that I missed adding a few things. This is in Access 2007 and I am basically trying to use this to replace an if statement that was used in Excel. The if function is as follows: =IF(O2="","",IF((J2+7)<O2,J2+7,IF(O2<J2,J2,O2))) where column J is 'DateInvReceived' and column O is 'RRDate'. Again, thank you all for your help.
    Last edited by jlokenvitz; 01-28-15 at 09:20. Reason: Additional information added

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    A few questions: Are all of the fields you have in your screenshot tied to a table? If the baselineDate is unbound, you can set it as a calculated field based on the other two dates. If you are storing this information, we need to stick this with this method. If these controls are all bound, is it the same table? This does not look like a couple of subforms, but I want to check.

    Running under the assumption that you have 1 table on this form and you want to store this information I will suggest the following:

    Create a module (since the procedure is the same between the two events) and call it AssignBaselineDate

    Code:
    Private Sub AssignBaseline()
    If IsNull(Me.RRDate) Then
        Me.baselineDate = Nothing
    ElseIf DateAdd("d", 7, Me.DateInvReceived) < Me.RRDate Then
        Me.baselineDate = DateAdd("d", 7, Me.DateInvReceived)
    ElseIf Me.DateInvReceived < Me.RRDate Then
        Me.baselineDate = Me.DateInvReceived
    Else
        Me.baselineDate = Me.RRDate
    End If
    
    End Sub
    Then set the event handler for both DateInvReceived and RRDate as

    Code:
    Private Sub DateInvReceived_AfterUpdate()
    
    AssignBaseline
    
    End Sub
    
    Private Sub RRDate_AfterUpdate()
    
    AssignBaseline
    
    End Sub
    This way, you will only have to update the one procedure. Hope this helps.

  3. #3
    Join Date
    Jan 2015
    Posts
    5

    RE: Help with VBA in Access

    That definitely looks much simpler than the approach I was trying to piece together through research on various sites. I made the changes and come up with the error "Compile error: Sub or Function not defined". This happens when I try to change either field. I have attached a screenshot of the error for your review. I also noticed that it put underscores in the sub names. I tried it with taking those underscores out but then it seemed like it didn't even try to run the event. Again, thank you for your help.
    Click image for larger version. 

Name:	Vendor Pay Compile Error.JPG 
Views:	4 
Size:	134.1 KB 
ID:	16136
    Post edit to attach another image. This is of both codes entered into VBA to ensure that I didn't mess anything up during the copy and paste.
    Click image for larger version. 

Name:	Vendor Pay Code.JPG 
Views:	1 
Size:	158.5 KB 
ID:	16137
    Last edited by jlokenvitz; 01-30-15 at 17:05.

  4. #4
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Yeah, from your code, I was not necessarily sure how your controls were named. The event handlers and the module will need to reflect the control names you are using (Date_Inv_Received v. DateInvReceived, RR_Date v. RRDate, etc.).

  5. #5
    Join Date
    Jan 2015
    Posts
    5
    I had them all named with C++ naming conventions i.e. no spaces and each word capitalized. I changed them all to have the underscore and still get the same error. Wonder if I could talk the boss into getting you some mileage to come down to Des Moines to help. lol. Screenshot of the error is attached.
    Click image for larger version. 

Name:	Vendor Pay Compile Error 2.JPG 
Views:	5 
Size:	125.2 KB 
ID:	16138

  6. #6
    Join Date
    Jan 2015
    Posts
    5
    Any ideas from anyone?

  7. #7
    Join Date
    Jan 2015
    Posts
    5
    Ok, I have finally gotten the field to update by doing a hybrid of my initial attempt with what Brian suggested. Now the problem is the only time the field updates is when I enter design mode and then switch back to form mode. Is there a different even that I should be using rather than AfterUpdate? Ideally, this would be something that would be updated automatically as it would in Excel but I don't know if that's even possible in Access. Attached screenshots to show both the code and the form that I am using. Thank you all again for your help.Click image for larger version. 

Name:	Vendor Pay Form.JPG 
Views:	3 
Size:	237.1 KB 
ID:	16160Click image for larger version. 

Name:	Vendor Pay VBA Code.JPG 
Views:	5 
Size:	152.3 KB 
ID:	16161

Posting Permissions

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