Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    65

    Question Unanswered: How to avoid entering the same data twice?

    For most of this afternoon I was trying to figure out a problem that is probably very simple for you guys.

    On a subform I have ProductId, ProductName, etc. I know that having both ProductId and ProductName is a redundancy, however I do need ProductName (there are many reasons). What I want to do is - after selecting the ProductId and move to the next field - ProductName - to have it filled automatically.

    Currently both ProductId and ProductName are comboboxes using a lookup table Products.

    As you can see, I want to avoid entering once the ProductId, and then again searching through hundreds of records to select the corresponding ProductName.

    Any ideas how to do this?

    Thanks,
    Jazz

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Set the second combo box (the one with the productname) to store two values, the first being the productID. then set an onchange event on the first combo box (productID) to setfocus to cmbBox2 and find record =cmbBox1.

  3. #3
    Join Date
    Feb 2003
    Posts
    65
    Originally posted by Teddy
    Set the second combo box (the one with the productname) to store two values, the first being the productID. then set an onchange event on the first combo box (productID) to setfocus to cmbBox2 and find record =cmbBox1.
    Teddy, thanks for your idea, but I do not know how to set focus and then find the record = combo1. Do I have to use sql statement? It would help alot if you give me an example.

    Jazz

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The easiest way to do it would be with a macro using the "gotocontrol" method.

    so a macro for this would be:

    OnChange cmbBox 1:

    echo - no
    hourglass - yes
    gotocontrol - cmbBox2
    findrecord - =[cmbBox1]
    hourglass - no
    echo - yes

  5. #5
    Join Date
    Feb 2003
    Posts
    65
    Originally posted by Teddy
    The easiest way to do it would be with a macro using the "gotocontrol" method.

    so a macro for this would be:

    OnChange cmbBox 1:

    echo - no
    hourglass - yes
    gotocontrol - cmbBox2
    findrecord - =[cmbBox1]
    hourglass - no
    echo - yes
    Teddy, I followed your advice. When I select a ProductId, it does go automatically to the second combo, however it sellects the very first record there (which does not correspond with the ProductId on the first combo).

    In your previous post when you say "set combo 2 to store two fields" what do you mean? Do you mean to format the combo to show two columns (that is what I did), first of which is ProductId and the second ProductName? Both columns are visible now, but combo 2 bounds to column 2 which is the ProductName. Is this correct?

    Jazz

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No... cmbBox 2 should bind to column 1.

  7. #7
    Join Date
    Feb 2003
    Posts
    65
    Originally posted by Teddy
    No... cmbBox 2 should bind to column 1.
    Well, the change you suggest did not make any difference. Combo2 still goes to the first record no matter what is the ProductId, which tells me that for some reason FindRecord does not work the way it should.

    Here are all the parameters for FindRecord in the macro:

    FindWhat - =[ProductId] (this is the name of the field and the cbobox)
    Match - Any part of the field
    Match case - no
    Search as formated - no
    Only current field - yes
    Find first - yes

    I wonder if RunSQL is needed here to filter only one record. And if yes, is the following going string to work:

    strSQL="SELECT Product.ProductId, Product.ProductName FROM Product
    WHERE (Product.ProductId = <here should go a refference to the value of Combo 1, but I do not know how to do it>). I was trying to do this in VBA.


    Jazz

Posting Permissions

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