SQL Update statement

[Asif]

I have a table (tbl_startdate) with two fields StartDateID (set as
number) and StartDate (set as Text). I also have a form with a textbox
(txt_StartDate) and on this same form I have a command button
(cmd_update). Now txt_StartDate takes its value from a listbox and
stores it as a default value but only untill the form is open if i
reopen the form the value in txt_StartDate dissappears. Hence I've
created this table (tbl_startdate) so before I close the form I click
on cmd_update and what happens is the value in txt_StartDate is placed
in StartDate field in tbl_startdate. I'm trying to run an SQL
statement for the on click event of cmd_update but nothing is
happening, have I missed something from my coding?

Dim strSQL As String
strSQL = "UPDATE tbl_startdate SET StartDate = " & txt_StartDate & " "

Please advise!! Thanks

 

[Andy Hull]

Hi Asif

The following code should work:

Dim strSQL As String
strSQL = "UPDATE tbl_startdate SET StartDate = '" & me.txt_StartDate & "'"
docmd.runsql(strSQL)

Note the single quote I have added after the = and between the final two
double quotes. These are needed as the StartDate field is text (they wouldn't
be needed if it was a number).

I also added me. just before txt_StartDate. The word "me" refers to the form
so me.txt_StartDate is the control called txt_StartDate on your form.
(Otherwise txt_StartDate is just an empty undeclared variable).

And the docmd.runsql(strSQL) runs the SQL (not sure if you already knew this)

But, for any of this to work, there must be an entry in your table already -
you can't update something that isn't there. So you need to add an entry to
your table by hand.

You'll also need code to set txt_StartDate to what is in the table when you
open your form.

hth

Andy Hull

 

[Asif]

Hi Asif

The following code should work:

Dim strSQL As String
strSQL = "UPDATE tbl_startdate SET StartDate = '" & me.txt_StartDate & "'"
docmd.runsql(strSQL)

Note the single quote I have added after the = and between the final two
double quotes. These are needed as the StartDate field is text (they wouldn't
be needed if it was a number).

I also added me. just before txt_StartDate. The word "me" refers to the form
so me.txt_StartDate is the control called txt_StartDate on your form.
(Otherwise txt_StartDate is just an empty undeclared variable).

And the docmd.runsql(strSQL) runs the SQL (not sure if you already knew this)

But, for any of this to work, there must be an entry in your table already -
you can't update something that isn't there. So you need to add an entry to
your table by hand.

You'll also need code to set txt_StartDate to what is in the table when you
open your form.

hth

Andy Hull

Andy, how do I go about linking txt_StartDate to tbl_startdate. I've
gone into the on enter event of txt_startdate and using expression
builder tried to do the following

=tbl_startdate!StartDate

but I get an error message "The expression On Enter you entered as the
event property setting produced the following error: The object
doesn't contain the Automation object 'tbl_startdate.'."

Thanks

 

[Andy Hull]

Hi Asif

Personally, I would set txt_StartDate as the form is opened.

So remove what you have iin the On Enter event of txt_StartDate.

Instead, go to the properties of the form and in the On Open event choose
Event Procedure and then click the 3 dots on the right.

You should then be in the vba editor and see...

Private Sub Form_Open(Cancel As Integer)

End Sub

....Between the above 2 lines, type,

me.txt_StartDate = dlookup("StartDate","tbl_StartDate")

....so in full you have...

Private Sub Form_Open(Cancel As Integer)
me.txt_StartDate = dlookup("StartDate","tbl_StartDate")
End Sub

....then click on the menu item debug then compile (then close the editor and
save and close your form).

When you open the form now it should automatically populate txt_StartDate
with whatever you have in tbl_StartDate.

hth

Andy Hull