Unhiding a detail form by double-clicking the primary key in a sub

[Michael Stanbarger]

Hello,

I am working on building a database from the ground up and am fairly new
to this, but I have created a form that has everything nested so there are no
pop-up forms/windows. Most of the people that are going to use this need it
to be ID10T proof.

The way I have it set up, I have a main form [frmDefaultMain], which has
several hidden subforms. When a user clicks on the navbuttons on the bar on
the right, the subform becomes visible in the view area of frmDefaultMain.

What I am trying to do at this point is this...

frmDefaultMain has a tabbed subform frmManagement, which is comprised of
subforms respectively.

On one tab [pgArchive] I have 2 visible subforms and 1 hidden

The visible subforms are [frmFilters] and [frmArchives].
[frmFilters] is a list of comboboxes and text boxes which do allow multiple
critera filtering of [frmArchives] which defaults to all archived records in
a table. The hidden form is a detail form for a single record in the
[frmArchives] list.
What I want to happen is the manager to be able to double click the Ticket#
in the Archives list on [frmArchives] and it open that record in the
[frmArchiveTicketDisplay] subform that is hidden. I have it become visible
fine, but it is always opening the very first record in the table,
disregarding the filterset. Is there a way I can have the
[frmArchiveTicketDisplay] go to that specific record.

I am able to get the Primary Key value into a variable in the code, but
can't seem to figure out how to get the subform to display the details of
that record. I have tried DoCmd.GoToRecord, but that only goes by offsets,
not specific records, and I have tried to Filter the subform, but get the
error that the object does not support that property.

This is the code I have tried:

Private Sub oTicketNumber_DblClick(Cancel As Integer)
Dim strRecord As String
Dim strForm As String


strRecord = "([TicketNumber] = " & Me.[oTicketNumber] & ")" --------->
Here the correct [TicketNumber] is being assigned
strForm =
"Forms![frmDefaultMain]![frmManagement].Form![frmArchiveTicketDisplay]"

strForm.Visible = True
strForm.SetFocus

strForm.Filter = strRecord <-------- This is where the debugger gets the
error saying that the form is unable to be filtered.
strForm.FilterOn = True

' DoCmd.GoToRecord acDataForm,,acGoTo,Me.[oTicketNumber] <- I tried this
hoping it would offset to this Primary Key number. No luck.

Exit_oTicketNumber_DblClick:
Exit Sub

Err_oTicketNumber_DblClick:
MsgBox Err.Description
Resume Exit_oTicketNumber_DblClick
End Sub

Kind regards,
Michael

 

[Marshall Barton]

I am working on building a database from the ground up and am fairly new
to this, but I have created a form that has everything nested so there are no
pop-up forms/windows. Most of the people that are going to use this need it
to be ID10T proof.

The way I have it set up, I have a main form [frmDefaultMain], which has
several hidden subforms. When a user clicks on the navbuttons on the bar on
the right, the subform becomes visible in the view area of frmDefaultMain.

What I am trying to do at this point is this...

frmDefaultMain has a tabbed subform frmManagement, which is comprised of
subforms respectively.

On one tab [pgArchive] I have 2 visible subforms and 1 hidden

The visible subforms are [frmFilters] and [frmArchives].
[frmFilters] is a list of comboboxes and text boxes which do allow multiple
critera filtering of [frmArchives] which defaults to all archived records in
a table. The hidden form is a detail form for a single record in the
[frmArchives] list.
What I want to happen is the manager to be able to double click the Ticket#
in the Archives list on [frmArchives] and it open that record in the
[frmArchiveTicketDisplay] subform that is hidden. I have it become visible
fine, but it is always opening the very first record in the table,
disregarding the filterset. Is there a way I can have the
[frmArchiveTicketDisplay] go to that specific record.

I am able to get the Primary Key value into a variable in the code, but
can't seem to figure out how to get the subform to display the details of
that record. I have tried DoCmd.GoToRecord, but that only goes by offsets,
not specific records, and I have tried to Filter the subform, but get the
error that the object does not support that property.

This is the code I have tried:

Private Sub oTicketNumber_DblClick(Cancel As Integer)
Dim strRecord As String
Dim strForm As String


strRecord = "([TicketNumber] = " & Me.[oTicketNumber] & ")" --------->
Here the correct [TicketNumber] is being assigned
strForm =
"Forms![frmDefaultMain]![frmManagement].Form![frmArchiveTicketDisplay]"

strForm.Visible = True
strForm.SetFocus

strForm.Filter = strRecord <-------- This is where the debugger gets the
error saying that the form is unable to be filtered.
strForm.FilterOn = True

I think the error is because you have defined strForm as a
string variable and then tried to use it as a form object.
You would need to use something more like:

Dim frm As Form
Set frm = Parent![frmArchiveTicketDisplay].Form
frm.Filter = strRecord

BUT, most of that is unecessary and you probably should not
be using a filter to do it

Instead, try adding a hidden text box (named txtTicketLink
to frmManagement. Then set the frmArchiveTicketDisplay
subform control's link master property to txtTicketLink and
link child to TicketNumber. These properties will then
automatically synchronize the two forms.

The final step is to set txtTicketLink. This would usually
be done by using a line of code in frmArchives Current
event:
Parent.txtTicketLink = Me.oTicketNumber
but if you really need to manage the visibility of the
detail data, it could be done just before you make it
visible.

 

[Michael Stanbarger]

Thanks, I think I figured it out. (duh) I just create a query showing the
entire tblTicketArchives records, then have that filter based on the field
clicked in the frmArchives... Base the frmArchiveTicketDisplay on the query
and voila, it should only have the one record in it. I will have to test this
once I get back to work tonight. If this does not work, I will test your
suggestion as well. Thanks for your timely reply!
--
Michael Stanbarger
Air Freight Coordinator
CRST Van Expedited

I am working on building a database from the ground up and am fairly new
to this, but I have created a form that has everything nested so there are no
pop-up forms/windows. Most of the people that are going to use this need it
to be ID10T proof.

The way I have it set up, I have a main form [frmDefaultMain], which has
several hidden subforms. When a user clicks on the navbuttons on the bar on
the right, the subform becomes visible in the view area of frmDefaultMain.

What I am trying to do at this point is this...

frmDefaultMain has a tabbed subform frmManagement, which is comprised of
subforms respectively.

On one tab [pgArchive] I have 2 visible subforms and 1 hidden

The visible subforms are [frmFilters] and [frmArchives].
[frmFilters] is a list of comboboxes and text boxes which do allow multiple
critera filtering of [frmArchives] which defaults to all archived records in
a table. The hidden form is a detail form for a single record in the
[frmArchives] list.
What I want to happen is the manager to be able to double click the Ticket#
in the Archives list on [frmArchives] and it open that record in the
[frmArchiveTicketDisplay] subform that is hidden. I have it become visible
fine, but it is always opening the very first record in the table,
disregarding the filterset. Is there a way I can have the
[frmArchiveTicketDisplay] go to that specific record.

I am able to get the Primary Key value into a variable in the code, but
can't seem to figure out how to get the subform to display the details of
that record. I have tried DoCmd.GoToRecord, but that only goes by offsets,
not specific records, and I have tried to Filter the subform, but get the
error that the object does not support that property.

This is the code I have tried:

Private Sub oTicketNumber_DblClick(Cancel As Integer)
Dim strRecord As String
Dim strForm As String


strRecord = "([TicketNumber] = " & Me.[oTicketNumber] & ")" --------->
Here the correct [TicketNumber] is being assigned
strForm =
"Forms![frmDefaultMain]![frmManagement].Form![frmArchiveTicketDisplay]"

strForm.Visible = True
strForm.SetFocus

strForm.Filter = strRecord <-------- This is where the debugger gets the
error saying that the form is unable to be filtered.
strForm.FilterOn = True

I think the error is because you have defined strForm as a
string variable and then tried to use it as a form object.
You would need to use something more like:

Dim frm As Form
Set frm = Parent![frmArchiveTicketDisplay].Form
frm.Filter = strRecord

BUT, most of that is unecessary and you probably should not
be using a filter to do it

Instead, try adding a hidden text box (named txtTicketLink
to frmManagement. Then set the frmArchiveTicketDisplay
subform control's link master property to txtTicketLink and
link child to TicketNumber. These properties will then
automatically synchronize the two forms.

The final step is to set txtTicketLink. This would usually
be done by using a line of code in frmArchives Current
event:
Parent.txtTicketLink = Me.oTicketNumber
but if you really need to manage the visibility of the
detail data, it could be done just before you make it
visible.