Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    35

    Unanswered: Requery and Stuck in continuous loop = can't edit my db app!

    Hello,

    I have a form that is loaded on startup [frmBOISurvey].

    I have a combo field in my form [providerID] that is supposed to query (based on a "where not in" query) our listing of all providers from [tblProvider] and provide a drop down of all the providers that haven't been added to the [tblSurvey].

    I have the form adding the survey results to the [tblSurvey], checking for required fields etc, and the last thing that the Save Survey button is supposed to do is to add the survey results.

    I have two problems:

    1.I was trying to figure out how to get my form to requery so that the providerID drop down would get updated so that it would not show the provider id for the survey results that were just added.
    I tried to add

    Forms!frmBOISurvey.Refresh
    Forms!frmBOISurvey.Repaint
    Forms!frmBOISurvey.Requery

    to Sub for the Save Survey, but that didn't work.

    Question 1 -- How do I the drop down for providerID to requery effectively??

    2. The last thing the Sub SaveSurvey does is to SetFocus on the providerID so I added the following code to the enter event of the providerID, which has made me be stuck in a continuous loop with my MsgBox popping up and I cant close it

    Private Sub providerID_Enter()
    MsgBox "Now in the providerID enter event"
    Me.requery
    End Sub

    Question 2 -- How can I get my form back into design mode and out of this loop??

    Any advise is appreciated.

    Joyce E. Evans

  2. #2
    Join Date
    Nov 2002
    Posts
    35

    Re: Requery and Stuck in continuous loop = can't edit my db app!

    Originally posted by perlgurl
    Hello,

    I have two problems:

    Question 1 -- How do I the drop down for providerID to requery effectively??

    Question 2 -- How can I get my form back into design mode and out of this loop??

    Any advise is appreciated.

    Joyce E. Evans
    Hello,

    Question 1 somewhat solved: will be writing a query to check to see of the record exists before the insert, if so, will warn my user they are tyring to enter a duplicate record. Still have to figure out how to refresh the dropdown on the form. So far the only way I can find is to close the form and reopen it, but given we have over 600 survey results to enter, that does not seem a viable solution.

    Question 2 somewhat solved: I have resorted to going to a backuped version of the db app.

    I still would like to hear from anyone with suggestions. This seems like such a cludge

    Joyce

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: Requery and Stuck in continuous loop = can't edit my db app!

    Originally posted by perlgurl
    Hello,

    Question 1 somewhat solved: will be writing a query to check to see of the record exists before the insert, if so, will warn my user they are tyring to enter a duplicate record. Still have to figure out how to refresh the dropdown on the form. So far the only way I can find is to close the form and reopen it, but given we have over 600 survey results to enter, that does not seem a viable solution.

    Question 2 somewhat solved: I have resorted to going to a backuped version of the db app.

    I still would like to hear from anyone with suggestions. This seems like such a cludge

    Joyce
    Put this code on the "onGotFocus" property of your combobox control

    DoCmd.Requery "write combo control name"

  4. #4
    Join Date
    Nov 2002
    Posts
    35
    tried the DoCmd.Requery "providerID" in the OnGotFocus property and I get a message that says

    cannot find macro DoCmd

    tried putting the DoCmd.Requery "providerID" into the Enter event and that just seems to loop and loop as well. What I would give to make this application web based ....

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Originally posted by perlgurl
    tried the DoCmd.Requery "providerID" in the OnGotFocus property and I get a message that says

    cannot find macro DoCmd

    tried putting the DoCmd.Requery "providerID" into the Enter event and that just seems to loop and loop as well. What I would give to make this application web based ....
    I know whats happening: you wrote the DoCmd.Requery " controlname" directly on the onGotFocus property if so Access thinks that you are calling a Macro Named DoCmd (you can only write macro), what I meant was that you should click on the onGotFocus property of your combo, then click the biuld button ([...]), then select "Code Builder" and write the code in the section that is provided.


    You other option is to build a macro that will do the exact same thing:
    open a new macro
    Name it (say :Mc_ReqProvider)
    on the Action window of your macro select "requery" from the drop dpwn and in the control name write your control name "ProviderID".
    Save the macro
    go back to your form, open in design vewi
    select your control, now when you click on the On Got Focus proprty you get a drop down list of macro's, select the macro you created and your done.

  6. #6
    Join Date
    Nov 2002
    Posts
    35

    Unhappy

    Hello,

    I tried using the DoCmd.Requery "providerID" both ways actually; as a line in the actual property of the provider Id (totally wrong I know but I figured I would try it) and also as lines of a subroutine within the code builder (code below) and still nothing.

    Private Sub providerId_OnGotFocus(Cancel As Integer)
    MsgBox "hello??"
    DoCmd.Requery (providerID)
    End Sub


    I have tried every event that seems to make sense, the onload, the enter, oncurrent, and I never get the hello?? popping up.


    I really need the entire form to refresh based on that click event but repaint, refresh and requery don't seem to fwork either

    Forms!frmBOISurvey.Refresh
    Forms!frmBOISurvey.Repaint
    Forms!frmBOISurvey.Requery

    all palced before the end of the sub that is called based upon the click of the command button for Save Survey.

    Help??

    Joyce

  7. #7
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Originally posted by perlgurl
    Hello,

    I tried using the DoCmd.Requery "providerID" both ways actually; as a line in the actual property of the provider Id (totally wrong I know but I figured I would try it) and also as lines of a subroutine within the code builder (code below) and still nothing.

    Private Sub providerId_OnGotFocus(Cancel As Integer)
    MsgBox "hello??"
    DoCmd.Requery (providerID)
    End Sub


    I have tried every event that seems to make sense, the onload, the enter, oncurrent, and I never get the hello?? popping up.


    I really need the entire form to refresh based on that click event but repaint, refresh and requery don't seem to fwork either

    Forms!frmBOISurvey.Refresh
    Forms!frmBOISurvey.Repaint
    Forms!frmBOISurvey.Requery

    all palced before the end of the sub that is called based upon the click of the command button for Save Survey.

    Help??

    Joyce

    I don't know what do say,
    I don't know if you tried but I think you should put provider Id in cotations : DoCmd.Requery "Provider ID" it won't work with paranthesis. Also try the macro, the macro should work.

  8. #8
    Join Date
    Nov 2002
    Posts
    35
    Hello,

    I tried providerId in both quotes and parens, again neither worked.

    I wrote the macro, and called it on the OnGotFocus Property; that didn't work either.

    I think Access knows I don't like it

    Thanks for all you help anyway.

    Joyce

  9. #9
    Join Date
    Nov 2002
    Posts
    35

    solution - kid of - sort of

    I almost have it now I think, the macro works, not quite like I expected in that once I push the command button to insert the record, the macro get called due to the providerId.SetFocus call as the last line in the sub for the command button which puts the focus into the providerId which calls the macro but the thing that seems weird is that the providerID from the record that was just inserted still shows up as the first option UNTIL I use the mouse to select a new providerID, at which time the listing gets updated. Strange behavior but perhaps normal for Access??

    Oh well.... job security I guess

    ~ Joyce

  10. #10
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: solution - kid of - sort of

    Originally posted by perlgurl
    I almost have it now I think, the macro works, not quite like I expected in that once I push the command button to insert the record, the macro get called due to the providerId.SetFocus call as the last line in the sub for the command button which puts the focus into the providerId which calls the macro but the thing that seems weird is that the providerID from the record that was just inserted still shows up as the first option UNTIL I use the mouse to select a new providerID, at which time the listing gets updated. Strange behavior but perhaps normal for Access??

    Oh well.... job security I guess

    ~ Joyce
    You can set a default value for your combo box.

  11. #11
    Join Date
    Nov 2002
    Posts
    35
    Tried that using
    = ""
    on the defualt and it still doesn't seem to work.

    Perhaps this visual would help --

    As I am clicking my Save Survey button for providerID 10048:
    http://www.perlgurl.net/work/onClick.gif

    Once I say ok to the insert
    http://www.perlgurl.net/work/afterInsert.gif

    Must resist urge to hit my computer !!!

  12. #12
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Originally posted by perlgurl
    Tried that using
    = ""
    on the defualt and it still doesn't seem to work.

    Perhaps this visual would help --

    As I am clicking my Save Survey button for providerID 10048:
    http://www.perlgurl.net/work/onClick.gif

    Once I say ok to the insert
    http://www.perlgurl.net/work/afterInsert.gif

    Must resist urge to hit my computer !!!
    set the default value to Null or like " "

Posting Permissions

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