I am recieving a duplicate declaration in current scope error message

Posted on 2004-11-04
Medium Priority
Last Modified: 2008-01-09
I am developingr an employee training database for the company and with this code I want the user to check in a checkbox if the user has taken a class or not. If the user has not taken the class then the checkbox should be empty and the user will then select a class and the class will go into my table. If the user does select the checkbox then they will have enter the class and a date the class was taken. One of first problems is that if the user does not check the checkbox and does not enter in a date then an error message comes up. If the user does select the checkbox and enter in a date then the code works fine. Does anyone have any suggestions as to how I can enter the information in the table without the user having to enter in a date and disable the date field if the checkbox is not selected ?

Private Sub cmdTrain_Click()
    Dim ClassDate As Date
    Dim empID As Integer
    Dim occid As Integer
    Dim req As Variant
    Dim classtaken As Integer
    Dim strSQL As String
    Dim str1SQL As String
    ClassDate = Me.Date
    occid = Me.OccupationID
    req = cboClass.Column(0)
    classtaken = -1
    empID = cboemp.Column(0)
     If Me.classcheck.Value = True Then
        Me.Date.Enabled = False
    End If
   If Not IsDate(Me.Date.Value) Then
      MsgBox "Please enter a valid date"
      Exit Sub
   End If
   If Not IsNull(cboClass.Column(0) And Me.Date) Then
          strSQL = "INSERT INTO Employee_Train (FileID, OccupationID, ClassID, ClassTaken, DateTaken) VALUES (" & empID & ", " & occid & ", " & req & ", " & classtaken & ", #" & ClassDate & "#)"
          CurrentDb.Execute (strSQL)
          ElseIf IsNull(Me.Date) Then
          str1SQL = "INSERT INTO Employee_Train(FileID, OccupationID, ClassID, ClassTaken, DateTaken) VALUES (" & empID & ", " & occid & ", " & req & ", " & classtaken & ")"
          CurrentDb.Execute (str1SQL)
        End If
End Sub
Question by:tupac1979
  • 2
  • 2
LVL 41

Accepted Solution

shanesuebsahakarn earned 375 total points
ID: 12493572
I think this line:
If Not IsNull(cboClass.Column(0) And Me.Date) Then

should read:
If Not IsNull(Me.cboClass) And Not IsNull(Me.Date) Then

Author Comment

ID: 12493655
I think that helped but now I am getting an run time error that states "Run Time Error '94' Invalid use of Null" and the line it points to when I select Debug is:
ClassDate = Me.Date

this only occurs when I do not input a date on my form
LVL 44

Assisted Solution

Arthur_Wood earned 375 total points
ID: 12493752
try this:

ClassDate = NZ(Me.Date, Date())

This makes use of the NZ function, so if Me.Date is NULL, it uses Today's date .

By the way, it is a VERY VERY bad idea to name a field in your table, or on your form DATE, as Date is an Access RESERVED word.  It is the name of the Access fucntion that returns the current system date.  You should rename your field to be more meaningful (perhaps ClassDate, or MeetingDate), and also rename the control on the form.


Author Comment

ID: 12493902
Thanks I have changed the name on my form from date to cDate but I donot want a date to go in there if the user does not select the checkbox. do you think there is a way for that to happen or do I have to enter in a date variable for the SQL statement
LVL 44

Expert Comment

ID: 12495906
if you do not want a date to be inserted if the user does not select the checkbox, what do you want to have happen in that case?  I think I ammissing something about what it is you want to have happen. Can you describe, in words (forgetting about the programminig for the moment) EXACTLY what is supposed to happen with your form?


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month13 days, 15 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question