Referring to a listbox from Main form in a module

R

rywello

Registered User.
Local time
Today, 16:50
Joined
Jan 13, 2016
Messages
68
I am currently working on code in a module that once you make a selection in a listbox it will open up an email with the selection from the listbox populated in a table. Right now my code is bringing back everything that is in the access table and not just the selection in the listbox. I am using the below code. I don't think that it is recognizing the listbox and it is just pulling the information from tblTrucks.

strSQL = "SELECT * FROM tblTrucks WHERE ([ID] = " & Forms!Main.lstorders.Column(0, i) & " )"
Set rst = CurrentDb.OpenRecordset(strSQL)

Not sure if you need more information or not. Any help would be appreciated.

Thanks!
 
assuming your bound column is 1:

strSQL = "SELECT * FROM tblTrucks WHERE [ID] = " & Forms!Main.lstorders
 
When I put that code in I get syntax error (missing operator) in query expression '[ID]=' then it highlights Set rst = CurrentDb.OpenRecordset(strSQL).
 
dont know if multiselect listbox or not?
 
I am able to get strSQL = "SELECT * FROM tblTrucks WHERE [ID] = " & Forms!Main.lstorders.Column(0) to work but I can only send one record at a time. I'm not able to send 2 records at a time. Do you know what I need to do to get this part to work?
 
Actually, It will work but if I select only 1 record to e-mail, it will automatically populate with all the records that have the same ID whether or not I selected it. How do I get it to send the records that I have selected in the listbox?
 
dim strList As String
dim varItem As Variant
for each VarItem In Forms!Main!lstorders.ItemsSelected
strList = strList & Forms!Main!lstorders.Column(0, varItem) & ","
next
if strList <> "" Then
strList = Left(strList, Len(strList)-1)
strList = "(" & strList & ")"
strSQL = "SELECT * FROM tblTrucks WHERE [ID] IN " & strList & ";"
(send e-mail)
else
(messsage nothing is selected from the list)
end if
 
Last edited:
Hi Just a follow-up on the code that you provided. When I don't select anything from the list it throws the runtime 3078 on the rest of my code and highlights the code in red. It works perfectly when I select items. Any suggestions? Thanks,

Set rst = CurrentDb.OpenRecordset(strSQL)


strTableBody = strTableBeg & strFntNormal & strTableHeader


Do Until rst.EOF
strTableBody = strTableBody & _
"<tr>" & _
TD(rst![Customer]) & _
TD(rst![Ship-to]) & _
TD(rst![Sales Doc]) & _
TD(rst![PO#]) & _
TD(rst![City]) & _
TD(rst![Mat Description]) & _
TD(rst![PlGI date]) & _
TD(rst![Plant Anticipated Date]) & _
"<tr>"

strBodyText = "<HTML><BODY>Hi<BR><BR>I wanted to advise you that we are currently experiencing constraints which may delay your order. Please let me know if you have any questions.<BR></BODY></HTML>"

rst.MoveNext
Loop

strTableBody = strTableBody & strFntEnd & strTableEnd

rst.Close
 
check my last post, you should put your code inside the first IF:

If strList <> ""
' rest of you code here
Else
Msgbox "Please select recipient from the listbox."
End If
 
I updated the code to below and it gives me the warning to select an order and when I click ok it still opens an email with the strBodyText in it. How do I prevent that from happening?

If strList <> "" Then
strList = Left(strList, Len(strList) - 1)
strList = "(" & strList & ")"
strSQL = "SELECT * FROM tblTrucks WHERE [ID] IN " & strList & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)


strTableBody = strTableBeg & strFntNormal & strTableHeader

Do Until rst.EOF
strTableBody = strTableBody & _
"<tr>" & _
TD(rst![PO#]) & _
TD(rst![City]) & _
TD(rst![Mat Description]) & _
TD(rst![Sales Doc]) & _
TD(rst![PlGI date]) & _
TD(rst![Plant Anticipated Date]) & _
"<tr>"

strBodyText = "<HTML><BODY>Hi<BR><BR>I wanted to advise you that we are currently experiencing constraints which may delay your order. Affected order(s) along with your requested and anticipated ship date is listed below. For orders without an anticipated ship date, we are working to confirm the load date and will provide you with this date as soon as it is available. Please let me know if you have any questions.<BR></BODY></HTML>"

rst.MoveNext

Loop
rst.Close
strTableBody = strTableBody & strFntEnd & strTableEnd

'(send e-mail)

Else
MsgBox ("Please select an order from the list.")

End If

End With
 
Else
MsgBox ("Please select an order from the list.")
Exit Sub
End If
 
I have another question for this thread. How do I allow for Null values if one of the columns have no data. The information shows in the list box with no value but when I try to send the e-mail based on the list box selection and one of the columns are blank and the other columns have data then I get an error Invalid use of Null. Some of the fields may not have data which is ok. How do I get rid of this error?
 
concatenate the empty column to an empty string:

strTableBody = strTableBody & _
"<tr>" & _
TD(rst![PO#]) & "" & _
TD(rst![City]) & "" & _
TD(rst![Mat Description]) & "" & _
TD(rst![Sales Doc]) & "" & _
TD(rst![PlGI date]) & "" & _
TD(rst![Plant Anticipated Date]) & "" & _
"<tr>"
 
I still get the same error when I concatenate the empty column to an empty string.
 
strTableBody = strTableBody & _
"<tr>" & _
TD(rst![PO#]) & "" & _
TD(rst![City]) & "" & _
TD(rst![Mat Description] & "") & _
TD(rst![Sales Doc] & "") & _
TD(rst![PlGI date] & "") & _
TD(rst![Plant Anticipated Date] & "") & _
"<tr>"
 
You must log in or register to reply here.

Similar threads

T
ListBox Doesn't Show Selected Values
Replies
10
Views
458
MajP
MajP
D
Solved Cannot Assign ActiveControl/ Screen.ActiveControl
Replies
8
Views
457
isladogs
isladogs
C
Matching vba recordset to form recordset
Replies
12
Views
703
Gasman
Gasman
GregDataReno
Listbox ItemsSelected returns NULL item on first call - but not if debugging - not a DoEvent problem
2
Replies
38
Views
3,191
GregDataReno
GregDataReno
B
VBA class modules & factory design
2
Replies
29
Views
2,236
bodders24
B

Users who are viewing this thread

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