Syntax error

[Nick T]

Hi,
On my customer visit log db, i have a command button which when clicked
displays in text boxes on my form certain details about the customer for whos
record i am viewing. I can scroll through all records (1 record for each
customer) using my mouse.

However, when i get to the end of all the records, the last record is blank
and has (AutoNumber) displayed in my 'customer id' text box. I know this is
effectively the next record to be filled in & the autonumber will become the
next sequential no. in my table.

Anyway, if on this record, and i click my command button (as above), i get a
Syntax error message. This i can understand, and isnt a problem for me,
however if a user does this, it could effectively give them access to my code
if the 'Debug' button on the error window is clicked.

How can i avoid this??
My code behind my command button is as follows:

Private Sub Command173_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("CustomerVisitLogQuery", dbOpenDynaset)
With rst
.FindFirst "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "The purchase Log for " & Me.FirstName & " contains no
data"
Me.TotalSpend = ""
Me.AverageSpend = ""
Me.NoofVisits = ""

Else

Me.TotalSpend = ![Sum Of Spend Value (£)]
Me.AverageSpend = ![Avg of Spend Value (£)]
Me.NoofVisits = ![Count of Customer Visit Log]

End If


Set rst = CurrentDb.OpenRecordset("Customer Visit Log", dbOpenDynaset)
With rst
.FindLast "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
Me.LastSpend = ""
Me.LastVisit = ""

Else

Me.LastVisit = ![Date of Visit]
Me.LastSpend = ![Spend Value (£)]

End If

.Close
End With
Set rst = Nothing



Sorry if this Q is a bit long winded!!

 

[John Spencer]

Either use an error trapping routine to trap the error or change your
code so you don't get the error

Avoid the error:
IF Me.NewRecord then Exit sub

SAMPLE Rudimentary Error Code

Private Sub Command173_Click()

On Error GoTo Error_Proc

<<< all your code here >>>

Exit sub

Error_Proc:
SELECT Case Err.Number
Case 2501,2222,3333
'Enter any specific error numbers you want to ignore
'do nothing
Else
Msgbox err.Number & " : " & err.Description,,"Whoops!"
End Select
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ken Snell \(MVP\)]

Add an Error Handler to your code to "handle" (in this case, ignore) the
error:

Private Sub Command173_Click()
On Error GoTo Err_Command173_Click
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("CustomerVisitLogQuery",
dbOpenDynaset)
With rst
.FindFirst "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "The purchase Log for " & Me.FirstName & " contains no
data"
Me.TotalSpend = ""
Me.AverageSpend = ""
Me.NoofVisits = ""

Else

Me.TotalSpend = ![Sum Of Spend Value (£)]
Me.AverageSpend = ![Avg of Spend Value (£)]
Me.NoofVisits = ![Count of Customer Visit Log]

End If


Set rst = CurrentDb.OpenRecordset("Customer Visit Log", dbOpenDynaset)
With rst
.FindLast "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
Me.LastSpend = ""
Me.LastVisit = ""

Else

Me.LastVisit = ![Date of Visit]
Me.LastSpend = ![Spend Value (£)]

End If

.Close
End With
Set rst = Nothing

Exit_Command173_Click:
Exit Sub

Err_Command173_Click:
Resume Exit_Command173_Click

End Sub

--

Ken Snell
<MS ACCESS MVP>

Hi,
On my customer visit log db, i have a command button which when clicked
displays in text boxes on my form certain details about the customer for
whos
record i am viewing. I can scroll through all records (1 record for each
customer) using my mouse.

However, when i get to the end of all the records, the last record is
blank
and has (AutoNumber) displayed in my 'customer id' text box. I know this
is
effectively the next record to be filled in & the autonumber will become
the
next sequential no. in my table.

Anyway, if on this record, and i click my command button (as above), i get
a
Syntax error message. This i can understand, and isnt a problem for me,
however if a user does this, it could effectively give them access to my
code
if the 'Debug' button on the error window is clicked.

How can i avoid this??
My code behind my command button is as follows:

Private Sub Command173_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("CustomerVisitLogQuery",
dbOpenDynaset)
With rst
.FindFirst "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "The purchase Log for " & Me.FirstName & " contains no
data"
Me.TotalSpend = ""
Me.AverageSpend = ""
Me.NoofVisits = ""

Else

Me.TotalSpend = ![Sum Of Spend Value (£)]
Me.AverageSpend = ![Avg of Spend Value (£)]
Me.NoofVisits = ![Count of Customer Visit Log]

End If


Set rst = CurrentDb.OpenRecordset("Customer Visit Log", dbOpenDynaset)
With rst
.FindLast "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
Me.LastSpend = ""
Me.LastVisit = ""

Else

Me.LastVisit = ![Date of Visit]
Me.LastSpend = ![Spend Value (£)]

End If

.Close
End With
Set rst = Nothing



Sorry if this Q is a bit long winded!!

 

[Ken Snell \(MVP\)]

In addition, you can eliminate the problem by disabling the command button
on a new record:

Private Sub Form_Current()
Me.Command173.Enabled = (Me.NewRecord = False)
End Sub

--

Ken Snell
<MS ACCESS MVP>

Add an Error Handler to your code to "handle" (in this case, ignore) the
error:

Private Sub Command173_Click()
On Error GoTo Err_Command173_Click
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("CustomerVisitLogQuery",
dbOpenDynaset)
With rst
.FindFirst "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "The purchase Log for " & Me.FirstName & " contains no
data"
Me.TotalSpend = ""
Me.AverageSpend = ""
Me.NoofVisits = ""

Else

Me.TotalSpend = ![Sum Of Spend Value (£)]
Me.AverageSpend = ![Avg of Spend Value (£)]
Me.NoofVisits = ![Count of Customer Visit Log]

End If


Set rst = CurrentDb.OpenRecordset("Customer Visit Log", dbOpenDynaset)
With rst
.FindLast "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
Me.LastSpend = ""
Me.LastVisit = ""

Else

Me.LastVisit = ![Date of Visit]
Me.LastSpend = ![Spend Value (£)]

End If

.Close
End With
Set rst = Nothing

Exit_Command173_Click:
Exit Sub

Err_Command173_Click:
Resume Exit_Command173_Click

End Sub

--

Ken Snell
<MS ACCESS MVP>

Hi,
On my customer visit log db, i have a command button which when clicked
displays in text boxes on my form certain details about the customer for
whos
record i am viewing. I can scroll through all records (1 record for each
customer) using my mouse.

However, when i get to the end of all the records, the last record is
blank
and has (AutoNumber) displayed in my 'customer id' text box. I know this
is
effectively the next record to be filled in & the autonumber will become
the
next sequential no. in my table.

Anyway, if on this record, and i click my command button (as above), i
get a
Syntax error message. This i can understand, and isnt a problem for me,
however if a user does this, it could effectively give them access to my
code
if the 'Debug' button on the error window is clicked.

How can i avoid this??
My code behind my command button is as follows:

Private Sub Command173_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("CustomerVisitLogQuery",
dbOpenDynaset)
With rst
.FindFirst "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "The purchase Log for " & Me.FirstName & " contains no
data"
Me.TotalSpend = ""
Me.AverageSpend = ""
Me.NoofVisits = ""

Else

Me.TotalSpend = ![Sum Of Spend Value (£)]
Me.AverageSpend = ![Avg of Spend Value (£)]
Me.NoofVisits = ![Count of Customer Visit Log]

End If


Set rst = CurrentDb.OpenRecordset("Customer Visit Log", dbOpenDynaset)
With rst
.FindLast "[Customer ID] = " & Me.CustomerID


If .NoMatch Then
Me.LastSpend = ""
Me.LastVisit = ""

Else

Me.LastVisit = ![Date of Visit]
Me.LastSpend = ![Spend Value (£)]

End If

.Close
End With
Set rst = Nothing



Sorry if this Q is a bit long winded!!