Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox Default Value (2002)

    I have a ComboBox, located on a subform, that I want to default to the value of the previous records ComboBox selection. I have a query, which I know works, that returns the correct sequence number I need. I pasted the query below. I tried entering an expression as follows for the Default Value property = [qryDfltJob]![AreaTypeSqnc]. It doesn't doesn't populate the ComboBox or generate an error. Next I tried adding the code below to the On Current event for the subform. When I run the form I get "Run-time error '424' object required. What am I not understanding or doing wrong? Any help would be greatly appreciated.

    Private Sub Form_Current()

    'Only if new record
    If Me.cbxAreaType.Value = 0 Then

    'Me.cbxAreaType.DefaultValue = Queries!qryDfltJob!AreaTypeSqnc
    Forms!frmPrprtyJobs!cbxAreaType.DefaultValue = Queries!qryDfltJob!AreaTypeSqnc

    End If

    End Sub

    Query that returns appropriate sequence number
    SELECT tblPrprtyJobs.AreaTypeSqnc, tblAreaTypes.AreaTypeDscrptn, tblPrprtyJobs.PrprtyJobsSqncID, tblPrprtyJobs.PrprtySqnc
    FROM tblAreaTypes INNER JOIN tblPrprtyJobs ON tblAreaTypes.AreaTypeSqncID = tblPrprtyJobs.AreaTypeSqnc
    WHERE (((tblPrprtyJobs.PrprtyJobsSqncID)=(SELECT MAX(tpj.JobsSqncID) - 1 FROM tblPrprtyJobs tpj WHERE tpj.PrprtySqnc = Forms!frmPrprtyJobs!PrprtySqnc)) AND ((tblPrprtyJobs.PrprtySqnc)=[Forms]![frmPrprtyJobs]![PrprtySqnc]));

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ComboBox Default Value (2002)

    You CANNOT reference a field in a query like that! You can do a DLookup or DMax on the query or you can open a recordset on the query, but you can't do what you're trying to do. The simplest way to set the default is in the AfterUpdate event of the combobox. Just set its default property to the selected value. Then when you start a new record, that default will be used.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Does that mean I need to use DLookup or DMax on the query in the AfterUpdate event? Your post said "Just set its default property to the selected value", my question is how do I get the selected value from the query? Sorry if I'm being dumb here, I just think want to make sure I understand this. Thanks for any help in advance.

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

    Re: ComboBox Default Value (2002)

    Charlotte's suggestion will set the default value of the combo box to the most recently selected value; this will usually be the value in the previous record, but not necessarily so:

    Private Sub cbxAreaType_AfterUpdate()
    If Not IsNull(Me.cbxAreaType) Then
    Me.cbxAreaType.DefaultValue = Me.cbxAreaType
    End If
    End Sub

    If you want to fill the combo box for a new record with the value of the previous record, take a look at ACC2000: Fill Record with Data from Previous Record Automatically. You can download a database with a working example from a link in the MSKB article.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Thanks Hans, I'll give this a shot.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Under what circumstances would this not be the value in the previous record?

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

    Re: ComboBox Default Value (2002)

    Say that you have 5 records. You are on the 2nd record, and modify the value of the combo box. This sets the default value. Now you click the New Record button. The default value in the combo box will be that from the 2nd record, not that from the record immediately before the new one (the 5th record).

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Here is the code that I am modeling after Charlott's message. The field FechaTransaccion is an unbound field, formatted to short date. I keep getting Dec 30, 1899 as the date.

    Private Sub FechaTransaccion_AfterUpdate()
    If Not IsNull(Me.FechaTransaccion) Then
    Me.FechaTransaccion.DefaultValue = Me.FechaTransaccion
    End If
    End Sub

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

    Re: ComboBox Default Value (2002)

    Try

    Me.FechaTransaccion.DefaultValue = Chr(34) & Me.FechaTransaccion & Chr(34)

    The DefaultValue property is a string.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    am trying to use this procedure, but I get an error message, "The expression AfterUpdate you entered as the event property setting produced the following error. A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control." I try the procedure in a DB that was developed on this PC, and it functions. When I use the same procedure on a copy of a DB that I brought from another PC, I get the error message.

    What could be happening that causes the error on on the copied DB?

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

    Re: ComboBox Default Value (2002)

    I think we'd have to see the database.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Open the form frmFalla. The field IDContradista has the AfterUpdata procedure.
    A bunch of missing tables and stuff show up, but that was to reduce the size of the db.
    I tried to run the db on Access 2007 on another PC, and got the same problems.
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    I base the frmFalla on a table. Could it be that changing to a query fixes the issue?

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

    Re: ComboBox Default Value (2002)

    I cannot do anything useful with the database, you've removed too many essential elements. Try the following:
    - Create a blank new database.
    - Copy all database objects from the problem database into the new database.
    - See if the problem occurs in the new database.

  15. #15
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Default Value (2002)

    Hans, it looks like changing the record source to a query has fixed it. I had obviously not known about that restriction.

Page 1 of 2 12 LastLast

Posting Permissions

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