Syntax error when inserting new record

[The]

Hi guys

I got Syntax error when running that code:

Private Sub AddTenants_Click()
Dim strInsert As String
strInsert = "Insert into Tenants ( T_Name, Nationality, Employer, Lease_Date, U_ID, Rent, Phone)" & _
" Values ( '" & Me.Name & " ', '" & Me.Nationality & "', '" & Me.Employer & "', '" & Me.Lease_Date & "', " & Me.U_ID & ", " & _
Me.Current_Rent & ", '" Me.Phone "')"
CurrentDb.Execute (strInsert)
Dim New_T_ID As dao.Recordset
Set New_T_ID = CurrentDb.OpenRecoedSet("select @@identity")
CurrentDb.Execute "UPDATE Units SET Current_Tenant=" & New_T_ID &, Current_Rent= " & Me.Rent & " WHERE U_ID= " & Me.U_ID & " ;"
End Sub

All the date taken from fields in the form. I press the add new record button to execute that code. Then get the last inserted id to use it in another update query.
There is auto increment id in the Tenants table, I didn't add it to the statement to allow database to generate one, not sure if that is wrong in access DB.
I am working on the latest version of MS access

Thank you guys

[June7]

Missing &s

& Me.Current_Rent & ", '" Me.Phone & "')"

[The]

Thanks man

Now I am getting "method or data member not found" and it highlits ".OpenRecoedSet" from the line:

Set New_T_ID = CurrentDb.OpenRecoedSet("select @@identity")

from that line, I am trying to get the generated id for the recently added row

[The]

Another issue I am dealing with, The insertion update the last current record instead of adding new one. How can that be if I am using "INSERT" SQL command

[June7]

Fix the spelling of OpenRecoedSet to OpenRecordSet

You have @@ in table or query name? That SELECT statement is incomplete, need FROM clause.

The UPDATE statement is missing quote mark.

& New_T_ID & ", Current_Rent= "

However, I don't think referencing a recordset like that will work. Don't need recordset anyway. Need SQL string.

Dim New_T_ID As String
New_T_ID = "SELECT identity FROM sometable"

Why are you using INSERT and UPDATE actions? Are you not using bound form?

[amrut]

June is already helping you out.Is Lease_Date a date field? If yes, then code will fail as date should be surrounded by # instead of ' .

[The]

Fix the spelling of OpenRecoedSet to OpenRecordSet

You have @@ in table or query name? That SELECT statement is incomplete, need FROM clause.

The UPDATE statement is missing quote mark.

& New_T_ID & ", Current_Rent= "

However, I don't think referencing a recordset like that will work. Don't need recordset anyway. Need SQL string.

Dim New_T_ID As String
New_T_ID = "SELECT identity FROM sometable"

Why are you using INSERT and UPDATE actions? Are you not using bound form?

This form was a bound form, and I am trying to make it manually because I have good experience on MySQL than MS Access. My date is related between different table, and this is easier to me.

Should your code be like:

Dim New_T_ID As String
New_T_ID = CurrentDb.Execute ("SELECT identity FROM sometable")

Is that better?

I really appreciate your valuable help

[The]

Thanks sir, yah it is a date, and I didn't know that before.

Thanks

[June7]

Good catch on the date delimiters.

No, should be as I already showed: New_T_ID = "SELECT identity FROM sometable"

You don't want a recordset, you want SQL string to concatenate with the other SQL string and then Execute the constructed nested SQL statement.

[ssanfu]

Also note that Currentdb.Execute can only be used with action queries: "INSERT" (append), "UPDATE" or "DELETE".
It will not function with "SELECT" queries.

[The]

Thank you guys

All done