Open a pop up form based on a selection made in a subform 2

[jschneider998]

Hello,

I have a form with a subform listing vendors. The subform is in the datasheet view and has several entries. I would like to be able to double click on any of the vendors in the subform and have a pop up form launch giving more detailed information on that particular vendor selected. I have the VBA code for this process as follows:

Dim stDocName as String
Dim stLinkCriteria As String

stDocName = "frmVendorInfo"
stLinkCriteria = "VendorID"

DoCmd. OpenForm stDocName,,,stLinkCriteria, acReadOnly

Exit_cmdChange_click:
Exit Sub

The problem I am having is that no matter which record I select in the datasheet, the pop up form only displays the very first record of the vendor table. How can I set the link criteria to allow for the possiblity of multiple selections?

 

[Remou]

Hi
I am not sure I get what you mean, but I think this:
[tt]stLinkCriteria = "VendorID"[/tt]
Should read something like:
[tt]stLinkCriteria = "VendorID =" & Me.VendorID[/tt]
Assuming your code is on the subform, and that VendorID is numeric.

 

[Pampers]

I think the problem is that the subform with VendorID is continuous (dataview), so Me.Vendor.ID will take the first VendorID it sees on the subform (=always to same), no matter which Vendor click in the subform.

But I'm sorry I can't provide you with a solution... just don't know how.


Pampers.

You're never too young to learn

 

[Remou]

Hi Pampers
I do not think this is true. I haved used subforms, both continuous and datasheet, with a double-click event to show more detail. It is quite useful, you should try it.

 

[MajP]

Here is one way:
I have
tblPersonnel with personnel data
tblFamilyMembers with their family member data
frmPersonnel based on tblPersonnel
subFormFamilyMembers with the name limited info on Family members
frmFamilyMembers with detailed info

In the subforms module

Private Sub strName_Click()
Dim intFamilyMemberID As Integer
Dim strCriteria As String
intFamilyMemberID = txtBxFamilyMemberID.Value
strCriteria = "autoDependentID = " & intFamilyMemberID
DoCmd.OpenForm "frmFamilyMembers", , , strCriteria
End Sub

if I would print out strCriteria I would get:
autoDependentID = 65
This then is converted into a sql where clause behind the scenes:
where autoDependentID = 65
In your original post your strlinkCriteria would be
65
This converts to
where 65
which is meaningless

What pampers was saying is definately incorrect
Me.controlName is short hand for
me.controls.item("control name").value
This returns the value of the control of the current record. You can prove this easily, by doing this to any text box on a continous form:

Private Sub ControlName_Click()
msgbox ControlName.value
End Sub

 

[Remou]

Hi Pampers and MajP
In jschneider998's original post s/he said stLinkCriteria = "VendorID", which must be a mistake as it would result in Where VendorID (text) not, for example, an ID of 65. I imagined a set-up consisting of Products, with a subform containing ProductID, VendorID. I have a similar set-up (Access 2000, originally 97), where I have Members with a Journals Subform and the following code in the double-click event:

<...>
Else 'Title code has been added

    vFind = "[Title_Code]=" _
        & Forms![Members]![Jnlsmem_subform].Form![Title_Code]
    
    DoCmd.OpenForm "Journal_Titles", , , vFind

And it works. It seemed to me that Pampers was saying this will not work, but perhaps that is not the case.

So, in what way am I not understanding you both?

 

[Pampers]

Sorry Guys,
I was plain wrong. I tested it as well, and it works fine.

I was thinking that i couldnot work because Me.Id would refer to ID on the form; since there is more then one ID, it will take the first ID it sees. But Me.ID refers to the control your in/at or has focus (nice test MajP).
Here is my code:

Private Sub Consignee_DblClick(Cancel As Integer)
On Error GoTo Err_Consignee_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEnterConsignee_popup"
    
    stLinkCriteria = "[ConsigneeID]=" & Me![ConsigneeID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Consignee_DblClick:
    Exit Sub

Err_Consignee_DblClick:
    MsgBox Err.Description
    Resume Exit_Consignee_DblClick
End Sub

Pampers.

You're never too young to learn

 

[MajP]

Pampers,
VB has created a double edge sword with the "sloppy" coding standards and the use of default events, methods, properties, collections. So your confusion is understandable. Yes the short hand is nice if you understand it, but a good chunk of the questions on this site are due to not understanding what is really going on.
Why does the following print out the value in the field ID? Logically this should not work, but we do it all the time.

msgbox Me.ID

The only reason this works is because VB allows this shorthand for what really is happening.

msgbox Me.Controls.item("ID").value

Me: refers to the instantiated class which is the form.

Controls: is the default object of a form which happens to be a collection

Item: is the default method of any collection

ID: is an index of a control in the controls collection

Value: is the default property of a control object

So in words I want to return the form object, get the collection of controls on the form, use the item method to return a specific control by its index, and use the value property to get its value.

So start with "Me". Don't need collection because it is the default, don't need item because it is the default, and don't need value because it is the default. So I am left with only the required:
Me.ID

Pretty damn sloppy, and this is why a lot of people do not understand why things work in VB.

Also Me.ID has absolutely nothing to do with the Field ID in any recordset. This refers only to the control ID with the same name. Another grand source of confustion. And do not get me started on the very sloppy bang notation. Half the people on this who call them selves programmers do not understand it.

 

[Pampers]

Tanks for the teaching MajP. I really appriciate it. It is the only way to learn this stuff.


Pampers.

You're never too young to learn