Syntax error with statement in my form when it loads

[Mechele]

When my form loads, I want it to check the records in underlying table to see if a record exist based on a selection in the previous form comparing Customer Number fields. See statement below:

Dim rs As Object

sSQL = "SELECT * From TPATable WHERE CustomerNumber = " & Str([Forms!UpdateForm!CustomerNumber])
Set rs = CurrentDb.OpenRecordset(sSQL)

If NoMatch = True Then
MsgBox ("False")
Else
MsgBox ("True")
End If

I'm getting a syntax error: "String expression "CustomerNumber = 6",
What is wrong with my statement?

 

[Fizzio]

Try this instead

sSQL = "SELECT * From TPATable WHERE CustomerNumber = '" & Str([Forms!UpdateForm!CustomerNumber]) & "'"

but why are you using a Str function to change a number to a string? Have you tried without the Str?

 

[Mechele]

I really don't want to change the fields from numeric to string. I was using an example from one of my books. I'm sorry to say but I'm a novice at writing code. All I want to do is to see if a record exists as the form loads.

 

[David R]

It would appear that if CustomerNumber is a numeric field in your table, then converting it to a string may be what's causing the issue.

What about:
sSQL = "SELECT * From TPATable WHERE CustomerNumber = " & [Forms!UpdateForm!CustomerNumber]

HTH,
David R

 

[Mechele]

Thank you!!!!! I don't know how I would survive without you and everyone participating in this forum group!!!

 

[Mechele]

That didn't work.... I get the following error message:

Syntax Error (missing operator)in query expression 'CustomerNumber = & [Forms!UpdateForm!CustomerNumber]'.

 

[David R]

Make sure the & is outside the quotes. It joins the "text" section to the [Field] section.

 

[Mechele]

I'm sorry I'm not getting this, but please don't give up on me. Now, I'm getting the following error message:

Microsoft access can find the "l" referred to in your expression. The l is really a straight vertical line.

My statement is exactly as follows:

Private Sub Form_Load()
Dim rs As Object

sSQL = "SELECT * From TPATable WHERE CustomerNumber =" & [Forms!UpdateForm!CustomerNumber]
Set rs = CurrentDb.OpenRecordset(sSQL)

If NoMatch = True Then
MsgBox ("No Record")
Else
MsgBox ("True")
End If
End Sub

 

[SimonC]

I don't know exactly what's causing your error message, but there are a few things in your code example which aren't right, so maybe it's one of these.

Firstly, I'm assuming that sSQL is defined elsewhere (as a string or variant) or you're not using option explicit.

Secondly "[Forms!UpdateForm!CustomerNumber]" isn't valid as (if you're going to use square brackets) you need to enclose each object in the hierarchy in them, not the string as a whole, e.g.:

[Forms]![UpdateForm]![CustomerNumber]

Assuming that you have a form called UpdateForm and that it contains a control called CustomerNumber, and that field contains a value, then I don't see any reason why the statements beginning "sSQL ="
and "Set rs" don't work. You might find it easier to "Dim rs As Recordset" rather than "as Object", though.

Next, NoMatch. This isn't a variable or a function, but is a property of the recordset object. So if you want to check its value, you need to refer to it as rs.NoMatch.

In this case, though, it won't tell you what you need to know. Instead, check rs.EOF. If rs.EOF is true then the recordset you've just opened is already at the end of file (EOF) which means that it doesn't contain any records.

Hope some of this helps,

Simon