Problems with DCount

B

Brad Holderman

Greetings,

I recently tweaked some code by entering a DCount expression, based on an
earlier post.

The original code simply presented criteria arguments in opening a table and
it worked fine. What I wanted to do is add a simple "No records found"
handler that would take me back to the switchboard.

However, once I ran the new change, VB didn't have a problem with the DCount
expression, what it now has a problem with is the "End Sub" command. I now
receive an error, "Block If Without End If."

I will post the code below, but first my question. Has the DCount changed
the structure of the Sub? And, do I simply have the DCount in the wrong
place?

Any help would be appreciated, and here is the code:

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'The OpenArgs should be delimited with a | character
'Get the OpenArgs into individual strings in an array
Dim strArgs() As String
strArgs = Split(Me.OpenArgs, "|")

Dim strCustomer As String
Dim strJob As String
Dim strType As String
Dim strTemp() As String
Dim I As Integer
For I = 1 To UBound(strArgs)
strTemp = Split(strArgs(I), "=")
Select Case strTemp(1)
Case "Customer"
strCustomer = strTemp(2)
Case "Job"
strJob = strTemp(2)
Case "Type"
strType = strTemp(2)
End Select
Next
Dim strSQL As String
strSQL = "SELECT .Customer, " & _
"Job, " & _
"Type , " & _
"[Sub-Type] , " & _
"[Sub-type2] , " & _
"Size , " & _
"[Job No] , " & _
"Field8 , " & _
"Field9 , " & _
"Priority , " & _
"[Project Lead] , " & _
"[Date Scanned] , " & _
"[Addendum Status] , " & _
"Status , " & _
"[Last Accessed] , " & _
"[Drive Location] , " & _
"[Image Count] , " & _
"[Document Type] , " & _
"Viewed_By_Brad " & _
"FROM EDDIE" & _
WhereClauseBuild
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Me.Recordset = rs

Else
If DCount("*", "EDDIE", "") = 0 Then
MsgBox "No records found"

End If

End Sub
 
O

Ofer

Hi, you are missing End If, you have two If, and only one End If, try this

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'The OpenArgs should be delimited with a | character
'Get the OpenArgs into individual strings in an array
Dim strArgs() As String
strArgs = Split(Me.OpenArgs, "|")

Dim strCustomer As String
Dim strJob As String
Dim strType As String
Dim strTemp() As String
Dim I As Integer
For I = 1 To UBound(strArgs)
strTemp = Split(strArgs(I), "=")
Select Case strTemp(1)
Case "Customer"
strCustomer = strTemp(2)
Case "Job"
strJob = strTemp(2)
Case "Type"
strType = strTemp(2)
End Select
Next
Dim strSQL As String
strSQL = "SELECT .Customer, " & _
"Job, " & _
"Type , " & _
"[Sub-Type] , " & _
"[Sub-type2] , " & _
"Size , " & _
"[Job No] , " & _
"Field8 , " & _
"Field9 , " & _
"Priority , " & _
"[Project Lead] , " & _
"[Date Scanned] , " & _
"[Addendum Status] , " & _
"Status , " & _
"[Last Accessed] , " & _
"[Drive Location] , " & _
"[Image Count] , " & _
"[Document Type] , " & _
"Viewed_By_Brad " & _
"FROM EDDIE" & _
WhereClauseBuild
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Me.Recordset = rs

Else
If DCount("*", "EDDIE", "") = 0 Then
MsgBox "No records found"
End If ' Missing here
End If

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Brad Holderman said:
Greetings,

I recently tweaked some code by entering a DCount expression, based on an
earlier post.

The original code simply presented criteria arguments in opening a table and
it worked fine. What I wanted to do is add a simple "No records found"
handler that would take me back to the switchboard.

However, once I ran the new change, VB didn't have a problem with the DCount
expression, what it now has a problem with is the "End Sub" command. I now
receive an error, "Block If Without End If."

I will post the code below, but first my question. Has the DCount changed
the structure of the Sub? And, do I simply have the DCount in the wrong
place?

Any help would be appreciated, and here is the code:

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'The OpenArgs should be delimited with a | character
'Get the OpenArgs into individual strings in an array
Dim strArgs() As String
strArgs = Split(Me.OpenArgs, "|")

Dim strCustomer As String
Dim strJob As String
Dim strType As String
Dim strTemp() As String
Dim I As Integer
For I = 1 To UBound(strArgs)
strTemp = Split(strArgs(I), "=")
Select Case strTemp(1)
Case "Customer"
strCustomer = strTemp(2)
Case "Job"
strJob = strTemp(2)
Case "Type"
strType = strTemp(2)
End Select
Next
Dim strSQL As String
strSQL = "SELECT .Customer, " & _
"Job, " & _
"Type , " & _
"[Sub-Type] , " & _
"[Sub-type2] , " & _
"Size , " & _
"[Job No] , " & _
"Field8 , " & _
"Field9 , " & _
"Priority , " & _
"[Project Lead] , " & _
"[Date Scanned] , " & _
"[Addendum Status] , " & _
"Status , " & _
"[Last Accessed] , " & _
"[Drive Location] , " & _
"[Image Count] , " & _
"[Document Type] , " & _
"Viewed_By_Brad " & _
"FROM EDDIE" & _
WhereClauseBuild
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Me.Recordset = rs

Else
If DCount("*", "EDDIE", "") = 0 Then
MsgBox "No records found"

End If

End Sub
Click to expand...
 
B

Brad Holderman

Ofer and Brian,

Thanks I had a feeling it was something simple. However, now my DCount
doesn't do anything, I still pull up an empty field when no records are found.

Sheesh, one of these days I am going to write a piece of code with noone's
help and it will actually work...

Cheers,
Brad

Ofer said:
Hi, you are missing End If, you have two If, and only one End If, try this

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'The OpenArgs should be delimited with a | character
'Get the OpenArgs into individual strings in an array
Dim strArgs() As String
strArgs = Split(Me.OpenArgs, "|")

Dim strCustomer As String
Dim strJob As String
Dim strType As String
Dim strTemp() As String
Dim I As Integer
For I = 1 To UBound(strArgs)
strTemp = Split(strArgs(I), "=")
Select Case strTemp(1)
Case "Customer"
strCustomer = strTemp(2)
Case "Job"
strJob = strTemp(2)
Case "Type"
strType = strTemp(2)
End Select
Next
Dim strSQL As String
strSQL = "SELECT .Customer, " & _
"Job, " & _
"Type , " & _
"[Sub-Type] , " & _
"[Sub-type2] , " & _
"Size , " & _
"[Job No] , " & _
"Field8 , " & _
"Field9 , " & _
"Priority , " & _
"[Project Lead] , " & _
"[Date Scanned] , " & _
"[Addendum Status] , " & _
"Status , " & _
"[Last Accessed] , " & _
"[Drive Location] , " & _
"[Image Count] , " & _
"[Document Type] , " & _
"Viewed_By_Brad " & _
"FROM EDDIE" & _
WhereClauseBuild
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Me.Recordset = rs

Else
If DCount("*", "EDDIE", "") = 0 Then
MsgBox "No records found"
End If ' Missing here
End If

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



Brad Holderman said:
Greetings,

I recently tweaked some code by entering a DCount expression, based on an
earlier post.

The original code simply presented criteria arguments in opening a table and
it worked fine. What I wanted to do is add a simple "No records found"
handler that would take me back to the switchboard.

However, once I ran the new change, VB didn't have a problem with the DCount
expression, what it now has a problem with is the "End Sub" command. I now
receive an error, "Block If Without End If."

I will post the code below, but first my question. Has the DCount changed
the structure of the Sub? And, do I simply have the DCount in the wrong
place?

Any help would be appreciated, and here is the code:

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then
'The OpenArgs should be delimited with a | character
'Get the OpenArgs into individual strings in an array
Dim strArgs() As String
strArgs = Split(Me.OpenArgs, "|")

Dim strCustomer As String
Dim strJob As String
Dim strType As String
Dim strTemp() As String
Dim I As Integer
For I = 1 To UBound(strArgs)
strTemp = Split(strArgs(I), "=")
Select Case strTemp(1)
Case "Customer"
strCustomer = strTemp(2)
Case "Job"
strJob = strTemp(2)
Case "Type"
strType = strTemp(2)
End Select
Next
Dim strSQL As String
strSQL = "SELECT .Customer, " & _
"Job, " & _
"Type , " & _
"[Sub-Type] , " & _
"[Sub-type2] , " & _
"Size , " & _
"[Job No] , " & _
"Field8 , " & _
"Field9 , " & _
"Priority , " & _
"[Project Lead] , " & _
"[Date Scanned] , " & _
"[Addendum Status] , " & _
"Status , " & _
"[Last Accessed] , " & _
"[Drive Location] , " & _
"[Image Count] , " & _
"[Document Type] , " & _
"Viewed_By_Brad " & _
"FROM EDDIE" & _
WhereClauseBuild
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
Me.Recordset = rs

Else
If DCount("*", "EDDIE", "") = 0 Then
MsgBox "No records found"

End If

End Sub
Click to expand...
Click to expand...
 
B

Brian Bastl

Try re-ordering your If clause.

If Me.OpenArgs <> ""
If DCount(blah) > 0 then
Dim stArgs as String
etc.
Else
MsgBox "No Records Found"
End If
End If

HTH,
Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Match style 0
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Word macro that can call cells out of Excel for a find and replace 0
AddPicturesToShapes 0
VBA Export to PDF 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
export vba not working 0

Top