Newbie's database question

[Faheemi]

Hi there,

I am very new to the Visual Basic. I want to build a simple aplication which will just only have a contact database.
The contact table will basically have name, company, and contact details like address, email, tel and fax.

I wanted to build this application as LIST and FORM structure. The LIST will show just the name, company and email in a grid or whatever format. The user can be able to search using all the 3 fields which are placed just above each column. When the user double clicks a row in the grid it needs to go to the FORM(detail) page where user can see all the information from the database for that particular record. Then the user should be able navigate NEXT or PREVIOUS and to EDIT or DELETE that particular record. From the FORM the user should be able to switch back to the LIST by clicking a button in the FORM and he should stay in the same record as he was in the FORM ( through the FORM he might have navigated to some other records by clicking NEXT or PREVIOUS buttons)

I have just started this project using MSHFlexGrid where the data is pulled from a ADO recordset that I create on the fly during the Form load. I am keeping the Connection(Conn) and Recordset(RS) object in a global variable. The user can search by each field in the LIST. The user can also sort the grid by double clicking on fixed row header of the grid using grid's sort method. Also when he double clicks on any other row I use the RS.Move MSHFlexGrid.Row method to go to that particular record and populating the text fields with recordset's current record value in the FORM page (it is not working if the user sorted the Grid prior to going to the FORM page. it is going to a wrong record)

I am not at all sure my approach is correct. I need your kind advise on this to show me what is the proper approach to accomplish this solution. Do I need to use any other grids or what?. Your help in this will be highly appreciated.

Thanks

Hameed

 

[aata]

i do a similar thing with mshflexgrid but the textboxes and comboboxes in the detail section are not navigable, ie, it is independent of the database(not bound). in your case, are the textboxes or whatever you use in detail page connected to the database directly? i don't know whether that creates any added complications. anyway, in my case, the method i use to populate my detail section is ,

text1.text = flex1.TextMatrix(flex1.Row, 0)
text2.text = flex1.TextMatrix(flex1.Row, 1)

etc......

there are no complications in this, whichever row you click, that particular row's data will be used to manually fill the detail section. will this do in your case?

 

[Faheemi]

Thanks aata,

I think your idea is quite useful. It worked for me. I use a local subroutine to fill the fields in the detail page. But one disadvantage is I need to select all the fields which are in detail page for the grid, while the grid needs to show just 2 or 3 fields.
But I like this method to populate the detail page fields since there are no overhead of database connections or keeping the connection/recordset on for long time until the user closes the page.

Thanks.

Hameed

 

[aata]

did you mean to say that you have 2-3 fields alone shown in the flex and you need to show more fields of the clicked row in the detailed page? i also do the same thing. what i do is that i keep the primary key of the table in the flex and when the flex's row is clicked, i retrieve just the primary key column's data from the flex and then manually get the full record from the table(database) using the select statement into a recordset and fill the textboxes in the detail section and close the recordset. if you don't want to show the primary key to the users, just keep it as the last column or so and don't show it to the user and don't show the horizontal scrollbar of the flex. does this solve your issue.

another thing i wanted to ask was that you said you can go to the previous and next records in the detail section. so, are those textboxes bound to the database. if so, is it working fine with this method of filling data?

 

[Faheemi]

I think I can use your primary key idea so that I do not need to select all the column for the grid. I had this idea too previously. I preferred to select all the columns for the grid and use it in the form page. This method avoids fetching the record from the database every time the user clicks next/previous buttons in the detail page or when he double clicks a row in the list.

The textboxes in the detail page are not bound to the database.
I keep a global variable for the grid's current row. I increase or decrease it when I go next or previous respectively. I also keep a global variable for total number of rows. I use this to check against the current row variable to find whether the user is at the end of the grid list( end of the recordset). When I insert a new record I normally use an Insert statement to insert the record in the table and add a row in the grid's add method. When update I use grid's Text method update the value in the grid and use a normal Update statement for the table. For delete I use grid's remove method.

This approach seem's OK or not?. There is a bit more lines of codes for this. But I feel this is an efficient one. Do you think there is any drawback?

Hameed

 

[aata]

if you keep all the fields in the grid, then there is no need of even using the primary key. as you said, it's better for navigation in the detail section. and the method you have used for next and previous is fine as far as i can see.
by the way, i have a doubt. do you keep the grid and the details in 2 seperate forms? what did you mean by form page and detail page. are they 2 separate forms? if so, why do you keep it in separate forms? any specific reason? wasn't it sufficient to keep it in the same form and separate it with frames or so? just curious.

 

[Faheemi]

I use the primary key when I update/delete action in the database. So I need it. form/detail page are same. They are referring to just one form. Sorry for using two terminologies.

But I am keeping the grid and detail page text boxes in two seperate forms. This is just a design issue. I use two forms for convenience. The grid occupies almost the whole page and on the top I control buttons /textboxes for search and skip back to the homepage form ( i am not using the menus)

By the way I have another question here. When I insert/update the table I need to update the grid. For that I am manually specifying all the textboxes for grids add or remove methods. Is there a way I can set an index on each textboxes manually during the designtime or even during the start-up with the column index of the grid. Then during the insert/update I want to able to retrieve the value of the textbox using that index. So I will use a loop statement to update the grid with the value of textboxes.

Is there a way?

Thanks

Hameed

 

[aata]

you can use control arrays for creating textboxes with index. suppose you have one textbox text1. select it and copy and paste it in the same form. vb will ask you whether you want to create a control array. say yes. now. you will have 2 textboxes with the same name but different indexes. like text1(0) and text1(1). use this method to paste as many text boxes as you want for the detail section. so, in the loop, you can use index to do whatever you are intending to do as

for i = 0 to 9
text1(i) = flex1.textmatrix(rownum,i)
next i

hope this is what you asked.

 

[Faheemi]

I could use this method. But it is not very convenient. I would like set the index or reference to those text objects and call/manipulate them using that reference. While in the control arrays we have tp pre-define the indexes. Also all the text fields get the same name and that is a bit confusing. If you know any other way please share that with me. Otherwise I will manually set the grids.

Thanks
Hameed

 

[aata]

do you use the loop and text boxes to fill the grid fully or just one row while inserting records and updating? for filling the entire grid there is

set flex1.datasource = rs1

where rs1 is the recordset into which you selected records from database.

for adding, you can just give

flex1.Rows = flex1.Rows + 1
flex1.Row = flex1.Rows - 1
flex1.Col = 0
flex1.Text = Trim(txt1.Text)
flex1.Col = 1
flex1.Text = Trim(txt2.Text)
flex1.Col = 2
flex1.Text = Trim(cmbo1.Text)


(or use flex1's textmatrix instead of flex1.text.)

for modifying purpose, you can use the primary key and select the specific row in flex with this primary key and change it's fields to the new values.

in which scenario are you needing the loop and index for textboxes?

if you need to reload the entire flex,

set flex1.datasource = rs1

is the easiest option.

 

[Faheemi]

I am using the following method only. Not looping the whole RS

set flex1.datasource = rs1

Also now I am using a method similar to your

flex1.Rows = flex1.Rows + 1 ( i am using flex1.add method the parameter is all the column values seperated by vbTab)
flex1.Row = flex1.Rows - 1

flex1.Col = 0
flex1.Text = Trim(txt1.Text)
flex1.Col = 1
flex1.Text = Trim(txt2.Text)
flex1.Col = 2
flex1.Text = Trim(cmbo1.Text)

I just don't wanted to use the above method as it is somewhat cumbersome to set flex with each and every detail layout object. See if I got 20 fields the code will grow quite longer. I wanted handle this with any kind of looping.

I have got an idea. I have not implemented it yet. Not sure it will work.

I want to set the tag property of each and every objects (text & combo) in the detail page with the name of respective grid column.

Then I do a For loop to scroll through the each grid column
Then I use a inner ( For Each ctrl in Me.Controls )loop to scroll through the form objects
If the current column name matches with current detail form object's tag value then we update the grid

By using this method I hope we can reduce some lines of codes
What you think about this? This looping method is efficient?

Hameed

 

[aata]

hameed, your idea is good. but about the efficiency, i'm not sure. tabindex is used to step thru the controls using tab key, isn't it. so, if you you assign the tabindexes of the controls according to your need in the datagrid, you will have to place the controls in the same order as you have them in the grid. if you later decide to change the order of the textboxes in the detail section, users using the tab key will find it annoying, won't they. maybe that is irrelevant. anyway, i was thinking, why not create an array to store the names of the textboxes or comboxes and use it in the loop as

Dim c(10) As Variant

Set c(0) = txtname 'if text of txtname should be in column 0 of grid
Set c(1) = txtcode 'if text of txtcode should be in column 1 of grid
Set c(2) = Combo1 'etc

'if you set it this way once in the form_load, then you can use c(i) in the loop , isn't it?

for i = 0 to 20
flex1.textmatrix(rownum,i) = c(i).text
next i

what do you think?

 

[Faheemi]

I am not speaking about the tab index property. I am only using the tag property. Each and every objects carry a tag property that actually used for carrying some extra information.

In my situation I use the tag property of each and every form layout objects to to hold the name of each grid column ( ie. the 0th row value of the column. ) corresponding to that object.

For ex. if I wanted to add a row, I add a row in the grid.
Then I loop through each column of the grid in the that row.
If the tag value of the current object matches the column name then I set the text value of
the current row to the grid's current col. Please see example below:

With frmContactList.MSHFlexGrid1
.Rows = .Rows + 1
.Row = .Rows - 1
For i = 0 To .Cols - 1
.Col = i
For Each ctrl In Me.Controls
' 0th row is the header of the grid
If .TextMatrix(0, i) = ctrl.Tag Then
.Text = ctrl.Text
End If
Next
Next i

Hope this is OK.

Hameed

 

[aata]

sorry, i misunderstood. this is a good method.