need advice

[lipin]

I have a database that keeps daily attendance records for about 100 employees. Well everyday 1 person is doing data entry, when an error was discovered on reports the user could go in to the table and just scroll the sorted table to find the record where the error was. Well we are about to add the whole facility to this system. It will be daily records for about 750 employees. And that way to fix errors will not be feasible. How do we go about making changes or fixing errors in a timely manner? Run a query that asks for Employee ID and date, to bring up that record to change it? And then when we save that record will there be two records for that employee for that day? (Can't have that) How does that work? What is best process?
Don't know the best way to do this.

 

[BigJimSlade]

Oh goodness, sounds like you are looking for an overall tutorial of Access. Let me see what help I can provide, and then maybe some other people can chime in:

You have been using the tables in Access pretty much like spreadhseets it sounds like. The great thing about Access is that you can look at the data in forms as opposed to tables.

I would suggest using the Form Wizard to create a Form with all of the fileds from the table that you want to see. The Wizard is very good and will help you through this process. After you create your form, you can open it and do a search on the form for employee number or whatever else you would like to search for.

In this way, you will only be updating existing records and not be making another copy of the data.

Hope this helps!

Big Jim

 

[lipin]

well I have the form that populates the table, but it is set for data entry. Can I use the same form to look up records or should I copy it and use a different form to look up records by emplID and make corrections/changes?

 

[BigJimSlade]

I would use the same form. Can you not scroll through previously entered records using the record tool bar provided at the bottom of the screen (hopefully)? If you can scroll through the different records, you should be able to click on the binoculars in the toolbar at the top of the screen and do a search for a specific record.

 

[lipin]

WHen I click the binocs it says records not readable. I have data entry prop set to yes so the user can add entries easily. So they can only scroll as far back as the first one they entered in one session. And after 3 days there will be over 2000 records so scrolling on the form throught records is a problem.

I would like to enter a EmplID and Date, have the entire record appear and make changes and then have a button or something that will save my changes to the table and only have 1 record per empl per day.

 

[BigJimSlade]

Ok lipin,

This is what you will do:

First off, you need to make sure that your form has access to all of the records. I do not know how you set it up, but hopefully my code below will allow you to see what you need to see.

Create 2 text boxes: txtDate, txtCustNum
Create a command button: cmdSearch

In the "On Click" Event of the command button, write this code:

me.Filter = "[your date field] = cdate('" & me.txtdate & "') AND [your customer number field] = '" & me.txtCustNum & "'
me.filteron = true

This is of course if your customer number field is text and not numeric. If numeric, just remove the single quotes. Anyhow, that code should allow you to filter to the specific record you are looking for. If you get an error telling you that you cannot filter because records are not readable, you will have to undo whatever it was that you did that made your records non-reviewable. Maybe someone else in here will read this and know the answer to that one.

Good luck!

Big Jim

 

[The_Doc_Man]

There are many ways to skin this cat.

As a pure data integrity issue, I would use separate forms to enter new data vs. update raw data, with changes to the form and control properties as appropriate.

Make the background for data entry one color. Make the background for record update a different color or even a different background theme.

That way, the folks doing data entry know which form they are in the moment they open it. AND that prevents folks from trying to do the wrong thing at the wrong time.

This has nothing to do with Access requirments and EVERYTHING to do with prudent business practices.

 

[Idjit]

For what it's worth, I very much agree with the last post - it's always a good idea to give a person just enough flexibility/power to do what they need, and no more. saves on errors.

Speaking of which, the reason I'm posting is to ask what kind of errors the data entry people are making. The thing I really like about Access for data entry is that with a little extra programming you can drastically cut down on mistakes. Doing this as a first step will go a long way to improve efficiency, if you haven't already done that.

And one note I didn't see specifically addressed - when you use a form to edit an existing record, you are changing the existing one, not adding a new one. And the changes are automatically saved, you don't have to do anything extra.

 

[lipin]

Idjit-
"And one note I didn't see specifically addressed - when you use a form to edit an existing record, you are changing the existing one, not adding a new one. And the changes are automatically saved, you don't have to do anything extra."

As for the errors, it's not errors that can be avoided by Access, this is like when the hard copies that the person doing the data entry by was incorrect and it is not discovered till days later.

Didn't know if I would be duplicating records or changing the originals. Thanks. Never did a search to alter records. Haven't needed it yet. Now I need it.