Update Recordset From Code Problem

[Corey]

I am missing something here. I have a pop up form (loads from the
"main form")that displays multiple command buttons. When a user
selects a particular button, the recordset from the main form should be
updated. Here is the code that I have written but it doesnt work. I
get too few parameters on rs1. Can anyone shed some light on what I am
missing? I appreciate help in advance-Corey
Private Sub APPRVD_Click()

Dim dbs As Database, rs As Recordset, rs1 As Recordset

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("QRY_NOTES_UPDATE")
Set rs1 = dbs.OpenRecordset("Select PROVISIONAL_STATUS From
CANDIDATES Where CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID.Value")

With rs
..AddNew
!CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID
!NOTE_DATE = Now()
!NOTE = "Status Change To Approved"
!NOTE_TYPE = "Status Change"
!NOTE_CREATOR = UserName()
..Update
..Close
End With

With rs1
..Update
!PROVISIONAL_STATUS = "APPROVED"
..Close
End With
End Sub

[John Mishefske]

Corey wrote:

I am missing something here. I have a pop up form (loads from the
"main form")that displays multiple command buttons. When a user
selects a particular button, the recordset from the main form should be
updated. Here is the code that I have written but it doesnt work. I
get too few parameters on rs1. Can anyone shed some light on what I am
missing? I appreciate help in advance-Corey
Private Sub APPRVD_Click()

Dim dbs As Database, rs As Recordset, rs1 As Recordset

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("QRY_NOTES_UPDATE")
Set rs1 = dbs.OpenRecordset("Select PROVISIONAL_STATUS From
CANDIDATES Where CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID.Value")

With rs
.AddNew
!CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID
!NOTE_DATE = Now()
!NOTE = "Status Change To Approved"
!NOTE_TYPE = "Status Change"
!NOTE_CREATOR = UserName()
.Update
.Close
End With

With rs1
.Update
!PROVISIONAL_STATUS = "APPROVED"
.Close
End With
End Sub

Looks like this part of your code needs revision:

With rs1
.AddNew
!PROVISIONAL_STATUS = "APPROVED"
.Update
.Close
End With
--
'---------------
'John Mishefske
'---------------

[Steve Jorgensen]

On 23 Jan 2005 02:16:08 -0800, "Corey" <co**********@yahoo.com> wrote:

I am missing something here. I have a pop up form (loads from the
"main form")that displays multiple command buttons. When a user
selects a particular button, the recordset from the main form should be
updated. Here is the code that I have written but it doesnt work. I
get too few parameters on rs1. Can anyone shed some light on what I am
missing? I appreciate help in advance-Corey
Private Sub APPRVD_Click()

Dim dbs As Database, rs As Recordset, rs1 As Recordset

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("QRY_NOTES_UPDATE")
Set rs1 = dbs.OpenRecordset("Select PROVISIONAL_STATUS From
CANDIDATES Where CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID.Value")

With rs
.AddNew
!CAN_ID = Forms!CANDIDATE_PROFILE!CAN_ID
!NOTE_DATE = Now()
!NOTE = "Status Change To Approved"
!NOTE_TYPE = "Status Change"
!NOTE_CREATOR = UserName()
.Update
.Close
End With

With rs1
.Update
!PROVISIONAL_STATUS = "APPROVED"
.Close
End With
End Sub

John's right, but of course, that's not your problem because your code can't
get that far. I'm assuming your QRY_NOTES_UPDATE uses parameters to know what
record to select, and since you don't provide parameters in your code, I
presume you were trying to get a parameter from a form control - that won't
work here.

When you execute a query via the UI, Access automatically resolves any
parameters that refer to controls on open forms, but this does not happen when
you open a DAO recordset in code. You;ll have to use a Querydef object, and
assign the parameter value in code before opening the recordset using the
..OpenRecordset method of the Querydef object.