Forms and tables

[caphillips]

I am creating a new database and every time i try to add new data the old gets overwritten. This happens if i put the data in the form or if i put the data directly in the table. Don't know what i am doing wrong new at access and this is the first database i have tried to create. I have the date in as TEXT because i only need the actual month not specific dates. I found this out because i need to make a query that will take the prior months mileage and subtract it from the current months mileage so i can get total miles driven in the month.

[June7]

Sounds like you are not on a new record row in the table or form.

[caphillips]

Don't understand. I thought access auto saved when you entered data

[June7]

Must be on new record row if you want to create a new record.

Tables are where the data is stored. Open a table and you see the data, it doesn't go anywhere else. So if you keep entering into the same record over and over, you are just editing the same record, not creating new records.

A bound form is an interface to the table. Data entered into bound controls will pass directly to the table. So if the form displays an existing record and you edit the data then that changes the record in table.

[caphillips]

I think i am following you

[caphillips]

don't need to enter a new record just new data for an existing record. I thought that is what the form was for to enter data for existing records but it keeps overwriting my old data that i need

[caphillips]

I ask a consultant and he said the same thing and i think that is what confused me all the records i need i got just can't get the month to month data to show up when i sort in the table because it don't exist any more

[caphillips]

i will check back later since it seems no one understands what i am trying to explain

[John_G]

Hi -

...don't need to enter a new record just new data for an existing record...

This is I think your misunderstanding. You cannot enter new data for an existing record without overwriting what is already there. A record in a table consists of one, and only one, set of data.

An analogy would be a driving logbook. Supposing you keep a record in a logbook of your gas mileage (Date, location, price, odometer reading, etc). Every time you buy gas, you write a new line in the logbook; you don't erase what is already there and write over the top of it.

An Access table is the same concept. Each logbook entry corresponds to a separate record in the table.

Entering new data into that table must be done into a new blank record, whether its in the table directly or through a form.

John

[caphillips]

Thanks john I never looked at it like that but I do understand. do I have to put a new record button on the form because all the records I have is all the records I am going to use. The only time there will be a new record is when we get a new vehicle. All the data I enter will be for the same records just another month. I have over looked something in my form because every time I return to it the data I entered prior is still there and don't understand why the form doesn't pop up blank ready for the new data.

[caphillips]

One other thing I know I can start a new blank record but there are about 160 different records/vehicles in my data base and to enter the tag # every single time would be so time consuming it wouldn't be worth doing plus it the very thing we are trying to get away from (Excel). So how do I fix this and enter new data for an existing record on my form and be able to access it in the table to do reports.

[caphillips]

Transportation CAP911.zip Here is my database hope it uploaded correctly please if there is anyone out there it the access world that can tell me what I am doing wrong do so I am very new at this!!!

[caphillips]

As you can see it is very big and I may have over looked a lot of stuff I should have done but the one thing I can't do is re-enter every tag # every month we have to enter data to time consuming. I want a form that when I am ready to enter new data it will keep the tag # but erase the old data from prior month. I added a lock on the tag # line so it could not be overwritten. Now I am at a loss...

[June7]

Tried to download your file but get error message.

If you want to keep only one record for each vehicle then what you need to do is find that record each month and edit the data. If you have to edit every single vehicle record every month then just start from the first record and progress down until the edits are done. How does having the fields blank make that any different? However, it is possible to run an UPDATE sql action that will blank all fields except the Tag number. How many fields are there?

You can build a query object to do that then use code to run the query object.

Or build and run the SQL statement in VBA which would look like:
CurrentDb.Execute "UPDATE tablename SET fieldname1=Null, fieldname2=Null, fieldname3=Null"

[John_G]

Hi -

Let's back up a step. What is the purpose of the database - what to you want it to do? From your description and after taking a look at it, it looks like the data is only a view of the current state of your inventory, a snapshot in time in effect. It has no historical data whatever.

Now, what happens if management asks you for the history of a vehicle? For example, how many miles were driven on vehicle X in each of the past 12 months? Or, if you want to see if the gas consumption of your vehicles is going up? Or, if you want to track fuel costs month by month?

You cannot answer any of those questions with the current arrangement, but that's the sort of thing Access is for. There is potential here to track a lot of valuable information.

Give it some thought, report back, and we can go from there.

John