How to insert value into database

[walid geagea]

I am writing a code to copy and insert any value that is inserted in the field FormUsername but when I run this, I got an error.

can anyone help

Private Sub CopyRecord()
Dim sSql As String
 
sSql = "INSERT INTO WelcomeTemp ([UserLog],) " & _
        "VALUES (" & WelcomeScreen![FormUsername] & ");"
 
CurrentDb.Execute sSql, dbFailOnError
 
End Sub

[Mariostg]

Coma aftr UserLog...
Also need single quotes surrounding the string to insert...

[Lysander]

Could you please tell us what the error message is, and what version of Access you are using.

I tried your code, modified for my table, and got the error "Syntax Error in Insert Command"

I have not seen your syntax before.
Ah, having just done a goggle search, I think the VALUES is new to Access 2007, I have never seen it in earlier versions (And have not used 2007 myself)

What could be an issue is what sort of field UserLog is. I suspect it is a string field, in which case, you are missing the quotes around FormUserName.

Try this. (I can't test it as I don't have Access 2007

sSql = "INSERT INTO WelcomeTemp ([UserLog]) " & _
"VALUES ('" & WelcomeScreen![FormUsername] & "');
 

Example from Microsofts Help Page, note the quotes around the values

    dbs.Execute " INSERT INTO Employees " _
        & "(FirstName,LastName, Title) VALUES " _
        & "('Harry', 'Washington', 'Trainee');"
 

[walid geagea]

This is the full code.
I am getting a Run-time error '424' Object required

In fact, all that i want is to record the username in when Login was successful, so i will recall the value from the tblWelcomeTemp fldUserLog and display it in the next form after the login,

Does anyone have a suggestion

Private Sub CopyRecord()
Dim sSql As String
 
sSql = "INSERT INTO WelcomeTemp.table ([UserLog]) " & _
        "VALUES ('" & WelcomeScreen.Form![FormUsername] & "') where WelcomeTemp[MyID] =1;"
 
dbs.Execute sSql, dbFailOnError
 
End Sub
 
 
Private Sub FormUsername_AfterUpdate()
  If Me.NewRecord Then Exit Sub
    Call CopyRecord
End Sub

[Mariostg]

Why did you change WelcomeScreen![FormUsername] to this WelcomeScreen.Form![FormUsername]

If you try to compile it, you will see that the object WelcomeScreen does not exists.

[NeoPa]

When you've sorted out the VBA problems in your code, you will need to look at your use of the WHERE clause in your SQL. INSERT INTO has no WHERE clause. Indeed, it would make little sense if you think about it.

Lysander,
Actually the VALUES() reference has been around for a while (Pre 2003 at least), but finding info about it has never been very easy. Check out Finding Jet SQL Help for a pretty good list of the syntax for all the SQL commands available to Access.

[Lysander]

Thanks NeoPa, I had never seen it before outside of SQL-Server, learnt something new.

[walid geagea]

Problem completely solved..thank you for your assistance

[NeoPa]

Well done Walid. Pleased to hear it :-)