[RESOLVED] Selecting RECORDS from an MSFlexGrid

[Red_Dragon_X]

Hey all,
Im using si_the_geek's method for filling an MSFlexGrid from a DB as stated here: Fill a MSFlex from DB

now im trying to populate Textboxes with values from the record that is selected from the Grid. I originally had planned on using the .TextMatrix props to get it done, but i need data from some of the fileds that i dont display on the Grid. So im trying to find a way to use something like i did with a ListView where i use a .Tag each time data is inserted to the Grid and then calling on it, but i cant find good documentation on how i would accomplish this with a FlexGrid.

Anyone have any suggestions ?

[Red_Dragon_X]

well i finally figured it out, but i had to use a datacontrol again, and seems like when ever i try and get away from them i have to use them again.

The part that i hate is that the "PhoneNumber" field is the primary key but i dont want to display that filed on the FlexGrid, so i just set the column width = 0 for that field so that it would still be there to call, but still not visible. then i just used the datacontrol to fill the txt boxes:

Private Sub MSFlexGrid1_Click()
Dim var1 As String
    var1 = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 5)
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= F:\DB1.mdb"
    cn.Open
strSQL = "SELECT Userid, CreateDateTime, Notes FROM CustomerNotes WHERE PhoneNumber = '" & var1 & "'"
    Set rs = New ADODB.Recordset 
    rs.Open strSQL, cn, adOpenKeyset
 
Call FillFromRecordset_FlexGrid(Form1.MSFlexGrid1, rs, True)
Call FlexGrid_colors1 'Fill color sub for Form1
 
strSQL = "SELECT * FROM Customers WHERE PhoneNumber = '" & var1 & "'"
datFind.RecordSource = strSQL
datFind.Refresh
 
Form1.txtClientName.Text = datFind.Recordset("FirstName") & " " & datFind.Recordset("LastName")
Form1.txtAddress.Text = datFind.Recordset("Address1")
Form1.txtCity.Text = datFind.Recordset("City")
Form1.txtState.Text = datFind.Recordset("State")
Form1.txtZip.Text = datFind.Recordset("zipcode")
Form1.txtHomePhone.Text = datFind.Recordset("PhoneNumber")
Form1.txtEmail.Text = datFind.Recordset("Email")
    
Form4.Hide
Form1.Show
End Sub

So im REALLY hoping there is an easier way to do this ... if anyone has some suggestions !

[si_the_geek]

If the unique field is numeric, you can use the RowData property (of each row) to store it, but as the field is text then the solution you have is one of the better methods.

I have amended your sub below, with a few improvements.

There is no need to ever use the data control, you can simply use a recordset variable (either another one, or re-use the same one). Once each type of recordset has been loaded, datFind.Recordset("FirstName") is the same as rs("FirstName") , but I have been more specific in the code below (it's slightly faster, and is less error prone too).

When you have finished with a recordset (or any other object variable, such as cn) you need to close it if appropriate, then set it to Nothing. This frees memory, and stops any conflicts with the objects (in the case of recordsets and connections, you might stop another user from working with the same database, even when your program has closed).

I have not put in code to close (or declare) the connection, as you can safely keep it open the entire time your program is open - but you need to close it properly when your program closes. I would normally open the connection at the start of the program, and close it at the end, but your usage determines if you should do the same (or declare & open & close it in this sub).

Private Sub MSFlexGrid1_Click()
Dim var1 As String
    var1 = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 5)
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= F:\DB1.mdb"
    cn.Open
 
Dim rs As ADODB.Recordset
    strSQL = "SELECT Userid, CreateDateTime, Notes FROM CustomerNotes WHERE PhoneNumber = '" & var1 & "'"
    Set rs = New ADODB.Recordset
'use the most restrictive options that allow what you need - in this case, the most restrictive there is!
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
 
    Call FillFromRecordset_FlexGrid(Form1.MSFlexGrid1, rs, True)
    Call FlexGrid_colors1 'Fill color sub for Form1
 
    rs.Close    'close object, so we can re-use it
 
    strSQL = "SELECT * FROM Customers WHERE PhoneNumber = '" & var1 & "'"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
 
    Form1.txtClientName.Text = rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value
    Form1.txtAddress.Text = rs.Fields("Address1").Value
    Form1.txtCity.Text = rs.Fields("City").Value
    Form1.txtState.Text = rs.Fields("State").Value
    Form1.txtZip.Text = rs.Fields("zipcode").Value
    Form1.txtHomePhone.Text = rs.Fields("PhoneNumber").Value
    Form1.txtEmail.Text = rs.Fields("Email").Value
    
    rs.Close    'close object, and clear memory
    Set rs = Nothing
    
    Form4.Hide
    Form1.Show
 
End Sub

[Red_Dragon_X]

YAY !!! i got to delete another Data Control on my forms !! LOL. THANKS !

btw, do you know much about emails via:

ShellExecute 0, vbNullString, "mailto:" & txtEmail & "?subject=""&body="", vbNullString, vbNullString, vbNormalFocus

im trying to get it to populate a draft(from outlook, but it could be in any format) that i have into the body of the email, that way i can have a new email pop up with the subject and body already filled out.

[si_the_geek]

Well that's a lucky coincidence... I can't remember what reminded me of it, but I found this this other day:
http://www.vbforums.com/showthread.php?t=326272
..post 6 contains a sub which can create it with a subject/body/etc.

[Red_Dragon_X]

Yes thats perfect !! thank you soo much !