Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 15th, 2006, 05:55 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default ListView record in a form

I have a search form which lists the results from a database in a ListView. Is there a way in that when I double click on any of the records in the listview, i can be able to launch another form which has details of the member i had searched on based on the ID? I dont know whether it is clear.

Reply With Quote
  #2 (permalink)  
Old May 16th, 2006, 03:15 AM
Authorized User
 
Join Date: Mar 2006
Location: Bratislava, , Slovakia.
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In Event YourListViewName_DblClick you get selected Item. And then you may use some SQL string to search detail information about it.

e.g.

Private Sub ListView1_DblClick()
Dim strSQL as String
dim ID as String

  ID = 'I don't know, where you store current ID (it's first column, or where?)
  strSQL = SELECT * FROM your_table WHERE ID = " & ID

  'open recordset (via ADO, or DAO)
  'open form with full details information and fill it's fields with specified record
End Sub

Reply With Quote
  #3 (permalink)  
Old May 16th, 2006, 06:52 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes my ID (UserId field) is in the first column in both the table and the listview control. I am still not sure of how to open the form that has the details. Should I use the form.show method or how exactly should I implement this? And how best should I declare the ID variable?



Reply With Quote
  #4 (permalink)  
Old May 16th, 2006, 07:54 AM
Authorized User
 
Join Date: Mar 2006
Location: Bratislava, , Slovakia.
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
ID can be declared as String, because it concat with SQL string which is string too.
Private Sub ListView1_DblClick()
Dim ID as String

  ID = Me.ListView.SelectedItem.Text

  frmDetail.ID = ID
  frmDetail.Show vbModal

End Sub

'open form Detail:


'Type in detail form:
'add new Property e.g. ID in declaration section of form
Private mstrID as String

Public Property Let ID (ByVal NewID As String)
  mstrID = NewID
End Property

Public Property Get ID As String
  ID = mstrID
End Property

'form Open event
Private Sub Form_Open()
Dim strSQL as String
Dim rs as New ADODB.Recordset

  strSQL = "SELECT * FROM your_table WHERE ID = " & mstrID
  rs.Open strSQL, yourConnectionObject, yourLocks

  me.Field1.text = rs.Field(0).Value
  ...

  rs.Close
  Set rs = Nothing

End Sub


Reply With Quote
  #5 (permalink)  
Old May 18th, 2006, 12:27 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi peko,

I have tried your solution but i get an error with the recordst. This is how i implemented the code.

For the form with the listview items i added:-

Code:
Private Sub ListView1_DblClick()
Dim strSQL As String
Dim UserId As String

    UserId = Me.ListView1.SelectedItem.Text

  frmUser.UserId = UserNo
  frmUser.Show vbModal

End Sub
And for the details table i added:-

Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Private mstrID As String

Public Property Let UserId(ByVal NewUserId As String)
  mstrID = UserId
End Property

Public Property Get UserId() As String
  UserId = mstrID
End Property

Private Sub Form_Load()
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open App.Path & "./databases/Visualtek.mdb"

Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM users WHERE UserId = " & mstrID

rs.Open strSQL, cn, adOpenDynamic

  txtLastName.Text = rs.Fields("LastName").Value
  txtFirstName.Text = rs.Fields("FirstName").Value

  rs.Close
  Set rs = Nothing
End Sub
The error given is "No value given for one or more parameters"
When I debug the code it pints at the rs.Open line and shows the sql to be:

strSQL = "SELECT * FROM users WHERE UserId = john"

Yet UserId should be picking the UserID field but in this case it is picking the LastName field. Where am I going wrong?

I will really appreciate your help.

Thanks

Reply With Quote
  #6 (permalink)  
Old May 19th, 2006, 02:44 AM
Authorized User
 
Join Date: Mar 2006
Location: Bratislava, , Slovakia.
Posts: 80
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi osemollie,
if you get LastName when you want to get ID from ListView, then ID isn't first column in ListView, because ListView1.SelectedItem.Text return value of first colunm.
How do you fill the ListView?

Peko

P.S. I find something wrong:
UserId = Me.ListView1.SelectedItem.Text - there you use UserID variable
frmUser.UserId = UserNo
- and there UserNo, it's correct?

Reply With Quote
  #7 (permalink)  
Old May 19th, 2006, 03:11 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a procedure called PopulateList which I call when I want to populate the ListView. The code is

Private Sub PopulateList(pList As ListView, _
                         pRst As ADODB.Recordset)
    On Error Resume Next
    Dim i As Long
    Dim iColCount As Long
    Dim sColName As String
    Dim sColValue As String
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oSI As ListSubItem
    Dim oFld As ADODB.Field

    With pList
        .View = lvwReport
        .GridLines = True
        .FullRowSelect = True
        .ListItems.Clear
        .Sorted = False
        pRst.MoveFirst
        ' set up column headers

        For Each oFld In pRst.Fields
            sColName = cn(oFld.Name)
            Set oCH = .ColumnHeaders.Add()
            oCH.Text = sColName
            iColCount = iColCount + 1
        Next oFld


        Do Until pRst.EOF
            i = 0
            ' setup fiprst column as a listitem
            sColValue = cn(pRst.Fields(i).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            ' add the remaining columns
            'as subitems

            For i = 1 To iColCount
                Set oSI = oLI.ListSubItems.Add()
                oSI.Text = cn(pRst(i))
            Next ' next column
            pRst.MoveNext
        Loop ' Next record
        ' refresh it all
        .Refresh
        ' make sure 1st row can be seen
        .ListItems(1).EnsureVisible
    End With
End Sub

The recordset for populating the ListView is:-

rst.Open("SELECT LastName, FirstName, UserId, Institution, MobileNo FROM users"

Or is there an easier way of populating the ListView with recordsets from the database?

The users table structure is:

UserNo, NUMBER, PrimaryKey
UserId, TEXT
LastName, TEXT
FirstName, TEXT
Address, TEXT
Telephone, TEXT
MobileNo, TEXT
E-mail, TEXT

My sample program is for registering members of a club. Members can be adults or children. Adults have ID cards whereas children don't have ID cards. That is why I opted to use UserNo as my primary key instead of UserId, since children members won't have ID cards.

Thanks

Reply With Quote
  #8 (permalink)  
Old May 19th, 2006, 04:05 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Peko,

I managed to sort out the problem. It was to do with my recordset and listview display. Thanks for your patience and help. Be blessed.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
passing unique record from form to form Baconant Beginning VB 6 1 January 2nd, 2008 06:20 PM
Search for a record using a form a_wahab Access 6 September 1st, 2006 12:03 PM
Print active form with listview pavel Pro VB 6 4 November 22nd, 2004 02:10 PM
Keep last record in a form patmolard Access VBA 17 April 22nd, 2004 01:02 PM
Record selected goes to new form evadhx VB How-To 1 December 23rd, 2003 08:29 AM



All times are GMT -4. The time now is 06:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.