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
348 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
  • 5
  • 4
  • 2
11 Comments
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
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
Comment Utility
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
Comment Utility
Are you using Access97, 2000 or 2002?
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:indyng
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
--- 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
Comment Utility
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
Comment Utility
I am using Access 97
0
 
LVL 1

Author Comment

by:indyng
Comment Utility
I finally got it working. Thanks!!!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now