Problem with DoCmd.OpenForm Command

J

John Ortt

This post is a follow-on from my previous post entitled: Help with a filter
when opening a form

Katrina has been a great help and put me on the right track but I am unable
to find the problem dispite her help.

I have a summary form which gives the total value of sales for the month.
When the user clicks on a record it opens another form which In want to
display the detail records for that month.

The problem centres around the fact that I am trying to set the source data
for the Detail table in the code on the summary report. This is because I
use the same form design for lots of queries but all the others are less
complex than this one.

The code breaks at the following point:

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

The form opens if I remove the "VendorMarginsDDQuery" section, which makes
me believe that it is the assigning of the source data which is causing
problems.

I have tried all of Katrina's suggestions with the following results:

1. All fields do contain data of some sort.
2. No fields are numeric - so the "'" text qualifier is OK
3. All of the fields used are included in the recordset

Thankyou for your time,

John.


I have posted my code below for further reference:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

'stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
'stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
'stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
'stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

'stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and "
& stLinkCriteria3 & " and " & stLinkCriteria4
DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

End Function
 
A

Allen Browne

Hi John

Your OpenForm statemenet has a comma missing (for the View argument).

Aside from that, trying to use both a FilterName and a WhereCondition at
once is not a good idea. Missed the previous thread, but I have never found
the FilterName part or OpenForm or OpenReport useful, and I suspect that it
is inconsistently implemented across versions of Access. There has to be a
better way.

Normally you would use the WhereCondition to automatically apply a filter
when you open a form. If you really do need to try to change the
RecordSource of the form conditionally on where it is opened from, you might
consider passing the complete RecordSource query statement in the OpenArgs
argument of OpenForm, and then checking and applying it in FormOpen.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Ortt said:
This post is a follow-on from my previous post entitled: Help with a filter
when opening a form

Katrina has been a great help and put me on the right track but I am unable
to find the problem dispite her help.

I have a summary form which gives the total value of sales for the month.
When the user clicks on a record it opens another form which In want to
display the detail records for that month.

The problem centres around the fact that I am trying to set the source data
for the Detail table in the code on the summary report. This is because I
use the same form design for lots of queries but all the others are less
complex than this one.

The code breaks at the following point:

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

The form opens if I remove the "VendorMarginsDDQuery" section, which makes
me believe that it is the assigning of the source data which is causing
problems.

I have tried all of Katrina's suggestions with the following results:

1. All fields do contain data of some sort.
2. No fields are numeric - so the "'" text qualifier is OK
3. All of the fields used are included in the recordset

Thankyou for your time,

John.


I have posted my code below for further reference:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

'stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
'stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
'stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
'stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

'stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and "
& stLinkCriteria3 & " and " & stLinkCriteria4
DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

End Function
Click to expand...
 
J

John Ortt

Solved it.......sort of....

Instead of trying to assign the record source in the code, I have put the
VendorMarginsDDQuery in as the default record source on the form. This now
makes my code work, but the other queries which use the same form are
loading up the VendorMarginsDDQuery data before refreshing to the new data
source.

Is this normal and is there any way to stop it?
Or whould I be better carrying on with my original method of assigning the
source data when the form is opened?

Thanks again,

John
 
J

John Ortt

Allen Browne said:
Hi John

Your OpenForm statemenet has a comma missing (for the View argument).

Aside from that, trying to use both a FilterName and a WhereCondition at
once is not a good idea. Missed the previous thread, but I have never found
the FilterName part or OpenForm or OpenReport useful, and I suspect that it
is inconsistently implemented across versions of Access. There has to be a
better way.

Normally you would use the WhereCondition to automatically apply a filter
when you open a form. If you really do need to try to change the
RecordSource of the form conditionally on where it is opened from, you might
consider passing the complete RecordSource query statement in the OpenArgs
argument of OpenForm, and then checking and applying it in FormOpen.
Click to expand...

Thanks for the reply Allen,

I think I get the jist of what you are saying but I have to confess my
coding skills are pretty limitted.

Could you give me an example chunk of code on how you do this please?

Thanks in Advance,

John
 
A

Allen Browne

If you want to the form to use VendorMarginsDDQuery instead of its normal
recordsource, pass it in the OpenArgs like this:

DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria,
OpenArgs:="VendorMarginsDDQuery"


Then in the Open event of the form:

Private Form_Open(Cancel As Integer)
If Len(Nz(Me.OpenArgs, vbNullString)) > 0 Then
Me.RecordSource = Me.OpenArgs
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Ortt said:
Solved it.......sort of....

Instead of trying to assign the record source in the code, I have put the
VendorMarginsDDQuery in as the default record source on the form. This now
makes my code work, but the other queries which use the same form are
loading up the VendorMarginsDDQuery data before refreshing to the new data
source.

Is this normal and is there any way to stop it?
Or whould I be better carrying on with my original method of assigning the
source data when the form is opened?

Thanks again,

John



John Ortt said:
This post is a follow-on from my previous post entitled: Help with a filter
when opening a form

Katrina has been a great help and put me on the right track but I am unable
to find the problem dispite her help.

I have a summary form which gives the total value of sales for the month.
When the user clicks on a record it opens another form which In want to
display the detail records for that month.

The problem centres around the fact that I am trying to set the source data
for the Detail table in the code on the summary report. This is because I
use the same form design for lots of queries but all the others are less
complex than this one.

The code breaks at the following point:

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

The form opens if I remove the "VendorMarginsDDQuery" section, which makes
me believe that it is the assigning of the source data which is causing
problems.

I have tried all of Katrina's suggestions with the following results:

1. All fields do contain data of some sort.
2. No fields are numeric - so the "'" text qualifier is OK
3. All of the fields used are included in the recordset

Thankyou for your time,

John.


I have posted my code below for further reference:

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

'stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
'stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
'stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
'stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

'stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & "
Click to expand...
and
"
& stLinkCriteria3 & " and " & stLinkCriteria4
DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with a filter when opening a form 4
My VB to open a form isn't working 5
Type Mismatch 2
Enter Parameter Value Error 4
Access Access DLookup Function – more than 1 criteria 0
open form multiple criteria 3
Opening forms with same ID no 2
stLinkCriteria 2

Top