Solved Save date from unbound text box to table (1 Viewer)

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
I have tblFD and on the frmFD have unbound text box where date is set Now(). Form is in continuous view and would like to save date for each ID with cmd button on click event. When I open form Date is set for present date but would like to save this date to FD_Date field in tblFD. Experts could you please help because it is very important to me for archiving purposes and future statistics.

Thank you very much.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, why are you using unbound controls? Why can't you just bind it to the table field?
 

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
Hi. Just curious, why are you using unbound controls? Why can't you just bind it to the table field?
When I add date it only add it on the first record. That is problem with continuous form. That is why I put unbound text box on continuous form next to each record and set Now() date. My FD_Date is invisible on the form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Feb 19, 2002
Messages
42,981
Are you talking about changing a saved date? I'm confused about what you are trying to do, probably because I think your table isn't properly normalzed. If you have to update all the rows of a table with a new value, you should not be storing the value in the chile table. The value should be in the parent table.

I was going to keep guessing but I'll stop now. Please give us more information on your table and why this field change has to affect multiple records.
 

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
Are you talking about changing a saved date? I'm confused about what you are trying to do, probably because I think your table isn't properly normalzed. If you have to update all the rows of a table with a new value, you should not be storing the value in the chile table. The value should be in the parent table.

I was going to keep guessing but I'll stop now. Please give us more information on your table and why this field change has to affect multiple records.
I have table FD with all beneficiaries where I open form to enter Food Distribution for every day. But my form is continuous form and cannot add date next to each ID number. I used unbound box with date Now() which is working fine, but need to save that date for statistical purposes in my periodic reports. In attachment is screenshot of my continuous form in design view.
 

Attachments

  • FD.png
    FD.png
    5.7 KB · Views: 131

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:41
Joined
Oct 29, 2018
Messages
21,358
I have table FD with all beneficiaries where I open form to enter Food Distribution for every day. But my form is continuous form and cannot add date next to each ID number. I used unbound box with date Now() which is working fine, but need to save that date for statistical purposes in my periodic reports. In attachment is screenshot of my continuous form in design view.
Hi. Can you post your table structure? Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Feb 19, 2002
Messages
42,981
If everyone gets food everyday, you can run a query that appends the distribution records for that day. If different people get distributions on different schedules, the process is a little more complex but we'd have to know your rules. Essentially, you use the schedule assigned to each person as select criteria to select only the people on that schedule.

Once you get the criteria defined and logged for each person, you shouldn't have to update the records manually again.
 

Rene vK

Member
Local time
Today, 06:41
Joined
Mar 3, 2013
Messages
123
I have the idea Saledo2000 is using the date to create a log. Date and ID should be written to a separate table. Correct me if I am wrong but he just needs an append-query with a selection of ID_Numbers and todays date.
 

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
I have the idea Saledo2000 is using the date to create a log. Date and ID should be written to a separate table. Correct me if I am wrong but he just needs an append-query with a selection of ID_Numbers and todays date.
Right I need to create a log for every ID for each day. Every day there is three meals, breakfast, lunch and dinner. I wrote a VBA code for a button on click event to append date but it is not working.

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
 

Isaac

Lifelong Learner
Local time
Yesterday, 22:41
Joined
Mar 14, 2017
Messages
8,738
If FD_Date is actually a date column in the table, might need to change that line to
Code:
strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)"
 

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
If FD_Date is actually a date column in the table, might need to change that line to
Code:
strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)"
I get an error CurrentDb.Execute strSQL when I press Add_Date button
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:41
Joined
May 7, 2009
Messages
19,169
here is a sample you might like to try.
 

Attachments

  • food.zip
    50.3 KB · Views: 130

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
here is a sample you might like to try.
Thank you for your solution but it is not working in my case. I have all ID numbers and other beneficiaries data in tblFD and every day when I open frmFD present date is set to unbound box. I would need to save that date for each ID number from unbound box which is after stored in Archive tblFD. Is there some VBA code I can use with button on click event to save present date. Hope there is solution. THANK YOU
 

saledo2000

Registered User.
Local time
Today, 05:41
Joined
Jan 21, 2013
Messages
94
It would be helpful if you shared what the error is.
Error happened because I added # # in
strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)". In my original code is strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES('" & Me.txtUnboundDate & "')" but nothing happens when I press cmdAdd_Date button.
 

Users who are viewing this thread

Top Bottom