Statement to get data from txtSearchID and ...

Hi,

I have one MSHFlexGrid that should show data from MS Access database. I have one textbox "txtSearchID". When I click on cmdDetails, it should get the number from txtSearchID and show the related records in MSHFlexGrid.

The following code works fine:

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied ", db
Set MSHFlexGrid1.DataSource = rs

But the following code to get data from txtSearchID and show records is not working...simply it shows nothing.

The following is the code (Which is not showing any record except the column headers:

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='txtSearchID'", db
Set MSHFlexGrid1.DataSource = rs

Regards,

Margaret

Try replacing it with:

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='" & txtSearchID.Text & "'", db

Quote:

---

Originally Posted by BillGeek

Try replacing it with:

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='" & txtSearchID.Text & "'", db

---

Getting the following error:

Operation is not allowed when the object is open.

But the following code doesn't give any error:

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied", db

What could be the problem?

When you pass values thru variables always concatenate the values using &

Seems the rs is opened and not closed. Close the rs if it is opened before you execute this query

Quote:

---

Originally Posted by ganeshmoorthy

When you pass values thru variables always concatenate the values using &

---

Hi,

Could you plz help me out.

Margaret

here is the code...

VB Code:

If Rs.State = adStateOpen Then
    Rs.Close
End If
Rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='" & txtSearchID.Text & "'", db, adOpenDynamic, adLockOptimistic

try this with

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied", db, adOpenDynamic, adLockOptimistic
MsgBox Rs.RecordCount

see what does it display in the msgbox

Quote:

---

Originally Posted by ganeshmoorthy

here is the code...

VB Code:

If Rs.State = adStateOpen Then
    Rs.Close
End If
Rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='" & txtSearchID.Text & "'", db, adOpenDynamic, adLockOptimistic

---

The same error comes again.

Margaret

is it displaying...Operation is not allowed when the object is open...can you post your full code of the sub...

Quote:

---

Originally Posted by ganeshmoorthy

try this with

VB Code:

rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied", db, adOpenDynamic, adLockOptimistic
MsgBox Rs.RecordCount

see what does it display in the msgbox

---

Msgbox shows "3" (and I have 3 records in table Supplied).

Margaret

Margaret: First try to solve one by one. what is the actual problem now...

Quote:

---

Originally Posted by ganeshmoorthy

Margaret: First try to solve one by one. what is the actual problem now...

---

Ok.

VB Code:

Private Sub Form_Load()
    Call SetGrid
End Sub

VB Code:

Public Sub SetGrid()
 
    If rs.State = adStateOpen Then
        rs.Close
    End If
    
Rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied Where Sid='" & txtSearchID.Text & "'", db, adOpenDynamic, adLockOptimistic
'MsgBox rs.RecordCount
 
Set MSHFlexGrid1.DataSource = rs
Call MSHFlexDatagrid_Columns
 
MSHFlexGrid1.SelectionMode = flexSelectionByRow
MSHFlexGrid1.FocusRect = flexFocusNone
 
End Sub

VB Code:

Private Sub MSHFlexDatagrid_Columns()
 
    MSHFlexGrid1.ColWidth(0) = 700
    MSHFlexGrid1.ColWidth(1) = 5500
    MSHFlexGrid1.ColWidth(2) = 1300
    MSHFlexGrid1.ColWidth(3) = 1300
    MSHFlexGrid1.ColWidth(4) = 1100
        
    MSHFlexGrid1.TextMatrix(0, 0) = " ID"
    MSHFlexGrid1.TextMatrix(0, 1) = "Customer Name"
    MSHFlexGrid1.TextMatrix(0, 2) = "Date"
    MSHFlexGrid1.TextMatrix(0, 3) = "Item No."
    MSHFlexGrid1.TextMatrix(0, 4) = "S. Qty."
    
    MSHFlexGrid1.ColAlignment(0) = 4
    MSHFlexGrid1.ColAlignment(1) = 2
    MSHFlexGrid1.ColAlignment(2) = 4
    MSHFlexGrid1.ColAlignment(3) = 4
    MSHFlexGrid1.ColAlignment(4) = 6
    
    MSHFlexGrid1.ColAlignmentFixed(0) = 4
    MSHFlexGrid1.ColAlignmentFixed(1) = 2
    MSHFlexGrid1.ColAlignmentFixed(2) = 4
    MSHFlexGrid1.ColAlignmentFixed(3) = 4
    MSHFlexGrid1.ColAlignmentFixed(4) = 6
 
Dim X As Long
    With MSHFlexGrid1
    
    For X = .FixedRows To .Rows - 1
        .TextMatrix(X, 4) = Format(.TextMatrix(X, 4), "#,0")
        Next X
        End With
 
End Sub

Getting error: 3705
Operation is not allowed when the object is open.

This is the error what I am getting right now.

Margaret

which line is getting highlighted, when this error occurs...

Quote:

---

Originally Posted by ganeshmoorthy

which line is getting highlighted, when this error occurs...

---

Hi,

I am attaching my project here. Please have a look at it and help me out. I need to get the total supplied quantity in txtSqty.

Regards,

Margaret

To me it look like you are missing

VB Code:

Set rs = New ADODB.Recordset

above:
Rs.Open "Select Sid, Scustname, Sdate, Sitemno ....................

Quote:

---

Originally Posted by Ember

To me it look like you are missing

VB Code:

Set rs = New (ADODB.)Recordset

above:
Rs.Open "Select Sid, Scustname, Sdate, Sitemno ....................

---

Still the same error occurs. You can have a look at my project which was attahced with name "Purchase Orders.zip". In this project frmSDetails giving the error.

Margaret

This works:

VB Code:

Public Sub SetGrid()
 
'    If rs.State = adStateOpen Then
'        rs.Close
'    End If
Set rs = New ADODB.Recordset
rs.Open "Select Sid, Scustname, Sdate, Sitemno, Sqty from Supplied", db, adOpenDynamic, adLockOptimistic
 
Set MSHFlexGrid1.DataSource = rs
Call MSHFlexDatagrid_Columns
 
MSHFlexGrid1.SelectionMode = flexSelectionByRow
MSHFlexGrid1.FocusRect = flexFocusNone
 
End Sub

Morten

Here you call Call SetGrid twice:

VB Code:

frmSDetails.SetGrid
frmSDetails.Show
frmSDetails.Top = 4360
frmSDetails.Left = 660
frmSDetails.txtSearchID = txtID

First by frmSDetails.SetGrid
Then by frmSDetails.Show ( in Form_Load: Call SetGrid)

And

VB Code:

frmSDetails.Left = 660
frmSDetails.Top = 4360
'frmSDetails.txtSearchID = frmLPO.txtID ' If txtId is Public or TextBox.Name in frmLPO

is best inside Form_LOad of frmSDetails

Nice program!
Morten

Quote:

---

Originally Posted by Ember

Here you call Call SetGrid twice: Name in frmLPO.
Morten

---

Hi, thanks for your reply. How to modify it now. Please explain a little bit with an example.

Margaret.

I'm at work now, but will look through the project later. At the first glance I find it hard to follow what actually the recordset 'rs' is filled up with or contains after been through different Sub routines. Maybe it's an idea to have one recordset to refill and empty, and one to keep original records. Or just have one to search and move through?
I'll come back later! Morten

Puh! Best Margaret, you have made a nice looking application, but with the timer in the UserControl to refresh the commandButtons, it is hard to debug. Managed to edit and update a new record nevertheless. Errors here (changes made bold):

VB Code:

BS = BS + txtSqty - [B]Val([/B]txtOqty[B])[/B]
[B]If Len(txtPodate) Then [/B] .Fields("Podate").Value = CDate(txtPodate.Text) [B]Else .Fields("Podate").Value = Now()[/B]
.Fields("Qty").Value = [B]Val([/B]txtQty.Text[B])[/B]
.Fields("Price").Value = CCur([B]IIf(txtPrice.Text <> "", [/B] txtPrice.Text[B], 0))[/B]
.Fields("Amount").Value = CCur([B]IIf(txtAmount.Text <> "", [/B] txtAmount.Text[B], 0))[/B]

But with so many different Call subroutine with even new Call subroutines inside them, you end up with a project that looks nice, but hard to make stable. Try with Breakpoints in every routine in the forms and see how often they are hit.
To rebuild the program for an outsider like me, is almost impossible.
Perhaps I take courage and try again?