i want to show the variables in a datagrid but it gives me "subscript out of range".

[aileengrace03]

Private Sub Text5_Change()
Dim ccs As Double
Dim bal As Double
Dim loss As Double
Dim pcs As Double
Dim bcs As Double
Dim bgl, aa As Double
Dim total As Double
Dim a As Long

Dim X, y, z As Integer

X = 0
z = 0
a = 0
aa = IIf(Text9.Text = "", 0, Text9.Text)
pcs = IIf(Text4.Text = "", 0, Text4.Text)
ccs = IIf(txtCase.Text = "", 0, txtCase.Text)
ctax = 0
Set Rs = New ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "DSN=BIRREM2"
Rs.Open "SELECT * from tblproducts where pcode='" & Trim(Text5.Text) & "'", conn, adOpenStatic, adLockOptimistic
If Not Rs.EOF Then
Text6.Text = Format(Rs!Cap_Liters, "###,###,###.00")
bal = Format(ccs * Rs!Cap_Liters, "###,###,###.00")
Text7.Text = Format(bal, "###,###,###.00")
Text11.Text = Format(pcs * Rs!Cap_Liters, "###,###,###.00")
'Text15.Text = Format(aa * Rs!Cap_Liters, "###,###,###.00")

'---------
Dim rsb As New ADODB.Recordset
dataconnection
rsb.Open "select prod_gl from tIceDetails where manu_date='" & CDate(txtmanufacture_date.Text) & "'", conn, adOpenStatic, adLockOptimistic
While Not rsb.EOF
a = rsb!prod_gl
rsb.MoveNext
Wend
rsb.Close
a = (Round((a * 1.018), 3)) - a
Text8.Text = a

'---------
Dim rsa As New ADODB.Recordset
dataconnection
rsa.Open "select bal_cs from tIceDetails where manu_date='" & CDate(txtmanufacture_date.Text) - 1 & "'", conn, adOpenStatic, adLockOptimistic
While Not rsa.EOF
X = rsa!bal_cs
rsa.MoveNext
Wend
rsa.Close

'---------
rsa.Open "select prod_cs, rem_cs from tIceDetails where manu_date='" & CDate(txtmanufacture_date.Text) & "'", conn, adOpenStatic, adLockOptimistic
While Not rsa.EOF
pcs = rsa!prod_cs
ccs = rsa!rem_cs
rsa.MoveNext
Wend
rsa.Close
aa = Format(Val(X) + Val(pcs) - Val(ccs), "###,###,###.00")
Text9.Text = Format(aa, "###,###,###.00")

'-----
Dim rsc As New ADODB.Recordset
dataconnection
rsc.Open "select Cap_Liters,TAX from tblproducts where pcode='" & Trim(Text5.Text) & "'", conn, adOpenStatic, adLockOptimistic
While Not rsc.EOF
total = Format(aa * rsc!Cap_Liters, "###,###,###.00")
bgl = Format(total / rsc!tax, "###,###,###.00")
rsc.MoveNext
Wend
rsc.Close
Text15.Text = Format(total, "###,###,###.00")
Text17.Text = bgl

Set conn = Nothing
Set Rs = Nothing
Set rsa = Nothing
Set rsb = Nothing
Set rsc = Nothing

End If
End Sub

----------------
Public Sub load_data()
Set conn = New ADODB.Connection
Set Rs = New ADODB.Recordset

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\otr.mdb"
Rs.Open "SELECT prod_cs,prod_gl,rem_cs,rem_gl,losses,bal_cs,bal_gl,pl FROM tIceDetails where manu_date like '" & CDate(txtmanufacture_date.Text) & "'", conn, adOpenStatic, adLockOptimistic
'Rs.Open "SELECT * FROM tIceDetails where manu_date like '" & CDate(txtmanufacture_date.Text) & "'", conn, adOpenStatic, adLockOptimistic

Set DataGrid1.DataSource = Nothing
Set DataGrid1.DataSource = Rs

With DataGrid1
' .ClearFields
'.ReBind

.Columns(0).Width = 800
.Columns(0).Alignment = 2
.Columns(0).Caption = "Prod CS"

.Columns(1).Width = 1000
.Columns(1).Alignment = 2
.Columns(1).Caption = "Prod GL"
.Columns(1).NumberFormat = "###,###,###.00"

.Columns(2).Width = 800
.Columns(2).Alignment = 0
.Columns(2).Caption = "Cases"

.Columns(3).Width = 1300
.Columns(3).Alignment = 1
.Columns(3).Caption = "Total Liters"
.Columns(3).NumberFormat = "###,###,###.00"

.Columns(4).Width = 800
.Columns(4).Alignment = 2
.Columns(4).Caption = "Losses"
.Columns(4).NumberFormat = "###,###,###.00"

.Columns(5).Width = 800
.Columns(5).Alignment = 0
.Columns(5).Caption = "Balance CS"

.Columns(6).Width = 1300
.Columns(6).Alignment = 1
.Columns(6).Caption = "Balance GL"
.Columns(6).NumberFormat = "###,###,###.00"

.Columns(7).Width = 1300
.Columns(7).Alignment = 2
.Columns(7).Caption = "PL"
.Columns(7).NumberFormat = "###,###,###.00"

End With
Set Rs = Nothing
Set conn = Nothing

End Sub

[hkdani]

Format your code.

You're using adOpenStatic for a SQL statement. I usually use a an adOpenDynamic when working with SQL statements. And adOpenStatic with Table Searches.

But if you're getting that error, you probably should put a stop point after opening up the recordset and then checking in the command window while in debug mode to see if you have any records selected with that query. If your record count shows 0 or null, then you probably have an invalid SQL statement, you just don't have any records that meet that criteria, or you are not using the proper recordset type.

I'm guessing that you should use adOpenDynamic. But it's hard to tell without any data to check.

[BitBlt]

What statement is causing the error? You should be doing some error trapping so that you can identify the specific line. Also, not sure where you call your sub "load_data" from. It isn't in the rest of the code snippet.

Finally, what DBMS does your DSN "BIRREM2" point to? If it is to MSAccess, then when doing date comparisons you can't use a single-quote as delimiter, it has to be a cross-hatch...e.g. ...where myDate = #01/03/2012#. If you are doing a "LIKE" operation, such as in your load_data sub, you can get away with single-quote as your delimiter, but you have to include a wildcard or else you'll get no rows returned. Here's a sample code snippet to illustrate the concepts:

Rs.Open "SELECT MyTable.MyKey, MyTable.MyCharacter, MyTable.MyNumeric, MyTable.MyDate
FROM MyTable
WHERE (((MyTable.MyDate)=#1/1/2011#)) OR (((MyTable.MyDate) Like '*2013'));", conn, adOpenStatic, adLockOptimistic

You will, of course, have to adjust the actual SQL to fit your specific requirement, but I think you can figure that part out.

Hope this helps! Good luck!