Save to table overwrites previous save

[shylock]

In my Access project I save Downtime records to a table from a form. When I save more than one record, the second record overwrites the first record even though the records are completely different. The form and all of the controls are bound to the table. Below is a copy of the table format. It is ordered by [Begin Date] DESC, [Begin Time] DESC. Does anyone have a suggestion as to how I can eliminate this problem?

[isladogs]

Why are you using a replication ID number type for your primary key?
Unless you have a very good reason for doing this, first make a backup & then change it to autonumber

If that doesn't solve it, you will need to provide details of your form where the problem occurs!

[shylock]

ridder52:

I did not design this table. This is how it was when I inherited it. However, I removed the number as the primary key and it did not resolve the problem. What "details" or the form do you need?

[isladogs]

Don't remove it. You need a PK field but not with a replication ID.
Is this a very old MDB file that has been used for replication?

Suggest you show the form design, examples of the data on the form and after saving, together with the code used to save the records

[shylock]

Don't remove it. You need a PK field but not with a replication ID.
Is this a very old MDB file that has been used for replication?

Suggest you show the form design, examples of the data on the form and after saving, together with the code used to save the records

Yes, this is a VERY old table imported from SQL Server. It has almost 17,000 records dating back to 2008. I changed the DowntimeEventID to an Autonumber and it now seems to save the records w/o overwriting the previous saved record. However when I try to save the record I get the error shown below. I can over-ride the error and it does save the record, but I want to get rid of the error. My code for the append query is also shown below along with the form for inputting the data. Also, on another form, I am showing All of the Downtime events in descending order. The most recent records don't show up until I "refresh" the form. Is thare a method to rfresh the form in code. I would like to not use the requery method as it takes too much time and I have had little success in locating an event that occurs every time I bring up the form.







SqlQry = "INSERT INTO DowntimeEventNew ([Begin Date], [Begin Time], [End Date], [End Time], Description, Planned, ReportedBy, EnteredBy, ServerName, UsePhysicalServer, Services)" _
& "VALUES (cdate([txtBeginDate]), cdate([txtBeginTime]), cdate([txtEndDate]), cdate([txtEndTime]), [txtDescription], [cboPlanned], [txtReportedBy], [txtEnteredBy], [cboServerNm], [txtUsePhysicalServer], [txtServices]);"
DoCmd.RunSQL SqlQry


Any suggestions, would be appreciated. Thanks.

[isladogs]

Which field is the wrong datatype. In other words which is converted to null?
What datatypes do you have for the 4 date and time fields? I ask as these are left aligned which indicates text rather than datetime.
Your query seems to confirm that

There are 4 events that have some similarities - requery, recalc, refresh and repaint.
Each does slightly different things - try googling all 4 words

[shylock]

Which field is the wrong datatype. In other words which is converted to null?
What datatypes do you have for the 4 date and time fields? I ask as these are left aligned which indicates text rather than datetime.
Your query seems to confirm that

There are 4 events that have some similarities - requery, recalc, refresh and repaint.
Each does slightly different things - try googling all 4 words

Thanks ridder52, you've been very patient and helpful. The formatted data types in the form are, respectively, Short Date, Short Time, Short Date, and Short Time although they are in text boxes (don't know if that makes any difference.). The dates are chosen from a date picker. I tried to cast the dates and times with Cdate in the query but did not have any success. Perhaps I did not have the correct syntax? Cdate([txtBeginDate]). Would that be correct?

[isladogs]

If these are all date fields:
1. why are they left aligned? Dates are stored as numbers and should be right aligned.
2. Whilst CDate is correct for converting dates stored as text back to dates, it shouldn't be needed if they are dates.
3. Does the Access date picker appear when you click on each textbox?

Nothing wrong with using textboxes for dates.

You haven't answered my question about which field was converted to null when saved.
My guess is your datatypes are text and you had an illegal value when converting to date e.g 31 Sept 2018

[shylock]

1. I have no clue why they are left justified. They a definitely formatted as short dates and short times.
2. I agree.
3. Date picker appears for the date controls only. I don't want them for the time controls.

To answer the null question, no nulls have been saved to the records in the table. The data types in the table for Begin Date, Begin Time, End Date and End Time are set to Date/Time. Could this be the problem and how can I get around this without combining the fields into date/time? For my purposes I need these fields to be separate.

Also what event should I use for the requery to update the form which lists all of the downtime events, including the new records just added? I have tried on open, on got focus, on activate and on current. None of these events occurs every time I navigate to that form.

[isladogs]

1. I have no clue why they are left justified. They a definitely formatted as short dates and short times.
2. I agree.
3. Date picker appears for the date controls only. I don't want them for the time controls.

To answer the null question, no nulls have been saved to the records in the table. The data types in the table for Begin Date, Begin Time, End Date and End Time are set to Date/Time. Could this be the problem and how can I get around this without combining the fields into date/time? For my purposes I need these fields to be separate.

If you need to requery the subform, use Me.SubformcontrolName.Requery in the button click event after running the query
You could also add the line DoEvents first to ensure the previous task has completed before requerying


Also what event should I use for the requery to update the form which lists all of the downtime events, including the new records just added? I have tried on open, on got focus, on activate and on current. None of these events occurs every time I navigate to that form.

I'm confused. All records are being saved and no fields contain null records ...despite the error message?
I would always suggest combining the date and time parts as one field. This simplifies coding.
You can still display the data using two textboxes.
there is absolutely nothing wrong with using datetime. That's what it should be

However, you can remove CDate from each field in your query

EDIT
Make sure you requery the subform in the button click event
Add the line DoEvents first to ensure the previous code has completed then requery the subform control name

[shylock]

ridder52

Thank you for all of your help the DoEvent and Requery from the calling form worked perfectly.

Sorry to be so dense. I am coming from a C, Baan ERP and UNIX programming background, but have not done any programming for over 8 years. My experience with Access was back in the mid '90's and very limited at that.

I found it very confusing and problematic, in this project, to work with a Date/Time combination field. It was far easier, programmatically, for me to split the fields into a date field and a time field.

In any case, I believe this, with your help, has completed this project.

Many, many thanks again!

[isladogs]

You're welcome. Good luck with the rest of your project.