For Next Issue

C

crisb184

Registered User.
Local time
Today, 15:15
Joined
Aug 30, 2013
Messages
29
Hello,

I have a For Next statement that I use to cycle through a list of variables. These variables populate a textbox on my form. Once this textbox is populated, i have other listboxes autopopulate based on a query result. My issue is that not every variable will have results in the query, therefore, I want to skip these and move on to the next iteration. I am having some trouble getting an If statement to work here as the continue for will not work, and I cannot get a goto statement to work here.

Here is my code:
Code: 
Dim PMOLead(1 To 8)
    
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
    
    
    For i = 1 To 8
    
    txtPMOLead_Box = PMOLead(i)
    
    If LeadEmail = Null Then GoTo NextIteration
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
    
    sendemail
    
   
    Next i
    
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
    
    'Turn on Warnings
    DoCmd.SetWarnings True

Any help is appreciated.
Thanks,
Cris
 
Use a DCount maybe? I could not be more specific because the question is very vague.
 
Is LeadEmail a text box?

If so, try this:

Code: 
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
    
    
    For i = 1 To 8
        
        txtPMOLead_Box = PMOLead(i)
        
        If LeadEmail.Value <> vbNullString Then
        
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
            sendemail
            
        End If
        
    Next i
    
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
    
    'Turn on Warnings
    DoCmd.SetWarnings True
 
Paul,

Sorry about the vagueness. Let me try to help clarify. I am using this to automatically send out reports in outlook. The variables populate a textbox that autopopulates certain information that I will use to send my emails. So once abc1 is populated on the form, it will autopopulate different textboxes that I will use to sent the email (cc, subject, body, etc.) and the query results are attached.

The problem is that some variables have no results in the query, therefore they leave the autopopulated textboxes empty and it breaks my code. I also do not want to send emails with empty tables. The hope is that I can add an IF statement that says if an autogenerated liset box is Null (example in code is LeadEmail) meaning they have no results in the query, then move on to the next variable in the list.
 
Dan,

Thanks for the code. LeadEmail is a List box.

I entered the code you suggested and got a Compile error: "Next without For"
 
compiles file on mine. Try checking if you pasted correctly. Also, the declaration statement is not at the top of that code snippet. Be careful of that.
 
pr2-eugin said:
Use a DCount maybe? I could not be more specific because the question is very vague.
Click to expand...

I'm guessing they have an event happening here:
Code: 
 txtPMOLead_Box = PMOLead(i)

That alters the value(s) of his LeadEmail listbox (don't know what it is a list box).

This might help you help us lol.
 
Dan,

LeadEmail is a List box.

I did not have the code pasted correctly, but once I ddi paste correctly the code did not kick out an error. Unfortunately, it did not execute anything after the If statement (where it should create the email and attach the file.

I have attached the entire sub so you can see everything. Is my declaration in the wrong spot?

Code: 
Private Sub Command4_Click()
    'Macro to export data for reports
    
    'Turn off Warnings
    DoCmd.SetWarnings False
    
    'Determine and set the path for the database location
    Dim Path
    
    Path = Application.CurrentProject.Path
    
    Dim PMOLead(1 To 8)
    
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
    
    
    For i = 1 To 8
    
    txtPMOLead_Box = PMOLead(i)
    
    If LeadEmail.Value <> vbNullString Then
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
    
    sendemail
    End If
    
    Next i
    
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
    
    'Turn on Warnings
    DoCmd.SetWarnings True
End Sub
 
What happens to your listbox on the event of assigning a value to your txtPMOLead_Box?
Does it assign multiple values to this LeadEmail list box and select a specific value, or does it clear it and add one value without selecting it?
I'm confused as to why this is a listbox :)
 
BlueIshDan said:
I'm guessing they have an event happening here:
Code: 
 txtPMOLead_Box = PMOLead(i)

That alters the value(s) of his LeadEmail listbox (don't know what it is a list box).

This might help you help us lol.
Click to expand...

Dan,

I have the LeadEmail as a List box because when I try to populate the LeadEmail text box using the following code, it does not pull the email from the table, but rather populates the code itself :/

Code: 
Private Sub txtPMOLead_Box_AfterUpdate()
Me.SCOList.RowSource = "SELECT DISTINCT [PastDue.SCO Email] " & _
                            "FROM PastDue " & _
                            "WHERE [PastDue].[PMO Reporting Group] = Nz(txtPMOLead_Box)" & _
                            "ORDER BY [PastDue].[SCO Email];"
Me.LeadEmail.RowSource = "SELECT DISTINCT [PastDue.Lead Email] " & _
                            "FROM PastDue " & _
                            "WHERE [PastDue].[PMO Reporting Group] = Nz(txtPMOLead_Box)" & _
                            "ORDER BY [PastDue].[Lead Email];"
 
Me.LeadEmailTextbox.Value = "SELECT DISTINCT [PastDue.Lead Email] " & _
                            "FROM PastDue " & _
                            "WHERE [PastDue].[PMO Reporting Group] = Nz(txtPMOLead_Box)" & _
                            "ORDER BY [PastDue].[Lead Email];"
End Sub
 
Oh dear.
Try this with a text box?
Code: 
    LeadEmail.Value = Nz(DLookup("[Lead Email]", "PastDue", "[PMO Reporting Group] = """ & Nz(txtPMOLead_Box.Value, "") & """"), vbNullString)
 
Dan,

I know I am really not good at this :) but appreciate you helping me out.

Your code to make the LeadEmail a textbox works perfectly. The problem I am running into now is that the If statement will not do anything because the LeadEmail field is empty to start out, it only get populated after the txtPMOLead_Box gets a value from the For loop, which triggers the AfterUpdate event that populates the LeadEmail textbox. Another issue I noticed is taht if I enter something in the LeadEmail box, then it will cycle through all variables as normal.

Do I need to refresh the form before every If statement?
 
Did you change it to check the textbox for a value istead of the list box? :)
 
It looks like you are using a lot of references to values in controls without referring to the form containing those controls, either by Me (if the code is in the form's module) or a form object (if it isn't).

Also, there's no need for a goto. You don't even need to count the number of objects if you're using an array. You can set it up along these lines:

Code: 
Dim PMOLead(1 To 8)
 
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
 
 
    For i = LBound(PMOLead) To UBound(PMOLead)
 
        txtPMOLead_Box = Nz(PMOLead(i), "")
 
        If Nz(Me.LeadEmail, "") <> "" Then
 
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
 
            sendemail
 
        End If
 
    Next i
 
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
 
    'Turn on Warnings
    DoCmd.SetWarnings True

Obviously that's just pseudocode, but that's at least the kind of structure I'd suggest. This doesn't resolve what Dan is covering with the text box values, but eliminates the need for a Goto.
 
I did :) the listbox is gone completely.

I think that it is executing the For code without allowing time for the LeadEmail textbox to update itself. I think this is why if it is null to start out then nothing happens after the if, and if a value is in there then it generates something for all 8 variables.

I have the AfterUpdate event in the txtPMOLead_Box. Should this be another type of event (got focus, etc) or should this afterupdate be somewhere else?
 
Say LeadEmail.Value <> vbNullString
Also, it shouldn't be. That would require another thread. What happens in reality is that it jumps to process the code in the after update event then returns to your click event's code.
 
Last edited:
show me what you now have for both the after update and the click event please.
 
Here is my click Event code:
Code: 
Private Sub Command4_Click()
    'Macro to export data for reports
    
    'Turn off Warnings
    DoCmd.SetWarnings False
    
    'Determine and set the path for the database location
    Dim Path
    
    Path = Application.CurrentProject.Path
    
    Dim PMOLead(1 To 8)
        
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
    
    
    For i = 1 To 8
    txtPMOLead_Box = PMOLead(i)
    
    If LeadEmail.Value <> vbNullString Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
    
    sendemail
    End If
    
    Next i
    
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
    
    'Turn on Warnings
    DoCmd.SetWarnings True
End Sub

Here is my After Event Code:
Code: 
Private Sub txtPMOLead_Box_AfterUpdate()
Me.SCOList.RowSource = "SELECT DISTINCT [PastDue.SCO Email] " & _
                            "FROM PastDue " & _
                            "WHERE [PastDue].[PMO Reporting Group] = Nz(txtPMOLead_Box)" & _
                            "ORDER BY [PastDue].[SCO Email];"
                            
LeadEmail.Value = Nz(DLookup("[Lead Email]", "PastDue", _
"[PMO Reporting Group] = """ & Nz(txtPMOLead_Box.Value, "") & """"), vbNullString)
End Sub
 
Put a breakpoint on the highlighted lines.
When you get to these points, hover your mouse over the ".value" areas of your text boxes before and after the lines are executed. This will tell us what values are being passed around and hopefully let me know that your information is being received from that DLookup.

If not, give me your db and I will do it.

crisb184 said:
Here is my click Event code:
Code: 
Private Sub Command4_Click()
    'Macro to export data for reports
    
    'Turn off Warnings
    DoCmd.SetWarnings False
    
    'Determine and set the path for the database location
    Dim Path
    
    Path = Application.CurrentProject.Path
    
    Dim PMOLead(1 To 8)
        
    PMOLead(1) = "abc1"
    PMOLead(2) = "abc2"
    PMOLead(3) = "abc3"
    PMOLead(4) = "abc4"
    PMOLead(5) = "abc5"
    PMOLead(6) = "abc6"
    PMOLead(7) = "abc7"
    PMOLead(8) = "abc8"
    
    
    For i = 1 To 8
    txtPMOLead_Box = PMOLead(i)
    
[COLOR="Red"]    If LeadEmail.Value <> vbNullString Then[/COLOR]
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PastDue", Path & "\YTD Past Due_" & PMOLead(i) & "_" & ".xlsx", True
    
    sendemail
    End If
    
    Next i
    
    'Notify completion
    MsgBox "Export Complete", vbOKOnly, "Export Notification"
    
    'Turn on Warnings
    DoCmd.SetWarnings True
End Sub

Here is my After Event Code:
Code: 
Private Sub txtPMOLead_Box_AfterUpdate()
Me.SCOList.RowSource = "SELECT DISTINCT [PastDue.SCO Email] " & _
                            "FROM PastDue " & _
                            "WHERE [PastDue].[PMO Reporting Group] = Nz(txtPMOLead_Box)" & _
                            "ORDER BY [PastDue].[SCO Email];"
                            
LeadEmail.Value = Nz(DLookup("[Lead Email]", "PastDue", _
[COLOR="red"]"[PMO Reporting Group] = """ & Nz(txtPMOLead_Box.Value, "") & """"), vbNullString)[/COLOR]
End Sub
Click to expand...
 
You must log in or register to reply here.

Similar threads

jeran042
Solved Export Multiple Record sets to Multiple Excel Sheets
Replies
8
Views
1,577
theDBguy
theDBguy
J
updating textbox from combobox for editing
Replies
4
Views
963
JJSHEP89
J
Y
VBA code for exporting an Access crosstab query into an excel template
Replies
6
Views
2,884
JHB
J
AOB
Function error (run time 91) seems to bypass handler? (Access 2007)
Replies
8
Views
2,060
AOB
AOB
T
1004: Application-defined or object-defined error
Replies
3
Views
5,537
CJ_London
C

Users who are viewing this thread

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