troubleshooting Question

edit a record using a date variable in the sql statement

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft AccessVBA
3 Comments1 Solution38 ViewsLast Modified:
access 2010  vba

in code prior to trying to do this..

I have a table linked via sql server   "dbo_t_nsc_trackcode_trans_time_entry"
in this table i have a value for lookup. [opened_at]  a date field


I'm trying to update a record in this table above.
In the sql string  
g =  date

Global g As Date
d = now()
Forms!NSC_DataEntry.txtReOpened = d
g = Forms!NSC_DataEntry.txtReOpened  





If m = True Then
           Dim db1 As DAO.Database
           Dim rst1 As DAO.Recordset
           Dim strSQL1 As String
           Set db1 = CurrentDb()
           strSQL1 = "SELECT [nsc_id_ref], [opened_at], [InProgress_or_closed_at] FROM [dbo_t_nsc_trackcode_trans_time_entry] where [opened_at] = '" & g & "' "
           Set rst1 = db1.OpenRecordset(strSQL1, dbOpenDynaset)
          ' Dim q As DAO.Recordset
          ' Set q = CurrentDb.OpenRecordset("SELECT * FROM [dbo_t_nsc_trackcode_trans_time_entry] where [opened_at] = '" & g & "'", dbOpenDynaset, dbSeeChanges)
           rs1.Edit
           rs1![InProgress_or_Closed_at] = Now()
           Forms!NSC_DataEntry.txtReOpened = ""
         End If
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros