Not A Clue

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Enrico,

Sorry. I cut and pasted that procedure from one of mine and forgot to
change the reference. It should have referred to the Exit_SubHandler label:

Resume Exit_SubHandler

But, in any case, I’ve studied your original code more carefully. Its
design seems to meet your needs, but has some problems.

1. The right hand side of the strLinkCriteria assignment statement does not
evaluate to a valid string.
2. You appear to want to refer to a control on the subform for your linking
criteria, but are using the wrong syntax. You must use the Form property of
a subform control to refer to the subform itself, where its controls reside.
Without it you are merely referring to the main form *control* that
*contains* the subform.
3. The subform control and your linking field have the name Date, which is
a reserved word in Access. Using reserved words as field or control names
can result in unpredictable behavior. Give the field and the control a more
descriptive name like FaultDate.
4. If the code is failing on the Select Case statement, it could be that
the value of Me.RecordType is not the strings that you mention, but rather a
numeric code for these strings. If this is a combo box with its Bound Column
set to 1, and the first column width set to 0â€, the control will display the
first non-zero-width column, but store the first column.

After changing the names of your field and the subform control, change the
code line:

strLinkCriteria = "[Date] = Forms!UnitData!UnitDataSubq!Date"

to:

strLinkCriteria = "[FaultDate] = " &
Forms!UnitData!UnitDataSubq.Form![FaultDate]


To see what is being stored in RecordType, insert a debugging MsgBox
statement:

MsgBox "The value of RecordType is " & Me.RecordType

Hope that resolves it.
Sprinks

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.