Not A Clue

[enrico1982]

I have created a button that opens a related record from a subform, like the
'See Product Details' button in the Access 2002 sample database 'orders'. My
difference is that the subform is connected to a union query and so the
related records are in a few forms. I was recently suggested this code. It
Looks like what i want to do but gets stuck on Me.RecordType. As i have
virtually no knoledge of basic i dont know what to do:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

Select Case Me.RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23": strDocName = "CD23"
Case "DA36": strDocName = "DA36"

End Select

strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

Where RecordType is the field in the subform which determines what form
should be opened.

Thanks in advance

 

[Sprinks]

Hello, Enrico.

Your related records are in a few *tables*, not forms. Forms are merely a
formatted container that displays table data (or data calculated from one or
more tables). Simply create your Union query, base the form you'd like to
open on the query, and add a command button on the first form with the
following code in its OnClick procedure:

On Error GoTo Err_Handler

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourSecondFormName"

stLinkCriteria = "[LinkingFieldInQuery]=" & Me![YourControlInCurrentForm]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "YourCurrentFormName", acSaveYes

Exit_SubHandler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_cmdOpenDistributorForm_Click

Sprinks

 

[rico]

Thanks Spinks,

I have tried the code and it comes up with an Compile error: Lable not
define on line: Resume Exit_cmdOpenDistributorForm_Click.

I also do not see anywhere in the code where the forms i want to pop up
would be mentioned. In the original code posted these are called -
FaultreportForm, CD23, DA36.

Thanks again

Hello, Enrico.

Your related records are in a few *tables*, not forms. Forms are merely a
formatted container that displays table data (or data calculated from one or
more tables). Simply create your Union query, base the form you'd like to
open on the query, and add a command button on the first form with the
following code in its OnClick procedure:

On Error GoTo Err_Handler

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourSecondFormName"

stLinkCriteria = "[LinkingFieldInQuery]=" & Me![YourControlInCurrentForm]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "YourCurrentFormName", acSaveYes

Exit_SubHandler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_cmdOpenDistributorForm_Click

Sprinks

I have created a button that opens a related record from a subform, like the
'See Product Details' button in the Access 2002 sample database 'orders'. My
difference is that the subform is connected to a union query and so the
related records are in a few forms. I was recently suggested this code. It
Looks like what i want to do but gets stuck on Me.RecordType. As i have
virtually no knoledge of basic i dont know what to do:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

Select Case Me.RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23": strDocName = "CD23"
Case "DA36": strDocName = "DA36"

End Select

strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

Where RecordType is the field in the subform which determines what form
should be opened.

Thanks in advance

 

[Dirk Goldgar]

I have created a button that opens a related record from a subform,
like the 'See Product Details' button in the Access 2002 sample
database 'orders'. My difference is that the subform is connected to
a union query and so the related records are in a few forms. I was
recently suggested this code. It Looks like what i want to do but
gets stuck on Me.RecordType. As i have virtually no knoledge of basic
i dont know what to do:

Private Sub ViewCurrentRecord_Click()

Dim strDocName As String
Dim strLinkCriteria As String

On Error GoTo ErrorHandler

Select Case Me.RecordType

Case "Fault Report": strDocName = "FaultReportForm"
Case "CD23": strDocName = "CD23"
Case "DA36": strDocName = "DA36"

End Select

strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End

Exit Sub

ErrorHandler:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
End Sub

Where RecordType is the field in the subform which determines what
form should be opened.

Thanks in advance

See my reply in the original thread. Note that it isn't good
"netiquette" to start a new thread on a subject while the original
thread is still active. And in newsgroups, threads shouldn't be
considered "dead" until there have been no new responses for a day or
two.