you can't assign a value to this object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This problem occurs in the on-click event of an unbound combo box in a
nested subform. When a record is selected from the combo box, I want to
update that record with the crewid from the parent form. When I try to
assign the value, I get the message "you can't assign a value to this
object". How should I be referring to this field to make this work? Here
is the code with the error line indicated:

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
On Error GoTo List2_Click_Error
' Set Rst = Me.RecordsetClone

stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![dailylog].[Form]![cboTdate] & "# )"
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(stsql, dbOpenDynaset)

STWHERE = "empno = " & Me![cboEmpno]
Rst.FindFirst STWHERE
' the following statement should never be true
If Rst.NoMatch Then
MsgBox "no match"
End If

Me!crewno = Me.Parent!cboCrewid <======= error occurs here

Me.Dirty = False
Me.Requery
Me.cboEmpno.Requery


List2_Click_exit:
On Error GoTo 0
Exit Sub

List2_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
List2_Click of VBA Document Form_members subform"
Resume List2_click_exit
end sub
 
You have misunderstood the usage of recordsets. Although you intially
assign the form's recordsetclone to the variable rst, your subsequent
OpenRecordset statement assigns a completely different recordset to the same
variable i.e. it no longer has any relation whatsoever to your form. Hence
your efforts to find a particular record will find that record in some
arbitrary recordset, NOT in your form.

If you want to open a recordset (Rst) and make it your form's recordset then
you should do this:

Set Me.Recordset = Rst

Although you would almost certainly be better off simply giving the form a
RecordSource property and then filtering/navigating the
recordset/recordsetclone provided by Access.
 
Thank you for your reply. As you indicate, I am confused. The form has a
record source which selects records with the crew id from the parent form.
Initially, there will be none of these. The unbound combo box on the form
is to select records to be added to the crew (by giving the record the crew
id). Once this has happened, the record would then become part of the form's
recordset when the form is requeried. This is why I need a separate
recordset - to get the crew id into the records in the first place. (The
statement with the recordset clone had been commented out.) Is there some
better way to do what I am trying to do? This is my first complex access
form - maybe I am going about it completely the wrong way.

Baz said:
You have misunderstood the usage of recordsets. Although you intially
assign the form's recordsetclone to the variable rst, your subsequent
OpenRecordset statement assigns a completely different recordset to the same
variable i.e. it no longer has any relation whatsoever to your form. Hence
your efforts to find a particular record will find that record in some
arbitrary recordset, NOT in your form.

If you want to open a recordset (Rst) and make it your form's recordset then
you should do this:

Set Me.Recordset = Rst

Although you would almost certainly be better off simply giving the form a
RecordSource property and then filtering/navigating the
recordset/recordsetclone provided by Access.


SandyR said:
This problem occurs in the on-click event of an unbound combo box in a
nested subform. When a record is selected from the combo box, I want to
update that record with the crewid from the parent form. When I try to
assign the value, I get the message "you can't assign a value to this
object". How should I be referring to this field to make this work? Here
is the code with the error line indicated:

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
On Error GoTo List2_Click_Error
' Set Rst = Me.RecordsetClone

stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![dailylog].[Form]![cboTdate] & "# )"
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(stsql, dbOpenDynaset)

STWHERE = "empno = " & Me![cboEmpno]
Rst.FindFirst STWHERE
' the following statement should never be true
If Rst.NoMatch Then
MsgBox "no match"
End If

Me!crewno = Me.Parent!cboCrewid <======= error occurs here

Me.Dirty = False
Me.Requery
Me.cboEmpno.Requery


List2_Click_exit:
On Error GoTo 0
Exit Sub

List2_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
List2_Click of VBA Document Form_members subform"
Resume List2_click_exit
end sub
Click to expand...
Click to expand...
 
Back
Top