If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Syntax error
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!! |
#2
|
|||
|
|||
Syntax error
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 '================================================= === Nick T wrote: 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!! |
#3
|
|||
|
|||
Syntax error
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 "Nick T" wrote in message ... 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!! |
#4
|
|||
|
|||
Syntax error
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 "Ken Snell (MVP)" wrote in message ... 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 "Nick T" wrote in message ... 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!! |
Thread Tools | |
Display Modes | |
|
|