VB code works in subform, but not in main form

[cswosu]

Hello all. I just found this forum; hopefully someone can help me with a problem I'm having.

I have a command button in a subform with VB code that executes upon clicking the button. The point of the code is to check a value on a table and determine which report should be opened. I'll post the code for reference:

******

Private Sub View_Reports_Click()
On Error GoTo Err_View_Reports_Click

Dim stDocName As String

'This code checks where the PSV vents to and opens the appropriate report form

Dim idTest As Variant

idTest = DLookup("[Vents To]", "PSV Data", "[Tag Number] = Forms![PSV Release Subform]![PSV Number]")

If idTest = 2 Or idTest = 3 Or idTest = 4 Or idTest = 15 Then
stDocName = "PSV Flare Release Subreport"
DoCmd.OpenReport stDocName, acPreview, , "[Record No] = Forms![Release Calculation Form]![Record No]"
Else
stDocName = "PSV Release Subreport"
DoCmd.OpenReport stDocName, acPreview, , "[Record No] = Forms![Release Calculation Form]![Record No]"
End If

Exit_View_Reports_Click:
Exit Sub

Err_View_Reports_Click:
MsgBox Err.Description
Resume Exit_View_Reports_Click

End Sub

******

The button is in subform "PSV Release Subform". When I click the button with just the subform open, it will prompt me for the value of "Forms![Release Calculation Form]![Record No]" since that form is not open. I can manually enter the value and the code will execute fine.

But if I open "Release Calculation Form" that has the "PSV Release Subform" in it, and click on the command button in the subform, I just get an Access error message box that says, "You canceled the previous operation." I would have expected the code to be able to find the value of "Forms![Release Calculation Form]![Record No]" and execute normally.

Does anyone know why the subform command button won't work when it is clicked in the main form?

Thanks in advance.

 

[KenReay]

Hi

Try

Parent.[Record No]

in place of

"Forms![Release Calculation Form]![Record No]"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

Thanks for the quick response. I'm sorry to report that the code substitution didn't work. I still get prompted for the value of "Parent.[Record No]" in the subform, and the "canceled operation" message in the main form.

Any other suggestions?

 

[KenReay]

Hi

If you run the sub form in isolation you will get prompted for the parametr value that is to be expected, since the Forms! syntax expects the relevant form to be open, and the Parent. syntax expects that the subform is running as a subform,

so can we just concentrate on what happens when you are running it "as intended" ie as a subform

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

Sure thing.

Here's the rundown - I open form "Release Calculation Form". "PSV Release Subform" exists as a subform in this form. I fill in all the relevant data in the form and subform, click the command button in the subform in order to open a report, and get the "canceled operation" message.

 

[KenReay]

Hi

have you done a compile to check for syntax errors

have yo tried stpping through the code to see where it gets to

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cswosu]

Ken,

I put a break in the code so I could step through it. It seems that it is jumping from the DLookup line straight down to "MsgBox Err.Description" near the end of the code. Any ideas why it would trip up on the DLookup like that? I'm going to keep digging.

Thanks again for your help.

 

[RoyVidar]

Hi!

Would be nice to know the errormessage too...

Guessing:
1 - one often needs to concatinate the criteria part
2 - reference

[tt]idTest = DLookup("[Vents To]", "PSV Data", "[Tag Number] = " & Me![PSV Number])[/tt]

pt 1 - note the concatination of the value into the criteria vs the control reference/name. Sometimes (when opening reports is one) one doesn't need to concatinate, but I never remember when one can and when one cannot, so I always concatinate;-)
pt 2 - to reference controls on the current form in code, one can use the Me keyword.

And ensure that you are using the correct control, field and table names (parhaps also add [brackets] around the table name for good measures?)

Here's a Microsoft link on How to Refer to a Control on a Subform or Subreport.

Roy-Vidar

 

[cswosu]

Ken,

Just got it to work. Had to change my DLookup line from

idTest = DLookup("[Vents To]", "PSV Data", "[Tag Number] = Forms![PSV Release Subform]![PSV Number]")

to

idTest = DLookup("[Vents To]", "PSV Data", "[Tag Number] = [PSV Number]")

Guess I didn't need that extra stuff in there referencing the field in the form that the code was excuting in.

I had to change Parent.[Record No] back though. It tripped up on that.

But everything is working now so I'm happy. Thanks for your help.