Double Click on a row in subform and open a related table/form using a common ID

[emsadoon]

I want to Double-Click on a row of a subform to open a related table/form that are connected with a common ID. So far, I tried the following code, but it does not work.

Private Sub PackingSlipT_subform_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ProductT"
stLinkCriteria = "[PackingSlip_ID]=" & Me.PackingSlipT_subform.Column(1)
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[CJ_London]

It looks like you have two problems:

stLinkCriteria = "[PackingSlip_ID]=" & Me.PackingSlipT_subform.Column(1)

should be

stLinkCriteria = "[PackingSlip_ID]=" & [PackingSlip_ID]

Assuming PackingSlip_ID is the name of the control in your subform

Secondly it looks like the code is in the wrong place

Private Sub PackingSlipT_subform_DblClick(Cancel As Integer)

Not sure where you have actually put the code (unless you have a control called PackingSlipT_subform) but it should be in your subform, possibly on the double click event of PackingSlip_ID but could also be on the form double click event so you can double click on the record selector

 

[emsadoon]

Thanks CJ_London. It worked.

 

[emsadoon]

For the above situation, is there a way to open a table/query that shows the current records related to the PackingSlip_ID. Is it also possible to add new records to that opened table.

 

[CJ_London]

yes - the form properties need to be set to Dataentry=no and allowadditions=yes. Also set allowedits = yes if you want to allow the user to change existing records and no if you don't.

If creating a new record, you will also need to populate the fields the user doesn't - an example may be PackingSlip_ID? - typically you would set the default in the form on current event or the value in the before update event.

Without knowing your table and form structure, it is difficult to advise further but you can determine the PackingSlip_ID from the openargs property, along the following lines:

Private Sub Form_Current()
    
    PackingSlip_ID.default=mid(me.openargs,instr(me.openargs,"=")+1)
 
End Sub

 

[emsadoon]

I tried above code in my form, but I get an error when I open the form. Just to make it clear, I have two forms( PackingSlip form and Product form), and as you mentioned I want to populate the PackingSlip_ID field on the new product form (when new record is added). In addition, being able to view/edit the previous product records related to the packingSlip ID.

 

[CJ_London]

Ah sorry, I was mixing openargs and filters

modify your openform to
DoCmd.OpenForm stDocName, , , stLinkCriteria,,,PackingSlip_ID

and the code in your subform event then just becomes

PackingSlip_ID.default=me.openargs

 

[emsadoon]

I may misunderstand. Please clear me if I am wrong. I put the following code:
Private Sub Form_Current()
PackingSlip_ID.default=me.openargs
End Sub

in my product form, but when I click on Packing_Slip ID on the PackingSlip table to open the product form, it gives the "Compile error: method or data member not found."

 

[CJ_London]

can you post the code you are using to open the product form

 

[emsadoon]

Private Sub PackingSlip_ID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ProductT"
stLinkCriteria = "[PackingSlip_ID]=" & [PackingSlip_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , PackingSlip_ID

End Sub

 

[CJ_London]

That bit looks OK to me

with regards this

Private Sub Form_Current()
PackingSlip_ID.default=me.openargs
End Sub

default should be appearing as Default and openargs as OpenArgs so is this is how it is appearing in your code - with the upper case letters?

If not then for some reason Access is not recognising the data members

Is PackingSlip_ID the name of your control in the subform? If not, it needs to be

 

[emsadoon]

I checked the code. But I am not sure about the the name of the control in my product form. Anyways, the relatioship between the packing slip and product is as following:

(PackingSlip_ID, Date)-||------0<(PackingSlip_ID(foreign key),Product_ID(primary key))

 

[CJ_London]

if the property sheet is not displayed, right click on the control which displays the packingslip_id, and select properties. Then click on the Other tab, the name of the control is the Name property.

It does occur to me that we are talking about packingslip id's in the product table - should this not be product id?

 

[emsadoon]

The control names are fine. The reason I have PackingSlip_ID in the product table, is to have a one to many relatioship ( many product_IDs for one packingslip_ID).

 

[emsadoon]

Thanks anyways CJ London, I actually solved the problem by putting =[Forms]![Order F]![PackingSlipT subform]![PackingSlip_ID] in the PackingSlip_ID default value in product form.