Database Import and Manipulation

[ssVBAdev]

I have an .mdb database with the tables as laid out below. The Main Table has a list of Groups where there can be any number ("N") of groups. In the groups are subgroupings (as noted by the number after the dash). Each subgroup has any number of Items in the subgroup (see the "ItemNO" field). In excel (see VBA Code below), I am able to import the database using the DAO object reference and sort the data into a matrix (see below also). In that matrix, I can edit the values in the cells and then, using transaction, update the database with the altered values.

recarray = rs.GetRows(rs.RecordCount)
For i = 0 To UBound(recarray, 2)
   For j = 0 To UBound(recarray, 1)
      DataArray(j) = CStr(recarray(j, i))
   Next j
   'First Determine if it has "Q" Value
   If UCase(Left(DataArray(2), 1)) = "Q" Then
      If Left(DataArray(0), 3) <> .Cells(RowCount, 1) Then
         RowCount = RowCount + 1
         .Cells(RowCount, 1) = Left(DataArray(0), 3)
      End If
      Select Case CStr(Right(RTrim(DataArray(0)), 2))
         Case Is = "10"
         .Cells(RowCount, 4) = Trim(DataArray(2))
         Case Is = "20"
         .Cells(RowCount, 5) = Trim(DataArray(2))
         Case Is = "30"
         .Cells(RowCount, 6) = Trim(DataArray(2))
         Case Is = "40"
         .Cells(RowCount, 7) = Trim(DataArray(2))
         Case Is = "50"
         .Cells(RowCount, 8) = Trim(DataArray(2))
         Case Is = "60"
         .Cells(RowCount, 9) = Trim(DataArray(2))
         Case Else
         .Cells(RowCount, 10) = .Cells(RowCount, 10) & "Size Error " & DataArray(0) & " "
         intErrorCount = intErrorCount + 1
      End Select
   End If
Next i

I'd like to do this using VB6 for a stand-alone version and not have to rely on the excel backbone. I'm pretty good with excel VBA but am a hobbyist when it comes to VB6 with no knowledge of the datagrid nor flexgrid. I am able to use the datasource properties of these controls to add the data to my program but I need the ability to manipulate the data into the matrix to organize the data better much like I do in excel.

What would be a good way to this? Is datagrid or flexgrid the way to go? Is there a way to use the datagrid or flexgrid like excel? I'm not looking for a fully coded solution just some pointers in the right direction (although some code or quasi-code might help ).

Any suggestions or input are greatly appreciated!



MainTable:
GroupID ItemNO Quantity ItemID
Gr1-10 1 5 ABC1
Gr1-10 2 Q+2 XYZ1
Gr1-20 1 2 ABC2
Gr1-20 2 Q-1 XYZ2
Gr1-30 1 3 ABC3
Gr1-30 2 2 XYZ3
Gr2-20 1 1 QWE2
Gr2-20 2 Q+1 RST5
Gr2-20 3 2 LMN1
Gr3-10 1 Q ABC1
.
.
.
GrN-xx x (Q Value) (ID Value)

NameTable:
GroupID Description
Gr1 Group 1
Gr2 Group 2
Gr3 Group 3
.
.
.
GrN Description of GrN


Matrix in Excel:
GroupID Description SubID10 SubID20 SubID30
Gr1 Group 1 Q+2 Q-1
Gr2 Group 2 Q+1
Gr3 Group 3 Q


********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Denster]

Are you just looking for a way to edit the data in the grid, then loop through the rows to save to a database?.

If so the only way I know is to snap a textbox over the cell you wish to edit.

I have a bit of code I could send you if it will help.

 

[ssVBAdev]

That is pretty much correct Denster. I guess I don't follow what you are doing by snapping a textbox over the cell though. I'm interested to know more about it though.

In Excel, I import the data into the matrix - which is much easier to read than trying to go through the database say in MSAccess. The data can then be altered in the cells of the excel spreadsheet as required. Then the data is then sent back to the database using .Edit and .Update and I use CommitTrans to finalize the database.

In excel, it works really slick (if I don't say so myself). I just want to be able to convert it to a standalone application format that does not rely on an installation of Excel. I hoped the Flexgrid or datagrid could, basically, act as an excel spreadsheet.

Like i said, I can get the data to the grid controls using the datasource properties at design time but I'd rather have the data in my matrix format. The matrix format is kind of the whole point. Otherwise, I'm just using a program to open the database as if it's MSaccess.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Denster]

Basicaly you place a textbox on the form and set its proporties to -
(Border = 0, Appearance = Flat, Visible = False)

Then when you click on the text you want to edit in the flexGrid you make the textBox the same size as that particular cell, and move it to the same position. Then set its property to visible = true and make the text the same so it looks like you are editng the flexgrid. Its a bit fiddley but I have used it to good effect.

Like I said the code is there if you want it, in separate modules to make it easy to use.

 

[ssVBAdev]

It's not so much the editing of the cells that I'm having trouble with right now (although, by the sounds of it, I may need your code soon [keep the notification on this post and I may get back to you ]), the issue is how to even get the data into the grid in the format that I want it. Using the datasource properties just shows the data as-is. I want to rearrange the data from it's usual rows/columns in the database into different rows/columns in the grid.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Denster]

Again this is more manual manipulation. You will probably have to loop through the recordset -
(Do while not rs.EOF) and target each cell that you want -
FlexGrid.TextMatrix(RowNum,ColNum) = rs!FieldName

Hope that helps

 

[ssVBAdev]

Ah, that's what I'm looking for. For some reason though, I tried something to that effect and the grid would not retain the data. If you close the database once you are done populating the grid (during runtime), does the data in the grid get cleared out?

I've also just switched from a Win98 machine with VB6 SP5 to a WinXP with VB6 SP6. Maybe it was something to do with the system. I don't know.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[Denster]

You are entering the data manualy so it is not connected to the recordset when it is in the flexGrid. So the data should not be lost if the database is closed.

 

[ssVBAdev]

Thanks for all your help and time, Denster! This program is on another computer so I'll have to give it a go when I get back to that computer. I'll try to post back with how things go.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[johnwm]

There is a FAQ (faq222-3262) about editing data in a flexgrid. It is taken direct from MSDN around 9 years ago and can readily be used for any type of input control (textbox, radio button, dtpicker etc)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[ssVBAdev]

Thanks for that info, John. I looked through some of the MSDN stuff that came with VB6 and could not find that. In fact, I did find one article that had some of that same wording but the examples they gave did not work as the data access logic had changed (or something to that effect anyway).

Anyway, I'll go through this, absorb it, and see what I can cook up.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!