By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

Update Recordset From Code Problem

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
'---------------
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.