indyng
asked on
How do I read data from a table into a string until all data matching the number in a text box on a form is read in?
Hi Experts,
How do I read data from a table into a string until all data matching the number in a text box on a form is read in?
This is the VBA code I have:
strtext = "ORDER DETAILS" & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "CustomerID: " & CustomerID & vbCrLf
strtext = strtext & "Customer Name: " & CustomerName & vbCrLf
strtext = strtext & "Address: " & Address & vbCrLf
strtext = strtext & "Order Date: " & OrderDate & vbCrLf
strtext = strtext & "Order Time: " & OrderTime & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "OrderID:" & OrderID & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & vbCrLf
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", strtext, 0
I want to read in the details of the delivery into strtext until all records matching the OrderID entered into a textbox in a form is read in. Do I do this with a do while loop? or Do until EOF?
Also how do I put a space between data that is read in? For example, I want a space between OrderID. How can I do this?
strtext = strtext & "OrderID:" & OrderID & OrderID & OrderID & vbCrLf
Thanks
How do I read data from a table into a string until all data matching the number in a text box on a form is read in?
This is the VBA code I have:
strtext = "ORDER DETAILS" & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "CustomerID: " & CustomerID & vbCrLf
strtext = strtext & "Customer Name: " & CustomerName & vbCrLf
strtext = strtext & "Address: " & Address & vbCrLf
strtext = strtext & "Order Date: " & OrderDate & vbCrLf
strtext = strtext & "Order Time: " & OrderTime & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "OrderID:" & OrderID & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & vbCrLf
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", strtext, 0
I want to read in the details of the delivery into strtext until all records matching the OrderID entered into a textbox in a form is read in. Do I do this with a do while loop? or Do until EOF?
Also how do I put a space between data that is read in? For example, I want a space between OrderID. How can I do this?
strtext = strtext & "OrderID:" & OrderID & OrderID & OrderID & vbCrLf
Thanks
Hi indyng,
Here's a snippet with your code.
Put it in the event code of on-click event of a botton or something, - it mustbe run from the form.
Read the comments and notes in the code, and perform the tasks I've specified.
As I'm not able to test the code of course you must post any errors here ASAP.
-------------------------- ---------- ---------- ---------- ------
Dim xxDb As Database
Dim xxRec As DAO.Recordset
Dim strSQL As String
Dim xxTmpDate As String, xxTmpID As String
Dim xxDate As String, xxID As String, xxName As String
Dim xxStrOutput As String
'NOTES:
' You Ned to edit the following:
' TableName - name of the table or query your data is taken from
' RefField - name of the field in the data table/query to match against control on form
' RefControl - name of the control on th form controlling the selection
strSQL = "SELECT * From [TableName]WHERE [RefField]=" & Me.RefControl
Set xxDb = CurrentDb()
Set xxRec = xxDb.OpenRecordset(strSQL)
xxRec.MoveLast
xxRec.MoveFirst
If xxRec.RecordCount = 0 Then
MsgBox "No items found in selection"
Else
' You can format the one-time header here
xxStrOutput = "ORDER DETAILS" & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
accrlf
For i = 0 To xxRec.RecordCount - 1
' This code walks through all records and appnds data to the string
With xxRec
' I put some tabsin it 'Chr(9)', - remov thm if they're bad looking
xxStrOutput = xxStrOutput & "CustomerID: " & Chr(9) & Chr(9) & .Fields("CustomerID") & vbCrLf
xxStrOutput = xxStrOutput & "Customer Name: " & Chr(9) & Chr(9) & .Fields("CustomerName") & vbCrLf
xxStrOutput = xxStrOutput & "Address: " & Chr(9) & Chr(9) & .Fields("Address") & vbCrLf
xxStrOutput = xxStrOutput & "Order Date: " & Chr(9) & Chr(9) & .Fields("OrderDate") & vbCrLf
xxStrOutput = xxStrOutput & "Order Time: " & Chr(9) & Chr(9) & .Fields("OrderTime") & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
xxStrOutput = xxStrOutput & "OrderID:" & Chr(9) & Chr(9) & .Fields("OrderID") & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
' get next record
.MoveNext
End With
Next
xxRec.Close
MsgBox xxStrOutput
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", xxStrOutput, 0
End If
-------------------------- ---------- ---------- ---------- -----
Kind regards,
Sven
Here's a snippet with your code.
Put it in the event code of on-click event of a botton or something, - it mustbe run from the form.
Read the comments and notes in the code, and perform the tasks I've specified.
As I'm not able to test the code of course you must post any errors here ASAP.
--------------------------
Dim xxDb As Database
Dim xxRec As DAO.Recordset
Dim strSQL As String
Dim xxTmpDate As String, xxTmpID As String
Dim xxDate As String, xxID As String, xxName As String
Dim xxStrOutput As String
'NOTES:
' You Ned to edit the following:
' TableName - name of the table or query your data is taken from
' RefField - name of the field in the data table/query to match against control on form
' RefControl - name of the control on th form controlling the selection
strSQL = "SELECT * From [TableName]WHERE [RefField]=" & Me.RefControl
Set xxDb = CurrentDb()
Set xxRec = xxDb.OpenRecordset(strSQL)
xxRec.MoveLast
xxRec.MoveFirst
If xxRec.RecordCount = 0 Then
MsgBox "No items found in selection"
Else
' You can format the one-time header here
xxStrOutput = "ORDER DETAILS" & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
accrlf
For i = 0 To xxRec.RecordCount - 1
' This code walks through all records and appnds data to the string
With xxRec
' I put some tabsin it 'Chr(9)', - remov thm if they're bad looking
xxStrOutput = xxStrOutput & "CustomerID: " & Chr(9) & Chr(9) & .Fields("CustomerID") & vbCrLf
xxStrOutput = xxStrOutput & "Customer Name: " & Chr(9) & Chr(9) & .Fields("CustomerName") & vbCrLf
xxStrOutput = xxStrOutput & "Address: " & Chr(9) & Chr(9) & .Fields("Address") & vbCrLf
xxStrOutput = xxStrOutput & "Order Date: " & Chr(9) & Chr(9) & .Fields("OrderDate") & vbCrLf
xxStrOutput = xxStrOutput & "Order Time: " & Chr(9) & Chr(9) & .Fields("OrderTime") & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
xxStrOutput = xxStrOutput & "OrderID:" & Chr(9) & Chr(9) & .Fields("OrderID") & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
xxStrOutput = xxStrOutput & vbCrLf
' get next record
.MoveNext
End With
Next
xxRec.Close
MsgBox xxStrOutput
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", xxStrOutput, 0
End If
--------------------------
Kind regards,
Sven
Are you using Access97, 2000 or 2002?
ASKER
Hi svenkarlsen
Thanks for taking the time to write the code.I had mentioned that I want to read data from a table. Actually, it's a query that I want to read from. How will this change the line
strSQL = "SELECT * From [TableName]WHERE [RefField]=" & Me.RefControl
I tried just putting the query name in the TableName but I get an error saying, "Too few parameters. Exptected 1
This is the code I have:
Private Sub SendDeliveryClick_Click()
Dim strtext As String
Dim xxRec As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & Forms!Orders.OrderID
Set xxRec = CurrentDb.OpenRecordset(st rSQL)
xxRec.MoveLast
xxRec.MoveFirst
If xxRec.RecordCount = 0 Then
MsgBox "No items found in selection"
Else
strtext = "ORDER DETAILS" & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "CustomerID: " & CustomerID & vbCrLf
strtext = strtext & "Customer Name: " & CustomerName & vbCrLf
strtext = strtext & "Address: " & Address & vbCrLf
strtext = strtext & "Order Date: " & OrderDate & vbCrLf
strtext = strtext & "Order Time: " & OrderTime & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "OrderID:" & OrderID & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "Item Code & Chr(9) Item Name & Chr(9) Qty & Chr(9) Price & Chr(9) Total" & vbCrLf
strtext = strtext & vbCrLf
For i = 0 To xxRec.RecordCount - 1
With xxRec
strtext = strtext & MenuItemID & Chr(9) & ItemName & Chr(9) & Quantity & Chr(9) & UnitPrice & Chr(9) & Total
' get next record
.MoveNext
End With
Next
xxRec.Close
MsgBox strtext
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", strtext, 0
'last parameter sends email right away. Use -1 to edit email before sending
End If
How can I resolve this? Thanks
Thanks for taking the time to write the code.I had mentioned that I want to read data from a table. Actually, it's a query that I want to read from. How will this change the line
strSQL = "SELECT * From [TableName]WHERE [RefField]=" & Me.RefControl
I tried just putting the query name in the TableName but I get an error saying, "Too few parameters. Exptected 1
This is the code I have:
Private Sub SendDeliveryClick_Click()
Dim strtext As String
Dim xxRec As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & Forms!Orders.OrderID
Set xxRec = CurrentDb.OpenRecordset(st
xxRec.MoveLast
xxRec.MoveFirst
If xxRec.RecordCount = 0 Then
MsgBox "No items found in selection"
Else
strtext = "ORDER DETAILS" & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "CustomerID: " & CustomerID & vbCrLf
strtext = strtext & "Customer Name: " & CustomerName & vbCrLf
strtext = strtext & "Address: " & Address & vbCrLf
strtext = strtext & "Order Date: " & OrderDate & vbCrLf
strtext = strtext & "Order Time: " & OrderTime & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "OrderID:" & OrderID & vbCrLf
strtext = strtext & vbCrLf
strtext = strtext & "Item Code & Chr(9) Item Name & Chr(9) Qty & Chr(9) Price & Chr(9) Total" & vbCrLf
strtext = strtext & vbCrLf
For i = 0 To xxRec.RecordCount - 1
With xxRec
strtext = strtext & MenuItemID & Chr(9) & ItemName & Chr(9) & Quantity & Chr(9) & UnitPrice & Chr(9) & Total
' get next record
.MoveNext
End With
Next
xxRec.Close
MsgBox strtext
DoCmd.SendObject acSendReport, "Delivery Details", acFormatRTF, ServiceByEmail, , , "Delivery Order #" & OrderID & "", strtext, 0
'last parameter sends email right away. Use -1 to edit email before sending
End If
How can I resolve this? Thanks
Indy,
The function will work with either a table, the name of a query or the query string directly, - you may want to have a look at MSDN on the subject to learn more about using recordsets:
http://msdn.microsoft.com/library/en-us/bapp2000/html/acbachap06.asp?frame=true
When the error occurs, try selecting the 'debug'-button, - this should take you to the code. Please let me know where it stopped.
When you've done that, stop the execution, and while in the VBasic editor select Debug > compile from the menu to check that you don't miss a module or something.
it shouldn't be required, but you can try ending it with ';' (and make sure spaces are in place)
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & Forms!Orders.OrderID & ";"
Is OrderID text or string ? If string you must quote it lik this:
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & chr(34) & Forms!Orders.OrderID & chr(34) & ";"
or this (single quotes)
:
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]='" & Forms!Orders.OrderID & "';"
Regards,
Sven
The function will work with either a table, the name of a query or the query string directly, - you may want to have a look at MSDN on the subject to learn more about using recordsets:
http://msdn.microsoft.com/library/en-us/bapp2000/html/acbachap06.asp?frame=true
When the error occurs, try selecting the 'debug'-button, - this should take you to the code. Please let me know where it stopped.
When you've done that, stop the execution, and while in the VBasic editor select Debug > compile from the menu to check that you don't miss a module or something.
it shouldn't be required, but you can try ending it with ';' (and make sure spaces are in place)
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & Forms!Orders.OrderID & ";"
Is OrderID text or string ? If string you must quote it lik this:
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & chr(34) & Forms!Orders.OrderID & chr(34) & ";"
or this (single quotes)
:
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]='" & Forms!Orders.OrderID & "';"
Regards,
Sven
ASKER
Hi Sven,
The error occurs at line:
Set xxRec = CurrentDb.OpenRecordset(st rSQL)
I will try what you recommended and get back to you.Thanks for all the help.
The error occurs at line:
Set xxRec = CurrentDb.OpenRecordset(st
I will try what you recommended and get back to you.Thanks for all the help.
Hi Indy,
Just checked the code: it is possible that you do not have the DAO module enabled.
In the VBasic editor, from menu: Tools > References
Check if this module is checked (it will be at the top if it is):
Microsoft DAO 3.5 Object library
If not, look down the list to find it and enable it.
Sven
Just checked the code: it is possible that you do not have the DAO module enabled.
In the VBasic editor, from menu: Tools > References
Check if this module is checked (it will be at the top if it is):
Microsoft DAO 3.5 Object library
If not, look down the list to find it and enable it.
Sven
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sven,
I do have the DAO enabled. I tried both things you recommended and it doesn't work still.
I do have the DAO enabled. I tried both things you recommended and it doesn't work still.
ASKER
I am using Access 97
ASKER
I finally got it working. Thanks!!!
If your textbox is called txtSendThisOrderID then do this...
dim rs as new adodb.recordset
dim sql as string
dim sText as string
sql = "Select * from tblOrders WHERE OrderID=" & me.txtSendThisOrderID.valu
rs.open sql, currentProject.connection,
do while not rs.eof
sText = sText & "New Order..."
sText = sText & "OrderID: " & rs!OrderID
sText = sText & "CustomerID: " & rs!CustomerID
... etc.
sText = sText & "End of Current Order" & vbcrlf & vbcrlf
rs.movenext
loop
I'm not sure what you mean about the spaces - it looks to me like your already have the appropriate spaces in there?