How to Open a Form and Subform based on a Listbox

[IJOC]

Hi:

This is my first post to this forum! I am a fairly novice user of MS Access. I am creating a trade database for sports cards. I want to be able to have a listbox of past due trades that a user can click on a particular trade and it will open the Trading Partner form (frmTradingPartner with a pkeyEmail) and the related trade in the Transaction Subform (frmTransactionsSubform with a pkeyTradeID). I have search the web and have not been very successful in figuring this problem out. Below id the code that I thought would work the best but it does not seem to find the pkeyEmail. I am sure it is a real simple fix. Thanks again for your assistance!

Private Sub lstIncomingAging_DblClick(Cancel As Integer)
Dim frm As Form, sfrm As Form, Cri As String

DoCmd.OpenForm "frmTradingPartners"
DoEvents
Set frm = Forms!frmTradingPartners
Set sfrm = frm!frmTransactionsSubform.Form

Cri = "[pkeyEmail] = " & Me!pkeyEmail
frm.Recordset.FindFirst Cri

Cri = "pkeyTradeID" = " & Me!pkeyTradeID"
sfrm.Recordset.FindFirst Cri

Set sfrm = Nothing
Set frm = Nothing
End Sub

 

[Remou]

If the trade in tblTransactions is related, what key is it related on? This key should be used for the Link Child and Link Master fields. As regards opening the form in the first place, it should be possible to use a key from the listbox to open to the correct record:

[tt]DoCmd.OpenForm "frmTradingPartners",,,"SomeNumericKey=" & Me.ListBoxNameHere[/tt]

This would suppose that the listbox row source is something on the lines of:

[tt]SELECT SomeID, Description FROM tblTransactions WHERE PastDueDate<=Date()[/tt]

ColumnCount : 2
Column Widths : 0,2

Do not forget to use the Northwind sample database as learning tool:

http://www.microsoft.com/downloads/...72-8DBE-422B-8676-C632D66C529C&displaylang=en

And Finally ...

Welcome to Tek-Tips! Don't forget to read the FAQs.

http://lessthandot.com

 

[IJOC]

I can do that without a problem, but if I want to go to a specific transaction that is were the problem is.

The tblTransactions is related via lkpEmail to the tblTrading Partners pkeyEmail.

Control Source for the lstIncomingAging is:
SELECT qryIncomingAging.pkeyTradeID, qryIncomingAging.Name, qryIncomingAging.dtmDateConfirmed, qryIncomingAging.Days, qryIncomingAging.pkeyTradeID, qryIncomingAging.pkeyEmail
FROM qryIncomingAging
WHERE (((qryIncomingAging.Days)>10))
ORDER BY qryIncomingAging.dtmDateConfirmed, qryIncomingAging.pkeyTradeID, qryIncomingAging.Name;

So lets Partner A has 2 active trades and they are both listed in the list box as TradeID 20 and 56. When I click on Trade ID 20 I want to open the frmTradingPartners and the related record in the frmTransactionsSubform.

I hope this make sense. Thanks again!

 

[Remou]

Ok, you have email listed as one of the fields in your listbox, this means that you can use the column property to get that value:

[tt]DoCmd.OpenForm "frmTradingPartners",,,"pkeyEmail='" & Me.lstIncomingAging.Column(4) & "'"[/tt]

Note that columns are counted from zero. I guess Email is text?

http://lessthandot.com

 

[IJOC]

That did seem to do either. It pull the trading partner but it did not get the correct record in the subform. Any other ideas?

 

[Remou]

Did you set up the Link Child and Link Master fields for the subform?

http://lessthandot.com

 

[IJOC]

Yes that was created when I originally created the form. I not sure if I am making sense with my request.

Table tblTradingPartners
pkeyEmail (text)

Table tblTransactions
pkeyTradeID (auto)
lkpEmail (lookup to the tblTradingPartners.pkeyEmail)

The frmTradingPartners has a subform of frmTransactionsSubform. Everyhting on the form works as it should.

On another form called frmActionItems I have a unbound listed box called lstIncomingAging that uses the control source that I listed earlier. Now lets say the listbox contains two enteries for John Doe ie:

Trade ID Name Date Confirmed Email Address
24 John Doe 01/01/2009 john.doe@hotmail.com
46 John Doe 01/04/2009 john.doe@hotmail.com

So when a user clicks on Trade ID 24 it will open the frmTradingPartners and display Trade ID in the frmTransactionsSubform. Currently, if I clicked on Trade ID 24 it would open the frmTradingPartners and display Trade ID 46. This is because of the order by on the form right now.

I hope this makes it a little clearer. Sorry for the lengthy read and thanks again for all the help

-Pat

 

[Remou]

Ok, I think I understand, frmTransactionsSubform is not a continuous form, is that correct? You were nearly there, so after a little wandering:

'Why does pkeyTradeID occur twice?
'
'SELECT
'0  qryIncomingAging.pkeyTradeID,
'1  qryIncomingAging.Name,
'2  qryIncomingAging.dtmDateConfirmed,
'3  qryIncomingAging.Days,
'4  qryIncomingAging.pkeyTradeID,
'5  qryIncomingAging.pkeyEmail
'FROM qryIncomingAging

'There is no need to refer to the column property for bound columns
intTradeID = Me.lstIncomingAging.Column(0)
strEmail = Me.lstIncomingAging.Column(5)

DoCmd.OpenForm "frmTradingPartners", , , "pkeyEmail='" _
        & strEmail & "'"

'Note that frmTransactionsSubform must be the name of the subform
'control, not the form contained by the control.

Set frm = Forms!frmTradingPartners.frmTransactionsSubform.Form

Set rs = frm.RecordsetClone
rs.FindFirst "pkeyTradeID=" & intTradeID

If Not rs.NoMatch Then
    frm.Bookmark = rs.Bookmark
Else
    'Problem
End If

http://lessthandot.com

 

[IJOC]

Thank You Thank You Thank You! That did the trick. This makes sense to me now that I see the code. Thanks everyone for helping me out!

-Pat