Error in SQL??

[selenau837]

I have a table I am trying to update with the following SQL string. Everytime it runs it says: "No value given for one or more required parameters."

I am not sure why I am getting this error. The SQL is as follows:

"UPDATE tblDocuments INNER JOIN tblDocumentReview " & _
                "ON tblDocuments.intDocumentID = tblDocumentReview.intDocumentID " & _
                "SET dtmMembershipRelease = #" & Me.txtReleaseDate.value & "#,  strEmpID = " & Me.txtEmpId.value & " " & _
                "WHERE tblDocumentReview.intEntryNumber = " & Me.txtEntryNumber.value

This code was written already, I added the strEmpID to be updated.
strEmpID is in the tblDocuments. It was a new field I added.

What did I do wrong? Should I have declared it somewhere in the SQL?

 

[namliam]

Are you sure the fields on your form have values?

 

[selenau837]

Yes it does because it automatically fills in the txtEmpId when the form loads.

 

[reclusivemonkey]

You can't use Access syntax in SQL. Make your access references as string variables then use these to make your SQL string.

i.e.

dteReleaseDate = Me.txtReleaseDate.value

 

[selenau837]

You can't use Access syntax in SQL. Make your access references as string variables then use these to make your SQL string.

i.e.

dteReleaseDate = Me.txtReleaseDate.value

How do you mean, because the code work fine until I added the strEmpId reference. I broke something when I added.

 

[reclusivemonkey]

How do you mean, because the code work fine until I added the strEmpId reference. I broke something when I added.

Then eveything I know about SQL must be wrong, and I should be quiet now.

 

[selenau837]

Then eveything I know about SQL must be wrong, and I should be quiet now.

I was in no way implying you were incorrect, I just know that through out this entire database that I inherited, it is like that.

 

[reclusivemonkey]

Here is an example from one of my DB's;

' Use the period selected from the drop down box
myPeriod = Forms!frmPostingTotalsAreaSubtotals.AppendPeriod
' Create SQL to Append data
strSQL = "INSERT INTO tblPostingTotalsQuery ( CODE, SPEND, BUDGET, PERIOD ) "
strSQL = strSQL + "SELECT qryPostingTotalsQueryFinal.CODE, qryPostingTotalsQueryFinal.SPEND, "
strSQL = strSQL + "qryPostingTotalsQueryFinal.BUDGET, " & myPeriod & " AS PERIOD "
strSQL = strSQL + "FROM qryPostingTotalsQueryFinal;"

When I used;

' Create SQL to Append data
strSQL = "INSERT INTO tblPostingTotalsQuery ( CODE, SPEND, BUDGET, PERIOD ) "
strSQL = strSQL + "SELECT qryPostingTotalsQueryFinal.CODE, qryPostingTotalsQueryFinal.SPEND, "
strSQL = strSQL + "qryPostingTotalsQueryFinal.BUDGET, " & [b]Forms!frmPostingTotalsAreaSubtotals.AppendPeriod[/b] & " AS PERIOD "
strSQL = strSQL + "FROM qryPostingTotalsQueryFinal;"

it doesn't work.

 

[selenau837]

Ok, I don't know enough to debate back and forth on this. I love a good debate, however I am not fully armed to debate SQL.

Can you please explain what I need to do since what I have is completely incorrect.

 

[reclusivemonkey]

I thought I had done that???

On looking at your code, you are trying to run an update query on linked tables. AFAIK, this isn't possible.

 

[selenau837]

Ok, thank you. I will try something else.

 

[nateobot]

On looking at your code, you are trying to run an update query on linked tables. AFAIK, this isn't possible.

I can run Update Queries on linked Access tables and SQL tables where I have the proper permissions.

 

[reclusivemonkey]

I can run Update Queries on linked Access tables and SQL tables where I have the proper permissions.

Sorry my mistake there; I should of said "Joined" not "Linked". As far as I am aware, you can't create an updatable query which has two tables/queries joined.

 

[selenau837]

This is my code now, and it still isn't working.

It keeps saying "One or more Required Paramters not given"

What have I done incorrectly now.

Private Sub cmdReleaseGroup_Click()

    Dim rst As New ADODB.Recordset
    Dim clsBackup As New clsBackup
    Dim strEmpId As String
    Dim dtmMembershipRelease As String
    
    strEmpId = Me.txtEmpID.value
    dtmMembershipRelease = Me.txtReleaseDate.value
    

    
    On Error GoTo Handle_Error
    
    txtReleaseDate.SetFocus
    txtEntryNumber.SetFocus
    
    If IsNull(txtReleaseDate.value) Then
        MsgBox ("You must enter a Release Date.")
    ElseIf Not CanUpdate(txtReleaseDate.value) Then
        MsgBox ("You cannot use a release date for a prior month.")
    ElseIf Not CheckDocumentReleaseDate(Me.txtEntryNumber.value) Then
        MsgBox ("You cannot update a document that already has a release date for a prior month.")
    ElseIf IsNull(txtEntryNumber.value) Then
        MsgBox ("You must enter an Entry Number.")
    Else
    
        With clsBackup
        
            .SQL = "UPDATE tblDocuments INNER JOIN tblDocumentReview " & _
                "ON tblDocuments.intDocumentID = tblDocumentReview.intDocumentID " & _
                [COLOR="Red"][B]"SET dtmMembershipRelease = #" & dtmMembershipRelease & "#, " & _
                     "strEmpId = " & strEmpId & " " & _[/B][/COLOR]               
 "WHERE tblDocumentReview.intEntryNumber = " & Me.txtEntryNumber.value
            .OpenCommand
            .Execute
            
            
            Set clsBackup = Nothing
        
        End With
        
        lstDocuments.Requery
        
        MsgBox ("All documents for Entry Number " & Me.txtEntryNumber.value & " have been released.")
        
    End If
    
    Exit Sub
    
Handle_Error:
    MsgBox "The following error has occurred: " & Err.Description
    Exit Sub

End Sub

The error is somwhere in the bolded red area. Any help would be great.

 

[selenau837]

clsBackup

Option Compare Database
Option Explicit

Dim strSQL As String
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Public Property Let SQL(value As String)
    strSQL = value
End Property
   
Public Property Get Fields(FieldIndex As String) As String
    Fields = rst.Fields(FieldIndex).value
End Property
   
Public Property Get EOF() As Boolean
    EOF = rst.EOF
End Property

Public Property Get BOF() As Boolean
    BOF = rst.BOF
End Property

Public Property Get RecordCount() As Integer

    'Move to the first record
    If rst.BOF = False Then
        rst.MoveFirst
    End If
        
    'Count the records
    While rst.EOF = False
        rst.MoveNext
        RecordCount = RecordCount + 1
    Wend
    
    'Move to the first record
    If rst.BOF = False Then
        rst.MoveFirst
    End If

End Property

Public Sub OpenRecordset()

    'Open the recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly
        
        'Open the recordset
        .Open strSQL
        
    End With

End Sub

Public Sub OpenCommand()

    'Open the command
    cmd.ActiveConnection = CurrentProject.Connection

End Sub

Public Sub AddNew()

    With rst
        .AddNew
    End With

End Sub

Public Sub Delete()

    With rst
        .Delete adAffectCurrent
    End With

End Sub

Public Sub UpdateString(value As String, FieldName As String)

    With rst
        .Fields(FieldName).value = value
    End With
    
End Sub

Public Sub UpdateInteger(value As Integer, FieldName As String)

    With rst
        .Fields(FieldName).value = value
    End With
    
End Sub

Public Sub UpdateBoolean(value As Boolean, FieldName As String)

    With rst
        .Fields(FieldName).value = value
    End With
    
End Sub

Public Sub UpdateNull(FieldName As String)

    With rst
        .Fields(FieldName).value = Null
    End With

End Sub

Public Sub UpdateRecordset()

    With rst
        .update
    End With

End Sub

Public Sub MoveNext()

    With rst
        .MoveNext
    End With

End Sub

Public Sub MovePrevious()

    With rst
        .MovePrevious
    End With

End Sub

Public Sub CloseRecordset()

    On Error GoTo Handle_Error

    'Close the recordset
    rst.Close

    'Clear objects from memory
    Set rst = Nothing
    
    Exit Sub
    
Handle_Error:
    
    MsgBox "Access was unable to save your changes.  Please try again."
     
    
    'Clear objects from memory
    
    Set rst = Nothing
    
    Exit Sub
    
End Sub

Public Function Execute()

    On Error GoTo Handle_Error
    
    'Set the command text
    cmd.CommandText = strSQL
    
    'Execute the command
    cmd.Execute

    'Clear objects from memory
    Set cmd = Nothing
    
    Exit Function
    
Handle_Error:
    
    MsgBox Err.Description
    
    'Clear objects from memory
    Set cmd = Nothing
    
    Exit Function
    
End Function