Link to home
Start Free TrialLog in
Avatar of indyng
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
Avatar of glennkerr
glennkerr

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?
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
Are you using Access97, 2000 or 2002?
Avatar of indyng

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(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
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
Avatar of indyng

ASKER

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.
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
ASKER CERTIFIED SOLUTION
Avatar of svenkarlsen
svenkarlsen

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of indyng

ASKER

Hi Sven,

I do have the DAO enabled. I tried both things you recommended and it doesn't work still.
Avatar of indyng

ASKER

I am using Access 97
Avatar of indyng

ASKER

I finally got it working. Thanks!!!