[RESOLVED] MS Flexgrid click on cell open form showing same record

[-zip-]

Hello,

Have a form which shows each record in an Access Database that has a lot of detail.

Then made an msflexgrid to show all the records in a grid fashion with less detail so the user can see the important stuff for all records at a glance.

Would like the user to be able to click on a row in the flexgrid and open the corresponding record which has the more detail and can edit etc. in the form.

Any suggestions on how to approach this?

Click on Msflexgrid row (anywhere)

open.Form1 with the same record as as the user clicked on the Msflexgrid.

[si_the_geek]

Do you have a field which is unique for each record?

If so, simply store that for each row in the grid (if the unique field is numeric, you can use the .RowData property), and use that value in your query for loading the whole record.

[-zip-]

Thanks Si,

I have a unique number for each row which is the ID autonumber from the access database in the msflexgrid. It is hidden within the flexgrid with .ColWidth = 0

I am using a bound Flexgrid control. The form also has the ID for the record in a Textbox.

So how do I get the ID number that is in the grid to search the database then open up the corresponding record in the form using MSFlexGrid1_Click()?

[-zip-]

Got the primary key on the grid working as Mykey. Now how do I get that primary key to show the record on Form1 that equals the primary key, the table is called Maintenance ?

Private Sub MSFlexGrid1_Click()

MSFlexGrid1.Col = 0
MyKey = MSFlexGrid1.Text
MsgBox "Selected record: " & MyKey
Form1.Show
End Sub

[si_the_geek]

How you would use it depends on how you have designed the second form, and how you interact with the database.

In terms of passing the value of MyKey across, there are a few options, such as:

• Using a Public variable: This is messy (as the code is in extra places), and it is unreliable as it can be changed by any part of your program.
• Using a Property or Sub on Form1: This is much tidier, as it allows you to put the variable (or rather, parameter) and the code that works with it in the same place. It also encapsulates the 'variable' so that it can only be changed from one place, which is much harder to use by mistake.

If you used a sub, it could be like this (in Form1):

Public Sub ShowRecord(p_lng_RecordID as Long)
  'code here to load the record (using p_lng_RecordID) and display it

  Me.Show
End Sub

To call that sub, you could change your _Click event to this:

Private Sub MSFlexGrid1_Click()
Dim lng_MyKey as Long  'if you are going to use a variable, you should declare it to save mistakes

    'read from the grid (without selecting the hidden cell)
  lng_MyKey = CLng(MSFlexGrid1.TextMatrix(MSFlexGrid1.MouseRow, 0)
    'equivalent which does select the cell
  'MSFlexGrid1.Col = 0
  'lng_MyKey = CLng(MSFlexGrid1.Text)

  MsgBox "Selected record: " & lng_MyKey

    'tell Form1 to show the record for this ID
  Form1.ShowRecord lng_MyKey
End Sub

[-zip-]

Thanks Si,

When you say

Public Sub ShowRecord(p_lng_RecordID as Long)
  'code here to load the record (using p_lng_RecordID) and display it

Do you mean an SQL statement to find "lng_MyKey"and fill the form with .. for example? Or does the above show the record without reloading it on the form?

.Fields("DateDone") = Text1.Text
    .Fields("MileageInterval") = MileageIntervalCombo.Text
    .Fields("Mileage") = Text2.Text
    .Fields("NextVisitMileage") = Text3.Text
    .Fields("WorkDone") = Combo2.Text
    .Fields("ShopName") = Combo3.Text
    .Fields("Cost") = Text4.Text
    .Fields("RateofWorkDone") = Shop_Rating.Text

...yes I am a noob, thanks for the GREAT help btw!

[si_the_geek]

You would use an SQL statement to load the record, and then code to display it (similar to what you posted, but with each line the opposite way around).

The code "Me.Show" simply makes that form visible ("Me" means 'the form this code is in').

[-zip-]

Hi Si, hope the UK is treating you well.

OK took a stab at it, no errors but I bet my code is messed up as it does not do anything.

This is on grid form, I used your second suggestion as the column where the Autokey is not hidden.

Private Sub MSFlexGrid1_Click()

  MSFlexGrid1.Col = 0
  lng_MyKey = CLng(MSFlexGrid1.Text)

  MsgBox "Selected record: " & lng_MyKey

  Form1.ShowRecord lng_MyKey
    

End Sub

This is on Form 1 - The Table is Maintenance, ID is the column where I want to search for the key and just used one field the textbox to load for testing.

Public Sub ShowRecord(p_lng_RecordID As Long)
  'code here to load the record (using p_lng_RecordID) and display it

strSQL = "Select ID From Maintenance WHERE EXISTS (& lng_MyKey & = ID)"

 Text1.Text = rs.Fields("DateDone")
 
 Me.Show
End Sub

[si_the_geek]

Note that an SQL statement is just a string until you do something with it.. You need to use it as a parameter to rs.Open to put the data into that recordset. You should also check that there are records (Not rs.EOF), and close the recordset when you have finished with it.

There are also a few problems with your SQL statement itself:

• you only specified the ID field to be returned, and it seems you want more than that (so specify the ones you want).
• "Exists" doesn't give you a particular record, but simply tells you if it exists or not. Remove that word, the rest of the Where clause is what you want.
• To append a variable (or anything else) to a string, you need to end/start the string with " , eg: (" & lng_MyKey & "=

[-zip-]

Got it working.. almost. The ID Key is not passing properly. I can click on a row on the grid and it does pass and fill the form beautifully but the record is incorrect.
So if I click on say ID 64 on the row of the grid, it will give me ID 63, then 64, then 65 etc.

I think maybe its the EoF line?

Public Sub ShowRecord(p_lng_RecordID As Long)
 Dim lng_MyKey As Long  'if you are going to use a variable, you should declare it to save mistakes
       
    'code here to load the record (using p_lng_RecordID) and display it
OpenConn
Set oRS = New ADODB.Recordset
strSQL = "Select * From Maintenance WHERE  ID IN (" & lng_MyKey & ")"
oRS.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
 If Not (rs.BOF = True Or rs.EOF = True) Then
                 rs.MoveNext 'move next
                If rs.EOF Then rs.MoveLast
 fillfields

End If
Me.Show
'oRS.Close
 'Set oRS = Nothing
End Sub

This is the Sub for fillfields

Public Sub fillfields()
    If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        Text1.Text = rs.Fields("DateDone").Value & vbNullString 'text1 = field2 and display that data
      MileageIntervalCombo.Text = rs.Fields("MileageInterval").Value & vbNullString 'as above
 Text2.Text = rs.Fields("Mileage").Value & vbNullString  'as above
 Text3.Text = rs.Fields("NextVisitMileage").Value & vbNullString 'text1 = field2 and display that data
 Text6.Text = rs.Fields("ID").Value & vbNullString
 Combo2.Text = rs.Fields("WorkDone").Value & vbNullString 'as above
 Combo3.Text = rs.Fields("ShopName").Value & vbNullString 'as above
 Text4.Text = rs.Fields("Cost").Value & vbNullString 'as above
 Shop_Rating.Text = rs.Fields("RateofWorkDone").Value & vbNullString 'as above
 Date_Interv_Combo.Text = rs.Fields("DateInterval").Value & vbNullString 'as above
 Text5.Text = rs.Fields("NextDate").Value & vbNullString 'as above
     Call SetRecNum
             Else
             MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
    End If

End Sub

[si_the_geek]

Ah.. I didn't spot the mistake last time.

The parameter is p_lng_RecordID, but instead of using that you have created a new variable called lng_MyKey. You should declare that variable in MSFlexGrid1_Click (assuming you still use it there, like you did in post #8), but should not be using it in ShowRecord.

Instead, simply use p_lng_RecordID. The value will be copied into it when you call the sub (when you use "Form1.ShowRecord lng_MyKey", p_lng_RecordID will have the same value as lng_MyKey).


Also, in ShowRecord there is no need for either "rs.MoveNext" or "rs.MoveLast". You don't really need the If statement either, as you have the same thing in fillfields (but with a message if there are no records).

[-zip-]

Almost there..

OK I grasp what you are saying now.

Form1.ShowRecord lng_MyKey

passes the key to:

Public Sub ShowRecord(p_lng_RecordID As Long)

This is where I get lost:

I figured its an SQL statement and then loop it that would bring the single record which equals the key pass.

The part that confuses me is how does ShowRecord(p_lng_RecordID) know what record to pull? I thought you would include the variable" lng_MyKey " in the SQL Statement.

So what code in ShowRecord P_Ing_RecordID do I put in to pull that one record using the " lng_MyKey" variable?

Pics of what I am trying to do below:

[si_the_geek]

The value of the key is passed from the variable lng_MyKey (which only exists in the source routine), and the ShowRecord sub receives it in the p_lng_RecordID parameter (which only exists in ShowRecord).

So, what you want in ShowRecord is something like this:

strSQL = "Select * From Maintenance WHERE  ID = " & p_lng_RecordID

There is no need to loop, as this will only load the matching record(s).


By the way, as you seem to have problems understanding where variables exist, I would recommend reading these articles:

What is Option Explicit, and why should I use it?
What is the difference between Dim/Private/Public/Global/Static/Const?

These are both from our from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

[-zip-]

Thanks a ton for being so patent with me Si, I can hardly wait to get home and try it. I will read the links tonight as well. Thanks a Ton!