Datagrid double click

[Maypen]

Hi Guys,
I am new to VB database and need your help. I am creating a small customer and product database. After populating a datagrid with, say, customers name and address, i would like to doubleclick on a particular name and open up a form with more details of this particular customer. How do i get the system to select that specific record. Please explain in detail as i am new to Vb DB. CHeers!!

 

[tonioJ]

'-------Code in the form where you place the datagrid object ---
'-- assume that the name of the form where this code is: Form1

Public PKVar as string
Private Sub DataGrid1_DblClick()
'--LOAD THE DETAILS OF THE DATA INTO THE DETAIL WINDOW

If rs.RecordCount <> 0 Then
isViewDetails = True
isAdding = False
isEdit = False
PKVar = rs![FieldName]

frmDetails.Show 1
End If
End Sub

'-------------------Code for the Detail form named frmDetails -------
Private Sub Form_Load()
dim strSQL as string
Dim rsDetail As New ADODB.Recordset

'--get the detail of the selected record ----

strSQL = "Select * from TableName where TablePKField = '" Form1.PKVar & "'"

rsDetail .Open strSQL, dbConnection, adOpenKeyset, adLockOptimistic, adCmdText

With rsDetail
textbox1.text = ![Field1]
textbox2.text = ![Field2]
....
....
So on...

End With

rsDetail.close


End Sub


I hope this helps..

 

[Maypen]

Hi tonioJ,
Thanks for your reply. i understood what you have written and it looks correct. I will try it later and get back to you. Much appreciated. Cheers!!

 

[Maypen]

Hi tonioJ,
Thanks for your previous answer. I tried it but kept getting a syntax error in the frmDetails: (Form1.PKVar & "'"). Can you look at my code and tell me where i am going wrong please. I know my code is a bit long winded but it helps me understand whats going on. Cheers!!

‘Code for Form1

Public PKVar As String


Private WithEvents connConnection As ADODB.Connection

Private WithEvents rsRecordSet As ADODB.Recordset

Dim mblnAddMode As Boolean

Private Sub Form_Load()

Dim strConnect As String

Dim strProvider As String

Dim strDataSource As String
Dim strDataBaseName As String
strProvider = "Provider= Microsoft.Jet.OLEDB.3.51;"

strDataSource = App.Path
strDataBaseName = "\Fanso.mdb;"
strDataSource = "Data Source=" & strDataSource & _
strDataBaseName


strConnect = strProvider & strDataSource
Set connConnection = New ADODB.Connection

connConnection.CursorLocation = adUseClient

connConnection.Open strConnect
Set rsRecordSet = New ADODB.Recordset

rsRecordSet.CursorType = adOpenStatic
rsRecordSet.CursorLocation = adUseClient
rsRecordSet.LockType = adLockPessimistic

rsRecordSet.Source = "Select * From Employees"
rsRecordSet.ActiveConnection = connConnection

rsRecordSet.Open


If rsRecordSet.BOF = True Or rsRecordSet.EOF = True Then
Exit Sub
End If
Set DataGrid1.DataSource = rsRecordSet
DataGrid1.Refresh
End Sub

Private Sub DataGrid1_DblClick()
'--LOAD THE DETAILS OF THE DATA INTO THE DETAIL WINDOW

If rsRecordSet.RecordCount <> 0 Then
isViewDetails = True
isAdding = False
isEdit = False
PKVar = rsRecordSet![EmployeeID]

frmDetails.Show 1
End If
End Sub


'-------------------Code for the Detail form named frmDetails -------
Private Sub Form_Load()
Dim strSQL As String
Dim rsDetail As New ADODB.Recordset

'--get the detail of the selected record ----

strSQL = "Select * from Employees where EmployeeID = '"
Form1.PKVar & "'"

rsDetail.Open strSQL, dbConnection, adOpenKeyset, adLockOptimistic, adCmdText

With rsDetail
textbox1.Text = ![FirstName]
textbox2.Text = ![LastName]
textbox3.Text = ![Title]


End With

rsDetail.Close

End Sub

 

[johnwm]

strSQL = "Select * from Employees where EmployeeID = '"
Form1.PKVar & "'"

should all be on one line - you have also missed an ampersand. Try :

strSQL = "Select * from Employees where EmployeeID = '" & Form1.PKVar & "'"

This assumes that PKVar contains a string value - if it's numeric you don't use the apostrophes

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[Maypen]

Hi johnwm,
Thanks for your quick reply. This has worked. But i now get an error on the next line:

rsDetail.Open strSQL, dbConnection, adOpenKeyset, adLockOptimistic, adCmdText

The error is "Arguments is of the wrong type, out of acceptable range ,or are in conflict with one another."
By the way i am using VB6 and Access.
Cheers!!

 

[Golom]

Where is dbConnection defined? I see a definition for connConnection only.

 

[Maypen]

Thanks Golom! I Didn't realise that i had not done it. It is now working. Cheers also to johnwm and tonioJ.