Solved: Refresh date fields on data form in Excel

[DaveyP]

I have a fault logger spreadsheet with multiple forms. This appears to work fine, however it has been pointed out to me that the date fields (there are several) do not show the actual date when moving from record to record. What appears to be happening is that the date of the previous record remains in the text box for the new record and only goes back to what it should be when you go back to the original record. (I hope I'm making sense nere). This does not affect any of the other text boxes, only ones with dates in. The problem is that I have set up the datatbase such that when you move to the next record, the previous record is saved, thus it is possible to get the wrong dates copied to existing records. I can attach the code if anyone needs to see it, once I have whittled down its size a little. Anyone out there with some ideas? Your help is as usual, much appreciated.

Davey

[MWE]

I have a fault logger spreadsheet with multiple forms. This appears to work fine, however it has been pointed out to me that the date fields (there are several) do not show the actual date when moving from record to record. What appears to be happening is that the date of the previous record remains in the text box for the new record and only goes back to what it should be when you go back to the original record. (I hope I'm making sense nere). This does not affect any of the other text boxes, only ones with dates in. The problem is that I have set up the datatbase such that when you move to the next record, the previous record is saved, thus it is possible to get the wrong dates copied to existing records. I can attach the code if anyone needs to see it, once I have whittled down its size a little. Anyone out there with some ideas? Your help is as usual, much appreciated.

Davey
Davey: Welcome to the forum !

Attaching the offending spreadsheet always helps. So if you could shrink it a bit and attach it to a future post, that would help.

I do not understand why the date text boxes would be handled differently than any other text box. Does new record mean a record for which there is essentially no data (and the user will fill in stuff)? Are you populating the new record with any default data?

[DaveyP]

Thanks for the prompt reply. No, this isn't new data, it is just the user scrolling down a list of entries from a data list that appear in the form as the user either presses "next" or "previous" or uses a slide bar. The date from the previous record was carried over into the next shown record and as the sub routines to do this are "Load Record" and Save Record" then the wrong date was saved to the data. I think I have sorted this by adding "Userform.RePaint" at the end of the sub routines. I have added a cut down version of the spread sheet, if you want to play with it, take out the "repaint line" and see what it does.

Davey

[MWE]

Thanks for the prompt reply. No, this isn't new data, it is just the user scrolling down a list of entries from a data list that appear in the form as the user either presses "next" or "previous" or uses a slide bar. The date from the previous record was carried over into the next shown record and as the sub routines to do this are "Load Record" and Save Record" then the wrong date was saved to the data. I think I have sorted this by adding "Userform.RePaint" at the end of the sub routines. I have added a cut down version of the spread sheet, if you want to play with it, take out the "repaint line" and see what it does.

Davey
Davey: "repaint" has frustrated :banghead: more developers than one can count! It sounds like you have solved your own problem.:thumb That is not so unusual after someone takes the time to think about their problem, post to a forum, think about the response, etc. Something akin to an electronic sounding board.

There are lots of good resources here in virtually any MS Office area (both appl functionality and VBA stuff), so think about this forum the next time you have a problem. The posts are a nice spectrum from fairly simple questions to real head scratchers.

[DaveyP]

Sorry,

I was a little premature in my previous statement!!! I have checked again by putting in the 3 dates, 1-Jan-2005 to 3-Jan-2005 and a final date of 20-Aug-2005 in all the date fields. The result is that by hitting the "Next" button, the dates will go 1-Jan-2005, 2-Jan-2005, 3-Jan-2005, 20-Aug-2005 and then hitting "Previous" goes 1-Mar-2005, 1-Feb-2005, 1-Jan-2005!!! If you only go down tree records and return up, you will get a different result?! I have attached the spreadsheet again, can someone have a look before I totally lose it:banghead:! Thanks

DaveyP