Results 1 to 10 of 10
  1. #1
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47

    VBA Nested IIf: Textbox to Update Depending on Combobox Selection

    Hi All:

    I am trying to get a textbox ("txtAvailtoSend") on Form ("frmProdtoExport") to update the record to the current date depending on what was selected in the combobox("cboExportStatus").

    • Combobox has 4 options from Value List- 'Yes', 'Yes-Reroute', 'No', and blank line (blank line is default combobox value for each record).


    • Only need date if either Yes or Yes-Remanufactured is selected. If No, or blank, then no date should populate.


    I tried to do this as a before update (and a nested IIf), but it doesn't seem to execute nor does it give errors.



    Code:
    Private Sub txtAvailtoSend_BeforeUpdate(Cancel As Integer)
    txtAvailtoSend = IIf(Me.cboExportStatus= "Yes", Date(), Me.cboExportStatus= "Yes-Reroute", Date(), "")
    End Sub
    Any ideas? As always, open to a better way to do this.

    Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, since the result depends on the combo, the proper place for the code is the after update event of the combo. Where you have it only fires if you manually change data in the textbox. I'd also use an If/Then/Else block, but that's me. Your IIf() isn't properly structured, but I'll let you play with it once you get it firing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Hi Pbaldy:

    Updated code to this:

    Code:
    Private Sub CboExportStatus_AfterUpdate()
    
    If Me.CboExportStatus.AfterUpdate = "Yes" Then txtAvailtoSend.DefaultValue = Now()
    
    ElseIf Me.CboExportStatus.AfterUpdate = "Yes-Reroute" Then
    Me.txtAvailtoSend.DefaultValue = Now()
    
    ElseIf Me.CboExportStatus.AfterUpdate = "No" Then
    Me.txtAvailtoSend.DefaultValue = Null
    
    ElseIf Me.CboExportStatus.AfterUpdate = "" Then
    Me.txtAvailtoSend.DefaultValue = Null
    
    Else: Me.txtAvailtoSend = Null
    End If
    End Sub
    It's yielding a compile error "else without if" but I am not seeing why it is flagging the highlighted line.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What's that ":" after the last else? Not right. I don't know why you are updating the Default Value property of the text box.

    Simplify it to :

    Code:
    If Left(Me.CboExportStatus,3)="Yes" Then
        Me.txtAvailtoSend = Now()
    Else
       Me.txtAvailtoSend = Null
    End If

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The compile error is because you're combining the one-line and block formats, which I wouldn't do.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    aytee111: Thank you, with the modified code, it somewhat works. But the date is not updating in the corresponding rows in the table.

    The ":" was automatically populating in the code. I tried deleting it and rewriting the code many times. It didn't go away until the code was modified:

    Code:
    Private Sub cboExportStatus_AfterUpdate()
    If Left(Me.cboExportStatus, 4) = "Yes" Then
        Me.txtAvailtoSend = Now()
    ElseIf Left(Me.cboExportStatus, 3) = "No" Then
        Me.txtAvailtoSend= ""
    ElseIf Left(Me.cboExportStatus, 12) = "Yes-Reroute" Then
        Me.txtAvailtoSend= Now()
    ElseIf Left(Me.cboExportStatus, 2) = "" Then Me.txtAvailtoSend = ""
    End If
    End Sub
    pbaldy: thanks, researching the formats and slowly learning about them.

    Thank you both again

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your If/else statement doesn't make any sense. The first and the third statements are both exactly the same - if the first three letters are yes then put Now() in the textbox otherwise put Now() in the textbox. Say that to yourself and see if you can find the error! Remove the second one.

    The second and the fourth are also both the same. Why did you change what I gave you? And pbaldy said not to mix one-line and block formats.

  8. #8
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    aytee11:

    When using exactly what you initially posted, every record was updating with the date if the combobox option "yes" was selected. So if "yes" was selected for record #1 and the user went to the next record (#2, #3, etc), the textbox had already updated to the current date for record #1 regardless if any option in the combobox had been selected or not. The same thing happened when the code was modified to remove the third statement and the fourth statement as stated in your latest post.

    The only "logical" thing I could come up with is to modifiy the code to look at 'Yes' (string of 3) versus 'Yes-reroute' (string of 12). Which seemed to work. Then the code was extended to include all 4 combobox options. Once the code was modified to explicitly include the options of whether the 'blank' or 'yes-reroute' were selected, the code executed in the form as requested for each record, i.e.

    • If the user selected combobox option 'yes-reroute' then the current date populated in the textbox for that record.
    • If the user selected combobox option 'blank', then no date populated in the textbox for that record.
    • If the user selected 'yes' then the current date populated in the textbox for that record.
    • If the user selected 'No' then no date populated in the textbox for that record.


    The only issue, after posting the modified code that worked as requested, was the underlying table was not keeping the timestamp date for the txtAvailtoSend

    Please elaborate on what is meant by "exactly the same": if a user selects 'yes-reroute' then the date is now. Else if the user selects 'yes' then the date is now. When this data is exported, the recipients of this data will need to know if the particular record was available to send (option 'yes') or if the particular record was sent as a rerouting (option 'yes-reroute') with the corresponding timestamp for each. It may seem exactly the same, but that selection tells a different story to the interested users in reports built off of this form with those selections. I.e. There were so many records out of the total amount of reviewed records that needed to be rerouted.

    As for pbaldy's statement (thanks pbaldy) I am learning VBA and the structure so when suggestions are made about formatting I have to research it to try to understand it. Currently, looking at this article http://www.databison.com/vba-if-func...n-in-vba-code/ . In the meantime, working to meet project requests (with changing specs and tight deadlines) as requests (demands) are made. If there are sources to reference that would be helpful, I would appreciate it.

    didiomm

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is txtAvailToSend an actual field on the underlying table? If so then it is bound to that field (in Control Source) and when you populate it above it will keep it stored on the table. It sounds as if it is an unbound control from all that you describe.

  10. #10
    didiomm is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Posts
    47
    Thank you, it seems to be working!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2016, 03:17 PM
  2. Replies: 1
    Last Post: 03-01-2016, 06:05 AM
  3. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  4. Replies: 3
    Last Post: 11-05-2014, 02:43 AM
  5. Replies: 6
    Last Post: 07-28-2011, 04:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums