Help! Filter not filtering - Access does not support this property or method (1 Viewer)

[veraloopy]

My database has a section where 'quotes' are created based on a 'follow up' and a 'demo' for a specific 'customer'

I have a form 'frmQuoteLink' with 2 subforms as we have 2 completely different types of quotes that we do. Each record on the subform contains a 'CustRef', 'CustFlwUpID' and a 'CustDemoID'

From these fields I need to create a 'Quote', each quote has to reference the CustRef, CustFlwUpID and CustDemoID, so I have the following code in place to set the filter to these fields and then open the form 'frmQuotes' using this information.

If a quote has already been created, the QuoteID is used to filter instead.

The problem is that when I try to open the form (using a command button), I keep getting an error saying that this property or method is not supported.

Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String
 
If Me.QuoteNumber = "" Or IsNull(QuoteNumber) Then
stDocName = "frmQuotes"
strFilter = "[CustRef] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv]![CustRef] & " AND [CustFlwUpID] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv].[CustFlwUpID] & " AND [CustDemoID] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv].[CustDemoID]
stLinkCriteria = strFilter
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Else
stDocName = "frmQuotes"
strFilter = "[QuoteID] = " & [Forms]![frmQuoteLink]![CreateQuoteConv]![QuoteID]
stLinkCriteria = strFilter
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Any help would be greatly appreciated as I have to give a presentation on this tomorrow morning

 

[JamesMcS]

Hi, I think you've got too many commas in there, and you don't need to make stlinkcriteria = strfilter. Try

DoCmd.OpenForm "frmQuotes", , strfilter

 

[veraloopy]

Tried that but its still showing the same error, on debug, it shows this line as having the problem:

strFilter = "[CustRef] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv]![CustRef] & " AND [CustFlwUpID] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv].[CustFlwUpID] & " AND [CustDemoID] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv].[CustDemoID]

 

[JamesMcS]

Hmm OK. What data types are custref, custflwupid etc.? All numbers? If any of them are strings you'd need to encapsulate them in quotes. Also try building the filter one element at a time and see if there's one particular one that's causing a problem.

 

[veraloopy]

They are all number fields...

How can I build the filter one at a time? sorry i've only ever done them as above

 

[JamesMcS]

Sorry, I meant start with

strFilter = "[CustRef] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv]![CustRef]

, then

strFilter = "[CustRef] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv]![CustRef] & " AND [CustFlwUpID] = " & [Forms]![frmQuoteLink]![frmSubQuoteLinkConv].[CustFlwUpID]

Just experiment to see if one particular one is causing the problem.

 

[JamesMcS]

And you're only going for two commas in the openform line yeah? If you have a look in the help file at docmd.openform, it'll explain what each of the arguments are for that command, and where in the line of code they should be.

 

[veraloopy]

Thanks for all your help James, just figured it out...

For some reason, it didn't like the fact that I had used the 'bang' symbol and the fullstop for the same command.

![CustRef] and .[CustFlwUpID]

Changed the both to '!' symbols and it seems to be working fine now ;-)

Once again, many thanks for all your help

 

[JamesMcS]

Whoops didn't spot that. Glad it's OK! Good luck with your presentation - I had to do one the other day, it's a breeze! (Nobody knows what you're talking about!)