Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Apr 2007
    Posts
    45

    Red face Unanswered: Access 2003 - Problems after Database Spiltting

    I split my database to create a FE and BE using the database wizard. I have two problems since doing this:

    1. It takes almost 3 minutes now for the database to boot up. I have it set to boot up to the main form (not a switchboard). I did a compact and repair and this did not addres this issue.

    2. I have a command button on the main form that excutes a macro. The macro opens up a query in form view - basically the query functions as filter with two sorting options. The problem is now that I have split the database, it doesn't sort correctly from the command button - however, if I execute it directly from the macro, it is still filtering and sorting correctly. Any suggestion as to why splitting the database would break the sort feature when executing with a command button. Any ideas how to fix it. Note: I have a copy of the unsplit database, and the command button still works in the unsplit copy.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    See if this link helps at all. Are your Macros using queries or going to the table directly?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Apr 2007
    Posts
    45
    Thanks for the link on the slow database issue.

    In regards to problem number 2, I realized that this is not an issue with the database splitting. Basically the command button executes a macro which sorts the database based on two field criteria - the macro is built on a query and not directly linked to the table. When you open up the database and execute this macro, it sorts both fields correctly. However, go back to all records and execute the macro again, and it only sorts by one criteria - not both. The only way around it, is to close the database after each time you execute the command button to get it to sort correctly. Rebooting the system after you execute the macro is not an option so I need a fix!

    Thanks for your willingness to help.

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    If you are referencing a static query (one made and saved by the query builder) then your sorting can be specified in the query with an OrderBy Clause. It can be done visually in the query builder as well by moving the columns around. A static query should sort the same way every time it is called.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Apr 2007
    Posts
    45
    If I open up the query and run it, it does filter everytime correctly. However, if I run the query from the macro, it won't do it. That is why I find this problem so confusing - if the query works, why wouldn't the macro that is using it?

  6. #6
    Join Date
    Mar 2007
    Posts
    277
    Are you defining the sort order in the query? If not, that could answer the question.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  7. #7
    Join Date
    Apr 2007
    Posts
    45

    Included a screen shot of query

    I have attached a screen shot of what the query looks like. If I run the query from the query builder, it works just fine, everytime - it sorts by putting closed at the bottom and lowest BMR# at the bottom. However, run the query from the macro, and it only works one time - when you first open database. Try to run the macro again, and it won't sort correctly by the closed field - however still sorts correctly by the BMR field - we need closed BMR's to go to the back everytime.
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Why are you using the query as a filter. Why not just use it as the Record Source for the form? Even if you have to add some fields or whatever to this query.

  9. #9
    Join Date
    Mar 2007
    Posts
    277
    You are definately using the OrderBy Clause in the static query. I have to admit, other than AutoExec, I don't use macros. Are you showing the user the query directly? Does Vic's idea make any sense to you? Maybe someone more familiar with macros will drop by with a suggestion.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  10. #10
    Join Date
    Apr 2007
    Posts
    45

    Response to GolferGuy's question

    Quote Originally Posted by GolferGuy
    Why are you using the query as a filter. Why not just use it as the Record Source for the form? Even if you have to add some fields or whatever to this query.
    I have included another screenshot to give you the big picture. This database is used by our facilities dept to keep track of workorders. On the right side of the screen are command buttons that serve as filters so that the individuals can choose to look at only the tickets that are assigned to them. Theoretically, the open tickets should go to the front and the closed tickets go to the back. This command button/filter works the first time you open the database - the closed tickets go to the back. Hit all records, then try to execute the filter again, and the closed tickets are intermingled with the open ones, although it still sorts by decending BMR order. Doesn't make sense to me that it would only sort it correctly when the database is first opened?
    Attached Files Attached Files

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Yes, I can see the buttons on the right side, but that does not answer my question about using the query as the RecordSource, rather than a filter? I have not used filters for quite a while, but I do know that using this query as the Record Source should fix the problem, so have you tried it? If so, did it still work the same as as using it as a filter? Or what happens when you use it as the Record Source?
    Please let us know what happens, good or bad.
    Thanks,

  12. #12
    Join Date
    Apr 2007
    Posts
    45

    Using qry as recordsource

    Okay. I built another command button, this time using the query as the record source instead of the macro. Attached is the result - it brings up the query in data view instead of in form view - which I think is why I went the macro way to begin with because of it doing this.

    I have figured out when this issue is happening. The filter works fine. If I use the remove filter button at the top of access, then the filter works again. However, if I hit the All Records command button I created to remove the filter, then my filter button on the right no longer sort right. What is up with my all records filter that it would demolish the code behind my other filters.
    Attached Files Attached Files

  13. #13
    Join Date
    Mar 2007
    Posts
    277
    Jill,
    Is there any change you could post your zipped up db with any sensitive data removed so we can play with it? I'll bet there is an easier way to achieve what you want and either Vic or I could put in some code to get there.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Where are you finding "Record Source" to be able to call what happened having put the query name into the Record Source?

    When discussing a form, which is what we were discussing, the Record Source is the "Source of records for that form" and is found at the top of all properties for a form in the properties dialog box, and the Data tab of the properties dialog box for the form. You can see the properties dialog box when you have the form in Design view, and use the menu View, Properties. What you have done with this new button, it looks like, is used the query name as what the button will open. That is not using the query as the Record Source for a form.

  15. #15
    Join Date
    Apr 2007
    Posts
    45

    Okay, now I am confused

    I know what the recordsource is. The record source for my frmWorkOrder is qryBMR - which opens up all Workorders sorted in descending order by the BMR number. However, the filters I have, I want them to filter by certain criteria - I can't change the recordsource to the frmWorkOrder because then we have no way of viewing all the BMRs. I admit, I am not great at writing code and rely on macros and other stuff to write the code for me. Is there a way to build a command button to open up a form and change it's recordsource only when that command button is executed?

Posting Permissions

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