Results 1 to 12 of 12
  1. #1
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8

    Look up last status using ID and use as default selection on form without filtering

    I'm pretty new to Access and trying to learn on the fly here. Any help from the experts here would be very much appreciated.

    I'm working on trying to auto-populate some parts of a form so that only need to change a handful of fields for their daily updates.

    The database table called Master_Issue_List that consists of Issue_ID, Date_Added, Status, and Status_Index. There are other fields too, but not relevant for this question.

    The form that updates that table has Issue_ID as a combo box and Status as a combo box. The items in the Status field are going to be the strings 'Green', 'Yellow', and 'Red'. I would like to create the logic so that once an Issue_ID has been selected by the user on the form, the default value for the Status field will be last known Status for that particular Issue_ID using MAX(Date_Added). Also, the Status field should NOT be filtered. 'Green', 'Yellow', and 'Red' should still all be options listed in the combo box drop down, but just that the default selected value will depend on prior records.

    Here's what I have, but I keep getting a "Run-time error '13': Type mismatch" error message. I have this included as the 'After Update' event code for the Issue_ID

    Private Sub Issue_ID_AfterUpdate()


    Dim lastStatusIndex As Integer


    lastStatusIndex = "SELECT Master_Issue_List.Status_Index FROM Master_Issue_List WHERE Master_Issue_List.Date_Added = (SELECT MAX(Master_Issue_List.Date_Added) FROM Master_Issue_List WHERE Master_Issue_List.Issue_ID = '" & Me.Issue_ID & "') "

    Me.Status = Me.Status.ItemData(lastStatusIndex)


    End Sub

    Without any coding, the combo box for Status goes off a fixed Value List of 'Green', 'Yellow', 'Red'. The Status Index just correlates those strings to integers of 0, 1, 2 respectively.

    Is there a better way to do this?

    Issue_ID Date_Added Status Status_Index
    AAA 12/1/19 Red 2
    BBB 12/2/19 Yellow 1
    AAA 12/3/19 Yellow 1




    In the above example, if I select AAA from the Issue_ID drop-down field on the form, the default Status selected should be Yellow, but still show the same Green, Yellow, and Red in the drop-down.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Either:
    put all values needed in the combo box, then when the user selects the ISSUE ID,
    all other boxes can be filled in via the combo.
    NOTE: lists in vb code, columns begin with zero.

    Code:
    sub cboIssueID_afterupdate()
    Code:
      me.Status = cboIssueID.column(1) 'actually column 2
    
    me.StatusIndex = cboIssueID.column(2) 'col 3 end sub
    or

    run an update query based on the
    ISSUE ID, to update all fields needed.
    Code:
    sub cboIssueID_afterupdate()
       docmd.setwarnings false
      docmd.openquery "quUpdFormData"
    
    docmd.setwarnings true
    me.requery end sub

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Or perhaps:

    http://www.theaccessweb.com/forms/frm0012.htm

    in the after update event of the combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8
    But if you don't reference the Date_Added field, how does it know to pull the latest status?

  5. #5
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8
    Any other ideas?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I misread your first post, so disregard my earlier response. Your error is because you can't set that property to SQL. I might open a recordset on this simpler SQL and populate with that.

    lastStatusIndex = "SELECT TOP 1 Status_Index FROM Master_Issue_List WHERE Issue_ID = '" & Me.Issue_ID & "' ORDER BY Date_Added DESC"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    I misread your first post, so disregard my earlier response. Your error is because you can't set that property to SQL. I might open a recordset on this simpler SQL and populate with that.

    lastStatusIndex = "SELECT TOP 1 Status_Index FROM Master_Issue_List WHERE Issue_ID = '" & Me.Issue_ID & "' ORDER BY Date_Added DESC"
    How would you use a recordset? I just tried swapping the SQL out and I still kept getting the mismatch error on the line with the SQL. So I thought maybe it's the variable classification that was incorrect. So I changed it to:

    Dim lastStatusIndexString As String
    Dim lastStatusIndex As Integer

    lastStatusIndexString = "SELECT TOP 1 Status_Index FROM Master_Issue_List WHERE Issue_ID = '" & Me.Issue_ID & "' ORDER BY Date_Added DESC

    lastStatusIndex = CInt(lastStatusIndexString)


    But now I'm getting the same mismatch error on the CInt line.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, like I said you can't do it like that. The string is just a string of text. To get the result from it something like:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT TOP 1 Status_Index FROM  Master_Issue_List WHERE Issue_ID = '" & Me.Issue_ID & "' ORDER BY Date_Added DESC"
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      If Not rs.EOF Then
        Me.Status = rs!Status_Index
      End If
    
      rs.Close
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    No, like I said you can't do it like that. The string is just a string of text. To get the result from it something like:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT TOP 1 Status_Index FROM  Master_Issue_List WHERE Issue_ID = '" & Me.Issue_ID & "' ORDER BY Date_Added DESC"
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      If Not rs.EOF Then
        Me.Status = rs!Status_Index
      End If
    
      rs.Close
      set rs = nothing
      set db = nothing
    What does that If statement do?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Makes sure the recordset found a record. The line inside would error if it didn't.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jlee8384 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2019
    Posts
    8
    Thanks! I think that worked. I can actually add this to a couple other fields too.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! As long as they're from the same record, you can just add them to the SELECT clause.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 09-03-2019, 08:28 AM
  2. Replies: 1
    Last Post: 09-10-2018, 01:26 PM
  3. Filtering based on selection
    By Naded in forum Access
    Replies: 5
    Last Post: 06-15-2018, 09:34 AM
  4. Replies: 8
    Last Post: 02-19-2016, 10:59 AM
  5. Replies: 24
    Last Post: 02-11-2014, 06:41 AM

Tags for this Thread

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