Update statement

[reysy28]

Hi Guys, what's wrong with these commands......

Private Sub Command49_Click()
Dim cn As ADODB.Connection
Dim m_strSql, m_elapsed As String

Me.EndTime.Value = Format(Time, "HH:MM:SS")
Set cn = CurrentProject.Connection

m_elapsed = Format(TimeValue(EndTime) - TimeValue(BegTime), "HH:MM:SS")
m_strSql = "update AppDataFile set Lang_Time = m_elapsed.value where AppId =" + CStr(Form_Examination.AppID.Value) + ";"
cn.Execute m_strSql


Set cn = Nothing



DoCmd.OpenForm "Tech_Examination"


End Sub

Am having an error at "cn.Execute m_strSql" NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETER

[JoeM]

Since m_elapsed is a variable, it needs to be outside the quotes when building m_strSQL, i.e.

m_strSql = "update AppDataFile set Lang_Time = '" & m_elapsed.value & "' where AppId =" & CStr(Form_Examination.AppID.Value) & ";"

I find the best way to create these type of SQL statements is to manually create an example of a query that does what I want. Then switch to SQL view and copy/paste the code that results. This is pretty much exactly what the code you are building needs to look like.

Then, in your VBA code, temporarily comment out the line that runs your SQL code (the "Execute" line), and replace it with a MsgBox that returns the SQL code you just built, i.e.

MsgBox m_strSql

Then compare it to the code you copied out earlier. Does it look similar? Does it have the correct values, quotes, etc in all the right places?
Once you are satisfied that it does, you can uncomment the VBA code that runs the SQL code, and get rid of the MsgBox.

[reysy28]

Thanks JoeM, am having error in m_elapsed variable.... what I did is removed the .value and it works!!!!

[orange]

I realize your code is working, but for reference:

This does Not do what you think
Dim m_strSql, m_elapsed As String

m_strSql will be a variant
m_elapsed will be a string

You have to explicitly Dim each variable, otherwise it will be a variant by default.

You could use

Dim m_strSql As String, m_elapsed As String
or
Dim m_strSql As String
Dim m_elapsed As String

[reysy28]

Noted Sir Orange.. thanks.....