Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    prohibit adding new records with older dates (Access2000/WinXP Pro)

    I'm finding that at times the y-t-d totals in my database are changing. When I look into what's causing this I find that somehow a record has been added with a previous date. The data can be entered manually but is usually imported from an excel file. I'm not sure which of these methods is being used to enter the records with the older dates. I know that if a form is used to enter the data, you can specify on the form that the date field must be > "some set date", but I don't know if it's possible to prevent this from happening if the data is entered directly into the file or imported. I'm desperate to solve this problem b/c I don't have confidence in the data right now. It seems that every week when I check my reports I need to correct an error on a record with an older date. I need to be able to prevent users from entering bad data.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: prohibit adding new records with older dates (Access2000/WinXP Pro)

    This is as much a procedural matter as a programming question. If you're importing an Excel spreadsheet, I'd suggest viewing it for anomalies first. You can use Data-->Sort to sort the data on the date in the spreadsheet so that you can quickly see any odd dates. That will at least tell you whether the data is coming in through the spreadsheet or being entered directly. You can certainly import the data into a new table rather than appending it directly and then use a query to append only data after a specified date into your actual table. As far as "directly entered into a file" goes, your users should never even be able to *see* tables and queries in a database, let alone enter data into them.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (Access2000/WinXP Pro)

    Thanks for your reply Charlotte. I already had the database designed to import into a separate table before appending to the main table. I didn't think about modifying the append query to verify the dates. That will work as long as I keep remembering to update the query each week after checking the data.

    On the other matter of users seeing the tables, this is a database I put together in 1996. At that time it was just a quick and dirty way of getting the data into a database so we could analyze it. Each year I purge the old records and start a new database using the original design. I've made a few modifications over the years, but never had a chance to add security features. We originally entered the records manually using a form, and only started importing from spreadsheets a year ago. I added the switchboard menu a couple of years ago to discourage users from "seeing" the list of tables, queries, etc, but they can still get to them if they want to. It's not a very fancy or sophisitcated database, but it does need some revamping.

    Can you please give me some advice as to the best way to set it up so that users CAN'T see the tables and queries?

    Thanks, Cathy

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Cathy,

    The quickest way to make tables & queries inaccessable to users is to go to the Tools:Startup menu and remove the checkbox for "Display Database Window". Be sure to also designate in the "Display Form/Page" field the initial form you want to be displayed on startup. Thereafter, when the MDB/MDE file is invoked, the Database Window is suppressed, and the application automatically starts up with the specified form.

    -- Jim

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Thanks Jim. I did what you said, but for some reason it still shows the database window minimized at the bottom of the open window. I previously used an autoexec macro to open the switchboard menu and thought that was the problem so I deleted the autoexec macro and put the switchboard form as the designated "Display Form/Page". That didn't change anything. Could the switchboard menu be causing the database window to stay there?

    Cathy

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Cathy,

    After setting up the Startup menu, you will have to close the MDB/MDE file entirely, and reopen it before the settings take effect. If you did that, and the minimized database window is still present, then something else is taking place that I don't understand.

    There is nothing special about the switchboard menu, so far as I know.

    -- Jim

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Jim, I did close the file. I even closed Access and then reopened the program. I tried using another form for my designated "Display Form/Page". When I used a different form, the database window did NOT show up at all, but when I changed it back to the switchboard menu, but minimized db window comes back. ????

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    What's the old "saw": If it hurts when you do that, don't do that...?

    I guess you've found a case where the Switchboard form/menu is treated differently from other forms. My suggestion would be to find a different method of navigating among forms than the use of Switchboard. According to your tests, that should solve the problem.

    Personally, I stopped using Switchboard in favor of direct form opens via a command button about a year ago. I pass the name of the calling form as a parameter, and allow the callee form to use that to set the caller form invisible. It's quick, and seems to work well. The extension of that concept is to have the caller code first use the "IsLoaded()" function to determine of the callee is already present, but just invisible. If so, then merely set the callee form visible again.

    -- Jim

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    <hr> I pass the name of the calling form as a parameter, and allow the callee form to use that to set the caller form invisible. It's quick, and seems to work well. The extension of that concept is to have the caller code first use the "IsLoaded()" function to determine of the callee is already present, but just invisible. If so, then merely set the callee form visible again.<hr>

    Thanks Jim, but I'm a little lost. Any chance you could give me an example of what you mean?

    Cathy

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: prohibit adding new records with older dates (

    The Switchboard is not treated differently from other forms, the only special property it has is that it is created by the Switchboard Manager. After that, it is just another form. But older versions of the Switchboard contained code in the On Load or On Open event that minimized the database window. In newer versions, there is still a comment that refers to this:
    <hr>' Minimize the database window and initialize the form.<hr>
    but the actual code to do so has been removed. As you indicate, your database was created in 1996, so the Switchboard probably contains the minimize code. Removing this would be enough.

  11. #11
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Thanks, Hans.

    I found the code you were referring to. It took me a few tries to figure out exactly which lines to keep and which ones to get rid of, but I finally figured it out and it works like a charm. That's exactly what was causing the problem with the switchboard menu. Mystery solved.

    Cathy

  12. #12
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prohibit adding new records with older dates (

    Cathy,

    Attached is a small example of the form switching logic I mentioned.

    -- Jim
    Attached Files Attached Files

Posting Permissions

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