docmd.openform: Filter being ignored

[keers]

Hi All,

I have a form which the user is able to select a customer, once
seletected a second form is presented which will enable the user to
update the customer contact details. The second form is to be
restricted to the required customer by using the filter parameter of
the docmd.openform command;

DoCmd.OpenForm "frmCustomer", acNormal, , """ID = " & Me!
[frmRenewalLettersByDate subform]!CustID & """", acFormEdit, acDialog

However, the filter is being ignored and regardless of which customer
is selected, another customers details always appears (and always the
same one(?)).

i have tried numerous contructs, but none seem to work, including
assinging a variable to create the filter string i.e.

IDFilter = """ID = " & Me![frmRenewalLettersByDate subform]!CustID &
""""
MsgBox "Cust id=" & IDFilter


The output from the msgbox appears (to me) to create the correct
syntax;

"ID = 546765438"

ID is the correct field name as detailed in the table to which the
second form is bound.

I am using Access 2003

Any help would be apprecited - Thanks

Keers

 

[Dirk Goldgar]

In

Hi All,

I have a form which the user is able to select a customer, once
seletected a second form is presented which will enable the user to
update the customer contact details. The second form is to be
restricted to the required customer by using the filter parameter of
the docmd.openform command;

DoCmd.OpenForm "frmCustomer", acNormal, , """ID = " & Me!
[frmRenewalLettersByDate subform]!CustID & """", acFormEdit, acDialog

However, the filter is being ignored and regardless of which customer
is selected, another customers details always appears (and always the
same one(?)).

i have tried numerous contructs, but none seem to work, including
assinging a variable to create the filter string i.e.

IDFilter = """ID = " & Me![frmRenewalLettersByDate subform]!CustID &
""""
MsgBox "Cust id=" & IDFilter


The output from the msgbox appears (to me) to create the correct
syntax;

"ID = 546765438"

ID is the correct field name as detailed in the table to which the
second form is bound.

I am using Access 2003

Any help would be apprecited - Thanks

Keers

It looks to me as if you are adding extra, improper quotes to the filter
string. Try this:

DoCmd.OpenForm "frmCustomer", acNormal, , _
"ID = " & Me![frmRenewalLettersByDate subform]!CustID, _
acFormEdit, acDialog

If CustID is a text field, then you do need to add some quotes, but not
where you put them. You would do this:

DoCmd.OpenForm "frmCustomer", acNormal, , _
"ID = """ & _
Me![frmRenewalLettersByDate subform]!CustID & _
"""", _
acFormEdit, acDialog

 

[Dale Fye]

Two things, I think you got your quotes wrong, but if CustID as a control on
the subform, I think you also have the syntax of that portion of things
wrong. Try:

"ID = """ & Me![frmRenewalLettersByDate subform].Form!CustID & """"

I hate having to use multiple quotes to represent a single one. I've got a
function that does it for me. Mine might look like:

"ID = " & Quotes(Me![frmRenewalLettersByDate subform].Form!CustID)

Public Function Quotes(TextToQuote as Variant) as string

Quotes = chr$(34) & TextToQuote & chr$(34)

End function.

HTH
Dale

 

[Dirk Goldgar]

In

Two things, I think you got your quotes wrong, but if CustID as a
control on the subform, I think you also have the syntax of that
portion of things wrong. Try:

"ID = """ & Me![frmRenewalLettersByDate subform].Form!CustID & """"

Normally, the reference syntax Keers posted,

Me![frmRenewalLettersByDate subform]!CustID

will work fine. The version you posted is more complete, and I believe
that in Access 2003 and later there may be some circumstances where the
shorter reference syntax may cause a problem, but it should be fine
here.

 

[keers]

In

Hi All,

I have a form which the user is able to select a customer, once
seletected a second form is presented which will enable the user to
update the customer contact details. The second form is to be
restricted to the required customer by using the filter parameter of
the docmd.openform command;

DoCmd.OpenForm "frmCustomer", acNormal, , """ID = " & Me!
[frmRenewalLettersByDate subform]!CustID & """", acFormEdit, acDialog

However, the filter is being ignored and regardless of which customer
is selected, another customers details always appears (and always the
same one(?)).

i have tried numerous contructs, but none seem to work, including
assinging a variable to create the filter string i.e.

IDFilter = """ID = " & Me![frmRenewalLettersByDate subform]!CustID &
""""
MsgBox "Cust id=" & IDFilter

The output from the msgbox appears (to me) to create the correct
syntax;

"ID = 546765438"

ID is the correct field name as detailed in the table to which the
second form is bound.

I am using Access 2003

Any help would be apprecited - Thanks

Keers

It looks to me as if you are adding extra, improper quotes to the filter
string. Try this:

DoCmd.OpenForm "frmCustomer", acNormal, , _
"ID = " & Me![frmRenewalLettersByDate subform]!CustID, _
acFormEdit, acDialog

If CustID is a text field, then you do need to add some quotes, but not
where you put them. You would do this:

DoCmd.OpenForm "frmCustomer", acNormal, , _
"ID = """ & _
Me![frmRenewalLettersByDate subform]!CustID & _
"""", _
acFormEdit, acDialog

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Dirk,

thanks for the solution - worked a perfectly.

Regards

Keers

 

[keers]

Two things, I think you got your quotes wrong, but if CustID as a control on
the subform, I think you also have the syntax of that portion of things
wrong. Try:

"ID = """ & Me![frmRenewalLettersByDate subform].Form!CustID & """"

I hate having to use multiple quotes to represent a single one. I've got a
function that does it for me. Mine might look like:

"ID = " & Quotes(Me![frmRenewalLettersByDate subform].Form!CustID)

Public Function Quotes(TextToQuote as Variant) as string

Quotes = chr$(34) & TextToQuote & chr$(34)

End function.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi All,

I have a form which the user is able to select a customer, once
seletected a second form is presented which will enable the user to
update the customer contact details. The second form is to be
restricted to the required customer by using the filter parameter of
the docmd.openform command;

DoCmd.OpenForm "frmCustomer", acNormal, , """ID = " & Me!
[frmRenewalLettersByDate subform]!CustID & """", acFormEdit, acDialog

However, the filter is being ignored and regardless of which customer
is selected, another customers details always appears (and always the
same one(?)).

i have tried numerous contructs, but none seem to work, including
assinging a variable to create the filter string i.e.

IDFilter = """ID = " & Me![frmRenewalLettersByDate subform]!CustID &
""""
MsgBox "Cust id=" & IDFilter

The output from the msgbox appears (to me) to create the correct
syntax;

"ID = 546765438"

ID is the correct field name as detailed in the table to which the
second form is bound.

I am using Access 2003

Any help would be apprecited - Thanks

Keers- Hide quoted text -

- Show quoted text -

Dale,

thanks for the tip - Quotes are a minefield (to me), so your function
will be of great help!

Regards

Kieran

 

[Dale Fye]

The Quotes function one I sent you didn't include some other thing that you
might want to consider.

1. If the text you want to quote has a quotation mark in it, you might want
to insert a Replace function to replace quotes with single quotes.
2. If you might use this with a SQL database, you might want to modify it
so that you pass it the character you want to use as a wrapper (in Access we
use quotes, SQL Server uses the apostrophe), and consider the concept of
Item #1 above along with this.

Dale

Two things, I think you got your quotes wrong, but if CustID as a
control on
the subform, I think you also have the syntax of that portion of things
wrong. Try:

"ID = """ & Me![frmRenewalLettersByDate subform].Form!CustID & """"

I hate having to use multiple quotes to represent a single one. I've got
a
function that does it for me. Mine might look like:

"ID = " & Quotes(Me![frmRenewalLettersByDate subform].Form!CustID)

Public Function Quotes(TextToQuote as Variant) as string

Quotes = chr$(34) & TextToQuote & chr$(34)

End function.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi All,

I have a form which the user is able to select a customer, once
seletected a second form is presented which will enable the user to
update the customer contact details. The second form is to be
restricted to the required customer by using the filter parameter of
the docmd.openform command;

DoCmd.OpenForm "frmCustomer", acNormal, , """ID = " & Me!
[frmRenewalLettersByDate subform]!CustID & """", acFormEdit, acDialog

However, the filter is being ignored and regardless of which customer
is selected, another customers details always appears (and always the
same one(?)).

i have tried numerous contructs, but none seem to work, including
assinging a variable to create the filter string i.e.

IDFilter = """ID = " & Me![frmRenewalLettersByDate subform]!CustID &
""""
MsgBox "Cust id=" & IDFilter

The output from the msgbox appears (to me) to create the correct
syntax;

"ID = 546765438"

ID is the correct field name as detailed in the table to which the
second form is bound.

I am using Access 2003

Any help would be apprecited - Thanks

Keers- Hide quoted text -

- Show quoted text -

Dale,

thanks for the tip - Quotes are a minefield (to me), so your function
will be of great help!

Regards

Kieran

 

[Dale Fye]

Thanks, Dirk.

I used to have problems with controls on sub forms, so now I just use the
more complete syntax all the time.

Dale

In

Two things, I think you got your quotes wrong, but if CustID as a
control on the subform, I think you also have the syntax of that
portion of things wrong. Try:

"ID = """ & Me![frmRenewalLettersByDate subform].Form!CustID & """"

Normally, the reference syntax Keers posted,

Me![frmRenewalLettersByDate subform]!CustID

will work fine. The version you posted is more complete, and I believe
that in Access 2003 and later there may be some circumstances where the
shorter reference syntax may cause a problem, but it should be fine here.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)