Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66

    Populate field from combobox choice

    Hello All,



    I have a DB that contains a form (based on a table) where the user raises a Non-Conformance.

    Part of the procedure for completing the form is to select a Category (Critical, Major or Minor) from a combobox named cboCategory.

    There is also a field named Due Completion Date which is formatted as a General Date.

    What I would like to happen is that when the user selects Critical from the combobox, the Due Completion Date is populated with today’s date plus 24 hours or 1 day, selects Major and the Due Completion Date is populated with today’s date plus 72 hours or 3 days, selects Minor and the Due Completion Date is populated with today’s date plus 168hours or 7 days.

    I have tried using IIF statement, but my VBA knowledge is letting me down.

    Can this be achieved and does anyone know of the best way to achieve this?

    Many thanks

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    In the AfterUpdate Event for your ComboBox you would enter this code

    Code:
    If me.ComboBox = "Critical" then
    me.DueCompletionDate = Date +1
    elseif me.ComboBox = "Major" then
    me.DueCompletionDate = Date + 3
    Else me.DueCompletionDate = Date +7
    End If

  3. #3
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi alansidman,

    That works great.

    Even though my VBA was something like yours ('ish) I was using it in the 'On Change' property field.

  4. #4
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi alansidman,

    This worked great when I first started using it, now though, regardless of whatever i choose from the combobox the DueCompletionDate field always populates with a date in 7 days time.

    Just to confirm, here is the code that I am using;

    Private Sub cboCategory_AfterUpdate()
    If Me.cboCategory = "Critical" Then
    Me.txtDueCompletionDate = Date + 1
    ElseIf Me.cboCategory = "Major" Then
    Me.txtDueCompletionDate = Date + 3
    Else: Me.txtDueCompletionDate = Date + 7
    End If
    End Sub

    Have you any ideas as to how to solve this?

    Regards

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Are you sure you need a code for this?
    Attached Files Attached Files

  6. #6
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Put a Break Point at first line "If Me.cboCategory = "Critical" Then", then step through "F8" and mouse over the Me.cboCategory to see what the value is. It appears to be skipping the first 2 conditions and hitting last else to do the +7 line.

    Just noticed in the code you posted there is a colon after the last Else?

  7. #7
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Quote Originally Posted by Bulzie View Post
    Just noticed in the code you posted there is a colon after the last Else?
    The colon enters as soon as I press the space-bar to type in the next bit of code. I have also tried it by not pressing the space-bar and the same still happens.

  8. #8
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi ArviLaannemets,

    Thanks for your reply.

    I tried you example which works fine however I need to save the category in the 'tblTest' table.

    Originally I had code to populate a field 'OnChange'.

  9. #9
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Quote Originally Posted by Bulzie View Post
    step through "F8"
    . Nothing happens when I press F8.

  10. #10
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you put the break point (click on left side of code line in grey area) and it will show a red circle. You probably did that but just checking. Then open your Form(s) like normal, and try the combo boxes, when it gets to that breakpoint in the code it will stop and show the code window, from there use F8 to step through.

  11. #11
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    When pressing F8 the following is highlighted.

    Private Sub cboCategory_AfterUpdate()
    If Me.cboCategory = "Critical" Then
    Me.txtDueCompletionDate = Date + 1
    ElseIf Me.cboCategory = "Major" Then
    Me.txtDueCompletionDate = Date + 3
    Else: Me.txtDueCompletionDate = Date + 7
    End If
    End Sub

    Else and : Me.txtDueCompletionDate = Date + 7 are highlighted separately after pressing F8.

    I’m still non the wiser so any help/guidance would be appreciated.

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by mick3911 View Post
    When pressing F8 the following is highlighted.

    Private Sub cboCategory_AfterUpdate()
    If Me.cboCategory = "Critical" Then
    Me.txtDueCompletionDate = Date + 1
    ElseIf Me.cboCategory = "Major" Then
    Me.txtDueCompletionDate = Date + 3
    Else: Me.txtDueCompletionDate = Date + 7
    End If
    End Sub

    Else and : Me.txtDueCompletionDate = Date + 7 are highlighted separately after pressing F8.

    I’m still non the wiser so any help/guidance would be appreciated.
    I've tested this code and it works as required when used in the "After Update" event of the combo box IF the combo's "Row Source Type" property is set to "Value List".

    What setting do you have for the "Row Source Type" and the "Row Source" properties.
    Last edited by Bob Fitz; 02-12-2020 at 10:13 AM. Reason: Additional info
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi Bob Fitz,

    Many thanks for coming to my aid on this.

    My current setting for Row Source Type is Table/Query.

    My current setting for Row Source is SELECT [tblCategory1].[CategoryID], [tblCategory1].[Category] FROM tblCategory1 ORDER BY [Category];

    The Category1 table being where Critical, Major and Minor is listed.

    I have changed the Row Source Type to Value List.

    How do I amend the Row Source?

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Make your Row Source and Row Source Type settings as they were.

    In the code you were given, change the 2 references of Me.cboCategory to
    Me.cboCategory.Column(1)

  15. #15
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66
    Hi Bob,

    That has done the trick.

    Thank you for your input, much appreciated.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  2. Replies: 3
    Last Post: 08-07-2016, 09:14 AM
  3. Replies: 1
    Last Post: 11-14-2014, 07:59 AM
  4. populate a field from multiple source fields using a combobox
    By tranquillity in forum Database Design
    Replies: 8
    Last Post: 09-13-2013, 06:46 PM
  5. Populate a field based on combobox selection
    By rscott7706 in forum Access
    Replies: 5
    Last Post: 06-02-2011, 03:18 PM

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