OpenForm on Subform on Tab Control

[kaneymac]

I've seen similar issues posted, but when I copy the answer code, it doesn't seem to work, so I'm hoping for clarification. Here's my hierarchy:

Main Form: "Account Details"
Tab Control: "TabCtl171"
Page: "Billed"
Sub Form: "Transactions Subform"

So Transactions Subform displays a continuous form of sales transactions with status "Billed" for the account showing on the main form. Page 2 and 3 are also the Transactions Subform but page 2 only lists transactions in status "Shipped", and page 3 only lists transactions in status "Received".

I need to be able to click the transaction ID on the current record on the current tab (so a transaction on the "Billed" page) and have it open a new form called "Transaction Details" filtered to that record.

I tried just using the OpenForm macro, and it works fine if I go straight from the "Transactions Subform" (opened independently as its own form). However if I click the TransactionID on Transactions Subform as it appears on the Billed Tab as a subform, it doesn't pass the TransactionID value.

The code I have right now is:

Private Sub TransactionID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transaction Details"
DoCmd.OpenForm stDoc, , , stLinkCriteria
End Sub

Am I missing something? Help is very appreciated as I have a tight deadline.

 

[kaneymac]

actually this is my current code:

Private Sub TransactionID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transaction Details"
stLinkCriteria = "Forms!Access_Details.Transactions_Subform.Form.TransactionID = TransactionID"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[vbaInet]

What about:

stLinkCriteria = Me!TransactionID

Welcome to AWF by the way

 

[kaneymac]

Private Sub TransactionID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transaction Details"
stLinkCriteria = "Me!TransactionID"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

That pops up a box asking for the value for Me!TransactionID.

 

[vbaInet]

Notice I didn't put Me!TransactionID in quotes as you've done.

 

[kaneymac]

doh!!

Private Sub TransactionID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transaction Details"
stLinkCriteria = Me!TransactionID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

It does open the form, but only takes me to the first of ## records.

 

[vbaInet]

Is TransactionID string or number?

 

[kaneymac]

I just tried...

Private Sub TransactionID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Transaction Details"
stLinkCriteria = "[TransactionID] = Forms![Transactions Subform].[TransactionID]"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

and it works great if I just view Transactions Subform independently (not from the main form). It goes straight to the same TransactionID on Transaction Details.

but if I put Transactions Subform on the page 1 of the tab control on Account Details, it asks for the value for Forms!Transactions Subform.TransactionID.

If I enter the TransactionID, then it still goes to the correct record on Transaction Details.

 

[kaneymac]

Is TransactionID string or number?

TransactionID is an AutoNumber in the Transactions Table.

 

[vbaInet]

Just use this:

DoCmd.OpenForm stDocName, , , Me!TransactionID

You don't need the strLinkCriteria variable.

 

[kaneymac]

Private Sub TransactionID_Click()
Dim stDocName As String
stDocName = "Transaction Details"
DoCmd.OpenForm stDocName, , , Me!TransactionID
End Sub

still just opens the form to the first record, not the correct TransactionID

 

[vbaInet]

Oops... my mistake, here you go:

DoCmd.OpenForm stDocName, , , "[TransactionID]=" & Me!TransactionID

 

[kaneymac]

lemme make sure I explained the "nesting" correctly, or whatever it's called.


Account Details (main form) ---> TabCtl171 (tab control) ---> Billed (tab page 1) ---> Transactions Subform (subform) ---> TransactionID (control)

I'm wondering if the fact that I have the same subform on 3 different pages in the same tab control, each just filtered different through Master-Child from the main form is causing it not to know which Transactions Subform I'm referring to?

 

[kaneymac]

Oops... my mistake, here you go:

DoCmd.OpenForm stDocName, , , "[TransactionID]=" & Me!TransactionID

YES IT WORKS BEAUTIFULLY!! If I knew who you are I'd pay you for that! I REALLY appreciate your time and troubleshooting. Thanks very much. I get to keep my job!!

 

[vbaInet]

YES IT WORKS BEAUTIFULLY!! If I knew who you are I'd pay you for that!

Maybe next time lol

I get to keep my job!!

Yay!!!

By the way, the nesting of subform controls within a tab or page in a tab doesn't really matter, you can reference the subform control because the names are unique.

 

[kaneymac]

By the way, the nesting of subform controls within a tab or page in a tab doesn't really matter, you can reference the subform control because the names are unique.

Yeah I SAW that and this thing was makin me an unbeliever. If I hit "add to reputation" do you at least get some kudos or something for helping me?

 

[vbaInet]

Yeap, I believe so

Glad it worked for you.

Here's a good link on referering to controls:

http://www.mvps.org/access/forms/frm0031.htm

 

[mafhobb]

OK, this code works great if what you wanted to see was the subform opened independently from its main form.

In my case, I have a main form (Contacts) with a continuous subform (call listing subform) and what I want to do it to double click on a listbox (lstsearch), listing a particular call (callID) and have it open the subform (call listing subform) within the main form and set the focus to the correct record in the subform (matching callID)

The code in the double click event (thanks for other people's help) in the listbox is the following:

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Contacts"
    stLinkCriteria = "[ContactID]=" & Me![lstsearch]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

      With Forms(stDocName)![Call Listing Subform]
              .SetFocus
              .Form!CallID.SetFocus
       End With

This opens the form and subform, but the focus goes to the 1st CallID item in the list, not the one I need.

I know I need to get the value for CallID from the listbox, and then I need to add some code at the end of this to tell it to go to that field. I have tried openargs at the suggestion of someone else, but I a still not getting the results I need...This programing is above my level.

Does anyone have any suggestion???

mafhobb

 

[mafhobb]

I have tried to use

                .FindFirst "CallID = " & Me![lstsearch]

but it tells me that this method cannot be used.

mafhobb

 

[mafhobb]

Anyone has any suggestions?