Solved Save date from unbound text box to table

[saledo2000]

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]

Hi. Just curious, why are you using unbound controls? Why can't you just bind it to the table field?

 

[saledo2000]

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.

 

[Rene vK]

Update Query?

 

[Pat Hartman]

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]

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.

 

[theDBguy]

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]

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]

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]

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]

If FD_Date is actually a date column in the table, might need to change that line to

strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)"

 

[saledo2000]

If FD_Date is actually a date column in the table, might need to change that line to

strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)"

I get an error CurrentDb.Execute strSQL when I press Add_Date button

 

[saledo2000]

Hi. Can you post your table structure? Thanks.

There is table structure and thank you very much because you helped me a lot in the past.

 

[arnelgp]

here is a sample you might like to try.

 

[saledo2000]

here is a sample you might like to try.

Thank you very much will try it when come form Leave

 

[Isaac]

I get an error CurrentDb.Execute strSQL when I press Add_Date button

It would be helpful if you shared what the error is.

 

[saledo2000]

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]

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.

 

[Isaac]

what is the error?

 

[saledo2000]

If FD_Date is actually a date column in the table, might need to change that line to

strSQL = "INSERT INTO Food_Distribution (FD_Date) VALUES (#" & Me.txtUnboundDate & "#)"

Yes FD_Date is a column in tblFD.