Statement to get data from txtSearchID and ...

[Margaret]

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:

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:

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

Regards,

Margaret

[BillGeek]

Try replacing it with:

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

[Margaret]

Try replacing it with:

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:

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

What could be the problem?

[ganeshmoorthy]

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

[ganeshmoorthy]

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

[Margaret]

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

Hi,

Could you plz help me out.

Margaret

[ganeshmoorthy]

here is the 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

[ganeshmoorthy]

try this with

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

see what does it display in the msgbox

[Margaret]

here is the 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

[ganeshmoorthy]

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

[Margaret]

try this with

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

[ganeshmoorthy]

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

[Margaret]

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

Ok.

Private Sub Form_Load()
    Call SetGrid
End Sub

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

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

[ganeshmoorthy]

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

[Margaret]

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

[Ember]

To me it look like you are missing

Set rs = New ADODB.Recordset

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

[Margaret]

To me it look like you are missing

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

[Ember]

This works:

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

[Ember]

Here you call Call SetGrid twice:

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

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

[Margaret]

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.

[Ember]

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

[Ember]

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):

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?