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

    Unanswered: SQL query including forms

    Hi guys,

    Been having a little trouble with getting my SQL query to get data from a form to run a comparison.

    Basically I have an ordering system for a company and each order has to be authorised by a member of management. However who can authorise it is based on the manager's authorisation level (number e.g. currency)

    What I want to do is on the order form have a drop down list which shows the users that can authorise the order, and then whoever is placing the order can select the person that they wish to authorise it.

    The usernames are stored in Usertbl - Username
    The auth level is stored in Usertbl - AuthLevel
    The form I'm working with is [New Order Entry Form] and the total cost of the order is held in Text41 on that form.

    What I currently have is this:

    Code:
    SELECT Usertbl.Username
    FROM Usertbl, [New Order Entry Form]
    WHERE [New Order Entry Form]![Text41]<=Usertbl.AuthLevel;
    Any help would be appreciated,

    David

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does this work?

    SELECT Usertbl.Username
    FROM Usertbl
    WHERE Usertbl.AuthLevel >= Forms![New Order Entry Form]![Text41]
    Paul

  3. #3
    Join Date
    Sep 2010
    Posts
    24
    Perfect mate, knew I was missing something obvious!

    Cheers

  4. #4
    Join Date
    Sep 2010
    Posts
    24
    One other thing that I can't get to work...

    The total value of Text41 updates automatically as extra parts of the order are put in.

    The query and combo box works perfectly the first time the total value box updates but won't change after that.

    Can't figure out how to get the combo box to re-run the query when the value in Text41 changes.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    At the appropriate point (perhaps the after update event of the ill-named Text41):

    Me.ComboName.Requery
    Paul

  6. #6
    Join Date
    Sep 2010
    Posts
    24
    Hmm that doesn't seem to be working.

    I don't get an error or anything, it just doesn't update when the total value goes above the auth value for certain people in the list.

    Any other thoughts?

  7. #7
    Join Date
    Sep 2010
    Posts
    24
    Bump.

    Have tried almost every possible way of using Me.Combo160.Requery (and .Refresh) and nothing works.

    The query and combo box works perfectly the first time the value of the text box changes (from NULL to having a value) but the combo box refuses to update when the text box changes again.

    I don't get any errors at all, it just doesn't work...

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Are you physically changing the value in the textbox? The after update event will only fire in that instance. If it's changing due to the change in some other control, the after update event doesn't fire. Can you post the db?
    Paul

  9. #9
    Join Date
    Sep 2010
    Posts
    24
    Ahh that would be why it doesn't work then!

    The text box references another text box in a sub form that calculates the total value of the order.

    If possible I'd prefer not to send the database, only it contains a few bits of company info etc...

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, you need to find some event that triggers the change in the textbox, perhaps the current event of the form where they're entering details. Or you might try the got focus event of the combo, which would fire when the user entered it.
    Paul

  11. #11
    Join Date
    Sep 2010
    Posts
    24
    Ahh Got Focus sorted it out perfectly.

    Cheers mate!

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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