Unanswered: Using a Query to specify records in a Form
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!
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).
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.
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
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.
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
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
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.
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 = "*"
hiddenBox.value = "*" & visibleBox.value & "*"
...and then .sourceobject
the criteria of the query are all
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.