Help with stLinkCriteria wanted

[midiman69]

Hi
I Have a form "frmmanufacturers" that has a subform listing parts supplied by a particular manufacturer. I wish to be able to double click the part number [partno] field on the subform which will open another form displaying the matching record.

I am trying the following - with no luck!

Private Sub partno_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmcompapp"

stLinkCriteria = "[partno] = " & Me.partno

DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.RunCommand acCmdApplyFilterSort

Exit_partno_DblClick:
Exit Sub
Err_partno_DblClick:
MsgBox Err.Description
Resume Exit_partno_DblClick
End Sub

This generates a Runtime error 2501 "The openform action was canceled"

Can any one help?

 

[alvechurchdata]

If partno is text then you could try:

stLinkCriteria = "[partno] = '" & Me.partno & "'"

because you're building a criterion string which wants to look something like "[partno]='test'" and the target part number needs quotes on both sides of it.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PaulF]

Why this?
DoCmd.RunCommand acCmdApplyFilterSort
are you attempting to apply a filter on your current form?

the form "frmcompapp" will open filtered to your Where Clause contained in the stLinkCriteria variable

Since you are passing the value from a subform control you need to identify it differently

Me!SubFormName.form!partno

One question... is the field partno Numeric? if not then you need to add delimiters to stLinkCriteria

stLinkCriteria = "[partno] = '" & Me!SubFormName.form!partno & "'"

PaulF

 

[midiman69]

Ahhhhhhhhh - Its a text field! (I'll never get that hang of this VBA!!)

stLinkCriteria = "[partno] = '" & Me![partno] & "'"

Works well - thanks for your help guys.

PaulF think I added the DoCmd.RunCommand acCmdApplyFilterSort in a fit of panic - it works with or without it. I have removed it as I am trying to filter another form.

Thanks again