Problem with DoCmd.OpenForm Command

  • Thread starter Thread starter John Ortt
  • Start date Start date
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
 
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...
 
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
 
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
 
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...
 
Back
Top