Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Altering behaviour of combo box (2k)

    Hi,

    Is it possible to alter the behaviour of a combo box, or rather set an event as below to it?
    I have a database that has live records and archived records. The main form is filtered to show records which have a value of false in a field called 'Archive'. Records that have 'Archive' equalling true are filtered and hidden but still stored on the database.
    From the main form the user can access a form called FrmArchiveReactivate. Here they enter the unique reference number (URN) for the case, and set the archive value to false through a combobox. The URN consists of numbers and letter in various formats.
    What I'd like to do is place a combobox on the main form to give a list of archived cases, the user selects a case to reactivate, the combo box copies the URN from this record, into the relevant field in FrmArchiveReactivate and opens that form. Is this (or something similar) possible?

    Normally I would place the combobox on FrmArchiveReactivate, but the user wants to be able to see whether a record is archived prior to entering a new record (if that makes sense).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Altering behaviour of combo box (2k)

    I would place a command button next to the combo box, and open FrmArchiveReactivate in the On Click event of the command button. (If you would do this in the After Update event of the combo box, the form would be opened as soon as the user selects a case, leaving no room for correction). The code would look like this:
    <code>
    Private Sub cmdReactivate_Click()
    If IsNull(Me.cboArchived) Then
    MsgBox "Please select an archived case", vbExclamation
    Me.cboArchived.SetFocus
    Else
    DoCmd.OpenForm FormName:="FrmArchiveReactivate", _
    WhereCondition:="URN=" & Me.cboReactivate
    End If
    End Sub
    </code>
    In this code, cmdReactivate is the name of the command button and cboArchived the name of the combo box. I have assumed that URN is a number field. If it is a text field, use
    <code>
    WhereCondition:="URN=" & Chr(34) & Me.cboReactivate & Chr(34)</code>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Altering behaviour of combo box (2k)

    Thinking about this, if you have a combobox with the details of all archived cases, presumably I could actually control the archive status from the Main form negating the need for FrmArchiveReactivate, which was previously there to purely do that function.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Altering behaviour of combo box (2k)

    Yes, that would be possible. You'd have to change the Archived status of the selected case, then requery the form to display the un-archived record.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Altering behaviour of combo box (2k)

    Not too sure where I would start with that one, presumably I would store the value from CboReactivate and use this value in CmdReactivate to set the value of Archive relating to that record (from TblMain) to False.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Altering behaviour of combo box (2k)

    Something like
    <code>
    Dim strSQL As String
    strSQL = "UPDATE [NameOfTable] SET Archive=False WHERE URN=" & Me.CboReactivate
    CurrentDb.Execute strSQL, dbFailOnError
    Me.Requery
    ' Optional - move to record that has just been reactivated
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "URN=" & Me.cboReactivate
    Me.Bookmark = rst.Bookmark
    </code>
    Replace NameOfTable with the name of the appropriate table.
    This code requires a reference (in Tools | References...) to the Microsoft DAO 3.6 Object Library.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Altering behaviour of combo box (2k)

    So at the moment I have:

    strSQL = "UPDATE [TblMain] SET Archive=False WHERE URN=" & Chr(34) & Me.CboReactivate & Chr(34) I've added this as URN is a text field, is this correct?
    CurrentDb.Execute strSQL, dbFailOnError
    Me.Requery
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "URN=" & Chr(34) & Me.CboReactivate & Chr(34)
    Me.Bookmark = rst.Bookmark

    This doesn't appear to work, although no errors come up.
    CboReactivate has two columns, first is Defendant (Text), second URN (Text)
    CboReactivate is based on QryArchive which basically draws Defendant, URN and Archive = False from TblMain, so that a list is shown of 'live' files only.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Altering behaviour of combo box (2k)

    The use of Chr(34) is correct.

    > CboReactivate is based on QryArchive which basically draws Defendant, URN and Archive = False from TblMain, so that a list is shown of 'live' files only.

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> But I thought that CboReactivate was intended to display a list of archived cases, not of 'live' cases? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    If URN is the second column of CboReactivate, you should set the Bound Column property of the combo box to 2. Or, make URN the first column and leave Bound Column set to 1.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Altering behaviour of combo box (2k)

    If URN is the second column of combo CboReactivate then you should use :
    strSQL = "UPDATE [TblMain] SET Archive=False WHERE URN=" & Chr(34) & Me.CboReactivate.Column(1) & Chr(34)

    Me.CboReactivate or Me.CboReactivate.Column(0) return the first column, in your case Defendant
    Francois

Posting Permissions

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