Recordset issues when using two subforms as recordset

F

freightguy

Registered User.
Local time
Today, 03:40
Joined
Mar 1, 2016
Messages
36
Hello everyone - I've been at this recordset issue all week and decided to come here and see if anyone can help point out what I'm doing wrong.

my goal: send an email with the data that was entered by user
what works so far: if I use ONE recordset it works perfectly
where it fails: the moment I add a second recordset (main form has two subforms) it crashes

Code: 
       Me.[frmExportShipmentDetails].Form.Recordset.MoveFirst
        Do While Not Me.[frmExportShipmentDetails].Form.Recordset.EOF
            strBody = strBody & "Line Items: " & Me.[frmExportShipmentDetails]![ordProductCode] & " | " & Me.[frmExportShipmentDetails]![ordProductDesc] & Chr(13)
            Me.[frmExportShipmentDetails].Form.Recordset.MoveNext
        Loop
                
        Me.[subfrmPelicanDIMS].Form.Recordset.MoveFirst [COLOR="DarkOrange"]' THIS IS WHERE I GET ERROR CODE 3021 - BUT I KNOW THERE IS A RECORD[/COLOR]
        Do While Not Me.[subfrmPelicanDIMS].Form.Recordset.EOF& Chr(13)
              strBody = strBody & "Details Items: " & Me.[subfrmPelicanDIMS]![Desc] & " | " & Me.[subfrmPelicanDIMS]![Case Nbr] & Chr(13)
              Me.[subfrmPelicanDIMS].Form.Recordset.MoveNext
        Loop


This is the code I used to set things up: (I figured creating a second instance of recordset would work. I suspect this is where my problem is)

Code: 
Dim db As DAO.Database
Dim rsDetail As DAO.Recordset
Dim strSQL As String

Dim db2 As DAO.Database
Dim rsDetail2 As DAO.Recordset
Dim strSQL2 As String

strSQL = "SELECT * FROM tblexportShipment WHERE expinvnbr = '" & Me!ProjectNumber & "'"
strSQL2 = "SELECT * FROM tblPelicanDIMS WHERE expid = " & Me!expID & ""

On Error GoTo ErrorHandler   'trap the errors

Set db = CurrentDb()
Set rsDetail = db.OpenRecordset(strSQL)

Set db2 = CurrentDb()
Set rsDetail2 = db.OpenRecordset(strSQL2)

thanking you all in advance.
Ray
 
Last edited:
Try using form RecordsetClone instead.

Don't concatenate empty string to expID.
 
I don't see how your 'set things up' code is related to your 'problem' code. The setup code opens a couple of stand-alone recordsets, but your problem code is consuming recordsets exposed by what look like subforms. Is there code that you haven't shown us that assigns the recordsets created in the setup code to the recordset properties of the subforms???
Mark
 
thanks June7 and Mark - sure thing. See below entire code for command button that runs the sequence. This one works exactly as expected. The moment I add a second recordset I get the error. BTW the SQL for the second recordset I'm trying to run uses the primary key field. Not sure if that would cause a problem or not. (thanks June7 for pointing out the string code error)

Code: 
[CODE]Private Sub Command20_Exit(Cancel As Integer)

Dim strEmail, strBody As String
Dim objOutlook As Object
Dim objEmail As Object

Dim db As DAO.Database
Dim rsDetail As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblexportShipment WHERE expinvnbr = '" & Me!ProjectNumber & "'"
'strSQL = "SELECT * FROM qryExportALLDetails WHERE expinvnbr = '" & Me!ProjectNumber & "'"


On Error GoTo ErrorHandler   'trap the errors

Set db = CurrentDb()
Set rsDetail = db.OpenRecordset(strSQL)
'collects the info to send via email

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'**************************************************************
'*create string with email address

strEmail = "ray@xxxxxxxx.com"
strBody = strBody & "Hello Shipping" & Chr(13) & Chr(13)
strBody = strBody & "I/We have submitted a shipping request. Please acknowledge receipt at your earliest." & Chr(13) & Chr(13)

strBody = strBody & "Date of Request:  " & TRDate & Chr(13)
strBody = strBody & "Ready to Ship Date:  " & SRreadytoshipdate & Chr(13)
strBody = strBody & "Must Be Delivered By Date:  " & Text189 & Chr(13) & Chr(13)

strBody = strBody & "Project Number:  " & UCase(ProjectNumber) & Chr(13)
strBody = strBody & "Incoterm:  " & IncotermCode & Chr(13)
strBody = strBody & "Transport Mode:  " & TransportMode & Chr(13)
strBody = strBody & "Service Level:  " & ServiceLevel & Chr(13)
strBody = strBody & "DG Flag (Non Hazardous = 0, Hazardous = -1):  " & DG & Chr(13)
strBody = strBody & "Dangerous Goods Description:  " & DGDesc & Chr(13) & Chr(13)

strBody = strBody & "Shipper Tax ID:  " & [expShipperTaxID] & Chr(13)
strBody = strBody & "Shipper Info:  " & [expShipperName] & ", " & expShipperAddress1 & ", " & expShipperCity & ", " & expShipperProv & ", " & expShipperPC & ", " & expShipperCountry & Chr(13)
strBody = strBody & "Shipper Contact Info:  " & expShipperContact & ", " & expShipperTel & Chr(13) & Chr(13)

strBody = strBody & "Consignee Tax ID:  " & [expConsigneeTaxID] & Chr(13)
strBody = strBody & "Consignee Info:  " & [expConsigneeName] & ", " & expConsigneeAddress1 & ", " & expConsgineeCity & ", " & expConsigneeProv & ", " & expConsigneePC & ", " & expCountry & Chr(13)
strBody = strBody & "Consignee Contact Info:  " & expContactName & ", " & expConsigneeTel & Chr(13) & Chr(13)

Me.[frmExportShipmentDetails].Form.Recordset.MoveFirst
Do While Not Me.[frmExportShipmentDetails].Form.Recordset.EOF
strBody = strBody & "Line Items: " & Me.[frmExportShipmentDetails]![ordProductCode] & " | " & Me.[frmExportShipmentDetails]![ordProductDesc] & " | " & Me.[frmExportShipmentDetails]![ordHSCode] & " | " & Me.[frmExportShipmentDetails]![ordCOO] & " | " & Me.[frmExportShipmentDetails]![ordProductPrice] & " | " & Me.[frmExportShipmentDetails]![ordCur] & Chr(13)
'strBody = strBody & "Details Items: " & Me.[qryExportAllDetails]![Desc] & " | " & Me.[qryExportAllDetails]![Case Nbr] & " | " & Me.[qryExportAllDetails]![Length (in)] & " | " & Me.[qryExportAllDetails]![Width (in)] & " | " & Me.[qryExportAllDetails]![Height (in)] & " | " & Me.[qryExportAllDetails]![GrossKgs] & " | " & Me.[qryExportAllDetails]![Height (in)] & Chr(13)
Me.[frmExportShipmentDetails].Form.Recordset.MoveNext
Loop


strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & UCase(RequesterName) & Chr(13)
strBody = strBody & "XXXX XXXXXXX Ltd."

'***creates and sends email
With objEmail

    If Not IsNull(strEmail) Then
    .To = strEmail
    End If
    .Subject = "SHIPPING REQUEST FOR PROJECT NUMBER " & UCase(ProjectNumber)
    If Not IsNull(Text196) Then
       .CC = (Text196)
    End If
    .Body = strBody
    If Not IsNull(Text194) Then ' Or Not IsNull(Text196) Then
    .Send
    End If
End With


Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit

Set rsDetail = Nothing
'rsDetail.Close - this bit of code is causing an error 91 therefore I dimmmed it out for now

Exit Sub

ErrorHandler:
    Dim iAnswer As Integer
    
    Select Case Err.Number
        Case 0
            Resume
        Case 20
            Exit Sub
        Case 3021
            Exit Sub
        Case 3265
            Exit Sub
        Case Else
            MsgBox "Unexpected error occured. " & Err.Number & " " & Err.description
            Exit Sub
     End Select



End Sub
[/CODE]
 
yes and still working at it (you're reply sent me back to the text books to read up on recordsetclone ;)
thank you
 
So this code you posted opens outlook and constructs an email message? Not sure how this is related to the earlier post.
Mark
 
Building string from two subforms records to include in email body. Gets error when referencing Recordset of second form. I would use RecordsetClone.

Do you really want to repeat all that header data for each record? Then if you continue to use strBody for the second recordset you are repeating eveything again. Really need 2 other variables to build the detail data and concatenate those to the strBody variable. Could even use HTML tags to build tables.
 
Last edited:
My bad. I posted the code that works well. Below is the same code when I try to add a second recordset using a different subform.

Code: 
Private Sub Command20_Exit(Cancel As Integer)

Dim strEmail, strBody As String
Dim objOutlook As Object
Dim objEmail As Object

Dim db As DAO.Database
Dim rsDetail As DAO.Recordset
Dim strSQL As String

Dim db2 As DAO.Database
Dim rsDetail2 As DAO.Recordset
Dim strSQL2 As String

strSQL = "SELECT * FROM tblexportShipment WHERE expinvnbr = '" & Me!ProjectNumber & "'"
strSQL2 = "SELECT * FROM tblPelicanDIMS WHERE expid =  & Me!expID & "

On Error GoTo ErrorHandler   'trap the errors

Set db = CurrentDb()
Set rsDetail = db.OpenRecordset(strSQL)

Set db2 = CurrentDb()
Set rsDetail2 = db.OpenRecordset(strSQL2)

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'**************************************************************
'*create string with email address

strEmail = "ray@xxxxxx.com"
strBody = ""
strBody = strBody & "Hello Shipping" & Chr(13) & Chr(13)
strBody = strBody & "I/We have submitted a shipping request. Please acknowledge receipt at your earliest." & Chr(13) & Chr(13)
strBody = strBody & "Date of Request:  " & SRDate & Chr(13)
strBody = strBody & "Ready to Ship Date:  " & SRreadytoshipdate & Chr(13)
strBody = strBody & "Must Be Delivered By Date:  " & Text189 & Chr(13) & Chr(13)

strBody = strBody & "Project Number:  " & UCase(ProjectNumber) & Chr(13)
strBody = strBody & "Incoterm:  " & IncotermCode & Chr(13)
strBody = strBody & "Transport Mode:  " & TransportMode & Chr(13)
strBody = strBody & "Service Level:  " & ServiceLevel & Chr(13)
strBody = strBody & "DG Flag (Non Hazardous = 0, Hazardous = -1):  " & DG & Chr(13)
strBody = strBody & "Dangerous Goods Description:  " & DGDesc & Chr(13) & Chr(13)

strBody = strBody & "Shipper Tax ID:  " & [expShipperTaxID] & Chr(13)
strBody = strBody & "Shipper Info:  " & [expShipperName] & ", " & expShipperAddress1 & ", " & expShipperCity & ", " & expShipperProv & ", " & expShipperPC & ", " & expShipperCountry & Chr(13)
strBody = strBody & "Shipper Contact Info:  " & expShipperContact & ", " & expShipperTel & Chr(13) & Chr(13)

strBody = strBody & "Consignee Tax ID:  " & [expConsigneeTaxID] & Chr(13)
strBody = strBody & "Consignee Info:  " & [expConsigneeName] & ", " & expConsigneeAddress1 & ", " & expConsgineeCity & ", " & expConsigneeProv & ", " & expConsigneePC & ", " & expCountry & Chr(13)
strBody = strBody & "Consignee Contact Info:  " & expContactName & ", " & expConsigneeTel & Chr(13) & Chr(13)

 
        Me.[frmExportShipmentDetails].Form.Recordset.MoveFirst
        Do While Not Me.[frmExportShipmentDetails].Form.Recordset.EOF
            strBody = strBody & "Line Items: " & Me.[frmExportShipmentDetails]![ordProductCode] & " | " & Me.[frmExportShipmentDetails]![ordProductDesc] & " | " & Me.[frmExportShipmentDetails]![ordHSCode] & " | " & Me.[frmExportShipmentDetails]![ordCOO] & " | " & Me.[frmExportShipmentDetails]![ordProductPrice] & " | " & Me.[frmExportShipmentDetails]![ordCur] & Chr(13)
            Me.[frmExportShipmentDetails].Form.Recordset.MoveNext
        Loop
                
        Me.[subfrmPelicanDIMS].Form.Recordset.MoveFirst
        Do While Not Me.[subfrmPelicanDIMS].Form.Recordset.EOF
             'strBody = strBody & "Line Items: " & Me.[frmExportShipmentDetails]![ordProductCode] & " | " & Me.[frmExportShipmentDetails]![ordProductDesc] & " | " & Me.[frmExportShipmentDetails]![ordHSCode] & " | " & Me.[frmExportShipmentDetails]![ordCOO] & " | " & Me.[frmExportShipmentDetails]![ordProductPrice] & " | " & Me.[frmExportShipmentDetails]![ordCur] & Chr(13)
              strBody = strBody & "Details Items: " & Me.[subfrmPelicanDIMS]![Desc] & " | " & Me.[subfrmPelicanDIMS]![Case Nbr] & " | " & Me.[subfrmPelicanDIMS]![Length (in)] & " | " & Me.[subfrmPelicanDIMS]![Width (in)] & " | " & Me.[subfrmPelicanDIMS]![Height (in)] & " | " & Me.[subfrmPelicanDIMS]![GrossKgs] & " | " & Me.[subfrmPelicanDIMS]![Height (in)] & Chr(13)
              Me.[subfrmPelicanDIMS].Form.Recordset.MoveNext
        Loop
 
 
strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & UCase(RequesterName) & Chr(13)
strBody = strBody & "XXX XXXXXXX Ltd."

'***creates and sends email
With objEmail

    If Not IsNull(strEmail) Then
    .To = strEmail
    End If
    .Subject = SRDate & " | " & "SR | " & UCase(expConsgineeCity) & " | " & UCase(RequesterName) & " | " & UCase(ProjectNumber)
    If Not IsNull(Text196) Then
       .CC = (Text196)
    End If
    .Body = strBody
    If Not IsNull(Text194) Then ' Or Not IsNull(Text196) Then
    .Send
    End If
End With


Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit

Set rsDetail = Nothing
Set rsDetail2 = Nothing
'rsDetail.Close - this bit of code is causing an error 91

Exit Sub

ErrorHandler:
    Dim iAnswer As Integer
    
    
    Select Case Err.Number
        Case 0
            Resume
        Case 20
            MsgBox "error code 20"
            Exit Sub
        Case 3021
            MsgBox "error code 3021"
            Exit Sub
        Case 3265
            MsgBox "error code 3265"
            Exit Sub
        Case Else
            MsgBox "Unexpected error occured. " & Err.Number & " " & Err.description
            Exit Sub
     End Select



End Sub
 
Did you read post 8?

I just did a test using Recordset of 2 subforms and don't get error. Each recordset has its own string variable.

So likely using RecordsetClone won't resolve issue. Something else is wrong.
 
Last edited:
Well that's doesn't sound promising :eek:
I didn't notice your post #8 until you mentioned it. Thanks
If the one SQL query included ALL fields required to send in email, it might work that way. I would only need one recordset to work with.
thanks for looking into this. I'll come back when I get this right and post the code.

Cheers!
 
The two recordsets you create at the start of the routine never end up getting used.
Code: 
Private Sub Command20_Exit(Cancel As Integer)
[COLOR="Green"]    '...[/COLOR]
    Set db = CurrentDb()
    Set rsDetail = db.OpenRecordset(strSQL)
    Set rsDetail2 = db.OpenRecordset(strSQL2)
[COLOR="green"]    '...[/COLOR]
    Set rsDetail = Nothing
    Set rsDetail2 = Nothing
End Sub
Is there something you intended to do with them? Are you confused between the recordsets in the subforms and the ones you opened here?
Mark
 
Hmmm
strSQL is being used because it produces a list of items (part number, qty, etc..) shipped. (this part works)
strSQL2 is suppose to return the weight and dimensions of the case shipped. I can't tell if it works because thats the second recordset where I'm having problems with.
 
You never use rsDetail and rsDetail2 recordset objects. Therefore strSQL (and strSQL2) accomplishes nothing. Data is coming from the main form record and subform Recordset.

You originally said the second subform Recordset was causing error. Is that still the case? Try commenting the first and see if the second runs solo.

The procedure says closing rsDetail causes error. Get rid of code pertaining to those recordset objects.
 
Last edited:
Interesting. You're both right. I just dimmed out all the code with recordset and I was still able to send email successfully. The code I used was done several years ago. I'm going to have to dig up my notes and see what the recordset was all about.
thank you both again
 
QUOTE
You originally said the second subform Recordset was causing error. Is that still the case? Try commenting the first and see if the second runs solo.

The procedure says closing rsDetail causes error. Get rid of code pertaining to those recordset objects
UNQUOTE

Yes still causing error. Was in the middle doing just that. Commented the code that worked to see if the second part works - errors code 2465.

Now checking my syntax, comma's, & etc ...
 
First hats off to both June7 and Mark for pointing out some useless code and other errors.

This code was riddled with syntax errors. First I was referencing the wrong subform name then I had a typo when referring to one of the fields.

All good now and thank you again. The more I cleaned up the code the closer I got to my goal.

Cheers!
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
458
oxicottin
oxicottin
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
625
CJ_London
C
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
1,412
mloucel
mloucel
A
Duplicate Records in Main Form and Subform
2
Replies
20
Views
1,818
Pat Hartman
P
K
Error 13 | Audit Log
Replies
4
Views
965
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom