?
Solved

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?

Posted on 2003-11-14
11
Medium Priority
?
372 Views
Last Modified: 2012-05-04
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
Comment
Question by:indyng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 2

Expert Comment

by:glennkerr
ID: 9752240
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9752260
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
 
LVL 2

Expert Comment

by:glennkerr
ID: 9752467
Are you using Access97, 2000 or 2002?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:indyng
ID: 9754079
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9754577
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
 
LVL 1

Author Comment

by:indyng
ID: 9755894
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9756731
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
 
LVL 9

Accepted Solution

by:
svenkarlsen earned 2000 total points
ID: 9756750
--- 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
 
LVL 1

Author Comment

by:indyng
ID: 9763241
Hi Sven,

I do have the DAO enabled. I tried both things you recommended and it doesn't work still.
0
 
LVL 1

Author Comment

by:indyng
ID: 9763303
I am using Access 97
0
 
LVL 1

Author Comment

by:indyng
ID: 9763408
I finally got it working. Thanks!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question