Duplicate Dimming - I know this isn't right, but...

[irunergoiam]

I’m kind of stuck on my Dim statements and was hoping you might be able to give me a little nudge.

The first thing I’m trying to do is see if a record (by employee ID) already exists, if a record does exist, I want the “Save” button to Update the related record in the table with the unbound fields from the form. The problem I’m having is that I’m already dimming strCriteria As String and dimming strSQL as String to see if the record exists (see Dim statements in red with *). When it determines that it exists, I am not able to get the DoCmd.RunSQL strSQL command to run as I’ve told it to Dim again (see Dim statements in red with #).

I know this isn’t correct, but can’t get it to run when I remove the Dim statements for the DoCmd.RunSQL strSQL command and am hoping that someone might be able to give me a nudge on this one as I’m pretty new to something this complex.

Thanks…

Private Sub Save_Click()

*Dim varExists As Variant
*Dim strCriteria As String
*Dim strSQL As String

strCriteria = "[Data File].Employee = '" & Me.cmbCandidate.Column(7) & "'"

varExists = DLookup("Employee", "Data File", strCriteria)

If Not IsNull(varExists) Then

response = MsgBox(" An Attestation record already exists for this employee. Update employee's attestation record?", vbYesNo, "EEID Attestation On File")
If response = vbYes Then

DoCmd.SetWarnings False

#Dim strSQL As String
#Dim strCriteria As String

strSQL = "UPDATE [Data File] SET [Data File].[Yrs of Experience] = " & unbExperience & ", [Data File].[As of Date] = #" & unbAsOfDate & "#, [Data File].[Comments] = '" & unbComments & "', [Data File].[Full Name] = '" & cmbCandidate & "', [Data File].[EnteredBy] = '" & unbEnteredBy & "', [Data File].[EnteredDate] = #" & unbEnteredDate & "#, [Data File].[EnteredTime] = #" & unbEnteredTime & "#, [Data File].[DateTime] = Now(), [Data File].[First Name] = '" & unbFirstName & "', [Data File].[Last Name] = '" & unbLastName & "'"
strSQL = strSQL & "WHERE((([Data File].Employee) = [Forms]![AttestationForm]![unbKenexaEEID]);"
DoCmd.RunSQL strSQL

 

[vbaInet]

How about this:

Dim strCriteria As String
Dim strSQL As String

strCriteria = "[Data File].Employee = '" & Me.cmbCandidate.Column(7) & "'"

If IsNull(DLookup("Employee", "Data File", strCriteria)) Then exit sub

response = MsgBox(" An Attestation record already exists for this employee. Update employee's " & _
                  "attestation record?", vbYesNo, "EEID Attestation On File")
If response = vbno Then exit sub

strSQL = "UPDATE [Data File] SET [Data File].[Yrs of Experience] = " & unbExperience & ", [Data File].[As of Date] = #" & _
                unbAsOfDate & "#, [Data File].[Comments] = '" & unbComments & "', " & _
                "[Data File].[Full Name] = '" & cmbCandidate & "', [Data File].[EnteredBy] = '" & _
                unbEnteredBy & "', [Data File].[EnteredDate] = #" & unbEnteredDate & "#, " & _
                "[Data File].[EnteredTime] = #" & unbEnteredTime & "#, [Data File].[DateTime] = Now(), " & _
                "[Data File].[First Name] = '" & unbFirstName & "', [Data File].[Last Name] = '" & unbLastName & "'"
strSQL = strSQL & " WHERE ([Data File].Employee) = [Forms]![AttestationForm]![unbKenexaEEID]);"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings true

 

[SteveSchapel]

Irunergoiam,

I generally tend to only Dim a variable if it is going to be used more than once in the subprocedure, the exception being where the definition of the variable is complex or long, as in the case of the strSQL variable.

I can not see that the repeat Dim of the variable can cause the code to fail. But certainly it can be tidied up. For the record, here's how I would write this...

Private Sub Save_Click()
 
   Dim strSQL As String
   If DCount("*", "Data File", "Employee = '" & Me.cmbCandidate.Column(7) & "'") Then
      If MsgBox("An Attestation record already exists for this employee." & _
              "Update employee's attestation record?", vbYesNo, "EEID Attestation On File") = vbYes Then
         strSQL = "UPDATE [Data File]" & _
            " SET [Data File].[Yrs of Experience] = " & Me.unbExperience & _
                ", [Data File].[As of Date] = #" & Me.unbAsOfDate & _
                "#, [Data File].[Comments] = '" & Me.unbComments & _
                "', [Data File].[Full Name] = '" & Me.cmbCandidate & _
                "', [Data File].[EnteredBy] = '" & Me.unbEnteredBy & _
                "', [Data File].[EnteredDate] = #" & Me.unbEnteredDate & _
                "#, [Data File].[EnteredTime] = #" & Me.unbEnteredTime & _
                "#, [Data File].[DateTime] = Now()" & _
                ", [Data File].[First Name] = '" & Me.unbFirstName & _
                "', [Data File].[Last Name] = '" & Me.unbLastName & "'" & _
            " WHERE((([Data File].Employee) = " & [Forms]![AttestationForm]![unbKenexaEEID])
         DBEngine(0)(0).Execute strSQL, dbFailOnError
      End If
   End if
End Sub

But that still begs the question, really. I think if it's not working, you need to look at the SQL statement for the cause of the problem, rather than to the Dim section.

 

[irunergoiam]

SteveSchapel and vbaInet, thank you both for your feedback. I definitely see how the code could certainly benefit from a good tidying up. The SQL statement runs fine when I run it by itself (without the code to see if a record already exists), the code gets a little more complicated because, in addition to looing to see if the Employee field already exists in the table and then trying to update on that record, I then need to see if a record already exists based on the last four of the SSN as either one of these fields may contain a value. If they are both null, then I can proceed with an Insert Into statement. I'll take your direction under advisement and continue to do some tinkering (one of the best ways to learn Access - aided by the generous help of Access gurus like yourselves). Thanks much...