Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    24

    Unanswered: If function for default value of a combo box

    Hi guys, have a problem that I can't get my head around.

    I currently have a combo box that takes its row source from a query. The query has SQL that decides whos names are displayed based on the value of a text box (cost)...

    Code:
    SELECT Usertbl.Username
    FROM Usertbl
    WHERE Usertbl.AuthLevel>=Forms![New Order Entry Form]!Text41 AND Usertbl.Username<>Forms![New Order Entry Form]!Text64;
    The control source for the combo box is the above query 'AuthBy' and the row source is this:

    Code:
    SELECT AuthBy.Username FROM AuthBy ORDER BY [Username];
    What I need to do is get the combo box to default to a name in the list when another condition on the form is true: Combo136 = "7300 DOM"

    But the other names in the list must be selectable if they are visible and match the cost criteria.

    EDIT: Forgot to mention - Access 2007 / 2010

    Any help would be appreciated!

    Cheers
    Last edited by DavidStone; 07-19-12 at 06:59.

  2. #2
    Join Date
    Sep 2010
    Posts
    24
    Nobody got any thoughts on this?

  3. #3
    Join Date
    Sep 2010
    Posts
    24
    Update:

    I've come to the conclusion that because of the way that the data in the combo box is updated and found, what I want to do isn't possible.

    Instead I've gone down the route of a prompt, rather than a default value. Unfortunately I can't get the last bit of an If function to work.

    I currently have this:
    Code:
    If (Me.Combo136 = " 7300 DOM") And (Me.Combo160 <> "Richard Kiernan") Then
                MsgBox "This order must be authorised by Richard Kiernan.", vbOKOnly, "Order Authorisation"
    Which works correctly. But due the nature of this database, there may be times where it is not possible for Combo160 to contain the value "Richard Kiernan" (i.e. he does not have the required authorisation limit)

    The cost of the order is calculated on the form and I'm trying to use that as another And statement to make sure that the message only appears if he is able to authorise the order.

    Code:
     If (Me.Combo136 = " 7300 DOM") And (Me.Combo160 <> "Richard Kiernan") And (Me.Text41 < "1,000") Then
                MsgBox "This order must be authorised by Richard Kiernan.", vbOKOnly, "Order Authorisation"
    When I add the last section, it stops working. Text41 is given it's value by a subform Control Source "=[New Order Entry subform].Form![Calculated Cost]" and is formatted to Currency.

    With the last section in place, the message box is never seen, so it looks like the last condition can never be met. Can anyone see any reason why this wouldn't be met?

    Cheers!

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Me.Text41 < "1,000"
    You can't do a less than compare on a text string and expect it to act like a number. Try < 1000 instead and see what happens.

    Steve

  5. #5
    Join Date
    Sep 2010
    Posts
    24
    Thanks so much! Had be looking at that for about an hour, shouldn't try and work on a Friday afternoon...

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Glad to help. Friday afternoons are definitely not made for thinking!

    Steve

Posting Permissions

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