Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Unhappy Unanswered: Couple of Access questions

    Hi all,

    1. To display my query i am using a macro to open with edit mode on. Results have to be displayed in excel type of format which is why i am using query since it opens like datasheet all that is fine however i want the user to be able to sort it anyway he wants but when he does that it prompts him to save the query with yes, no or cancel button.

    Q: How can make that pormpt go away?? And also let him save the sorting as well until he changes it himself??


    2. How do you refresh form on its own??

    Thanks again

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    WHY are you allowing this? With the designer open whomever is editing can COMPLETELY change the query (add/drop columns, add/drop tables, relate tables differently) ... This is just asking for trouble ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You've already got your query, why not use the Form Wizard and make a form based on this query? Make a form using the Wizard, base it on your query and under Layout select Datasheet. It'll still display in an Excel type design, and your user can sort as he likes, and it'll open up the next time with the same sort.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jul 2005
    Posts
    144

    Smile Almost done here

    Thanks Mowen and missinglinq
    I forgot to mention this user have full access to that query but he doesnt like the popup question about save or not. I was hoping if there is an alternative.

    Quote Originally Posted by Missinglinq
    You've already got your query, why not use the Form Wizard and make a form based on this query? Make a form using the Wizard, base it on your query and under Layout select Datasheet. It'll still display in an Excel type design, and your user can sort as he likes, and it'll open up the next time with the same sort.
    I have just tried your idea and it worked like a charm. I dont know why i never thought of that but i am glad i have you guys here to assist me in my difficult tasks.

    I was wondering if you had time to talk a look at my second question on how can i refresh the form automatically?
    Thanks again

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by ranjah
    Thanks Mowen and missinglinq
    I forgot to mention this user have full access to that query but he doesnt like the popup question about save or not. I was hoping if there is an alternative.


    I have just tried your idea and it worked like a charm. I dont know why i never thought of that but i am glad i have you guys here to assist me in my difficult tasks.

    I was wondering if you had time to talk a look at my second question on how can i refresh the form automatically?
    Thanks again
    How automatic do you want it? You could setup a timer and in that event issue a Requery call ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jul 2005
    Posts
    144
    If and when the form is open i would like it to refresh every hour and timer sounds good how do i go about doing that??
    ALso after i made form using wizard with query as the source when i change the view to datasheet save and exit after i go back in its back to formview i dont know why?? what am i doing wrong??

  7. #7
    Join Date
    Oct 2006
    Posts
    7
    hey, you are doing good job.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by caden23
    hey, you are doing good job.
    And to whom are you refering????
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Jul 2005
    Posts
    144

    Cool almost there

    Quote Originally Posted by ranjah
    If and when the form is open i would like it to refresh every hour and timer sounds good how do i go about doing that??
    ALso after i made form using wizard with query as the source when i change the view to datasheet save and exit after i go back in its back to formview i dont know why?? what am i doing wrong??
    Here is an update to that if i open the form from a button it opens as a form rather than datasheet format, however if i go into the database window and open it manually from there it opens up in datasheet format.

    what am i doing wrong there??

    How about that form refresher any ideas??

    thanks

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Post the code behind the button that opens the form, and goto the form's Properties Box then select the Format tab then see what it says for Default View, 2nd property from the top, I think.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I had a situation where users opened a table made by a query and resized the columns and it always asked them if they wanted to save the changes when they closed the table. I was wondering if there was a way around this as it always bugged me (even though the documentation on opening the table stated this would happen and it didn't matter whether they selected yes or no to save the table since it was recreated each time). Is there a solution to this other than making it a report, etc...?
    Last edited by pkstormy; 10-26-06 at 01:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jul 2005
    Posts
    144
    Here is the code behind button on the form that opens that form and yes the view is set to datasheet. which is what doesnt make any sense to me?

    Thanks

    Private Sub Command34_Click()
    On Error GoTo Err_Command34_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "UserEditDripQry"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command34_Click:
    Exit Sub

    Err_Command34_Click:
    MsgBox Err.Description
    Resume Exit_Command34_Click

    End Sub

  13. #13
    Join Date
    Oct 2006
    Posts
    3
    Hi Ranjah,

    All you got to do is simply change the 7th line:

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

    That should allow you to open the Form on Datasheet view using the Command Button.

    Cheers,

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Makes absoultely no sense, of course, but cicanico is correct! Access allowing you to set a form's property for it to be displayed in one way, and then opening it another way UNLESS you, in essence, set the property a second time is absurd! But, of course, that's why Access is so much fun!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  15. #15
    Join Date
    Jul 2005
    Posts
    144
    LOL @ Missinglinq
    You are absolutely right and thanks alot to cicanico it works like a charm.

    Thanks alot guys

Posting Permissions

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