References to inactive form

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

Guest

I need to manipulate the recordset of a loaded form from code outside of the
form. References to Forms!myForm.Recordset return the error "Object variable
or With Block Variable not set "
Procedures within the form's code module work fine when the form has focus,
but how do I call them from other modules? Calls to Forms!myForm.mySub()
return the error "Type mismatch"
What are the magic incantations that I'm missing?
Environment: Access 2002, Reference to DAO 3.6 turned on.
Thanks.
 
Hi, Ted.
I need to manipulate the recordset of a loaded form from code outside of the
form.
Click to expand...

Depending upon your needs, this can be probably be done with the form's
RecorsetClone Property. In the following example of the syntax, txtStuff is
any control that can receive the focus as soon as the button is clicked,
frmTC is the name of the open form whose recordset needs manipulation, and
txtID is the name of the text box bound to the primary key of the table:

Private Sub CloseBtn_Click()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim fOpenedRecSet As Boolean

Me!txtStuff.SetFocus
Me.Dirty = True
Forms("frmTC").Requery
Set recSet = Forms("frmTC").RecordsetClone
fOpenedRecSet = True

If (Not (recSet.BOF And recSet.EOF)) Then
recSet.FindFirst "ID = " & Me!txtID.Value

If (Not (recSet.NoMatch)) Then
Forms("frmTC").Bookmark = recSet.Bookmark
Else
MsgBox "Cannot find record matching " & vbCrLf & """ID = " & _
Me!txtID.Value & """", vbExclamation + vbOKOnly, _
"Record Not Found!"
End If
End If

Forms("frmTC").Visible = True
DoCmd.Close acForm, Me.Name

CleanUp:

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in CloseBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub
Procedures within the form's code module work fine when the form has focus,
but how do I call them from other modules?
Click to expand...

Try:

Call Form_frmMain.MySub
or:
Call Form_frmMain.cmdApplyFilter_Click

.... where frmMain is the name of the form, MySub is a public sub, and
cmdApplyFilter_Click is the click event of a button named cmdApplyFilter. Of
course, frmMain must be open at the time this code is executed.
What are the magic incantations that I'm missing?
Click to expand...

"Next time, I'm going to hire an expert to do this for me!"

One must truly be sincere when uttering this incantation, or else it won't
work. ;-)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
This worked for me. I'm assuming an MDB, not an ADP?

Public Sub GetRecordsetFromForm()

Dim rst As DAO.Recordset
Dim fld As DAO.Field

If Not CurrentProject.AllForms("frmTest").IsLoaded Then
DoCmd.OpenForm "frmTest"
End If
Set rst = Forms("frmTest").Recordset
With rst
.MoveFirst
Do Until .EOF
For Each fld In .Fields
Debug.Print fld.Name, fld.Value
Next fld
Debug.Print "-----"
.MoveNext
Loop
End With

End Sub
 
Back
Top