Dim As String syntax help

[Kirsti]

Hi, I have this code which is working perfectly:



Private Sub cmdbutton1_Click()
Dim sWHERE As String

sWHERE = "[MyQuery].[MyField] = '" & Me.cboField & "'"


'Open the form
DoCmd.OpenForm "MyForm", acNormal, , sWHERE


End Sub

I got this basic syntax on-line, however, I want to add an AND clause, and just don't understand the use of " or ' and so I can't get it working. I've tried a number of different variations. Basically I want to have something like this:


Private Sub cmdbutton1_Click()
Dim sWHERE As String

sWHERE = "[MyQuery].[MyField1] = '" & Me.cboField1 & "'"

AND "[MyQuery].[MyField2] = '" & Me.cboField2 & "'"

'Open the form
DoCmd.OpenForm "MyForm", acNormal, , sWHERE


End Sub

Any help would be appreciated.

Thanks,
Kirsti

[ssanfu]

You almost had it.

Private Sub cmdbutton1_Click()
Dim sWHERE As String

sWHERE = "[MyQuery].[MyField1] = '" & Me.cboField1 & "' AND [MyQuery].[MyField2] = '" & Me.cboField2 & "'"

'------ comment out (or delete) the next line after debugging
Debug.Print swhere

'Open the form
DoCmd.OpenForm "MyForm", acNormal, , sWHERE

End Sub

You use the single quotes (known as delimiters) if the data in cboField1 and cboField2 are text.
If "MyField1" was a date type, you would use "#" as the delimiters.
Numbers do not require delimiters.

[Kirsti]

Thanks Steve...

The strange thing is that part of the code is working ok, but I am now getting Run-time error '2501': The OpenForm action was canceled.

Kirsti

[ssanfu]

but I am now getting Run-time error '2501': The OpenForm action was canceled.

When are you getting the error?
Before the form opens or after the form opens?

[Kirsti]

Hi there,

I'm getting the error before the form opens.

[ssanfu]

Try

Private Sub cmdbutton1_Click()
Dim sWHERE As String

sWHERE = "[MyField1] = '" & Me.cboField1 & "' AND [MyField2] = '" & Me.cboField2 & "'"

'------ comment out (or delete) the next line after debugging
Debug.Print swhere

'Open the form
DoCmd.OpenForm "MyForm", acNormal, , sWHERE

End Sub

Note the blue line

What is returned from the debug line?

Will the form open or is there an error if you use:

DoCmd.OpenForm "MyForm", acNormal

[pbaldy]

If you need a visual on what Steve is suggesting:

BaldyWeb-Immediate window

I visited NZ a couple of months ago; my daughter moved there in January. Hope you didn't get shaken up too much last week!

[Kirsti]

I definitely have the blue line right.

The line returned from the debug line is:

DoCmd.OpenForm "MyForm", acNormal, , sWHERE

And yes, it opens using:
DoCmd.OpenForm "MyForm", acNormal

[Kirsti]

Thanks Paul, I will take a look at your video.

I live in Christchurch, so am used to a bit of shaking, but as luck would happen, happened to be on holiday right near Seddon last week! All ok though.

[Kirsti]

Thank you thank you thank you.

Immediate Window helped.

It was a data mismatch error - and I now have it sorted.

Thank you both again,

Kirsti

[pbaldy]

Oh, yes you've had some bad ones recently. My daughter lives in Wellington, but luckily had no significant damage. I hope to get to the South Island on my next visit. We only made it up as far as Martinborough and Castle Point. Spent most of the trip right around Wellington.

[ssanfu]

sWHERE = "[MyField1] = '" & Me.cboField1 & "' AND [MyField2] = '" & Me.cboField2 & "'"
  '------ comment out (or delete) the next line after debugging 
Debug.Print swhere

Should have been something like
"[MyField1] = 'Hello' and [MyField2] = 'Goodby'"

Are both fields text type?