Referencing subforms

E

elyse

How would I reference a subform that exists on a tab control which exists on
a main form?

Here's what I've written:

Dim db As Database
Dim rs As Recordset
Dim strsql1 As String
Dim qry As QueryDef

Set db = CurrentDb
Set qry = db.QueryDefs("QryEducatorLogistics")
Set rs = qry.OpenRecordset(dbOpenDynaset)
'strsql1 = Me!Combo47
rs.FindFirst "[REVIEW_COURSE]= '" & Me![Combo47] & "' "
If rs.NoMatch Then
MsgBox "No Entry Found.", vbInformation
Else
Forms!FrmEducatorInformationMain!Pages![CURRENT TRAVEL
ARRANGEMENTS]![FrmEducatorLogistics subform].Bookmark = rs.Bookmark
End If

Thanks
Elyse
 
M

Marshall Barton

elyse said:
How would I reference a subform that exists on a tab control which exists on
a main form?

Here's what I've written:

Dim db As Database
Dim rs As Recordset
Dim strsql1 As String
Dim qry As QueryDef

Set db = CurrentDb
Set qry = db.QueryDefs("QryEducatorLogistics")
Set rs = qry.OpenRecordset(dbOpenDynaset)
'strsql1 = Me!Combo47
rs.FindFirst "[REVIEW_COURSE]= '" & Me![Combo47] & "' "
If rs.NoMatch Then
MsgBox "No Entry Found.", vbInformation
Else
Forms!FrmEducatorInformationMain!Pages![CURRENT TRAVEL
ARRANGEMENTS]![FrmEducatorLogistics subform].Bookmark = rs.Bookmark
End If
Click to expand...


The recordset you've opened will probably have different
bookmarks than the form's RecordSource table/query, so I
would expect that to have problems.

If the code is running in the main form (the tab control is
irrelevant), then the code would be more like this:

With Me.[FrmEducatorLogistics subform].Form.RecordsetClone
.FindFirst "[REVIEW_COURSE]= '" & Me![Combo47] & "' "
If .NoMatch Then
MsgBox "No Entry Found.", vbInformation
Else
Me.[FrmEducatorLogistics subform].Form.Bookmark _
=.Bookmark
End If
End With
 
M

Marjorie

Please accept my apologies for replying with a new question, for some reason,
when I click on New, Question, nothing happens.

Please help if you can:

I have a combo box on a form (frmTest) that contains zipcodes. In the
'notinlist' function, I have working code that asks, "Do you want to add
'11122' to the zipcode table?" Yes/No. If you choose yes, then the data
entry form (frmZipcode) opens to add a zipcode, city, and state. Closing
this form saves the new zip, etc.; closes the zip data entry, and advances to
the next new record on frmTest.

The problem: If a person enters a zipcode (22211) on frmTest, and they
choose to enter it into frmZipcode, I do NOT want them to have to retype the
zipcode on frmZipcode...I want it to 'carry over' from the first form where
they first entered it. I know that I'm missing something easy
here...something wrong with referencing the field or something. I would
greatly appreciate any help.

Here's the code on the not in list:
Public Sub Zip_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add '" & NewData & "' to the zipcode table?",
vbOKCancel, "Add New Item?") = vbOK Then

'Remove new data from combo box so control requeries
DoCmd.RunCommand acCmdUndo

'Display Zipcode form to collect new data.
DoCmd.OpenForm "frmtblZipcode", acNormal, , , acFormAdd, acDialog,
NewData


'Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


End Sub


============
I was trying something like:
Forms![frmtblZipcode]![Zipcode] = NewData

HELP!!!!!!!!!!!!!!

Have a great day,
Marjorie
 
M

Marshall Barton

Marjorie said:
Please accept my apologies for replying with a new question, for some reason,
when I click on New, Question, nothing happens.

Please help if you can:

I have a combo box on a form (frmTest) that contains zipcodes. In the
'notinlist' function, I have working code that asks, "Do you want to add
'11122' to the zipcode table?" Yes/No. If you choose yes, then the data
entry form (frmZipcode) opens to add a zipcode, city, and state. Closing
this form saves the new zip, etc.; closes the zip data entry, and advances to
the next new record on frmTest.

The problem: If a person enters a zipcode (22211) on frmTest, and they
choose to enter it into frmZipcode, I do NOT want them to have to retype the
zipcode on frmZipcode...I want it to 'carry over' from the first form where
they first entered it. I know that I'm missing something easy
here...something wrong with referencing the field or something. I would
greatly appreciate any help.

Here's the code on the not in list:
Public Sub Zip_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add '" & NewData & "' to the zipcode table?",
vbOKCancel, "Add New Item?") = vbOK Then

'Remove new data from combo box so control requeries
DoCmd.RunCommand acCmdUndo

'Display Zipcode form to collect new data.
DoCmd.OpenForm "frmtblZipcode", acNormal, , , acFormAdd, acDialog,
NewData


'Continue without displaying default error message.
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
============
I was trying something like:
Forms![frmtblZipcode]![Zipcode] = NewData
Click to expand...


It's a matter of where you place the code to set the zipcode
in the new record. The code you tried should work, if it's
in the right place.

Try using the frmtblZipcode's Load event and code like this:

If Not IsNull(Me.OpenArgs) Then
Me.Zipcode = NewData
End If
 
D

David C. Holley

Create a global variable glbResponse. In the AfterUpdate() event of the
form that captures the new record, set glbResponse to the VBA constant
DATA_ERRADDED
Then in the OnNotInListFunction add the statement response = glbResponse.
DATA_ERRADDED indicates that a record was added, requery the combo box
and display it.

My actual implementation is over 7 years old. The technique above is the
way I would do it now, but may require some tweaking itself.

David H
 
Top