Variables and Filtering

[ted.martin]

I want to use a variable to filter a form when it opens. In the example below I want the word King to be replaced by a variable like strName which in turn will come from an Input Box.

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

Can someone please give me the correct code? Thanks and Merry Christmas.

 

[ShiftyJaffa]

Hi

try

Dim strName as String
strName = Inputbox ("Please enter name")
DoCmd.OpenForm "Employees", , ,"LastName = '" & strName & "'"

You have to be careful when using an apostrophy as a string delimeter, eg the above will fail if you put in a name of O'brien because the apostrophy in the name will act as the end delimeter meaning the remaining text (brien') will force an error. As a way around this you could use
DoCmd.OpenForm "Employees", , ,"LastName = " & chr$(34) & strName & chr$(34)

and use code to check your name for quotes before opening your form then display a nice error and go back to re-enter.

chr$(34) is a "

Hope that helps

Paul

 

[Mile-O]

Replace each ' with a ""