SQL Delete Recordset

**Jester0001** · 03-07-2012, 12:20 PM

I have a code which pbaldy helped me to write (previous post), which inserts sequential values into a subform based upon a value in a textbox on the mainform.

Main form = [frmSampleLogIn01]
Subform = [subMycoData]
Link Master/Child = [AccessionNo]
Mainform textbox control = NoOfSamples

Original code:

Code:

Private Sub NoOfSamples_AfterUpdate()
'To autopopulate the [SampleNo] field in the subform with
    'sequential numbers up to the value in the [NoOfSamples] field in the main form.
    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
 
    If Me.Dirty Then Me.Dirty = False
 
    For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

    Me.subMycoData.Form.Requery

End Sub

Now, I am trying to add to the code to check to see if the subform is empty before executing the code above, or else to delete the records in the subform before executing the code.

Here is what I have come up with so far:

Code:

Private Sub NoOfSamples_AfterUpdate()
Dim dbs As DAO.Database
Dim rsMycoData As DAO.Recordset
Dim strSQL1 As String

'Open pointer to current database
Set dbs = CurrentDb()

'SQL string to select records from [tblMycoData] where the [AccessionNo] & _
is the same as on the form [frmSampleLogIn01]
strSQL1 = "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
            
Debug.Print "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
            
'Create recordset based on SQL1
Set rsMycoData = dbs.OpenRecordset(srtSQL1)

'To check if the recordset on the subform [subMycoData] is empty
'then insert sequential values into [SampleNo], or else delete
'recordset before putting in new sequential values into [SampleNo]

'If Me.subMycoData.Form.Recordset.RecordCount = 0 Then
If rsMycoData.RecordCount = 0 Then

    'To autopopulate the [SampleNo] field in the subform with
    'sequential numbers up to the value in the [NoOfSamples] field in the main form.
    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
 
    If Me.Dirty Then Me.Dirty = False
 
    For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

    Me.subMycoData.Form.Requery
 
 Else
 
    'Delete the records on the subform [subMycoData] that are & _
    associated with the [AccessionNo] field that links the main & subforms
    
    Do Until Me.subMycoData.Form.Recordset.EOF
    Me.subMycoData.Form.Recordset.Delete
    Me.subMycoData.Form.Recordset.MoveNext
    Loop
    
    'All records in the subform should be deleted before continuting code below
    
        'To autopopulate the [SampleNo] field in the subform with
        'sequential numbers up to the value in the [NoOfSamples] field in the main form.
 
        If Me.Dirty Then Me.Dirty = False
 
        For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

        Me.subMycoData.Form.Requery
 
 End If
 'To close the recordset
 rsMycoData.Close
 
End Sub

But, I can't test the rest of my code, since I get:

Run-time error '3078':
The Microsoft Access database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly.

at the line:

Code:

Set rsMycoData = dbs.OpenRecordset(srtSQL1)

I am guessing that the error has to do with my SQL string:

Code:

'SQL string to select records from [tblMycoData] where the [AccessionNo] & _
is the same as on the form [frmSampleLogIn01]
strSQL1 = "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo

The field tblMycoData.AccessionNo is a number data type.

I'm new to working with SQL and recordsets in VBA. I'm trying to learn them, so any help is appreciated.

**pbaldy** · 03-07-2012, 12:33 PM

You'll kick yourself:

strSQL1 = "SELECT..."

Set rsMycoData = dbs.OpenRecordset(srtSQL1)

**Jester0001** · 03-07-2012, 02:20 PM

Yup, kicked myself, but glad to know I was very close! I did a little more tweaking to get the code running the way I need (added a vbOkCancel msgbox). The code runs smoothly now.

SOLVED

Code:

Private Sub NoOfSamples_AfterUpdate()
Dim dbs As DAO.Database
Dim rsMycoData As DAO.Recordset
Dim strSQL1 As String

'Open pointer to current database
Set dbs = CurrentDb()

'SQL string to select records from [tblMycoData] where the [AccessionNo] & _
is the same as on the form [frmSampleLogIn01]
strSQL1 = "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
            
'Create recordset based on SQL1
Set rsMycoData = dbs.OpenRecordset(strSQL1)

'To check if the recordset on the subform [subMycoData] is empty
'then insert sequential values into [SampleNo], or else delete
'recordset before putting in new sequential values into [SampleNo]

    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
    
'If Me.subMycoData.Form.Recordset.RecordCount = 0 Then
If rsMycoData.RecordCount = 0 Then

'To autopopulate the [SampleNo] field in the subform with
    'sequential numbers up to the value in the [NoOfSamples] field in the main form.

 
    If Me.Dirty Then Me.Dirty = False
 
    For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

    Me.subMycoData.Form.Requery
 
 Else
    Dim MsgResponse As Integer
    
    MsgResponse = MsgBox("Changing the number of samples will delete" & vbCrLf & " all records on the subform.", vbOKCancel)
    
    If MsgResponse = vbOK Then
        'Delete the records on the subform [subMycoData] that are & _
        associated with the [AccessionNo] field that links the main & subforms
    
        Do Until Me.subMycoData.Form.Recordset.EOF
            Me.subMycoData.Form.Recordset.Delete
            Me.subMycoData.Form.Recordset.MoveNext
        Loop
    
        'All records in the subform should be deleted before continuting code below
    
        If Me.Dirty Then Me.Dirty = False
 
        For i = 1 To intSmplNo
            strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
            CurrentDb.Execute strSQL2, dbFailOnError
            Next i

        Me.subMycoData.Form.Requery
    Else
        Exit Sub
    End If
 End If
 'To close the recordset
 rsMycoData.Close
 
End Sub

Another question (should I should start a new thread for this quesion?): I noticed that I have redundant code in the procedure above:

Redundant code highlighted in red:

Code:

Private Sub NoOfSamples_AfterUpdate()
Dim dbs As DAO.Database
Dim rsMycoData As DAO.Recordset
Dim strSQL1 As String

'Open pointer to current database
Set dbs = CurrentDb()

'SQL string to select records from [tblMycoData] where the [AccessionNo] & _
is the same as on the form [frmSampleLogIn01]
strSQL1 = "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
            
'Create recordset based on SQL1
Set rsMycoData = dbs.OpenRecordset(strSQL1)

'To check if the recordset on the subform [subMycoData] is empty
'then insert sequential values into [SampleNo], or else delete
'recordset before putting in new sequential values into [SampleNo]

    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
    
'If Me.subMycoData.Form.Recordset.RecordCount = 0 Then
If rsMycoData.RecordCount = 0 Then

'To autopopulate the [SampleNo] field in the subform with
    'sequential numbers up to the value in the [NoOfSamples] field in the main form.

 
    If Me.Dirty Then Me.Dirty = False
 
    For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo)  Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i  & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

    Me.subMycoData.Form.Requery
 
 Else
    Dim MsgResponse As Integer
    
    MsgResponse = MsgBox("Changing the number of samples will delete"  & vbCrLf & " all records on the subform.", vbOKCancel)
    
    If MsgResponse = vbOK Then
        'Delete the records on the subform [subMycoData] that are & _
        associated with the [AccessionNo] field that links the main & subforms
    
        Do Until Me.subMycoData.Form.Recordset.EOF
            Me.subMycoData.Form.Recordset.Delete
            Me.subMycoData.Form.Recordset.MoveNext
        Loop
    
        'All records in the subform should be deleted before continuting code below
    
        If Me.Dirty Then Me.Dirty = False
 
        For i = 1 To intSmplNo
            strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo)  Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i  & ")"
            CurrentDb.Execute strSQL2, dbFailOnError
            Next i

        Me.subMycoData.Form.Requery
    Else
        Exit Sub
    End If
 End If
 'To close the recordset
 rsMycoData.Close
 
End Sub

Would it be proper to put this code into different sub in a module, such as:

Code:

Sub AutoPopulate_subMycoData()

'To autopopulate the [SampleNo] field in the subform with
    'sequential numbers up to the value in the [NoOfSamples] field in the main form.
    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
 
    If Me.Dirty Then Me.Dirty = False
 
    For i = 1 To intSmplNo
        strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
        CurrentDb.Execute strSQL2, dbFailOnError
        Next i

    Me.subMycoData.Form.Requery
End Sub

and then call this sub in the other procedure when needed, using:

Code:

Call AutoPopulate_subMycoData

The reasoning I have for doing this is to reduce the redundant code (normalize it?) and to call it when it is needed, thus tidying up the first procedure.

**pbaldy** · 03-07-2012, 02:33 PM

You could use a function, but in this instance I wouldn't. I'd change the logic a bit. If I understand the desired flow it's currently:

Code:

If there are no records Then
  Add them
Else there are existing records
  If you want to delete them Then
    Delete old
    Add them
  Else
    Exit Sub
  End If
End If

I would do this, which eliminates the duplication:

Code:

If there ARE records Then
  If you want to KEEP them Then
    Exit Sub
  End If
End If

Add them

Also, more efficient than your loop to delete records would be:

Code:

dbs.Execute "DELETE * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _  
             Forms!frmSampleLogIn01!AccessionNo

**Jester0001** · 03-07-2012, 03:01 PM

Thanks again pbaldy. Works well.

Final Code:

Code:

Private Sub NoOfSamples_AfterUpdate()
Dim dbs As DAO.Database
Dim rsMycoData As DAO.Recordset
Dim strSQL1 As String

'Open pointer to current database
Set dbs = CurrentDb()

'SQL string to select records from [tblMycoData] where the [AccessionNo] & _
is the same as on the form [frmSampleLogIn01]
strSQL1 = "SELECT * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
            
'Create recordset based on SQL1
Set rsMycoData = dbs.OpenRecordset(strSQL1)

'To check if the recordset on the subform [subMycoData] is empty
'then insert sequential values into [SampleNo], or else delete
'recordset before putting in new sequential values into [SampleNo]

    Dim intSmplNo As Integer
    Dim strSQL2 As String
 
    intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
    
'If Me.subMycoData.Form.Recordset.RecordCount = 0 Then
If rsMycoData.RecordCount <> 0 Then

    Dim MsgResponse As Integer
    
    MsgResponse = MsgBox("Changing the number of samples will delete" & vbCrLf & " all records on the subform.", vbOKCancel)
    
    If MsgResponse = vbCancel Then
        Exit Sub
    End If
 
 End If
 
'To force save the record data on the main form [frmSampleLogIn01]
If Me.Dirty Then Me.Dirty = False
    
'To delete all records on the subform [subMycoData]
dbs.Execute "DELETE * FROM tblMycoData WHERE tblMycoData.AccessionNo = " & _
            Forms!frmSampleLogIn01!AccessionNo
    
'To autopopulate the [SampleNo] field in the subform with
'sequential numbers up to the value in the [NoOfSamples] field in the main form.
For i = 1 To intSmplNo
    strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
    CurrentDb.Execute strSQL2, dbFailOnError
    Next i

Me.subMycoData.Form.Requery
 
'To close the recordset
rsMycoData.Close
 
End Sub

**pbaldy** · 03-07-2012, 03:04 PM

Happy to help. By the way, your original error would have been flagged for you if you had Option Explicit at the top of the module:

http://www.baldyweb.com/OptionExplicit.htm

You would have gotten a compile error pointing to what was an undeclared variable.

**Jester0001** · 03-07-2012, 04:20 PM

Cool. I have it set. I can't wait to see what happens while writing my next bit of code

SQL Delete Recordset

Thread Tools

SQL Delete Recordset

Similar Threads

Delete records from a table: Use Find Dups Query & Delete or not?

Recordset

Trying to Delete record using delete query

Delete Query "Could not delete from specified table"

get data from one recordset with the field name being in another recordset

Posting Permissions