reading field in a subform using vba

[Loek]

I am new in access and I am sorry before. My English not good.

I have main form (f_order) with a subform (f_order_detil) and was linked with order_id. I want print line by line like receipt in the mini market or etc. But, my problem is I can't read the data (field) in a subform using VBA. then I was try the code :

Private Sub cmdPrint_Click()
Dim strSQL As String, strLine1 As String, strLine2 As String
Dim db As DAO.Database, rst As DAO.Recordset
Dim lReturn As Long, lpcWritten As Long, sWrittenData As String

set strSQL = "SELECT*FROM q_rcp" 'q_rcp is my query_order
set db = CurrentDb()
set rst = db.OpenRecordset(strSQL)

strLine1 = rst!order_id 'field in main form
strLine2 = rst!product & rst!price 'field in a subform

sWrittenData = strLine1 & vbCrLf & _
strLine2 & vbCrLf
lReturn = WritePrinter(lhPrinter, ByVal sWrittenData, _
Len(sWrittenData), lpcWritten)
lReturn = EndPagePrinter(lhPrinter)
lReturn = EndDocPrinter(lhPrinter)
lReturn = ClosePrinter(lhPrinter)

End Sub

it is working properly. But the fields in my subform just one record (not many record like a form on screen). Then I gave criteria :

strSQL = "SELECT * FROM q_rcp WHERE" _
"order_id=" & Forms![f_order_detil]!order_id

I have error clause

any one help me....?
thanks

 

[Banana]

Your SQL is:

strSQL = "SELECT * FROM q_rcp WHERE" & _
"order_id=" & Forms![f_order_detil]!order_id

If you did this:

Debug.Print strSQL

just after you did the above code, you would see that the result is:

SELECT * FROM q_rcp WHEREorder_id=x

You need a space, so the correct expression would be:

strSQL = "SELECT * FROM q_rcp WHERE " & _
"order_id=" & Forms![f_order_detil]!order_id

Note the addition of a space after the word "WHERE"

EDIT: Also you need a & to concatenate the string, added above. Without it, Debug.Print cannot work because it's not a valid statement.

 

[Loek]

Thank you Banana for your response

I was add space, but the same error. I get Run-time error 3131 "Syntax error in FORM clause"

 

[darkstar257]

I was also wondering the same thing. How would you pull or call out every single line or values in a subform from the mainform because

when he does

[FONT=Courier New]strLine2 = rst!product & rst!price [/FONT]

Won't this just grab the first line of the value Product & Price? Shouldn't there be some way to go through and grab values For Each line of the subform f_order_detil?

 

[Loek]

thank you...

I was fix up my code and working properly.

Private Sub cmdPrint_Click()
Dim strLine1 As String, strLine2 As String
Dim rst As DAO.Recordset
Dim lReturn As Long, lpcWritten As Long, sWrittenData As String

strLine1 = me![order_id]

set rst = Me.f_order_detil.Forms.RecordsetClone
Do While Not rst.EOF
strLine2 = strLine2 & Chr(32) & rst![product] & _
Chr(9) & Format(CCur(rst!price), "Standard") & vbCrLf
rst.MoveNext
Loop

sWrittenData = strLine1 & vbCrLf & _
strLine2 & vbCrLf
lReturn = WritePrinter(lhPrinter, ByVal sWrittenData, _
Len(sWrittenData), lpcWritten)
lReturn = EndPagePrinter(lhPrinter)
lReturn = EndDocPrinter(lhPrinter)
lReturn = ClosePrinter(lhPrinter)

End Sub

 

[gemma-the-husky]

I was also wondering the same thing. How would you pull or call out every single line or values in a subform from the mainform because

when he does

[FONT=Courier New]strLine2 = rst!product & rst!price [/FONT]

Won't this just grab the first line of the value Product & Price? Shouldn't there be some way to go through and grab values For Each line of the subform f_order_detil?

it all depends what you want to do

so - you have a main form - say its a SALES ORDER
and a subform - say ORDER LINES

so if you want to print the order, you need a query that draws together the order and all the items, and base a report on this.

you may need to get some information about the selected line in the subform

it's actually quite rare to need to go through all the items in the subform, in code. Often you can process them all with a single query, which is the preferable - easier, and less prone to programmer error

So the question is - what are you trying to do that you need to iterate all the items?

 

[gemma-the-husky]

I was also wondering the same thing. How would you pull or call out every single line or values in a subform from the mainform because

when he does

[FONT=Courier New]strLine2 = rst!product & rst!price [/FONT]

Won't this just grab the first line of the value Product & Price? Shouldn't there be some way to go through and grab values For Each line of the subform f_order_detil?

it all depends what you want to do

so - you have a main form - say its a SALES ORDER
and a subform - say ORDER LINES

so if you want to print the order, you need a query that draws together the order and all the items, and base a report on this.

you may need to get some information about the selected line in the subform in which case you only ARE interested in one line.

it's actually quite rare to need to go through all the items in the subform, in code. Often you can process them all with a single query, which is the preferable - easier, and less prone to programmer error

So the question is - what are you trying to do that you need to iterate all the items?

 

[Loek]

Hi..everybody...

Private Sub cmdPrint_Click()
Dim strLine1 As String, strLine2 As String
Dim rst As DAO.Recordset
Dim lReturn As Long, lpcWritten As Long, sWrittenData As String

strLine1 = me![order_id]

set rst = Me.f_order_detil.Forms.RecordsetClone
Do While Not rst.EOF
strLine2 = strLine2 & Chr(32) & rst![product] & _
Chr(9) & Format(CCur(rst!price), "Standard") & vbCrLf
rst.MoveNext
Loop

sWrittenData = strLine1 & vbCrLf & _
strLine2 & vbCrLf
lReturn = WritePrinter(lhPrinter, ByVal sWrittenData, _
Len(sWrittenData), lpcWritten)
lReturn = EndPagePrinter(lhPrinter)
lReturn = EndDocPrinter(lhPrinter)
lReturn = ClosePrinter(lhPrinter)

End Sub

with the code above I got print line by line like this (for example) :

order_id : 1
----------------
product|price
A......... 2,000.00
B......... 300.00

I want get :

order_id : 1
----------------
product|price
A......... 2,000
B............ 300

I was set the property control (DecimalPlace =0 and Format Text= Standard) in a subform, but not worked like I want. any code else for my code ?

Thank You very much for your help.

 

[CBrighton]

You are converting it to currency but don't want decimals? In which case why not remove the ccur function, it's converting it to a format which you don't want?

 

[Loek]

Yes, I am.

if I remove the ccur function, I will get like this :

order_id : 1
---------------
product|price
A..........2000
B..........300

if print it by report, nothing problem with converting it to currency (not decimal).
But, I was print it line by line using data from form (main form and subform) on screen. I don't know the code or else for get like I want.

 

[CBrighton]

I'm sorry, I still don't understand exactly what you want that is different from what you posted in your code above.

 

[Loek]

I sorry before. My English not fluently.

Let it converting to currency with decimal place.
And I get like this :

order_id : 1
----------------
product|price
A......... 2,000.00
B......... 300.00

I want get like this :
order_id : 1
----------------
product|price
A......... 2,000.00
B............ 300.00

look the different for price 300.00.

 

[Loek]

I sorry before. My English not fluently.

Let it converting to currency with decimal place.
And I get like this :

order_id : 1
----------------
product|price
A......... 2,000.00
B......... 300.00

I want get like this :
order_id : 1
----------------
product|price
A......... 2,000.00
B............ 300.00

look the different for price 300.00.

 

[CBrighton]

Ah, you want to right align the display of the price field.

If you have a max length of 8 characters (up to $9,999.99) you can do this something like:

Private Sub cmdPrint_Click()
Dim strLine1 As String, strLine2 As String
Dim rst As DAO.Recordset
Dim lReturn As Long, lpcWritten As Long, sWrittenData As String
strLine1 = me![order_id]
set rst = Me.f_order_detil.Forms.RecordsetClone
Do While Not rst.EOF
strLine2 = strLine2 & Chr(32) & rst![product] & _
Chr(9)
If len(Format(CCur(rst!price), "Standard"))=8 then
 strLine2 = strLine2 & Format(CCur(rst!price), "Standard") & vbCrLf
ElseIf len(Format(CCur(rst!price), "Standard"))=7 then
 strLine2 = strLine2 & " " & Format(CCur(rst!price), "Standard") & vbCrLf
ElseIf len(Format(CCur(rst!price), "Standard"))=6 then
 strLine2 = strLine2 & "  " & Format(CCur(rst!price), "Standard") & vbCrLf
ElseIf len(Format(CCur(rst!price), "Standard"))=5 then
 strLine2 = strLine2 & "   " & Format(CCur(rst!price), "Standard") & vbCrLf
ElseIf len(Format(CCur(rst!price), "Standard"))=4 then
 strLine2 = strLine2 & "    " & Format(CCur(rst!price), "Standard") & vbCrLf
End if
rst.MoveNext
Loop
sWrittenData = strLine1 & vbCrLf & _
strLine2 & vbCrLf
lReturn = WritePrinter(lhPrinter, ByVal sWrittenData, _
Len(sWrittenData), lpcWritten)
lReturn = EndPagePrinter(lhPrinter)
lReturn = EndDocPrinter(lhPrinter)
lReturn = ClosePrinter(lhPrinter)
End Sub

It's not the most elegant solution, but it will do the job.

 

[Loek]

I had try your help, but not worked. I still got like this :

2,000.00
300.00

 

[Loek]

I am sorry. I wasn't careful with your help ( space in "").
Thank you very much. it is very helpful