Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Using a Query to specify records in a Form

    Hi Everyone,

    I am currently using a query criteria to prompt users to enter a name in a message box to view records in a form (i.e. As the form opens it pulls the query and prompts the user to enter a name. Based on that name the records are pulled - essentially a Like "Name" criteria). This works fine with the exception of a user entering a name that is not pulled by the query. How can I prompt an error message when the user types in the an invalid entry (ex. Suppossed to type "ABC" but instead types "XYZ")? Even better yet, is there a way to use a drop down box and eliminate all human error?n Any help would be greatly appreciated. Thanks in advance for your help!

    Will

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes you can use the combo.

    make the combo
    in the query design grid, click once in the criteria box and then on the builder magic-wand thing in the main menu. in the left pane, navigate forms/allforms/yourForm and in the right pane, double-click the combo name.

    NOTE that the criteria will be the bound column of the combo (which is not necessarily the displayed column).

    izy

  3. #3
    Join Date
    Aug 2003
    Posts
    39
    Thanks for your help; however, when I follow your instructions there is no combo name function to select. Also I was hoping to have a popup windo appear before the form actually opens which is the reason that I was using the criteria in the query and not in the form originally. Any other insight you could provide would be most helpful.

    Thanks!!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oooops!

    there are three panes on the builder. the combo name should show up on the middle pane and not the right pane as i stupidly said earlier - i have't found a use for the right pane so my mind just doesn't see it. sorry about that!


    for the rest: what's the real problem that you are trying to fix?

    are you just trying to avoid the problems that occur because the combo has no value when the form originally opens? there ought to be a smart way to fix this, but i only know three stupid ways:

    first and most stupid fix is to set a literal default value for the combo... this is fine until you delete the record holding the default value

    second not-so-stupid fix is to set the default value to
    =DFirst("[theBoundColumnName]","theTableName")

    third stupid fix takes more effort: have a mother form with your combo and a form showing you company logo as subform of the mother. only in the afterupdate of the combo do you switch .sourceobject of the subform to your real form.

    izy

  5. #5
    Join Date
    Aug 2003
    Posts
    39
    I think I know where you are going with this..and here is the direction that I've been heading in the meantime.

    I created a form with only a drop down box (frmOwner) and prompted it to open when the user opens the main form I want to use (frmAccounts).

    Using a drop down box the user selects the Owner form frmOwner.

    frmAccount is based off of a query (qryAccountInformation) with the owner filed set to [Forms]![frmOwner]![OwnerName] so frmAccount will display the appropriate information associated with the owner.

    This all works except for one flaw so far. The Enter Parameter Value box still appears before my frmOwner form pops up. Any idea how to hide this box so the following occurs:

    User opens frmAccount
    frmOwner opens
    User selects a name from the dropdown box
    frmAccount populates data
    The user is happy!

    Love to hear your ideas!

    Will

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    please try the third stupid solution

    third stupid fix takes more effort: have a mother form with your combo and a form showing you company logo as subform of the mother. only in the afterupdate of the combo do you switch .sourceobject of the subform to your real form
    mother=frmOwner

    subForm placed on mother in design view is empty except for a caption "select something from the combo!" (or you logo, or nothing)

    in .afterupdate of your combo
    me!theSubformName.sourceobject = frmAccount

    use properties/other/name in mother to work out theSubformName

    izy

  7. #7
    Join Date
    Aug 2003
    Posts
    39
    Works beautifully. Not the way I had intended but it does the job and the changing logo will be a nice touch too.

    Next question though (and I may post this as another thread):

    Say I want to use more than one combo box. I am having a difficult time defining AND/OR functions. For exapmple: one owner may have many accounts. i want to specify the owner and show all acounts, then narrow down by another criteria, and so on and so forth; however I also want the user to be able to leave a given field blank if they so choose. Right now a blank combo box is finding null values and no matches are appearing because it is based off the AND function (Owner AND null account). But if I use the OR function it doesnt drill far enough down (owner - all owner accounts OR blank accounts - so all the accounts still appear).

    This seems like it should be really easy but it just isn't working out for me.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there was a discussion of cascading combos on this site in the past week.

    depending on the results you want, there may also be another approach:

    mother has some visible text boxes, a goFindIt button, and a clearCriteria button. for each visible text box on mother there is also a hidden text box.

    clearCriteria nulls all the visible text boxes

    goFindIt examines each visible text box and sets it's hidden partner
    if isnull(visibleBox.value) then
    hiddenBox.value = "*"
    else
    hiddenBox.value = "*" & visibleBox.value & "*"
    endif
    ...and then .sourceobject

    the criteria of the query are all
    Like hiddenBox.value

    all criteria are ANDed.

    ...a user entry of "sT" finds "Start" "Istanbul" "Last" but not "sat", which may or may not be what you want. a combination of this substring searching plus combos (..cascading combos) might also be useful.

    izy

Posting Permissions

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