Connecting 2 Flex Grids in One form?

Question

Ayesha.Shahnawaz 0 Newbie Poster

11 Years Ago

I've got 2 flex grids in one VB form and I wanna connect both of these Flex Grids to 2 different tables in MS Access.
This is how my form looks like:
http://i213.photobucket.com/albums/cc228/rose_408/form_zps100c290c.png

Here's the code for the service flex grid (FlexService):

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
Set rec1 = New ADODB.Recordset
Set rec2 = New ADODB.Recordset
Set rec3 = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
                        "\ParlourDesign.mdb;Persist Security Info=False"
conn.Open
sql = "select * from PartPayment"
sql2 = "select * from Receipt"
sql3 = "select * from ReceiptService"
sql4 = "select * from Service"
rec.Open (sql), conn, adOpenDynamic, adLockOptimistic
rec1.Open (sql2), conn, adOpenDynamic, adLockOptimistic
rec2.Open (sql3), conn, adOpenDynamic, adLockOptimistic
rec3.Open (sql4), conn, adOpenDynamic, adLockOptimistic
rec.MoveLast
rec.MoveFirst


Dim RR As Integer

FlexService.Clear
FlexService.Rows = rec.RecordCount + 1
FlexService.Cols = rec.Fields.count
FlexService.FixedCols = 0
FlexService.TextMatrix(0, 0) = "Service"
FlexService.TextMatrix(0, 1) = "Quantity"
FlexService.TextMatrix(0, 2) = "Price"
FlexService.TextMatrix(0, 3) = "Total"
rec.MoveFirst
RR = 1

Do While Not rec.EOF()
FlexService.TextMatrix(RR, 0) = rec.Fields("Service")
FlexService.TextMatrix(RR, 1) = rec.Fields("Quantity")
FlexService.TextMatrix(RR, 2) = rec.Fields("Price")
FlexService.TextMatrix(RR, 3) = rec.Fields("Total")

RR = RR + 1
rec.MoveNext

Loop


End Sub

And here's the code for PartPayment (FlexPP):

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
Set rec1 = New ADODB.Recordset
Set rec2 = New ADODB.Recordset
Set rec3 = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
                        "\ParlourDesign.mdb;Persist Security Info=False"
conn.Open
sql = "select * from PartPayment"
sql2 = "select * from Receipt"
sql3 = "select * from ReceiptService"
sql4 = "select * from Service"
rec.Open (sql), conn, adOpenDynamic, adLockOptimistic
rec1.Open (sql2), conn, adOpenDynamic, adLockOptimistic
rec2.Open (sql3), conn, adOpenDynamic, adLockOptimistic
rec3.Open (sql4), conn, adOpenDynamic, adLockOptimistic
rec.MoveLast
rec.MoveFirst


Dim RR As Integer

FlexPP.Clear
FlexPP.Rows = rec.RecordCount + 1
FlexPP.Cols = rec.Fields.count
FlexPP.FixedCols = 0
FlexPP.TextMatrix(0, 0) = "PPID"
FlexPP.TextMatrix(0, 1) = "ReceiptNo"
FlexPP.TextMatrix(0, 2) = "PPDate"
FlexPP.TextMatrix(0, 3) = "AmountPaid"
rec.MoveFirst
RR = 1

Do While Not rec.EOF()
FlexPP.TextMatrix(RR, 0) = rec.Fields("PPID")
FlexPP.TextMatrix(RR, 1) = rec.Fields("ReceiptNo")
FlexPP.TextMatrix(RR, 2) = rec.Fields("PPDate")
FlexPP.TextMatrix(RR, 3) = rec.Fields("AmountPaid")

RR = RR + 1
rec.MoveNext

Loop


End Sub

Entering both of these "Form Load" codes in one form will be clashing. So what should I do?
Please help! :\

visual-basic

Edited 11 Years Ago by Ayesha.Shahnawaz because: had to make changes to it

2 Contributors
10 Replies
273 Views
2 Days Discussion Span
Latest Post 11 Years Ago Latest Post by AndreRet

AndreRet 526 Senior Poster

11 Years Ago

You can still use this in one form load event, not recomended though - res usage overload...

''For FlexService...
Set connS = New ADODB.Connection
Set recS = New ADODB.Recordset
Set recS1 = New ADODB.Recordset
Set recS2 = New ADODB.Recordset
Set recS3 = New ADODB.Recordset

''Open recset from same tables here ...

recS.Close
recS1.CLose ''etc...

''For FlexPP...
Set connPP = New ADODB.Connection
Set recPP = New ADODB.Recordset
Set recPP1 = New ADODB.Recordset
Set recPP2 = New ADODB.Recordset
Set recPP3 = New ADODB.Recordset

''Open new recset from here using same table...

''Close all recsets and connection...

Edited 11 Years Ago by AndreRet

AndreRet 526 Senior Poster

11 Years Ago

No. I would recommend having an icon on your main form to let the user choose what they want to view - Service or the PartPayment part ON A SEPERATE form for each. :)

Imagine the user has like 10 000 entries on both tables, it will freeze your app right there and then if you want to populate 2 grids with data AT THE same time. ;)

Also, you said it is from 2 different tables but your table names in your sql is EXACTLY the same?? -

sql = "select * from PartPayment"
sql2 = "select * from Receipt"
sql3 = "select * from ReceiptService"
sql4 = "select * from Service"

Not sure what you want to do here. :)

AndreRet 526 Senior Poster

11 Years Ago

Nope, It will crash.... Do the following.

Dim connPP As ADODB.Connection
Dim connS As ADODB.Connection
Dim recPP As ADODB.Recordset
Dim recPP2 As ADODB.Recordset
Dim recPP3 As ADODB.Recordset
Dim recPP4 As ADODB.Recordset
Dim recS As ADODB.Recordset
Dim recS2 As ADODB.Recordset
Dim recs3 As ADODB.Recordset
Dim rec4 As ADODB.Recordset
Dim recReceipt As ADODB.Recordset
Dim sqlPP As String
Dim sqlPP2 As String
Dim sqlPP3 As String
Dim sqlPP4 As String
Dim sqlS As String
Dim sqlS2 As String
Dim sqlS3 As String
Dim sqlS4 As String
Dim searchvar As String

Private Sub Form_Load()
Set connS = New ADODB.Connection
Set recS = New ADODB.Recordset
Set recS1 = New ADODB.Recordset
Set recS2 = New ADODB.Recordset
Set recs3 = New ADODB.Recordset

''Call the first connection. You can not leave it on conn, need to use connS...

connS.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
                        "\ParlourDesign.mdb;Persist Security Info=False"
conn.Open
sqlS = "select * from PartPayment"
sqlS2 = "select * from Receipt"
sqlS3 = "select * from ReceiptService"
sqlS4 = "select * from Service"
recS.Open (sqlS), connS, adOpenDynamic, adLockOptimistic ''Need to call the connection you declared and set...
recS2.Open (sqlS2), connS adOpenDynamic, adLockOptimistic
recs3.Open (sqlS3), connS, adOpenDynamic, adLockOptimistic
recS4.Open (sqlS4), connS, adOpenDynamic, adLockOptimistic
''rec.MoveLast ''Not sure why you are doing this. Move it to the first record, point...
rec.MoveFirst

''Can not close the recset here, what will be loaded to listview if the records are closed???
Dim RR As Integer
FlexService.Clear
FlexService.Rows = rec.RecordCount + 1
FlexService.Cols = rec.Fields.count
FlexService.FixedCols = 0
FlexService.TextMatrix(0, 0) = "Service"
FlexService.TextMatrix(0, 1) = "Quantity"
FlexService.TextMatrix(0, 2) = "Price"
FlexService.TextMatrix(0, 3) = "Total"

RR = 1
Do While Not recS.EOF() ''rec will not work, needs the recordset you declared - recS etc...
FlexService.TextMatrix(RR, 0) = rec.Fields("Service") ''change rec here as well to whichever recset you need...
FlexService.TextMatrix(RR, 1) = rec.Fields("Quantity")
FlexService.TextMatrix(RR, 2) = rec.Fields("Price")
FlexService.TextMatrix(RR, 3) = rec.Fields("Total")
RR = RR + 1
rec.MoveNext ''and here...
Loop

''NOW close all recsets....
recS.Close ''Don't need this...(sqlS), connS, adOpenDynamic, adLockOptimistic
recS2.Close ''(sqlS2), connS, adOpenDynamic, adLockOptimistic
recs3.Close ''(sqlS3), connS, adOpenDynamic, adLockOptimistic
recS4.Close ''(sqlS4), connS, adOpenDynamic, adLockOptimistic

''Do the same here as per above...

    Set connPP = New ADODB.Connection
    Set recPP = New ADODB.Recordset
    Set recPP2 = New ADODB.Recordset
    Set recPP3 = New ADODB.Recordset
    Set recPP4 = New ADODB.Recordset
    conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
    "\ParlourDesign.mdb;Persist Security Info=False"
    conn.Open
    sqlPP = "select * from PartPayment"
    sqlPP2 = "select * from Receipt"
    sqlPP3 = "select * from ReceiptService"
    sqlPP4 = "select * from Service"
    recPP.Open (sqlPP), conn, adOpenDynamic, adLockOptimistic
    recPP2.Open (sqlPP2), conn, adOpenDynamic, adLockOptimistic
    recPP3.Open (sqlPP3), conn, adOpenDynamic, adLockOptimistic
    recPP4.Open (sqlPP4), conn, adOpenDynamic, adLockOptimistic
    rec.MoveLast
    rec.MoveFirst
    Dim RR As Integer
    FlexPP.Clear
    FlexPP.Rows = rec.RecordCount + 1
    FlexPP.Cols = rec.Fields.count
    FlexPP.FixedCols = 0
    FlexPP.TextMatrix(0, 0) = "PPID"
    FlexPP.TextMatrix(0, 1) = "ReceiptNo"
    FlexPP.TextMatrix(0, 2) = "PPDate"
    FlexPP.TextMatrix(0, 3) = "AmountPaid"
    rec.MoveFirst
    RR = 1
    Do While Not rec.EOF()
    FlexPP.TextMatrix(RR, 0) = rec.Fields("PPID")
    FlexPP.TextMatrix(RR, 1) = rec.Fields("ReceiptNo")
    FlexPP.TextMatrix(RR, 2) = rec.Fields("PPDate")
    FlexPP.TextMatrix(RR, 3) = rec.Fields("AmountPaid")
    RR = RR + 1
    rec.MoveNext
    Loop
    End Sub

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Ayesha.Shahnawaz 0 Newbie Poster · Answer 1 · 2013-02-17T16:06:27+00:00

Why is it not recommended? So you think It would be better if I have a seperate form for the second flex grid?

Ayesha.Shahnawaz 0 Newbie Poster · Answer 2 · 2013-02-18T13:07:19+00:00

Oh, ok. Makes sense! :)
Nah, they're different. I still need to sort them out.
Thanks a bunch! C:
-Hits the solved button-

Ayesha.Shahnawaz 0 Newbie Poster · Answer 3 · 2013-02-18T13:43:53+00:00

Ok, hold on.. Have got something more to ask.
Yes, I'm an idiot and i probably dont even get the basics. So will you please check this out and let me know if this is ok or what changes I need to make to it? :|

Dim connPP As ADODB.Connection
Dim connS As ADODB.Connection
Dim recPP As ADODB.Recordset
Dim recPP2 As ADODB.Recordset
Dim recPP3 As ADODB.Recordset
Dim recPP4 As ADODB.Recordset
Dim recS As ADODB.Recordset
Dim recS2 As ADODB.Recordset
Dim recs3 As ADODB.Recordset
Dim rec4 As ADODB.Recordset
Dim recReceipt As ADODB.Recordset
Dim sqlPP As String
Dim sqlPP2 As String
Dim sqlPP3 As String
Dim sqlPP4 As String
Dim sqlS As String
Dim sqlS2 As String
Dim sqlS3 As String
Dim sqlS4 As String
Dim searchvar As String

Private Sub Form_Load()
Set connS = New ADODB.Connection
Set recS = New ADODB.Recordset
Set recS1 = New ADODB.Recordset
Set recS2 = New ADODB.Recordset
Set recs3 = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
                        "\ParlourDesign.mdb;Persist Security Info=False"
conn.Open
sqlS = "select * from PartPayment"
sqlS2 = "select * from Receipt"
sqlS3 = "select * from ReceiptService"
sqlS4 = "select * from Service"
recS.Open (sqlS), conn, adOpenDynamic, adLockOptimistic
recS2.Open (sqlS2), conn, adOpenDynamic, adLockOptimistic
recs3.Open (sqlS3), conn, adOpenDynamic, adLockOptimistic
recS4.Open (sqlS4), conn, adOpenDynamic, adLockOptimistic
rec.MoveLast
rec.MoveFirst

recS.Close (sqlS), conn, adOpenDynamic, adLockOptimistic
recS2.Close (sqlS2), conn, adOpenDynamic, adLockOptimistic
recs3.Close (sqlS3), conn, adOpenDynamic, adLockOptimistic
recS4.Close (sqlS4), conn, adOpenDynamic, adLockOptimistic

Dim RR As Integer

FlexService.Clear
FlexService.Rows = rec.RecordCount + 1
FlexService.Cols = rec.Fields.count
FlexService.FixedCols = 0
FlexService.TextMatrix(0, 0) = "Service"
FlexService.TextMatrix(0, 1) = "Quantity"
FlexService.TextMatrix(0, 2) = "Price"
FlexService.TextMatrix(0, 3) = "Total"
rec.MoveFirst
RR = 1

Do While Not rec.EOF()
FlexService.TextMatrix(RR, 0) = rec.Fields("Service")
FlexService.TextMatrix(RR, 1) = rec.Fields("Quantity")
FlexService.TextMatrix(RR, 2) = rec.Fields("Price")
FlexService.TextMatrix(RR, 3) = rec.Fields("Total")

RR = RR + 1
rec.MoveNext

Loop



    Set connPP = New ADODB.Connection
    Set recPP = New ADODB.Recordset
    Set recPP2 = New ADODB.Recordset
    Set recPP3 = New ADODB.Recordset
    Set recPP4 = New ADODB.Recordset
    conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" & App.Path & _
    "\ParlourDesign.mdb;Persist Security Info=False"
    conn.Open
    sqlPP = "select * from PartPayment"
    sqlPP2 = "select * from Receipt"
    sqlPP3 = "select * from ReceiptService"
    sqlPP4 = "select * from Service"
    recPP.Open (sqlPP), conn, adOpenDynamic, adLockOptimistic
    recPP2.Open (sqlPP2), conn, adOpenDynamic, adLockOptimistic
    recPP3.Open (sqlPP3), conn, adOpenDynamic, adLockOptimistic
    recPP4.Open (sqlPP4), conn, adOpenDynamic, adLockOptimistic
    rec.MoveLast

    rec.MoveFirst

    Dim RR As Integer
    FlexPP.Clear
    FlexPP.Rows = rec.RecordCount + 1
    FlexPP.Cols = rec.Fields.count
    FlexPP.FixedCols = 0
    FlexPP.TextMatrix(0, 0) = "PPID"
    FlexPP.TextMatrix(0, 1) = "ReceiptNo"
    FlexPP.TextMatrix(0, 2) = "PPDate"
    FlexPP.TextMatrix(0, 3) = "AmountPaid"
    rec.MoveFirst
    RR = 1

    Do While Not rec.EOF()
    FlexPP.TextMatrix(RR, 0) = rec.Fields("PPID")
    FlexPP.TextMatrix(RR, 1) = rec.Fields("ReceiptNo")
    FlexPP.TextMatrix(RR, 2) = rec.Fields("PPDate")
    FlexPP.TextMatrix(RR, 3) = rec.Fields("AmountPaid")
    RR = RR + 1
    rec.MoveNext
    Loop
    End Sub

Ayesha.Shahnawaz 0 Newbie Poster · Answer 4 · 2013-02-18T15:46:28+00:00

    recS4.Open (sqlS4), connS, adOpenDynamic, adLockOptimistic
        ''rec.MoveLast ''Not sure why you are doing this. Move it to the first record, point...
        rec.MoveFirst <---------It shows an error here.

It says "Operation is not allowed when the object is closed" :|

AndreRet 526 Senior Poster · Answer 5 · 2013-02-18T16:03:50+00:00

That is because the recordset was closed . After the close you have asked it to move to the first record, which it can not do because it is closed...

Open a recordset, get the data and show whatever you like... ONLY then do you close it.

Ayesha.Shahnawaz 0 Newbie Poster · Answer 6 · 2013-02-19T12:37:38+00:00

Ooh, I think I get it now. Ok, so I can take it from here. Thanks so much for ur help!! :)

AndreRet 526 Senior Poster · Answer 7 · 2013-02-19T14:49:16+00:00

AndreRet 526 Senior Poster

11 Years Ago

Only a pleasure. Happy coding :)