Dount VBA code producing Run-time error '3075'

Hi,

I am trying to build a bit of code which will search and go to a record in the form Boxes. It shoudl first check to see if the number is valid in the table and then go to that record in the form. The code below keeps producing an error on the line highlighted, it displays the error "Run-time error '3075': Syntax error (missing operator) in expression 'count(Box Number)'."

Dim Ref As Integer
Dim strSQL As Integer
Dim stWhereStr As String
Set db = CurrentDb()
Ref = Me.Text4.Value
stWhereStr = "[Box Number] = " & Ref
strSQL = DCount("Box Number", "Boxes", "Box Number = " & )
If strSQL = 0 Then
    MsgBox "This is box number does not exist. Please try again."
Else
    DoCmd.OpenForm "Boxes", acViewNormal
    DoCmd.GoToRecord acDataForm, "Boxes", acGoTo, Ref
    DoCmd.Close acForm, "Box Search", acSavePrompt
End If

Does anyone know why I keep getting this message? Have I missed out a bit of code? or is the dcount function wrong?

Thank you in advance

Jeskit

What is the value of Ref?

Um, yeah - I can read. I mean what is the actual value when you run the code and is that a valid record number?

Originally Posted by jeskit

The value is valid number in the table.

Meaning what exactly?

Originally Posted by jeskit

Does this make sense?

Not to me in the context of what you have posted. Why don't you just open the form using a filter for the box number you want?

DoCmd.OpenForm "Boxes", acViewNormal, , stWhereStr

rather than

DoCmd.OpenForm "Boxes", acViewNormal, stWhereStr

Well, you can just clear the filter. Alternatively, you could revise your original code to something like

Dim rst As Recordset
Dim strSQL As Integer
Dim stWhereStr As String
Set db = CurrentDb()
stWhereStr = "[Box Number] = " & Me.Text4.Value
    DoCmd.OpenForm "Boxes", acViewNormal
    Set rst = Forms("Boxes").RecordsetClone
    rst.FindFirst stWhereStr 
    If rst.NoMatch Then
MsgBox "This is box number does not exist. Please try again."
Else
       Forms("Boxes").Bookmark = rst.Bookmark
End If
    rst.Close