Overwriting Records?

[DataUser]

I created a database to track my time. (Screen-shot is attached). Sometimes when I submit my time Access seems to put the record over a previous record, and thus replacing. I'm wanting it to just keep going to the next record. You'll notice in the screen-shot that it would update the first record of the datasheet if I hit submit. Any help anyone can give is appreciated. If needed, I can take a Camtasia shot, which might help show things a little better. Thanks!!

[Toyman]

I created a database to track my time. (Screen-shot is attached). Sometimes when I submit my time Access seems to put the record over a previous record, and thus replacing. I'm wanting it to just keep going to the next record. You'll notice in the screen-shot that it would update the first record of the datasheet if I hit submit. Any help anyone can give is appreciated. If needed, I can take a Camtasia shot, which might help show things a little better. Thanks!!

Are you saying that you want the record to appear in the botton of the list?

[DataUser]

Are you saying that you want the record to appear in the botton of the list?

Yes. Because that seems to be why it keeps overwriting the records. Sometimes it works fine and adds the records to the bottom of the datasheet. But then it adds them in other spots, which overwrites the record that was previously there. (If this doesn't make sense, I understand!)

[Toyman]

Yes. Because that seems to be why it keeps overwriting the records. Sometimes it works fine and adds the records to the bottom of the datasheet. But then it adds them in other spots, which overwrites the record that was previously there. (If this doesn't make sense, I understand!)

Ok, the problem is in the sort order of query for the list form. You need to put a sort in the time (ascending). However, since the time period you entered is not actually a time field, you will have to create a time field in you query for the sort. In the underlaying query, create a new field and put the follow:

StartTime: CDate(Left([YourTimePeriodFieldName],instr([YourTimePeriodFieldName]," ")-1))

Replace "YourTimePeriodFieldName" with the field name for the Time Period field. If there is a space between the end of the first time and the hyphen leave the " " at the end of the "instr" function. If it does not have a space replace " " with "-"
Sort this new field ascending

[DataUser]

Ok, the problem is in the sort order of query for the list form. You need to put a sort in the time (ascending). However, since the time period you entered is not actually a time field, you will have to create a time field in you query for the sort. In the underlaying query, create a new field and put the follow:

StartTime: CDate(Left([YourTimePeriodFieldName],instr([YourTimePeriodFieldName]," ")-1))

Replace "YourTimePeriodFieldName" with the field name for the Time Period field. If there is a space between the end of the first time and the hyphen leave the " " at the end of the "instr" function. If it does not have a space replace " " with "-"
Sort this new field ascending

Excellent! Thank you so much for the help!

[DataUser]

Actually, something seems to be wrong with the datasheet itself. (My form main form - not the sub - is displaying the first row). I went ahead and zipped the database and attached it. If anyone could take a second to look at it, I would be grateful. I'm just getting started, and I think I jumped in over my head

[Toyman]

Actually, something seems to be wrong with the datasheet itself. (My form main form - not the sub - is displaying the first row). I went ahead and zipped the database and attached it. If anyone could take a second to look at it, I would be grateful. I'm just getting started, and I think I jumped in over my head

Ok, I made some changes in the db. See is this works

[DataUser]

Ok, I made some changes in the db. See is this works

Now it's working perfectly! Thank you very much. Would you mind explaining what was wrong? I would love to get better at this. Thanks again.

[Toyman]

Now it's working perfectly! Thank you very much. Would you mind explaining what was wrong? I would love to get better at this. Thanks again.

What you were trying to achieve was a way to enter the your data and display the data in a form of a running log. What I did was unbound your main form to the table. The combo boxes were also unbounded because you wanted to use a update button to save the data. After the fields were unbound, it now acts only as a entry field. Once the data is entered into the fields, I use an append sql statement tied to the update botton to append the new data to the table. After the data is updated, the last part of the code refreshes the subform displaying the newly entered record. Also, you have set the "time period" field as a number field. However, the number format you enter is this: 18:00-18:30. This is actually a text format. Because of that, I changed the format for the field to a text field. You also have a TimePeriodID in the order of the time period. I used this id to sort the time ascending. Therefore, when the subform is updated, the new record falls into place according to the sort order of the record.