VB6 and MS Access, save time and date if user is true

Question

--

I badly need your help guys. I'm stuck with these problems for a almost a month . I need to get the time and date if the user tap on the scanner.

Private Sub Command1_Click()
Adodc1.RecordSource = "select * from tbl_login where user = '" + Text1.text + "'"
Adodc1.Refresh

If Adodc1.Recordset.EOF Then
    MsgBox "Login Failed", vbCritical + vbOKOnly, "Error"
Else
    Adodc1.RecordSource = "update tbl_login set dte = '" & Label1.Caption & "' set tme '" & Label2.Caption & "' where user = '" + Text1.text + "'"
    MsgBox "Login Successful!", vbExclamation + vbOKOnly, "Welcome"
    Text1.text = ""
    Text2.text = ""
End If
End Sub


Private Sub Timer1_Timer()
Label1.Caption = Format(Now, "dddd   mmmm dd, yyyy")
Label2.Caption = Format(Now, "hh: mm: ss ampm")
End Sub

It shows the Login Successful, but it doesn't record the time and date of the user. Help please, help me fix this.

score 0 · Answer 1 · answered Dec 01 '18 at 20:48

Firstly, I would suggest always using the ampersand operator (&) for string concatenation, not the addition operator (+).

Assuming that your fields dte and tme are datetime fields, then the reason that your UPDATE query fails is because you are not providing the date & time data in an appropriate format required for SQL.

However, rather than using concatenation to insert the data into your UPDATE query, I would instead suggest parameterising the query and passing the date, time, and username to the parameters, for example:

With CurrentDb.CreateQueryDef("", "update tbl_login set tbl_login.dte = @dte, tbl_login.tme = @tme where tbl_login.user = @usr")
    .Parameters(0) = Date
    .Parameters(1) = Now
    .Parameters(2) = Text1.Text
    .Execute
End With

You can find out more about parameterised queries from this excellent answer.

score 0 · Accepted Answer · answered Dec 01 '18 at 21:39

0

Simply insert date and time:

Adodc1.RecordSource = "update tbl_login set dte = Date(), tme = Time() where user = '" & Text1.text & "'"

answered Dec 01 '18 at 21:39

Gustav

53,498
7
29
55

Doesn't work sir. It says: Adodc1: Syntax error FROM clause; Method 'Refresh' of object 'IAdodc' failed. Help please – Lucky Bartolome Dec 08 '18 at 03:48
The syntax of the SQL is correct, but it is not a _RecordSource_ but something to _execute_. So change your code from trying to open a recordset to execute a command. – Gustav Dec 08 '18 at 09:11
How do I make that sir? – Lucky Bartolome Dec 08 '18 at 15:42
There will be dozens of examples if you bing/google for this. – Gustav Dec 08 '18 at 16:51

VB6 and MS Access, save time and date if user is true

2 Answers2