Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    17

    Unanswered: Form values used in a Row Source Query?

    Hello, I wanted to know if this was possible as i have tried a few things and cant seem to get it to work.

    I have a combo box that uses a SELECT statement in the row source field in order to populate its options, I would like to limit the options available based on a value from another combo box on the form.

    here is an example of how i've been trying to accomplish this:
    Code:
    SELECT items FROM itemTable WHERE items <> '!Forms.frmMyForm.myComboBox.Value'
    Am i on the right track? Or is this method not feasible?

    Thanks for any help.
    James

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What you are saying is where <> !Forms.frmMyForm.myComboBox.Value

    So you need to change so that you READ the value of the mycombobox

    SQL = ""
    SQL = "SELECT items FROM itemTable WHERE items <> '" & !Forms.frmMyForm.myComboBox.Value & "'"
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2007
    Posts
    17

    Thanks however...

    I did try using that method and it did not work, if i hard code a number into it, it behaves like it should. That is why i posted because i started thinking that the combo box value is perhaps not available from the row source field.

    Don't know if i'm even making sense

    Any other ideas?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Remember for number comparisions, use " & Forms!MyForm!MyField & " " <- space put between " " to show character.
    For string comparisons, use ' " & Forms!MyForm!MyField & " ' " <- spaces put between " ' " to show characters.
    For date comparisons, use # " & Forms!MyForm!MyField & " # " <- spaces put between " # " to show characters.

    Also notice the syntax of Forms!MyForm!MyField verses !Forms.frmMyForm.myComboBox.Value
    Last edited by pkstormy; 10-23-07 at 18:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Posts
    17
    Thanks for the great tips pkstormy. I am working with strings so i tried using your second example as a way to input it into the record source field. For some reason it did not work either. I played with several variations of what you had put in your post (removing spaces, adding .Value to the Form Field i am trying to compare with) but could not get the result i was after

    here is the complete query i am placing into the record source field:

    SELECT tblInFLitemNumberSet.itemNumber
    FROM tblInFLitemNumberSet
    LEFT OUTER JOIN tblInItemUom
    ON SUBSTRING(tblInItemUom.UPCcode,9,3) = tblInFLitemNumberSet.itemNumber
    AND SUBSTRING(tblInItemUom.UPCcode,7,2) = ' " & Forms!frmInFLassignUPC!txtPlineCode & " '
    WHERE SUBSTRING(tblInItemUom.UPCcode,9,3) IS NULL

    ORDER BY tblInFLitemNumberSet.itemNumber

    But if i do this it works like it should:

    SELECT tblInFLitemNumberSet.itemNumber
    FROM tblInFLitemNumberSet
    LEFT OUTER JOIN tblInItemUom
    ON SUBSTRING(tblInItemUom.UPCcode,9,3) = tblInFLitemNumberSet.itemNumber
    AND SUBSTRING(tblInItemUom.UPCcode,7,2) = '00'
    WHERE SUBSTRING(tblInItemUom.UPCcode,9,3) IS NULL

    ORDER BY tblInFLitemNumberSet.itemNumber


    The Value for my form item is 00 I tried placing a text box on the form and had the combo box populate it with its value after update and tried using the text box for my value instead of the combo box, but i cant see how this could shange anything. Now i am at a loss as to what it could be.

    I'll keep pluggin away hopin to hit paydirt.

    Thanks Again
    James

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    James,

    I'm a little confused on why you are using the SUBSTRING function. If you're working with strings and you want to construct a select statement, your select statement should look something like....
    Select * from MyTableName where MyFieldName like ' " & SomeVariable & " ' "
    or
    Select * from MyTableName where MyFieldName like " " " & SomeVariable & " " " "
    or
    Select * from MyTableName where MyFieldName like "Paul*"

    There are different variations and perhaps you could upload a sample where it might be easier to see it and make a few suggestions, uploading back something which will work for you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2007
    Posts
    17
    Hi John

    I am using the substring because the column UPCcode contains a 12 character code for each item and i needed to compare certain parts of the string.

    what i am trying to do is build a form that will assign a new upc code to items based on selections from 3 combo boxes. I was hoping by using this method i could limit the selections in the last combo box and not allow for the selection of already used number segments.

    based on the UPC-A specification only 5 digits are actually usable for assignment (digits 7 thru 11 with 12 being a check digit) so with the 5 usuable digits at my disposal i broke that down into 2 segments the first 2 digits will represent a product Line and the last 3 used to assign an item number within that product line.

    With that i was hoping that when the end user selected say 00 (the first product line) the next combo box would only show them the 3 digit combos that are not already in use, eliminating the opportunity for someone to pick a number combination in use and subsequently having to pop a MsgBox with something like "This Item Number is Already In Use please select another item number"

    Perhaps i am using the completely wrong approach, but being new to alot of this i often find my self hacking my way to success without a real understanding of the correct processes.

    Up until this issue i must have been lucky because everything else on the form is working great. I would upload a sample but i am just making small additions to a giant ADP on our test server at work trying to learn new things.

    James

  8. #8
    Join Date
    Nov 2007
    Posts
    1
    Hi James,

    This has been frustrating me too (hence I found this post!) - I am much the same as you in that I seem to hack my way round.

    Anyway after lots of hacking and swearing - I eventually found that it seemed to work with no quotes or & at all (for a string comparison) i.e. using you example:

    SELECT tblInFLitemNumberSet.itemNumber
    FROM tblInFLitemNumberSet
    LEFT OUTER JOIN tblInItemUom
    ON SUBSTRING(tblInItemUom.UPCcode,9,3) = tblInFLitemNumberSet.itemNumber
    AND SUBSTRING(tblInItemUom.UPCcode,7,2) = Forms!frmInFLassignUPC!txtPlineCode
    WHERE SUBSTRING(tblInItemUom.UPCcode,9,3) IS NULL

    In my case, as I was basing the query on another combo box, I had to add an On Enter Event with a Me.Refresh

    Hope this helps!

  9. #9
    Join Date
    Jan 2007
    Posts
    17
    Thanks, thats good to know for next time. I ended up playing with it seemingly forever cause none of the & ' " "' $ combinations were working for me so i eventually just tried to think about the problem differently. what i did was put a trigger on the table containing the upc, which on update populated 2 new varchar columns with the 2 and 3 digit strings i needed and put this into the vb

    Me!cboItemNumber.RowSource = " SELECT dbo.tblInFLitemNumberSet.itemNumber From dbo.tblInFLitemNumberSet LEFT OUTER JOIN dbo.tblInItemUom ON dbo.tblInItemUom.itemNumber = dbo.tblInFLitemNumberSet.itemNumber AND pLinecode = '" & Me!cboPlineCode & "' WHERE tblInItemUom.itemNumber Is Null ORDER BY dbo.tblInFLitemNumberSet.itemNumber "

    Worked like a charm, but now i am thinking i have added extra storage and processing needs do the db that are not necessary, because my SUBSTRING calls in the first script were fine and my problems were with the way i was trying to populate the combo in the first place. I will change it up again and remove those 2 extra columns/trigger on the table when i get back to messing around with it.

    Thanks for all the info Mara1 myle, and pkstormy

    J

Posting Permissions

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