Solved Save date from unbound text box to table

[Isaac]

Ok, then I think the problem might be with the date value from that unbound textbox.
I know there are many date formats out there, so it might just be that I am unfamiliar, but:

dd.mm.yyyy. hh:mm:ss

does not look like any acceptable date format that I've ever seen. Try entering the date in the unbound textbox as

6/27/2020 1:40 pm

and then run your code and see what happens.

 

[arnelgp]

updated my sample.
it will load the records from tblFD.

 

[saledo2000]

Ok, then I think the problem might be with the date value from that unbound textbox.
I know there are many date formats out there, so it might just be that I am unfamiliar, but:

dd.mm.yyyy. hh:mm:ss

does not look like any acceptable date format that I've ever seen. Try entering the date in the unbound textbox as

6/27/2020 1:40 pm

and then run your code and see what happens.

My date has format Short date dd.mm.yyyy on the form in unbound box and in the tblFD field as well e.g. 26.06.2020.

 

[Isaac]

Actually I just looked closer at your error that you posted the screenshot.

Looks like you forgot to add the second #
Copy the code that I had given you exactly.

 

[saledo2000]

updated my sample.
it will load the records from tblFD.

Thank you very much for your effort. I will try to implement to my DB. Will post feedback.

 

[saledo2000]

Actually I just looked closer at your error that you posted the screenshot.

Looks like you forgot to add the second #
Copy the code that I had given you exactly.

There is a code with your suggestion.
Private Sub cmdAdd_Date_Click()
Dim strSQL As String
strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES(#" & Me.txtUnboundDate & "#)"
CurrentDb.Execute strSQL
DoCmd.RunCommand acCmdSaveRecord
End Sub

Error on screenshot does not show last #. I have checked and error is the same.

 

[Isaac]

Looks good.
Did you look at the error message you posted? In the error message, it shows an insert value without the second # symbol.
The code you just posted looks good.

 

[saledo2000]

Looks good.
Did you look at the error message you posted? In the error message, it shows an insert value without the second # symbol.
The code you just posted looks good.

Yes I have checked three times and code has both # but error still occur. By the way thank you for helping me to solve this trivial problem but I am new in Access world. Learning every day hard and hope to get where you are guys.

 

[Isaac]

Hmm, odd. You could try adding a space after the word value and see if that helps. There must be something else going on that I can't see.

 

[saledo2000]

Yes I have checked three times and code has both # but error still occur.

Hmm, odd. You could try adding a space after the word value and see if that helps. There must be something else going on that I can't see.

I did but error is still there.

 

[Isaac]

Attached is a database where I have the exact same table, same table column, same textbox, same command button and click event code as you do (minus the DoCmd.RunCommand acCmdSaveRecord, since you have stated that your code errs before that line, and my form isn't bound).

The code works fine.

Thus, something else is going on that I can't see.

 

[saledo2000]

updated my sample.
it will load the records from tblFD.

I have tried your solution and if I select the yesterday date or any other date even present date I get an error.

 

[saledo2000]

Attached is a database where I have the exact same table, same table column, same textbox, same command button and click event code as you do (minus the DoCmd.RunCommand acCmdSaveRecord, since you have stated that your code errs before that line, and my form isn't bound).

The code works fine.

Thus, something else is going on that I can't see.

Thanks I will try and post feedback

 

[saledo2000]

I have tried your solution and if I select the yesterday date or any other date even present date I get an error.

There is a screenshot to get picture what I am trying to achieve.

 

[saledo2000]

Attached is a database where I have the exact same table, same table column, same textbox, same command button and click event code as you do (minus the DoCmd.RunCommand acCmdSaveRecord, since you have stated that your code errs before that line, and my form isn't bound).

The code works fine.

Thus, something else is going on that I can't see.

There is a screenshot to get picture what I am trying to achieve.

 

[arnelgp]

i hope this fixed the issue of your Date Regional setting.

 

[saledo2000]

i hope this fixed the issue of your Date Regional setting.

Thank you very much arnelgp. I have included your solution in my DB and it works. You are the man. I wish to buy you a beer. I have another question if you can help. I have UNION query of multiple Entry and Exit date and I have made another query out of UNION query. But I cannot put criteria to insert a date to get results only for that date. I have added =[Enter date] into criteria but I do not get any record. There is a screenshot of my query and results without criteria
Thanks mate for your help

 

[Gasman]

I have tried your solution and if I select the yesterday date or any other date even present date I get an error.

You have a full stop after the year AND you still do not have the ending #

 

[saledo2000]

i hope this fixed the issue of your Date Regional setting.

I have tried your solution for date format fix and is not working. The first day I took your solution was working perfectly but today does not work. I am access beginner and would need your help. My date format is DDMMYYY. Time zone in Windows settings is UTC + 01:00. I have set date format in cboDate combo box as DD\.MM\.YYYY and I get correct date 01.07.2020., but still get a syntax error

 

[saledo2000]

I've done it. In the code I did not set date format for ThisDate wich is in function set as Date.
strDate = Format(ThisDate, "yyyy\/mm\/dd")