Problem with form writing to table... 1

[twospoons]

Hello,

I have a table called Control_File, which stores Start_Date, End_Date and Div_Nbr. I built a form using the Create Form Wizard and selected the Start_Date and End_Date fields. Next I added a button to the form and used the visual basic editor to automatically loop through a set of queries when the button is clicks and output the results to an Excel spreadsheet.

The problem I am having is, when I type in the Start_Date and End_Date it doesn't update the Control_File table unless I scroll to the next record and back before clicking the button. If I click the button without scrolling forward and back, the queries run for the previous date range. How can I make sure that when I click the button it will update the Start_Date and End_Date in my Control_File table before running the queries?

 

[twospoons]

I have tried troubleshooting this by adding the following to my cmdRunReport_Click() function:

MsgBox Start_Date.Value

CurrentDb.Execute "UPDATE Control_File SET Start_Date = " & Start_Date.Value & " Where Control_ID = 1"

The date appears correctly in the message box, but it does not save correctly to the table. When I open the table it looks like it saved as 12/30/1899. Then when I click on the field it changes to 12:00:43 AM.

The field is setup as a Date/Time field and the Format is set to Short Date.

Any ideas?

Thanks

 

[PHV]

Private Sub cmdRunReport_Click()
DoCmd.RunCommand acCmdSaveRecord
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[twospoons]

Thanks, that worked!