VB Code Problem

[LeeSmith]

Hi, Im currently trying to write code to save records to a temporary table. This is the code I have so far:

Private Sub cmdSaC_Click()

Dim strSql As String
Dim rstSaveTemp As Recordset


Set dbs = CurrentDb

MsgBox "Are You Sure you want to save and continue this form at a later date", vbYesNo, "Save?"
If vbYes Then

strSql = "SELECT * FROM [tblStartSave]"
Set rstSaveTemp = dbs.OpenRecordset(strSql)
With rstSaveTemp.AddNew
!PmName = PmName


End With

Else

End If

When I get to the part of the code i've coloured red, I get the compile error: Expected function or variable. Does anyone have a clue as to why this might be?

 

[KenHigg]

Maybe your 'with' should be formed as:

With rstSaveTemp
.AddNew
!PmName = PmName
End With

I spot a few more potential errors but see if this gets you past the first error...

 

[LeeSmith]

Private Sub cmdSaC_Click()

Dim strSql As String
Dim rstSaveTemp As Recordset


Set dbs = CurrentDb

MsgBox "Are You Sure you want to save and continue this form at a later date", vbYesNo, "Save?"
If vbYes Then

strSql = "SELECT * FROM [tblStartSave]"
Set rstSaveTemp = dbs.OpenRecordset(strSql)
With rstSaveTemp.AddNew
!PmName = PmName


End With

Else

End If
?

That worked cheers Ken, Im now getting a runtime error from the highlighted line, any ideas?

 

[LeeSmith]

Hi All, still having problems with the higlighted code, does anyone have any ideas?

Private Sub cmdSaC_Click()

Dim strSql As String
Dim rstSaveTemp As Recordset
'Dim dbs As Access


Set dbs = CurrentDb



MsgBox "Are You Sure you want to save and continue this form at a later date", vbYesNo, "Save?"
If vbYes Then

strSql = "SELECT * FROM [tblStartSave]"
Set rstSaveTemp = dbs.OpenRecordset (strSql)
With rstSaveTemp
.AddNew

!PmName = PmName



End With



Else

End If