ADO and GetRows confusion and advice 1

[rb74]

I hope you all are having a good weekend.

I have a recordset with x amount of records that will have 30 fields each. I would like to have the records input into a 2D array. I am using the GetRows method, but VB is inputting the records in reverse order so to speak. Instead of having the array populated (i.e. rows, columns) like a normal database record, my array is populated (columns, rows) in reverse, so to speak. How can I get it populated in the right fashion?

I also would like some advice about the method I am using. My app allows the user to search for records in the database by a member last name. The data in the recordset then populates a FlexGrid to allow the user to choose a record to view on the input form. My design calls for the data in the recordset to populate an array then for the app to take the data in the array and populate the objects on the input form. Would it be easier for me to use the recordset to populate the objects on the input form and bypass using the array? If so, I tried to make my recordset public, but the search function would not work (the sub procedure that contains the recordset is public).

Any suggestions?

Thanks,

Rob [Vader2]

 

[zemp]

For the first part I would try and avoid using a two dimentional array. I would use a used defined type(udt) instead. Because you have a predefined number of fields(30) you can you can preset the second dimension of the array. In a udt you can also set different variable types as needed and use decriptive names which will make you code more readable and easier to debug. You create a udt in a module with

Public Type MyUDT
field1 as string
field2 as string
field3 as integer
...
End Type

Then declare it as an array where you need to.

Dim array() as MyUDT

I use SQL statements or stored procedures via ADO to retrieve my data and have not had the reversing problem that you speak of.

On the second part of your question. I always use an array and never bind my grids. That is my preference and I know other have diferent views. My reasoning is that I like to have control over the data. I can format it before it gets displayed. i have the data in an array and can work with it without having to go back to the database. In your case you want to place a selected record into an input form, so I would suggest keeping it in the array and use the row property of the grid to determine which element of the array to display. (msflexgrid.row-1). Make your changes and then update the array, update the database and repopulate the grid with the existing array.

Another consideration might be to drop the input form and add a floating text box to the grid in order to simulate direct editing of the flexgrid by the users. This involes a bit more code but might eliminate flipping back and forth between forms. There have been many threads in this forum about the editing the flexgrid, a search should yield good results and sample code.

Hope this helps, if my response spawns more questions don't hesitate to post them. Thanks and Good Luck!

zemp

 

[CCLINT]

Try using the third parameter in GetRows so the GetRows starts at the first record.

 

[rb74]

Zemp,

Thanks for the input. I guess it would help if I give a bit more background for this app. This app has about 7 forms, but the main form is the Input form. On this form I have an SSTab with three tabs. The first tab is for data entry. The second tab is for a formatted version of the data input for the user to print and hand to a client. The third form is the form with the flexgrid. I want to allow the user to search for previously entered data for a client. My flexgrid is not bound, for I totally agree with you that I want to be able to manipulate my data first. So upon clicking on the search icon in the toolbar, the user is prompted with an input box to enter a user last name. Upon entering the last name, the data is either returned or not if the clients exists in the db in a recordset. The code then populates the flexgrid with certain fields from the recordset. So this is where I am stuck. I have created a UDT already for this app when data is saved to the db, and it was my intention to have the app act exactly as you said. I want the data loaded into an array and based upon the row selected by the user, then populate the objects on the input tab with the data in that record. But this all comes back to my dilemma with GetRows. My code looks like this with the GetRows function:

Let Records = rstFind.RecordCount
Let arrRecords() = rstFind.GetRows(Records)

There are two different Sub Procedures I have for this task. The first one is the Procudure to find the records in the database and display them in the FlexGrid, if records are returned. The second Procudure is the click event on the flexgrid. Now this is where I am stuck. First, the GetRows function populates the array fields first then rows. The second problem is that I can't exactly pass the arrRecords array to the click event procudure because of order of operation. (Does the GetRows method require me to code anything else to have the data assigned to the array? I am asking because I tested passing the array to a dummy procedure and I attempted to display the data at 0,0 in a text box. Upon trying this, I received an error message that the subscript was out of range.) So at this point I was considering making arrRecords a global array, but I don't want to get in the habit of using global variables when I get frustrated. I think I may be missing something or just have been staring at the screen too long or reading to many VB books to solve this problem. But at the same time I am relieved that your actions to use the arrays was exactly what I have drawn up.

Thanks again,

Rob [Vader2]

 

[zemp]

rb74,

I have a small test project that I created that demonstrates how I use the flexgrid, fill it with records and make it editable. It shows fetching and updating of records and the use of a udt. This might help explain what I tried to explain in words. If you are interested in taking a look at this test project email me at zemanpw@hotmail.com and I will email you a copy that you can look at and experiment with.

I don't use the getrows function so I can't comment on that. Sorry. I am getting ready to go home from work right now, so I will look at your last post in more detail later this evening and post a reply. Thanks and Good Luck!

zemp

 

[zemp]

rb74,

After reading your post again I recommend that you take a look at my test project. It covers almost all of what you have mentioned. It will keep your main form smaller and allow your user to work from one screen. The only thing that I don't quite understand is your printing for the user on the second tab. Unless you are doing a screen shot you would be better writing the information directly to the printer which allows minor formatting, to a text file or using a reporting tool such as Crystal Reports.

Below is a procedure that I use to print out registration information that a client can then fax to us. It does the job on a simple print.

Private Sub Print_Registration()
'// Sends the registration information to the printer with some minor formatting.
Printer.PrintQuality = vbPRPQHigh
Printer.Font.name = "Arial"
Printer.Font.Bold = True
Printer.PaperSize = 1 '// 8.5" x 11" paper.
Printer.ScaleLeft = -1000
Printer.Font.Size = 12
Printer.Print vbCrLf & vbCrLf & vbCrLf
Printer.Font.Size = 20
Printer.Print vbTab & vbTab & App.ProductName
Printer.Font.Bold = False
Printer.Font.Size = 14
Printer.Print vbCrLf & vbCrLf & vbCrLf & "REGISTRATION FORM" & vbCrLf
Printer.Print "Fax to: " & LoadResString(153) & LoadResString(154) & vbCrLf
Printer.Print "Date: " & Date
Printer.Print "Product: " & txtProduct.Text
Printer.Print "Version: " & App.Major & "." & App.Minor & "." & App.Revision
Printer.Print "Serial Number: " & txtSerialNumber.Text & vbCrLf
Printer.Font.Size = 12
Printer.Print "Company: " & txtCompany.Text
Printer.Print "Name: " & txtName.Text
Printer.Print "Title: " & txtTitle.Text
Printer.Print "Address 1: " & txtAddress1.Text
Printer.Print "Address 2: " & txtAddress2.Text
Printer.Print "City / Town: " & txtCity.Text
Printer.Print "Province / State: " & cboProvSt.Text
Printer.Print "Country: " & cboCountry.Text
Printer.Print "Postal / Zip Code: " & txtPCZip.Text
Printer.Print "Phone Number: " & txtPhone.Text
Printer.Print "Fax Number: " & txtFax.Text
Printer.Print "E-Mail Address: " & txtEmail.Text & vbCrLf
Printer.Print "Comments: " & txtComments.Text
Printer.EndDoc
End Sub

Again let me know if you are interested in the test project.
Thanks and Good Luck!

zemp

 

[CCLINT]

You can use GetRows like this:
Dim vData As Variant
vData = rs.GetRows(adGetRowsRest,adBookmarkFirst)
'Now the rs is at EOF, so move back to the first record
rs.MoveFirst

GetRows may not work right depending on the type of cursor and it's location.

You could also use the GetString method of the recordset, and then use that to fill the flex grid:

rs.MoveLast
rs.MoveFirst

With FlexGrid1
'Mark the section in the grid to paste data:
.Rows = rs.RecordCount - 1
.Cols = rs.Fields.Count - 1
.Row = 0
.Column = 0
.RowSel = .Rows
.ColSel = .Cols
'Get the data and clip it to the grid
.Clip = rs.GetString(adClipString, adGetRowsRest, vbTab, vbCrLf)
End With

 

[CCLINT]

But I would still try getting the GetRows method to work for you. This method can return records over 50% faster, even though it is less comfortable to work with.

But, then again, from what I see in your question, you want:
1. the records from the rs to be filled into an array
2. the flex grid to be filled with the records from the rs
3. the input form to have its' controls filled with field values from the rs.

So, it may be best to create a data class with one rs to do all the work, and:
extract the data straight from the class properties for the input form;
have a property proceedure that captures the clip data for the grid;
and a GetRows proceedure to pass an variant to in order to get an array of data for other uses, if needed.

 

[rb74]

CCLINT,

Thanks. I was actually experimenting with a module last night. It's kind of weird because the two Sub Procudures just need to reference either the recordset or the array. Since the module would be global it would solve one problem. Would you recommend to use a module, and if so, should I just reference the recordset (by having the recordset populated in the module), or use the array in the module?

Rob [Vader2]