Listbox Database Question

[Future_FireFighter]

Hey all. Just fyi I am noob to vb. still got alot to learn. But i've been stuck on this situation for a couple weeks now. I'm workin on this program, and its fully functional except the listbox portion of it. In form1 i have a listbox that holds entries from a column of my database. what i want it to do. is double click a listbox entry which opens form2 and displays the information regarding the selected entry. that part works, but its not pulling the right entry, its only pulling the first entry of the database no matter which entry i select. could anyone help me plz?
thanx in advance to anyone who can help me with this.

[Bonnie West]

Which version of VB are you using?

[Future_FireFighter]

vb 6

[dee-u]

Could you show us some code? In your sql are you using something like "SELECT * FROM Table WHERE Column=value"?

[Future_FireFighter]

the only code i have for the double click event is the form.show.
sql i have Select * from Accounts

sorry if thats not enough. i'm still new to workin with databases

[bhavik]

u have to select that records only which ur item is selected and match with the one of the colunm value as
so ur select statement will be

Select * from Accounts where column_name = "& Listview.itemseleted &"

then check the recordcount of result select query if it is greater then one then assign the values to appropriate text fields
for example
Text1.text= rs.fields(0)
Text1.text= rs.fields(1)

and so on

Hope this help u

[Future_FireFighter]

ty bhavik, i tried workin with that. kept gettin error on form load
Data1.recordset.movefirst

here is the code for that section

Data1.Recordset.MoveFirst
Do While Data1.Recordset.EOF = False
lstAcc.AddItem Data1.Recordset.Fields(0).Value & ""
Data1.Recordset.MoveNext
Loop

the information i'm requesting from the database will display on labels not textboxes.

[SamOscarBrown]

[QUOTE=Future_FireFighter;4389555], but its not pulling the right entry, its only pulling the first entry of the database no matter which entry i select.

When you click on your listbox, you need to return the .text value of that listbox....
ex:
private sub list1_click()

'create your query like: (assuming you have dimmed cmd and rs
cmd.CommandText = "select * from Accounts where column_name = " & list1.text
Set rs = cmd.Execute
Do While NOT rs.EOF
lstAcc.AddItem rs!myfieldname 'whatever field name you are trying to return to your listbox (lstAcc?)
rs.MoveNext
Loop

[Future_FireFighter]

no i did not dim cmd and rs

[SamOscarBrown]

No, because you are not doing it the same way I am...HOWEVER, the CONCEPT is the same...you need a QUERY like my cmd.commandtext, and you need to call your query from the click event of your listbox
Then loop through whatever records your query returns (data1.recordset), and add them to your lstAcc listbox.

If you can't figure it out, post (or attach -- less .exe file) your code/project.

[SamOscarBrown]

FF---on the Go Advanced button below, find the place to attach your zipped file and send (per our PM discussions).

I have attached the first five steps in jpg images on how to attach files to this Forum....(As I can only upload 5 per post, I'll send the last image in a moment.) Follow the steps as outlined and you will be able to attach your zipped code.

[SamOscarBrown]

Here's the last step......Good Luck...look forward to seeing your code......
Click the Go Advanced Button.
Find and click the Manage Attachments button. (A new popup appears)
Click Add Files
Click Basic Uploader
Click Browse (and locate your zipped file)
Click Upload (Your file will appear in the bottom part of that screen)
Click Done
Then Submit your new post by clicking 'Submit Reply'

[Future_FireFighter]

Here it is. If you have issues let me know

[Nightwalker83]

I will have a look at it! BTW you should remove the executable from the attachment as we do not allow executable files to be uploaded. Also, when I open the project I receive a message that "MyCommandButton.ocx" could not be found.

Edit:

Also, do not use static locations such as

C:\Documents and Settings\_~FireMan~_\Desktop\VB6\WIP\PW Savior\Test.mdb;Persist Security Info=False

use code to retrieve the path dynamically. This allows the code to run on another machine without problems.

I managed to get all three test accounts to show up if that was the problem? You had the same password for each account so I changed them to individual passwords.

[SamOscarBrown]

FF. I see quite a bit of confusion in your code.

I made some mods
First, I added one more REFERENCE to your project:
MICROSOFT ACTIVEX DATA OBJECTS RECORDSET 2.8 LIBRARY

Next, I added this to the top of your main form:

Option Explicit  'ALWAYS USE THIS - prevents issues with declaration/misuse of variables/constants
'Dim db As Database  Commented out these three lines
'Dim rs As Recordset
'Dim ws As Workspace

Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

Next, I changed your frmPWSavior load event to be like this:

Private Sub Form_Load()
'frmPWSavior.Visible = True  (NOTICE: I reset this form's visible to TRUE in the IDE
'frmLogin.Visible = False

'Set ws = DBEngine.Workspaces(0)
'Set db = ws.OpenDatabase(App.Path & "\Test.mdb")
'Set rs = db.OpenRecordset("Accounts", dbOpenTable)

'Data1.Recordset.MoveFirst
'Do While Data1.Recordset.EOF = False
'lstAcc.AddItem Data1.Recordset.Fields(0).Value & ""
'Data1.Recordset.MoveNext
'Loop

dbConnect  'THIS IS A CALL TO THE DB  (See later...)

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "Select * from accounts"

Set rs = cmd.Execute
Do While Not rs.EOF  'Go through the Recordset and add the account names to your listbox
lstAcc.AddItem (rs!account)
rs.MoveNext
Loop
End Sub

This is the code I added to your main form in order to connect to the DB
(Notice the use of App.Path vice hardcoded locations)

Public Function dbConnect()
   Set cnn = New ADODB.Connection
      With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "User ID=Admin;password=;" & " Data Source=" & App.Path & "\test.mdb;"
       .CursorLocation = adUseClient
       .Open
      End With

End Function

Then, I changed the listbox double-click event to this:

Private Sub lstAcc_DblClick()
gAcctName = lstAcc.Text  'NOTICE....the only change I made is this one line here (gAcctName is a global variable set in a mod...later)
frmAccount.Show
'Set rs = db.OpenRecordset("Select * from Accounts where Account = '" & Trim(lstAcc.List(lstAcc.ListIndex)) & "'")
'rs.MoveFirst
    frmAccount.Caption = "Account                    By: _~Future_FireFighter~_ "
    frmAccount.framNew.Visible = False
    frmAccount.framAccount.Visible = True
    'frmAccount.Height = 1335
    'frmAccount.Width = 5630
End Sub

Then, in your frmAccount load event, I did this:

Private Sub Form_Load() 'THIS IS THE frmACCOUNT Load Event
'If frmPWSavior.cmdAdd.CausesValidation = True Then
'Me.Caption = "New Account                 By: _~Future_FireFighter~_"
'  framNew.Visible = True
' framAccount.Visible = False
'Me.Height = 1700
'Me.Width = 5630
'Else
'    Me.Caption = "Account                    By: _~Future_FireFighter~_ "
'    framNew.Visible = False
'    framAccount.Visible = True
'    Me.Height = 1335
'    Me.Width = 5630
'End If
'lblAccount.Caption = gAcctName  'I ADDED THIS to place the Account Name in your label...you can use the same type
                                                               'of process to return the pwd and/or ID
End Sub

Then I added a MODULE to your project and put the following in it:

Public gAcctName As String

There is LOTS more I could do to improve your program, but this is a start...and if you use my db call (dbconnect) vice ADODC's, I think you will experience much more flexibility.

[Future_FireFighter]

tyvm sam i'll get into it here in a bit. and night walker thanx for your info as well. could u explain how i go about using code to retrieve the path so i can send my codes to other machines. i've had that issue alot as i'm sure u can see. and never figured out how to change it. ty both for all ur help. and to answer ur question. yes nightwalker that was my issue.getting all the different accounts to show on the second form by dblclicking the account on the main form

[Nightwalker83]

A code example that may be used to retrieve the user of a computer can be found here.

[Future_FireFighter]

sorry for the confusion. i left alot of stuff in the code that i've been experimenting with. i should have cleaned it up b4 i sent it.

[Future_FireFighter]

Ty for all ur help. I got it to work great, Had to code it a bit different which i didn't want to. but it works great so i'm not complaining. but i'm having 1 last issue.
when i'm tryin to close out my program in certain parts, the database won't close out. the program just hides and i have to go to task manager to fully close it out.
here is the code where the issue keeps coming up

Dim response As Integer

If rs.RecordCount = 0 Then
    response = MsgBox("Are You Sure You Want To Exit Without Adding An Account?", vbQuestion + vbYesNo, "PW Savior")
        If response = vbYes Then
            db.Close
            End
        ElseIf response = vbNo Then
            frmPwSavior.Visible = True
            frmAccount.Visible = False
        End If
    Else
            frmPwSavior.Visible = True
            frmAccount.Visible = False
    End If
End Sub

everywhere else in the program it closes just fine (database and all)
but here in my message box vbyes function, when i click yes the program hides and i have to manually close it out. any suggestions?

[si_the_geek]

There are a couple of articles about this in our Classic VB FAQs (in the FAQ forum).

First off, there is Why is using the 'End' statement (or VB's "stop" button) a bad idea?

Then for an explanation (and example code) for how you should be closing a form/program, see the article How should I close my form/program/class?

[Future_FireFighter]

great thanx man. worked flawlessly. great tut. my login form was reloading for some reason after i closed the program. thanx again