goto specific subfrm recordset

D

Daniel

Appearantly I have a lot of questions today!

I need to have an event that will 1 goto a specific frm record and then goto
a specific subfrm record. I have a functional code for the 1st part (goto
specific frm record) but am encountering problems with the latter. Below is
the code that I have thus far:

***
Private Sub lstRptNum_DblClick(Cancel As Integer)

Dim ctlList As Control
Dim varItem As Variant
Dim strRptNumID As String
Dim strRptIssueID As String
Dim strRptNum As String
Dim strRptIssue As String

'Return Control object variable pointing to list box.
Set ctlList = Me.lstRptNum
'Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
strRptNum = ctlList.Column(0, varItem)
strRptIssue = ctlList.Column(1, varItem)
Forms![Stress Reports Frm].Form.[Stress Report Number Lookup] =
strRptNum
Next varItem

'Goto Specific Report Number
Call Forms("Stress Reports Frm").Stress_Report_Lookup_Button_Click

'Goto Specific Report Issue
Dim rs As Object

strRptNumID = DLookup("[Stress Report ID]", "Stress Reports - General
Info Tbl", "[Stress Report Number]='" & strRptNum & "'")
strRptIssueID = DLookup("[Stress Report Issue AutoNumber]", "Stress
Reports - Issue Info Tbl", "[Stress Report AutoNumber] =" & strRptNumID & "
AND [Stress Report Issue]='" & strRptIssue & "'")

Set rs = Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].RecordsetClone

'Forms![Stress Reports Frm].[Stress Reports Frm - Issue SubFrm].

rs.FindFirst "[Stress Report Issue AutoNumber]=" & strRptIssueID
If rs.NoMatch Then
MsgBox "Unable to locate Stress Report Number " & strRptNum & "." &
vbCrLf & "Please verify the Stress Report Number and try again.",
vbInformation, "Stress Report Number Not Found"
Else
Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Bookmark = rs.Bookmark
End If

End Sub
***

I am currently getting an error with the set rs line stating that it does
not support this method? If this is the case, then how can I retrieve the
subfrm record where "[Stress Report Issue AutoNumber]=" & strRptIssueID?

Thank you,

Daniel
 
D

Dirk Goldgar

Daniel said:
Appearantly I have a lot of questions today!

I need to have an event that will 1 goto a specific frm record and
then goto a specific subfrm record. I have a functional code for the
1st part (goto specific frm record) but am encountering problems with
the latter. Below is the code that I have thus far:

***
Private Sub lstRptNum_DblClick(Cancel As Integer)

Dim ctlList As Control
Dim varItem As Variant
Dim strRptNumID As String
Dim strRptIssueID As String
Dim strRptNum As String
Dim strRptIssue As String

'Return Control object variable pointing to list box.
Set ctlList = Me.lstRptNum
'Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
strRptNum = ctlList.Column(0, varItem)
strRptIssue = ctlList.Column(1, varItem)
Forms![Stress Reports Frm].Form.[Stress Report Number Lookup]
= strRptNum
Next varItem

'Goto Specific Report Number
Call Forms("Stress Reports Frm").Stress_Report_Lookup_Button_Click

'Goto Specific Report Issue
Dim rs As Object

strRptNumID = DLookup("[Stress Report ID]", "Stress Reports -
General Info Tbl", "[Stress Report Number]='" & strRptNum & "'")
strRptIssueID = DLookup("[Stress Report Issue AutoNumber]",
"Stress Reports - Issue Info Tbl", "[Stress Report AutoNumber] =" &
strRptNumID & " AND [Stress Report Issue]='" & strRptIssue & "'")

Set rs = Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].RecordsetClone

'Forms![Stress Reports Frm].[Stress Reports Frm - Issue SubFrm].

rs.FindFirst "[Stress Report Issue AutoNumber]=" & strRptIssueID
If rs.NoMatch Then
MsgBox "Unable to locate Stress Report Number " & strRptNum &
"." & vbCrLf & "Please verify the Stress Report Number and try
again.", vbInformation, "Stress Report Number Not Found"
Else
Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Bookmark = rs.Bookmark
End If

End Sub
***

I am currently getting an error with the set rs line stating that it
does not support this method? If this is the case, then how can I
retrieve the subfrm record where "[Stress Report Issue AutoNumber]="
& strRptIssueID?
Click to expand...

Without looking into your code too deeply, I can see that you need one
more level of qualifier, to get to the Form object being displayed by
the subform control on the main form. Where you have this ...
Set rs = Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].RecordsetClone
Click to expand...

.... change it to this:

Set rs = Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Form.RecordsetClone

(Correct the line wrap, of course.)

And later, where you have this ...
Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Bookmark = rs.Bookmark
Click to expand...


.... change it to this:

Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Form.Bookmark = rs.Bookmark

(Again, correct the line wrap.)

If it were me, I'd probably use a With block to avoid those long lines;
e.g.,

With Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Form

Set rs = .RecordsetClone

' ....

.Bookmark = rs.Bookmark

End With
 
D

Daniel

I solved my own problem after finding another post answered by Dirk. Below
is the solution for anyone following.

****
Dim ctlList As Control
Dim varItem As Variant
Dim strRptNumID As String
Dim strRptIssueID As String
Dim strRptNum As String
Dim strRptIssue As String

'Return Control object variable pointing to list box.
Set ctlList = Me.lstRptNum
'Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
strRptNum = ctlList.Column(0, varItem)
strRptIssue = ctlList.Column(1, varItem)
Forms![Stress Reports Frm].Form.[Stress Report Number Lookup] =
strRptNum
Next varItem

MsgBox Me.lstRptNum.ItemData.Count

'Goto Specific Report Number
Call Forms("Stress Reports Frm").Stress_Report_Lookup_Button_Click

'Goto Specific Report Issue
Dim rs As Object

strRptNumID = DLookup("[Stress Report ID]", "Stress Reports - General
Info Tbl", "[Stress Report Number]='" & strRptNum & "'")
strRptIssueID = DLookup("[Stress Report Issue AutoNumber]", "Stress
Reports - Issue Info Tbl", "[Stress Report AutoNumber] =" & strRptNumID & "
AND [Stress Report Issue]='" & strRptIssue & "'")

Set rs = Forms("Stress Reports Frm").Controls("Stress Reports Frm -
Issue SubFrm").Form.RecordsetClone

rs.FindFirst "[Stress Report Issue AutoNumber]=" & strRptIssueID
If rs.NoMatch Then
MsgBox "Unable to locate Stress Report Number " & strRptNum & "." &
vbCrLf & "Please verify the Stress Report Number and try again.",
vbInformation, "Stress Report Number Not Found"
Else
Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Form.Bookmark = rs.Bookmark
End If

'Cleanup
Forms![Stress Reports Frm].Form.[Stress Report Number Lookup] = Null
****




Daniel said:
Appearantly I have a lot of questions today!

I need to have an event that will 1 goto a specific frm record and then goto
a specific subfrm record. I have a functional code for the 1st part (goto
specific frm record) but am encountering problems with the latter. Below is
the code that I have thus far:

***
Private Sub lstRptNum_DblClick(Cancel As Integer)

Dim ctlList As Control
Dim varItem As Variant
Dim strRptNumID As String
Dim strRptIssueID As String
Dim strRptNum As String
Dim strRptIssue As String

'Return Control object variable pointing to list box.
Set ctlList = Me.lstRptNum
'Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
strRptNum = ctlList.Column(0, varItem)
strRptIssue = ctlList.Column(1, varItem)
Forms![Stress Reports Frm].Form.[Stress Report Number Lookup] =
strRptNum
Next varItem

'Goto Specific Report Number
Call Forms("Stress Reports Frm").Stress_Report_Lookup_Button_Click

'Goto Specific Report Issue
Dim rs As Object

strRptNumID = DLookup("[Stress Report ID]", "Stress Reports - General
Info Tbl", "[Stress Report Number]='" & strRptNum & "'")
strRptIssueID = DLookup("[Stress Report Issue AutoNumber]", "Stress
Reports - Issue Info Tbl", "[Stress Report AutoNumber] =" & strRptNumID & "
AND [Stress Report Issue]='" & strRptIssue & "'")

Set rs = Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].RecordsetClone

'Forms![Stress Reports Frm].[Stress Reports Frm - Issue SubFrm].

rs.FindFirst "[Stress Report Issue AutoNumber]=" & strRptIssueID
If rs.NoMatch Then
MsgBox "Unable to locate Stress Report Number " & strRptNum & "." &
vbCrLf & "Please verify the Stress Report Number and try again.",
vbInformation, "Stress Report Number Not Found"
Else
Forms![Stress Reports Frm].[Stress Reports Frm - Issue
SubFrm].Bookmark = rs.Bookmark
End If

End Sub
***

I am currently getting an error with the set rs line stating that it does
not support this method? If this is the case, then how can I retrieve the
subfrm record where "[Stress Report Issue AutoNumber]=" & strRptIssueID?

Thank you,

Daniel
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Programming help (debug) 1
VBA works in orignal but fails in copy of database 1
Use HTML tags in e-mail Message 10
Transferspreadsheet anomoly (A2007) 0
Removing OrderBy from Tables and Queries in VBA 2
code simplification question 1
Multiple Criteria in Listbox 3
My string does not want to execute 5

Top