Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Value Transfer (Access 97)

    Help, I am relatively new in application setup in Access so would like to stay with macro driven commands if possible.
    I have a list box on a form that lists 4 options which when picked, populate a Product Field. Based on the selection, I want a drop-down (combo box) list to come up with only the selections for that selected option. The drop down box has symptom codes associated with each product type. I have set up a filter query but the whole thing just doesn't seem to work right.

    What I want is the filter query to read the product parameter from the list box selection. Give me only the values that pertain to that selection and then, (and this is where I'm failing miserably) - when the user chooses the symptom selection from the drop-down - the filter should be removed for the next record entry. What is happening is that I seem to be able to get the filter to work according to the Product selection but the next record doesn't have the filter cleared and if I put another Product selection in, the wrong drop down list appears, i.e. - it doesn't clear from the previous record. Or, if someone wants to change the Product selection on the same record, they need to get the new drop-down list based on that Product.

    Can somebody help with a macro dialog. It just doesn't seem that hard, but I have been struggling with it.
    Thanks!
    Christy

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

    Re: Value Transfer (Access 97)

    Can you tell us how you filter the combo box (drip down list) now?

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value Transfer (Access 97)

    Yes, I have the filter saved as a query with the Code, Family and Reason for Call fields included.

    Under the Family field criteria (which Family = Product) - I have [Forms]!<troubleshootFm1>![Product] which on the first record I do works perfect. It only brings in the the Product Codes corresponding to the correct entry in the Product Field.

    Does that help - would a repaint of the field help?
    Christy

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

    Re: Value Transfer (Access 97)

    I don't know how to do this with a macro, but the VBA code needed is extremely simple. You need it in two places.

    1. In the On Current event of the form.
    <UL><LI>Open the form in design view.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the On Current event.
    <LI>Select [Event Procedure] from the dropdown list.
    <LI>Click the builder button ... to the right of the dropdown arrow.
    <LI>You will see something like this:

    Private Sub Form_Current()

    End Sub

    <LI>The insertion point is in the blank line between Sub and End Sub. Press the Tab key, then type

    Me.ComboBoxName.Requery

    where ComboBoxName is the name of the combo box.
    <LI>Switch back to Access (Alt+F11)[/list]2. In the On Click event of the list box:
    <UL><LI>Open the form in design view.
    <LI>Select the list box.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the On Click event.
    <LI>Select [Event Procedure] from the dropdown list.
    <LI>Click the builder button ... to the right of the dropdown arrow.
    <LI>You will see something like this:

    Private Sub ListBox1_Click()

    End Sub

    <LI>The insertion point is in the blank line between Sub and End Sub. Press the Tab key, then type

    Me.ComboBoxName.Requery

    where ComboBoxName is the name of the combo box.
    <LI>Switch back to Access (Alt+F11)[/list]Now, close the form and save the design.

  5. #5
    New Lounger
    Join Date
    Mar 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value Transfer (Access 97)

    Hans, Thanks a lot ...I think I will try this on Monday. I am seriously brain-dead from trying all different things today to get it to work.
    I'll let you know how it goes. Have a great weekend.

    Christy

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Value Transfer (Access 97)

    Hans, tried this first thing this morning and it worked like a charm. Thank you, Thank you! Now, I'm still going to try and figure out the logic....I would love to figure out the programming side of Access...but I don't get to work with it enough. Is there any type of book or training you could recommend to give me a jump start on programming fundamentals. It just seems so confusing when you start.
    Christy

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

    Re: Value Transfer (Access 97)

    Here are some Access VBA books for beginners:

    Microsoft Access 2002 Visual Basic for Applications Step by Step
    Beginning Access 2002 VBA (Programmer to Programmer)

    You can do a search in this forum for VBA books beginners to find other threads about this subject.

Posting Permissions

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