You can't assign a value to this object (1 Viewer)

[iglobalusa]

I'm trying to pass a field value (Me.Record_No) from Form A (frmAddDetails) as an OpenArgs to Form B (frmAddTest). Me.Record_no is an AutoNumber field. The code works until Form B (frmAddTest) is opened, accepts the record_no in Me.OpenArgs and finds the record_no match in a table called labdata so I can populate frmAddTest with certain fields from the recordset ADO_RS created from the labdata table.

I get this error messsage on Form B (frmAddTest): "You can't assign a value to this object" when the code starts assigning values to frmAddTest from the labdata recordset.

Can someone tell me what I'm doing wrong?

Code from Form A (frmAddDetails)

Option Compare Database 'Use database order for string comparisons
Option Explicit
Dim strFormName As String

Private Sub cmdaddlabtest_Click()
On Error GoTo Err_cmdaddlabtest_Click
strFormName = "frmAddTest"

'pass record_no an autonumber field to frmAddTest as Openargs
If Not Me.NewRecord Then

DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acWindowNormal, Me.record_no
​

End If
​

Exit_cmdaddlabtest_Click:

Exit Sub
​

Err_cmdaddlabtest_Click:

MsgBox Err.Description
Resume Exit_cmdaddlabtest_Click
​

End Sub

Code from Form B (frmAddTest) with additions allowed using acFormAdd when opening form

Option Compare Database 'Use database order for string comparisons
Option Explicit
Dim strFormName As String
Dim strSQL As String
Dim intrecord_no As Long

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

'check openargs
If Not IsNull(Me.OpenArgs) Then

intrecord_no = Me.OpenArgs
strSQL = "Select * from labdata Where record_no Like '" & intrecord_no & "'"
Set DAO_DB = CurrentDb()
Set DAO_RS = DAO_DB.OpenRecordset(strSQL, dbOpenDynaset)
With DAO_RS

'Find the record_no in openargs
.FindFirst "[record_no] = " & intrecord_no
If DAO_RS.NoMatch Then

MsgBox "Record_no no match!"
​

Else
( I get the error after Else)

Me.stateno = DAO_RS("stateno")
Me.site_cd = DAO_RS("site_cd")
Me.l_name = DAO_RS("l_name")
Me.f_name = DAO_RS("f_name")
Me.m_name = DAO_RS("m_name")
Me.phone = DAO_RS("phone")
Me.ssn = DAO_RS("ssn")
Me.addr_1 = DAO_RS("addr_1")
Me.addr_2 = DAO_RS("addr_2")
Me.city = DAO_RS("city")
Me.cnty = DAO_RS("cnty")
Me.state = DAO_RS("state")
Me.zip_code = DAO_RS("zip_code")
Me.cntry = DAO_RS("cntry")
Me.birth_dt = DAO_RS("birth_dt")
Me.gender = DAO_RS("gender")
Me.lab_clia_no = DAO_RS("lab_clia_no")
Me.accession_no = DAO_RS("accession_no")
Me.medrecno = DAO_RS("medrecno")
Me.prison_no = DAO_RS("prison_no")
'refresh screen
Me.Refresh
​

End If
​

End With
'close recordset
DAO_RS.Close
Set DAO_RS = Nothing
​

End If
​

Exit_Form_Open:

Exit Sub
​

Err_Form_Open:

MsgBox Err.Description
Resume Exit_Form_Open
​

End Sub

 

[ajetrumpet]

if code is reaDable, you might get more responses

 

[iglobalusa]

Thanks, Adam. Per your suggestion, I have made my code more readable.

 

[iglobalusa]

Resolved the error message by changing the FormOpen event to FormLoad on the second form, Form B:


Private Sub Form_Load() instead of Private Sub Form_Open(Cancel As Integer)

 

[missinglinq]

That's what I was just getting ready to suggest! The Form_Open event is too early to be assigning values to a record as the recordset hasn't really loaded at that point in time. I can't remember the last time I used Form_Open for anything, always using Form_Load instead.

Glad you got it resolved!

 

[iglobalusa]

Thanks, Missinglinq!

 

[iglobalusa]

Missinglinq:

By the way, while we are on the subject of forms, perhaps you can tell me how to use Me.Dirty when trying to detect if a user has edited a form bound to a table as its data source. I tried using Me.Dirty when a form was unloading and it just ignored Me.Dirty even if I had made changes to the field values on the form. Is there something I'm missing?

Thanks for your time.

 

[boblarson]

You need to use the BEFORE UPDATE event of the form for validation and then use

Cancel = True

if you want to stop the update. The Unload event is too late.

 

[iglobalusa]

You've helped me twice today! What a super guy!

Thanks a bunch,
iglobalusa

 

[The_Doc_Man]

Just to clarify - Form_Open occurs when you are still setting up the form, building the controls for the first time, and making the linkages. If you wanted to do recordset or operating system calls in the form's startup, it is best done in Form_Open.

The reason is that the form's class module is present and therefore, so are its variables. They can be initialized in a Form_Open. I put my security code in there to look up the username and user account properties based on a recordset operation and the username as defined by the Environ("Username") function call.

You don't really have a record yet for binding with the form. But you can use recordset operations anytime since they don't interfere with the rest of the form's operations. You also have form Load, Resize, and Activate, with a sequence that ends with Form_Current. That latter event occurs every time you do a save or navigate. (Or create a new record.)

 

[highandwild]

Instead of opening a recordset and populating controls on Form B you could open Form B from Form A using the DoCmd.openfrom command
and use the WHERE clause to specify which record to display?