[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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
0
indyng
Asked:
indyng
  • 5
  • 4
  • 2
1 Solution
 
glennkerrCommented:
Set up a recordset and loop through the results.

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.value
rs.open sql, currentProject.connection, 3, 3

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?
0
 
svenkarlsenCommented:
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
0
 
glennkerrCommented:
Are you using Access97, 2000 or 2002?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
indyngAuthor Commented:
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(strSQL)
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
0
 
svenkarlsenCommented:
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
0
 
indyngAuthor Commented:
Hi Sven,

The error occurs at line:

Set xxRec = CurrentDb.OpenRecordset(strSQL)

I will try what you recommended and get back to you.Thanks for all the help.
0
 
svenkarlsenCommented:
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
0
 
svenkarlsenCommented:
--- Indy,

1. if that doesn't work, try the code below. It's ADO (more modern ;-), - no use in spending to much time on running my old DAO templates ;-)

2. This line you've inserted won't work:
        strtext = strtext & "Item Code & Chr(9) Item Name & Chr(9) Qty & Chr(9) Price & Chr(9) Total" & vbCrLf
   The chr(9) only works if it's 'visible', so you have to do like this:
        strtext = strtext & "Item Code" & Chr(9) & "Item Name" & Chr(9) & "Qty" &  Chr(9) & "Price" & Chr(9) & "Total" & vbCrLf


3. Code found below:

Private Sub SendDeliveryClick_Click()

Dim skRst As New ADODB.Recordset
Dim strText As String, strSQL As String

' Use next line if OrderID = a number
strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]=" & Forms!Orders.OrderID

' Use next line if OrderID = a text string
' strSQL = "SELECT * From [Order Details Qry] WHERE [OrderID]='" & Forms!Orders.OrderID & "'"

rs.Open strSQL, CurrentProject.Connection, , adOpenKeyset, adLockOptimistic

If skRst.RecordCount = 0 Then
    MsgBox "No items found in selection"
Else
    While Not skRst.EOF
        strText = "ORDER DETAILS" & vbCrLf
        strText = strText & vbCrLf
        strText = strText & "CustomerID: " & skRst!CustomerID & vbCrLf
        strText = strText & "Customer Name: " & skRst!CustomerName & vbCrLf
        strText = strText & "Address: " & skRst!Address & vbCrLf
        strText = strText & "Order Date: " & skRst!OrderDate & vbCrLf
        strText = strText & "Order Time: " & skRst!OrderTime & vbCrLf
        strText = strText & vbCrLf
        strText = strText & "OrderID:" & skRst!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
       
        strText = strText & skRst!MenuItemID & Chr(9) & skRst!ItemName & Chr(9) & skRst!Quantity & Chr(9) & skRst!UnitPrice & Chr(9) & skRst!Total
    Wend
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

skRst = Nothing

end sub

'Regards Sven
0
 
indyngAuthor Commented:
Hi Sven,

I do have the DAO enabled. I tried both things you recommended and it doesn't work still.
0
 
indyngAuthor Commented:
I am using Access 97
0
 
indyngAuthor Commented:
I finally got it working. Thanks!!!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now