MSAccess2003: Programming Text in a Control for Migration to Notepad

[dynamictiger]

I am making a hybrid application between SQL, Firefox and Access.

Most of the processing I am doing in Access as it is the simpler choice and easier to deal with.

I have a piece of Code I am using to move through a subform in Datasheet view and grab some data:

~DoWhile Not rstSubForm.EOF

       strItemDescription = rstSubForm.Fields("Item Description")

       curLine = rstSubForm.Fields("Quantity") *rstSubForm.Fields("UnitSell")

       Me.Parent.txtInvoice = Me.Parent.txtInvoice & vbCrLf & _
        rstSubForm.Fields("Quantity") & Space(15 -Len(rstSubForm.Fields("Quantity"))) & strItemDescription & _
        Space(41 - Len(strItemDescription)) & _
        Format(rstSubForm.Fields("UnitSell"), "Currency") &Space(15 - Len(rstSubForm.Fields("UnitSell"))) & _
        Format(curLine, "currency")

         Debug.Print Len(strItemDescription)

        rstSubForm.MoveNext


   Loop

The purpose of this code is to create a nice neat table in the notepad when I get to the output. What it is doing and is giving me the, words not suited to a public space, is Debug.Print Len(strItemDescription) giving me a constant 35 regardless of the length of the actual content.



I don't get it. Why is it constant 35. Doesn't seem to matter what I use as the source it is 35.

Thanks

[June7]

Could you provide sample data? If you want to provide db, follow instructions at bottom of my post.

[dynamictiger]

Difficult to supply the database it is at work on a registered system etc.

The data is a mixture of stock items imported from a FoxPro system and descriptions typed by staff directly into a third party cloud system, and imported via excel.

The data as presented in notepad is:

~Quantity ItemDescription Unit Price Line Total
1 BUFFER 25KG $65.00 $65.00
1 CALCIUM CHLORIDE 25KG $70.00 $70.00
2 LIQUID CHLORINE 15LTR $18.00 $36.00
3 HYDROCHLORIC ACID 20LTR $23.50 $70.50
1 LIQUID CHLORINE 15LTR $18.00 $18.00
2 HYDROCHLORIC ACID 20LTR $23.50 $47.00

[June7]

Advise not use spaces or special characters/punctuation (underscore is exception) in naming convention.

Provide rest of the code for this procedure. What you posted does not show declaring and setting variables.

txtInvoice is textbox on main form for primary key? Shouldn't Invoice also be foreign key in the subform? There is no field for this in the example data. Why are you setting textbox on main form to concatenated data from subform?

I built table and forms and I cannot replicate the issue.

[PC-Gram]

Try: Debug.Print Len(trim(strItemDescription))

[dynamictiger]

June7 - Naming Convention is not entirely practical when I have so many conflicting sources and programs working together. I did start out using Naming Conventions but it ended in conflicts and user issues and confusion
PC Gram - I tried Trim ages back. Added back in in case something had changed. Doesn't make a difference.

PrivateSub dpsItems()DimrstSubForm As DAO.Recordset
DimcurLine As Currency
DimstrItemDescription As String


  Set rstSubForm = Me.Recordset


'Thenloop through it and manipulate.


   Do While Not rstSubForm.EOF

       If Not IsNull(rstSubForm.Fields("Item Description")) Then

       strItemDescription = Trim(rstSubForm.Fields("Item Description"))

       curLine = rstSubForm.Fields("Quantity") *rstSubForm.Fields("UnitSell")

       Me.Parent.txtInvoice = Me.Parent.txtInvoice & vbCrLf & _
        rstSubForm.Fields("Quantity") & Space(15 -Len(rstSubForm.Fields("Quantity"))) & strItemDescription & _
        Space(41 - Len(strItemDescription)) & _
        Format(rstSubForm.Fields("UnitSell"), "Currency") &Space(15 - Len(rstSubForm.Fields("UnitSell"))) & _
        Format(curLine, "currency")

       mcurTotal = mcurTotal + curLine

       End If

        rstSubForm.MoveNext
   Loop

   Set rstSubForm = Nothing

EndSub

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

Still advise no spaces or special characters/punctuation in naming.