Results 1 to 8 of 8

Thread: Combo (A2000)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo (A2000)

    I have a form, "frmEstimateDetails" which contains [EstimateNo] & [Supp].
    I wanted to pull info from another table without using a DLookup (Too slow) into two unbound texts on the form.
    "=Combo.Column(0)"
    "=Combo.Column(1)" etc etc

    So I've created a combo with record source as follows:

    SELECT tblInsurerDetails.EstimateNo, tblInsurerDetails.supp, tblInsurerDetails.InsurerCode, tblInsurerDetails.Insurer
    FROM tblInsurerDetails
    WHERE (((tblInsurerDetails.EstimateNo)=[forms]![frmEstimateDetails]![EstimateNo]) AND ((tblInsurerDetails.supp)=[forms]![frmEstimateDetails]![Supp]));

    When the form opens, the combo is blank and I have to manually open it to select the data within it.
    The data is correct as with the SQL above.

    I find this really strange, as I have done this before with success.!!!!

    Any Ideas ?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Combo (A2000)

    Have you tried the On Open event of the form to set the parameter of the combo box such as

    me.Combobox.value = "value"

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo (A2000)

    Gary

    The SQL should really set the value as it is by Forms!frmEst.................. and so on.

    It has no problem on another form I created, but what I can't understand is why it doesn't work on this form.

    On opening, the data is, there but has to be selected from the combo.
    The SQL limits the data to only one record so it should be visble.

    See the attached doc to see what I mean.
    Attached Files Attached Files

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Combo (A2000)

    So what you are saying is you make a selection in the combo box and the resultant data should only show related records. If so, then check your SQL statement that provides the results to ensure you are applying a filter equal to the combobox value. (Or use a Like condition) You may also need to add a refresh or requery to re-generate the data based on the form setup to see the new results.

    HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: Combo (A2000)

    Dave, you could set the value of the combo box to the first (and in this case only) item in the list after you have set the row source:

    Combo.RowSource = "..."
    Combo = Combo.ItemData(0)

    The list index is zero-based, so ItemData(0) is the first item.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo (A2000)

    The following has sorted the issue, (Thanks Gary & Hans), but still couldn't understand why the straight forward SQL works on another form. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    This form is set up more or less the same as the other, no itemdata code to force the row.
    The combo will be hidden in the footer and the two unbounds will pull only the info it needs.




    Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    cmbInsurer.RowSource = "SELECT tblInsurerDetails.EstimateNo, tblInsurerDetails.supp, tblInsurerDetails.InsurerCode, tblInsurerDetails.Insurer FROM tblInsurerDetails WHERE (((tblInsurerDetails.EstimateNo)=[forms]![frmEstimateDetails]![EstimateNo]) AND ((tblInsurerDetails.supp)=[forms]![frmEstimateDetails]![Supp]));"
    cmbInsurer = cmbInsurer.ItemData(0)
    End Sub

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo (A2000)

    Just for info-
    <hr>When the form opens, the combo is blank and I have to manually open it to select the data within it.
    <hr>
    Presumably because the combo is not bound to a field?
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo (A2000)

    Well observed Steve.
    Sometimes it's the easiest things which cause the biggest problems.
    You were exactly right.
    I knew I'd done it before, I just couldn't see why I had a problem this time.

Posting Permissions

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