Hello Everyone,
I sort of have a problem that may be hard to understand so I will do my best to give you as much information as possible. Any questions please let me know.
First off I have a Form with a MSHFlexgrid, ComboBox, and alot of TextBoxes on it.
What I Am Looking To Do
Upon loading the MSHFlexgrid displays data from two tables having a relationship on Table1.CompanyID = Table2.CompanyID from an Access 2002 database. It displays them as follows in the MSHFlexgrid.
Column (0) Fixed Column No Data Displayed.
Column (1) CompanyID From Table 1
Column (2) Company Name From Table 1
Column (3) City From Table 1
Column (4) ComboBox (That moves pending the row clicked) With All The Contacts From Table 2 That Are Related To Table1
When the user clicks any row in this grid the ComboBox is automatically displayed in the right most column and is filled with all the contact names from Table 2 that are related to the CompanyID in Table 1. The MSHFlexGrid Click Event is coded as folllows: Also
txtCell is the name I gave my ComboBox.
Code:
Private Sub MSHFlexGrid1_Click()
'********************* DIM VARIBLES ********************************************
Dim sCustInfoSQL As String 'Dim SQL String
Dim connCustInfo As New ADODB.Connection 'Dim Connection
Dim rsCustInfo As New ADODB.Recordset 'Dim Recordset
Dim RowValue As Long
Dim IDValue As String
With MSHFlexGrid1
RowValue = .MouseRow 'Sets Mouse Row Value Into Varible
IDValue = .TextMatrix(RowValue, 1) 'Read The Value In Column 1 Based On The Row Clicked
End With
txtCell.Clear
'********************* CONNECTION ********************************************
connCustInfo.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Mechanical Contractor\Access\Customers.mdb;" & _
"Persist Security Info=False" 'Connection
'************** BEGIN SQL STATEMENT FOR SELECTING CONTACT NAMES ********************
sCustInfoSQL = "SELECT FirstName + "" "" + LastName AS FullName "
sCustInfoSQL = sCustInfoSQL & "FROM CompanyInfo "
sCustInfoSQL = sCustInfoSQL & "INNER JOIN ContactInfo "
sCustInfoSQL = sCustInfoSQL & "ON CompanyInfo.CompanyID = ContactInfo.CompanyID "
sCustInfoSQL = sCustInfoSQL & "WHERE CompanyInfo.CompanyID = " & IDValue & " "
sCustInfoSQL = sCustInfoSQL & "ORDER BY FirstName"
'********************* OPEN RECORDSET **********************************************
rsCustInfo.Open sCustInfoSQL, connCustInfo, adOpenKeyset, adLockBatchOptimistic
While Not rsCustInfo.EOF 'Loop through all records in recordset
With rsCustInfo
If IsNull(.Fields("FullName").Value) Then
Exit Sub
Else
txtCell.AddItem rsCustInfo!FullName
txtCell.ListIndex = 0
End If
.MoveNext
End With
Wend
rsCustInfo.Close
connCustInfo.Close
showTxtCell
End Sub
After the click event is through, it then goes to the
showTxtCell sub to show the ComboBox in the row that the user has clicked on.
Code:
Private Sub showTxtCell()
If m_lCellRow = -1 Then
With MSHFlexGrid1
' store the current row and column
m_lCellRow = .Row
.Col = 4
' move the combobox to the correct cell
txtCell.Move .Left + .CellLeft, .Top + .CellTop, .CellWidth
End With
txtCell.Visible = True
End If
End Sub
So now I have this neato combobox that shows all my contact names for the compay that is in my grid row that the user has selected. So now a user can select a specific contact and have all his data (ie. FirstName, LastName, Phone etc) dispayed in my TextBoxes. This is where my problem begins.
Ive tried using txtCell Click() to determing what contact was selected. When a user selects a contact it goes to the LoadContact_Info() which looks like this.
Code:
Private Sub LoadContact_Info()
Dim sContactDataSQL As String 'Dim SQL String
Dim connContactData As New ADODB.Connection 'Dim Connection
Dim rsContactData As New ADODB.Recordset 'Dim Recordset
Dim RowValue As Long
Dim IDValue As String
With MSHFlexGrid1
RowValue = .MouseRow 'Sets Mouse Row Value Into Varible
IDValue = .TextMatrix(RowValue, 1) 'Read The Value In Column 1 Based On The Row Clicked
End With
'********************* CONNECTION ********************************************
connContactData.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Mechanical Contractor\Access\Customers.mdb;" & _
"Persist Security Info=False" 'Connection
'************** BEGIN SQL STATEMENT FOR SELECTING CONTACT DATA ********************
sContactDataSQL = "SELECT CompanyInfo.CompanyID,CompanyName,MailAddress, "
sContactDataSQL = sContactDataSQL & "MailAddress2,MailCity,MailState,MailZipCode,MailCountry, "
sContactDataSQL = sContactDataSQL & "ShipAddress,ShipAddress2,ShipCity,ShipState,ShipZipCode,ShipCountry, "
sContactDataSQL = sContactDataSQL & "FirstName,LastName,MiddleInitial, "
sContactDataSQL = sContactDataSQL & "Phone1,Extension,TollFreePhone,Phone2,Phone3, "
sContactDataSQL = sContactDataSQL & "Fax,Fax2,Fax3, "
sContactDataSQL = sContactDataSQL & "Mobile,Mobile2, "
sContactDataSQL = sContactDataSQL & "Email,Email2,Website,Website2, "
sContactDataSQL = sContactDataSQL & "Title "
sContactDataSQL = sContactDataSQL & "FROM CompanyInfo "
sContactDataSQL = sContactDataSQL & "INNER JOIN ContactInfo "
sContactDataSQL = sContactDataSQL & "ON CompanyInfo.CompanyID = ContactInfo.CompanyID "
sContactDataSQL = sContactDataSQL & "WHERE CompanyInfo.CompanyID = " & IDValue & " "
sContactDataSQL = sContactDataSQL & "ORDER BY CompanyName"
rsContactData.Open sContactDataSQL, connContactData, adOpenKeyset, adLockBatchOptimistic
While Not rsContactData.EOF 'Loop through all records in recordset
With rsContactData
'************************* Customer ID ***************************************
If IsNull(.Fields("CompanyID").Value) Then
Label3.Caption = ""
Else
Label3.Caption = rsContactData!CompanyID
End If
.MoveNext
End With
Wend
rsContactData.Close
connContactData.Close
Here is where I have my problem. If the ComboBox has more than 1 contact in it, lets say there are 3 and the user selects the 2nd or 3rd contact. It wants to lookup the ID value that is 2 or 3 rows down (giving me the wrong contact data), since the ComboBox drops down to display all the contacts. But if the 1st contact is selected it will properly work.
I have also tried the Change() event but that will only work if the user manually types the name in.
Any suggestions?
I hope I have made this post clear enough. In the event that there are questions please let me know.
Thankyou In Advance.
ComboBox Within A MSHFlexgrid
Mshflexgrid sorting problem
Linear Mode
