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

    Re: ComboBox (saved value) (2002 SP-2)

    I'm sorry, but I'm not sure what you're trying to indicate by this:
    <hr>qfltCustomersActive > qryCustomersMaster > tblCustomers<hr>
    or this
    <hr>This Combo resides in frmOrderNew > qryMaster > tblMaster<hr>
    Here's a quick summary that might help you, but I can't be sure:

    * A combobox can have any table or query as its rowsource. It is ordinarily used to insert a key or value from another table or a values list into the current one. It may be bound (a field as its controlsource) or unbound.

    * The only "relation" between the the combobox and the datasource of the form it's on is the field it is bound to, if any. The underlying tables may be related, but that isn't the issue.

    * You can use code in the AfterUpdate of the combobox to write a particular column of that combobox to another textbox or field, bound or unbound, but only in the current record.

    * Alternatively, you can set the controlsource of a textbox to something like "=[Combobox1].Column(2)" but then you have to make sure it gets requeried when you change the combobox selection. If you use that technique, the value in the textbox will NOT be written anywhere.

    * If you get answers you don't expect, you need to post the details of the method you're using to get them, including the column being referenced, the .code or control source you're using to get the value, etc. A general statement that you're getting bizarre results doesn't provide any more information than just saying it isn't working.
    Charlotte

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox (saved value) (2002 SP-2)

    Could you post a boiled down version in zip format. That might help.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    ComboBox (saved value) (2002 SP-2)

    <P ID="edit" class=small>(Edited by bfxtrfcmgr on 30-Jan-04 20:41. )</P>Is there a twelve-step program for VBA, SQL, and/or Combo boxes?

    I have a ComboCustNo based on the following SQL. The Combo comes from qfltCustomersActive > qryCustomersMaster > tblCustomers, and has a control source of txtFSCBilledPercent (a text box on the same form, but different underlying tables). Here is the SQL statement:

    SELECT qfltCustomersActive.CustomerNoText, qfltCustomersActive.CustomerName, qfltCustomersActive.FSCper
    FROM qfltCustomersActive;

    (This Combo resides in frmOrderNew > qryMaster > tblMaster). The columns are in the order of the SQL with column "0" displayed in the box after selection. What I'm try to do is get the FSCper (a percentage figure) from column "2" to appear in txtFSCBilledPer of the form. I've done something similar to this any number of times when the control source was from the same underlying table, but never from two different tables. There is currently a one to many relationship between tblCustomers and tblMaster (at: CustomerNoText). I tried to get the tblMaster into the mix in the query, but kept getting inner-join issues.
    I've been working on this for quite some time now, but keep coming up with really bizarre results (i.e. txtFSCBilledPer displaying the number of the row selected in the combo, 0%, 100%, 200%, . . .etc.) when a selection is made from the drop-down.

    I would say I'm tearing my hair out, but most of that went within six months of creating this app.

    Edit: Hey everybody! Look at me! I just wrote my very first original code all by myself <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Solution:
    Private Sub ComboCustNo_Change()
    TxtFSCBilledPer.Value = (ComboCustNo.Column(2))
    End Sub
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (saved value) (2002 SP-2)

    Mountains out of mole hills. I beat myself to death for nothing.

    Thanks Charlotte for your kind reply (and some good info). See my edit.
    If there is anything wrong with doing it this way, please let me know. My first code!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (saved value) (2002 SP-2)

    Thanks mate! Please see my edit.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: ComboBox (saved value) (2002 SP-2)

    The code is OK but use the AfterUpdate event rather than the Change event. Change will fire if the user types into the combobox to make a selection, but it fires with every keystroke. The AfterUpdate event happens if the user selects an item from the list by clicking it or by typing in a partial value to get a match, but it only happens once, after the selection is made.
    Charlotte

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (saved value) (2002 SP-2)

    Charlotte:

    Thanks for that bit of advice; I did make that change.
    I still can't believe I actually came up with that all by myself. You've seen some of my posts; I still have a long way to go. But what the heck, I'm climbing up out of the black ooze, my fins are changing to feet and hands. (O.K. Bryan, settle down, you're getting a bit giddy here)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: ComboBox (saved value) (2002 SP-2)

    Bryan,

    I just looked at it again and wondered why you had put parens around (ComboCustNo.Column(2)). The usual method is something like this:

    Me!ComboCustNo.Column(2)

    That immediately lets Access know that you're referring to a control on the current form (Me!ComboCustNo) and is faster than forcing the application to figure out whether you're dealing with a control or a variable of some kind. Parens are used when you want to force an evaluation, which isn't really necessary in this case.
    Charlotte

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (saved value) (2002 SP-2)

    Charlotte:

    I actually started out with something like that, but kept getting errors. I must not have been doing exactly that (VBA is so unforgiving), but something similar. Anything that makes it run faster is good by me, I have made the change and thank your for the tip.
    <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15> . . .Have a good one!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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