Update SQL

[D]

Hi:

What please would be syntax for update rather than Insert; I want my table
which has already the record to be updated (the three fields) - see who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[Douglas J. Steele]

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's more, all
rows will be updated. To have it only update a single row, you'd have to
suppy an appropriate WHERE clause.

 

[D]

Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that does
the 'change' gets into his records by selecting an account ID; not the (LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

 

[Douglas J. Steele]

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that does
the 'change' gets into his records by selecting an account ID; not the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's more,
all
rows will be updated. To have it only update a single row, you'd have to
suppy an appropriate WHERE clause.

 

[D]

Thanks Doug!

Is there a work around?

Dan

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that does
the 'change' gets into his records by selecting an account ID; not the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's more,
all
rows will be updated. To have it only update a single row, you'd have to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I want my
table
which has already the record to be updated (the three fields) - see who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[Douglas J. Steele]

Isn't the value of KAMID on the form, so that you can simply determine which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

Is there a work around?

Dan

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that
does
the 'change' gets into his records by selecting an account ID; not the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's
more,
all
rows will be updated. To have it only update a single row, you'd have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I want my
table
which has already the record to be updated (the three fields) - see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[D]

Hi Doug:

No, it is on the table (security, so to speak); can I put in the form and
make it Visible = No? what after?

Thanks,

Dan

Isn't the value of KAMID on the form, so that you can simply determine which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

Is there a work around?

Dan

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that
does
the 'change' gets into his records by selecting an account ID; not the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's
more,
all
rows will be updated. To have it only update a single row, you'd have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I want my
table
which has already the record to be updated (the three fields) - see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[Douglas J. Steele]

Sure. You'd refer to the field as though it wasn't hidden.

If Kamid is a numeric field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = " & Me.KAMID

If it's a text field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = '" & Me.KAMID & "'"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug:

No, it is on the table (security, so to speak); can I put in the form and
make it Visible = No? what after?

Thanks,

Dan

Isn't the value of KAMID on the form, so that you can simply determine
which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

Is there a work around?

Dan

:

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that
does
the 'change' gets into his records by selecting an account ID; not
the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's
more,
all
rows will be updated. To have it only update a single row, you'd
have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I want
my
table
which has already the record to be updated (the three fields) -
see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[D]

Thanks Doug!

It is text; but I get Error 3061: Too few parameters. Expected 2?

Dan

Sure. You'd refer to the field as though it wasn't hidden.

If Kamid is a numeric field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = " & Me.KAMID

If it's a text field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = '" & Me.KAMID & "'"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug:

No, it is on the table (security, so to speak); can I put in the form and
make it Visible = No? what after?

Thanks,

Dan

Isn't the value of KAMID on the form, so that you can simply determine
which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug!

Is there a work around?

Dan

:

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person that
does
the 'change' gets into his records by selecting an account ID; not
the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If there's
more,
all
rows will be updated. To have it only update a single row, you'd
have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I want
my
table
which has already the record to be updated (the three fields) -
see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[Douglas J. Steele]

What's the actual code you're trying to run?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

It is text; but I get Error 3061: Too few parameters. Expected 2?

Dan

Sure. You'd refer to the field as though it wasn't hidden.

If Kamid is a numeric field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = " & Me.KAMID

If it's a text field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = '" & Me.KAMID & "'"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug:

No, it is on the table (security, so to speak); can I put in the form
and
make it Visible = No? what after?

Thanks,

Dan

:

Isn't the value of KAMID on the form, so that you can simply determine
which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug!

Is there a work around?

Dan

:

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person
that
does
the 'change' gets into his records by selecting an account ID;
not
the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered
when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If
there's
more,
all
rows will be updated. To have it only update a single row, you'd
have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I
want
my
table
which has already the record to be updated (the three
fields) -
see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

 

[D]

Hi Doug:

I just got it working; pheew..! THANKS A LOT!!

Please see below.(please ignore the comments)

Dan

****
Private Sub Form_Load()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter


Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "UPDATE bff " & _
"Set USERname ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
"WHERE KAMID = '" & Me.KAMID & "'"
'qdf.Parameters("[Forms]![bfff]![kamid]") = _
'Forms![bfff]![KAMID]
'qdf.Parameters("[Forms]![bfff]![YourControlName]") = _
Forms![YourForm]![YourControlName]
'qdf.Parameters ("[Please enter a number:]) = 999")

'Set rs = qdf.OpenRecordset
'Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub

What's the actual code you're trying to run?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug!

It is text; but I get Error 3061: Too few parameters. Expected 2?

Dan

Sure. You'd refer to the field as though it wasn't hidden.

If Kamid is a numeric field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = " & Me.KAMID

If it's a text field:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#") & _
" WHERE KAMID = '" & Me.KAMID & "'"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug:

No, it is on the table (security, so to speak); can I put in the form
and
make it Visible = No? what after?

Thanks,

Dan

:

Isn't the value of KAMID on the form, so that you can simply determine
which
record it is they've changed?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug!

Is there a work around?

Dan

:

I'd suggest that you do not want to prompt for KAMID.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug:

Thanks a lot!

Yes, all the records are updated; my problem is that the person
that
does
the 'change' gets into his records by selecting an account ID;
not
the
(LAN
name).

So, should the 'where' be: where KAMID = KAMID initialy entered
when
the
form is Loaded? or where KAMID =[Enter your KAMID]

Thanks again,

Dan

:

sql = "UPDATE LOG " & _
"Set USER ='" & fOSUserName & "', " & _
"Action = 'Entered', " & _
"When = " & Format(Now(), "\#yyyy\-mm\-dd hh\:nn\:ss\#")

That assumes that there's only one record in the table. If
there's
more,
all
rows will be updated. To have it only update a single row, you'd
have
to
suppy an appropriate WHERE clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

What please would be syntax for update rather than Insert; I
want
my
table
which has already the record to be updated (the three
fields) -
see
who
changed the records.

Please see below.

Thanks,

Dan

Private Sub Form_Load()
Dim sql As String
Me.Caption = "Welcome " & fOSUserName
sql = "Insert into Log (UserName, Action, When) " & _
"Values ('" & fOSUserName & "','Entered',Now())"
Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError
Set db = Nothing
End Sub