Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Lookup Question (2000)

    Hi,
    I had a Purchase Request Form where I had a button that the user would click to create a New PR. Because I'm working with very green users, I decided I should create 2 forms, one for viewing data, where the data is locked and one for adding new data. No problem there, except on how I'm going to make a lookup combo box for the viewing data form. Let me explain more. On the original form, I had 2 synchronized combo boxes where the user would pick the JON, then the Vendor that have orders for that JON and Parts (in a subform) would appear matching that JON and Vendor. I've left that alone for the creating New PR form. Works great.
    Well with the view data form, I'm not sure how I can have them pick the machine and vendor. I'd like to have 1 combo box that lists the JON and Vendor for a particular order, but a little confused as to how to make that happen. Any thoughts?
    Thanks,
    Deb

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

    Re: Combo Lookup Question (2000)

    Deb

    Your form can actually only use a combo as a selector.
    ie:

    The combo used for selection can be on the form and then unbound text controls to view the data.
    Each unbound text can have it's control source set to :- e.g:

    = Combo1.Column(0)
    = Combo1.Column(1)
    = Combo1.Column(2)
    = Combo1.Column(3)
    = Combo1.Column(4)
    (Combo1 is an example Combo name)
    etc

    So when a selection is made from the combo, the unbound texts will show data relevant to the column.
    You can lock each unbound text so the user doesn't mess with any thing.

    Or alternatively one of the combo wizards may also solve your issue.
    Select the one which states "Find a record on my form ...................."

    You could even only use a single form and protect each field via VBA, e.g a double click event.

    <pre>Private Sub MyField_DblClick(Cancel As Integer)
    Me.MyField.Locked = False
    End Sub</pre>


    And then lock them Again after_Update:-

    <pre>Private Sub MyField_AfterUpdate()
    Me.MyField.Locked = True
    End Sub</pre>


    Of course you'd have to set each field to locked in the first place.

    I'm sure there's slicker ways, but hope this gives you idea's

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    Thanks for that input, but making the data unchangeable is not my problem. It's how do I create a lookup combo box that the user can select from to view the data so they don't have to scroll through each record with the navigation buttons. I want to show the JON and Vendor, which is actually an order, in the combo box. I created a query with that data and based the combo box on that query and it gives me the correct choices as a drop down list but I can't select anything from the list, like it's locked.
    Thanks,
    Deb

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    Deb,

    See if you have the option 'Allow Edit' set to 'NO' on your form properties.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    Yes, I did and that fixed the problem of being able to actually select something. Now my problem is, my combo box is not tied into my parts subform. The combo box created the following code:
    Private Sub cboDPLVendorExist_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[strMaterialJON] = '" & Me![cboDPLVendorExist] & "'"
    Me.Bookmark = rs.Bookmark
    End Sub

    Can I modify the above code so that it looks at the strMaterialJON and the txtVendorName that are both listed in the combo box? The subform updates properly when you use the navigation buttons.

    Thanks,
    Deb

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    Deb,

    What I usually do is hide unbound text boxes on a form and place the data from my comboboxes into then when the user clicks on the combobox and makes a selection. I then requery the subform where the source for the subform is using a query that uses my hidden textboxes as criteria.

    I have to run but if no one responds with help while I'm gone I'll post the code when I get back.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Combo Lookup Question (2000)

    If Vendor is the second column in the combo box, try this:
    <pre>...
    rs.FindFirst "[strMaterialJON] = '" & Me![cboDPLVendorExist] & _
    "' AND [txtVendorName] = '" & Me![cboDPLVendorExist].Column(1) & "'"
    ...
    </pre>

    Columns in a combo box or list box are numbered starting at 0, so Column(1) is the second column. Use Column(2) if you need the third column, etc.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    That worked Great Hans. I have since added 2 more columns and am getting an error, which I think is just syntax, but I can't figure out what is wrong. I'm getting a datatype mismatch error when I try and used the combo box and when I choose debug it's pointing to the date line. Here's my code:
    Private Sub cboDPLVendorExist_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone

    rs.FindFirst "[strMaterialJON] = '" & Me![cboDPLVendorExist] & _
    "' AND [txtVendorName] = '" & Me![cboDPLVendorExist].Column(1) & _
    "' AND [txtPurchaseRequestNo] = '" & Me![cboDPLVendorExist].Column(2) & _
    "' AND [dtmDatePartOrdered] = '" & Me![cboDPLVendorExist].Column(3) & "'"
    Me.Bookmark = rs.Bookmark
    End Sub

    Any thoughts?
    Thanks,
    Deb

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

    Re: Combo Lookup Question (2000)

    The code I posted before was valid for text fields, not for date fields. The role of quotes for text fields is played by # for date fields. Replace

    <code>"' AND [dtmDatePartOrdered] = '" & Me![cboDPLVendorExist].Column(3) & "'"</code>

    by

    <code>"' AND [dtmDatePartOrdered] = #" & Me![cboDPLVendorExist].Column(3) & "#"</code>

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup Question (2000)

    Hans as always "You da Man!"
    Thanks
    Deb

Posting Permissions

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