I am recieving a duplicate declaration in current scope error message

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.SetFocus
    Else
        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
tupac1979Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tupac1979Author Commented:
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
0
Arthur_WoodCommented:
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.

AW
0
tupac1979Author Commented:
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
0
Arthur_WoodCommented:
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?

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.