Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    Memphis, TN
    Posts
    13

    Question Unanswered: Unwanted Prompt during Form Load

    My first post. Hope I'm not out of line.

    I usually do a good search first.. but I wouldn't have a clue how to search for this. I'm fairly new with Access. I see its powers.. but I also and having a hard time trying to figure out how to make it do what I need... hopefully those are due to my ingnorance (most likely) than weaknesses.

    I hope I'm not out of line with this, but I hope I can get some good suggestions:

    ******************************************

    I've got THIS SQL as my RECORD SOURCE for my subform.

    SELECT *
    FROM Y_CLIENT_UNITS_ENTRY AS a
    WHERE (((a.CLIENT_ID)=[Forms]![frmClient]![txtFormClientID]) AND ((a.START_DATE)=[Forms]![frmClient]![txtISPEffectiveDate]) AND ((a.DISCIPLINE)=[cmbUNITSdiscipline]));


    cmbUNITSdiscipline is a combo box on the subform. When the form first loads it is EMPTY.. so the query should not find anything.. but it prompts me to enter a value.. :-( I don't want it to do that.. I want it to just display the screen and let me choose something from the combo box.. THEN have it display the record. Any thoughts?

    If I don't use a.DISCIPLINE=[cmbUNITSdiscipline] and use just a.DISCIPLINE='PT' then it will find and display that record.

    Thanks for any and all suggestions.

    MemphisBill

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    it asks you not because the variable is empty or null, but because it cannot find the variable (it is not defined) and that's why MS Access assumes that it is a paramater....

    it looks that the problem is in AND ((a.DISCIPLINE)=[cmbUNITSdiscipline]));

    replace it with AND ((a.DISCIPLINE)=[Forms]![frmClient]![cmbUNITSdiscipline]));


    jiri

  3. #3
    Join Date
    Jun 2003
    Location
    Memphis, TN
    Posts
    13
    thanks for the suggestion jiri,

    But the combo box is on a SUBFORM named 'sfrmUNITSpt' - but even using your suggestion and putting in THIS code:

    ((a. DISCIPLINE)=[Forms]![sfrmUNITSpt]![cmbUNITSdiscipline]));

    still yielded a prompt to enter a parm value for:
    Forms!sfrmUNITSpt!cmbUNITSdiscipline

    Oh well.. let me keep at it!

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    on the open event of the form set the combobox value a default value and then requery the form

    if the combobox is the prime criteria for the data being displayed I imagine you are requering the form based on the afterupdate of the combo box any way

    so just set a default value to the box

  5. #5
    Join Date
    Jun 2003
    Location
    Memphis, TN
    Posts
    13
    Ok, I'm gonna revive this thread because this problem still hounds me. Once I figure out why its happening I'll be able to deal with it in the future.

    The SAME problem is arising.. just a little different..

    I have a SUBform (sfrmUNITS) that has a text field. This text field has a CONTROL SOURCE of a 'key' field on the table which happens to be the RECORD SOURCE for the subform. This 'key' value is numeric. During testing I can SEE the value displayed in this text field. For the sake of our discussion here the field name is txtUNITSENTRYkey on the sfrmUNITS subform.

    Now.. there is a SECOND subform (named sfrmUNITSmonthly) which I placed on sfrmUNITS and this subform has a record source as follows:

    SELECT *
    FROM CLIENT_UNITS
    WHERE (((CLIENT_UNITS.KEY)=[sfrmUNITS]![txtUNITSENTRYkey]));

    You'll see I'm trying to retrieve the rows where the KEY column (in the WHERE clause) are equal to the txtUNITSENTRYkey value on the sfrmUNITS subform. HOWEVER, everytime I open the BASE FORM - frmClients - (which sfrmUNITS and consequently sfrmUNITSmonthly are placed on... I get prompted to enter the PARM value for sfrmUNITS!txtUNITSENTRYkey.

    I assume I'm being asked that because when sfrmUNITSmonthly tries to load there is no value in sfrmUNITS!txtUNITSENTRYkey yet. Maybe the solution is to somehow force a requery of the record source on the sfrmUNITSmonthly form after the sfrmUNITS subform is loaded?

    Any help with this is truely appreciated.. I'm not a pro and its driving me nuts when I spin my wheels..

    EDIT: I've reread this and previewed it several times to see if I was making myself clear.. unfortunately it IS clear.. but at the same time is still confusing as hell. Sorry.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my original post was possibly a brilliant solution but certainly not for your question. unforgiveably irrelevant: deleted!

    moving on...

    have you tried setting the subform.sourceobject in design view to a stupid form containing nothing except a caption "select something from the combo", and then in the afterupdate of the combo .sourceobject = the realform.

    i enjoy .sourceobject and .recordsource ...so much sweeter than that wretched gatesware tab control. most of my apps now have only one "home" form with a subform or few that switch(es) to suit wherever the user wanders.

    izy
    Last edited by izyrider; 07-04-03 at 14:03.

Posting Permissions

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