Good Morning everyone: I am crea 1

[MichHart]

Good Morning everyone:

I am creating a program that as the form loads displays a list of approximately 56,000 names. From this list the user will click on a name. As this is done all the informaiton from several tables will appear on the form. The form consists of a Tab Set of 8 tabs. There are a variety of textboxes, combo boxes, list boxes, etc.

What the problem is: As I click on the name and try to grab information for each of the fields on the form, it gives me this error: "VB6.exe has generated errors and will be closed by Windows. You will need to restart the program."

The error only occurs when I click on a name from the list.

I have never seen this error before, can anyone explain to me what exactly this means, and why?

Here is the code that is associated with the name click event.

Private Sub lstPlayers_Click()

Rem as player is selected, display all info from appropriate record in controls on the form

Set db = New Connection

db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Common.mdb"


Set rs = New Recordset

Dim strSQL As Variant

Dim spacepos As Integer

spacepos = InStr(lstPlayers.Text, " &quot

Set rs = db.Execute(("select * from Players where " & "FirstName = " & "'" & Left(lstPlayers, spacepos - 1) & "'" & " and " & "LastName = " & "'" & Right(lstPlayers.Text, Len(lstPlayers.Text) - spacepos) & "'&quot, db, adCmdText)

txtLstNme.Text = rs.Fields("LastName&quot & vbNullString
txtFrstNme.Text = rs.Fields("FirstName&quot & vbNullString
txtCommon.Text = rs.Fields("CommonName&quot & vbNullString
txtMiddle.Text = rs.Fields("MiddleName&quot & vbNullString
txtPhonetic.Text = rs.Fields("Phonetic&quot & vbNullString
txtHomeTown.Text = rs.Fields("HomeTown&quot & vbNullString
txtBirthPlace.Text = rs.Fields("BirthPlace&quot & vbNullString
txtColNotes.Text = rs.Fields("Notes&quot & vbNullString
txtJersey.Text = rs.Fields("Jersey&quot & vbNullString
txtHeight.Text = rs.Fields("Height&quot & vbNullString
txtWeight.Text = rs.Fields("Weight&quot & vbNullString
cboShot.Text = rs.Fields("Shot&quot & vbNullString

If rs.Fields("InActive&quot.Value = 0 Then
Let chkActive.Value = False
End If

If rs.Fields("IsPro&quot.Value = 0 Then
Let optAmt.Value = True
Else
Let optProf.Value = True
End If

If rs.Fields("European&quot.Value = 0 Then
Let optAmer.Value = True
Else
Let optEuropean.Value = True
End If
Set rs = db.Execute("SELECT Leagues.[Index], Leagues.[ShortName] FROM Leagues WHERE Leagues.[Index] IN SELECT Teams.[Index], Teams.[LongName], Players.[CurTeam] " & " FROM Teams, Players WHERE Players.[CurTeam] = Teams.Index&quot
cboLeague.Text = rs.Fields("ShortName&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Positions.[Index], Positions.[Long], Players.[Position] " & " FROM Positions, Players WHERE Players.[Position] = Positions.Index&quot, db, adCmdText)
cboPosition.Text = rs.Fields("Long&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT DraftCodes.[Index], DraftCodes.[Description], Players.[DraftStatus] " & " FROM DraftCodes, Players WHERE Players.[DraftStatus] = DraftCodes.Index&quot, db, adCmdText)
cboStatus.Text = rs.Fields("Description&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Countries.[Index], Countries.[Long], Players.[SCountry]" & " FROM Countries, Players WHERE Players.[SCountry]= Countries.Index&quot, db, adCmdText)
cboNation.Text = rs.Fields("Long&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Contacts.[Index], Contacts.[LastName], Contacts.[FirstName], Players.[Agent] " & " FROM Contacts, Players WHERE Players.[Agent] = Contacts.Index&quot, db, adCmdText)
cboAgent.Text = rs.Fields("FirstName&quot & " , " & rs.Fields("LastName&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Teams.[Index], Teams.[LongName], Players.[CurTeam] " & " FROM Teams, Players WHERE Players.[CurTeam] = Teams.Index&quot, db, adCmdText)
cboCurTeam.Text = rs.Fields("LongName&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT NHLResTypes.[Index], NHLResTypes.[Long], Players.[NHLResType] " & " FROM NHLResTypes, Players WHERE Players.[NHLResType] = NHLResTypes.Index&quot, db, adCmdText)
cboType.Text = rs.Fields("Long&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Teams.[Index], Teams.[LongName], Players.[NHLResTeam] " & " FROM Teams, Players WHERE Players.[NHLResTeam] = Teams.Index&quot, db, adCmdText)
cboTeam.Text = rs.Fields("LongName&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT Teams.[Index], Teams.[LongName], Players.[ColCommit] " & " FROM Teams, Players WHERE Players.[ColCommit] = Teams.Index&quot, db, adCmdText)
cboColCom.Text = rs.Fields("LongName&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT CollegeYears.[Index], CollegeYears.[Long], Players.[ColYear] " & " FROM CollegeYears, Players WHERE Players.[ColYear] = CollegeYears.Index&quot, db, adCmdText)
cboYear.Text = rs.Fields("Long&quot & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute(("SELECT DraftDetails.[Round],DraftDetails.[Team], DraftDetails.[Overall], DraftDetails[Player], Players.[Index],Teams.[Index],Teams[LongName]" & " FROM DraftDetails, Players, Teams WHERE DraftDetails.[Player] = Players.Index&quot, db, adCmdText)
lstDraft.AddItem rs.Fields("LongName&quot
txtRound.Text = rs.Fields("Round&quot & vbNullString
txtOverall.Text = rs.Fields("Overall&quot & vbNullString
rs.Close
Set rs = Nothing



'rs.Close
'Set rs = Nothing



End Sub


Thanks for any help!!

MichHart
" Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[MichHart]

Hi, well this is what the debug gave me

Last Name: 'Hart'
First Name: 'Michelle'
SQL: select * from Players where FirstName = 'Michelle' and LastName = 'Hart'

Then when it moved to line: rs.movefirst the program kicked me out.

Sorry to be a nag, but I have to figure this out.

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[mattKnight]

Ok, It is probably isn't returning records!

looking through the code again
can you explain

Set rs = db.Execute(strSQL, db, adCmdText)

why the 2nd parameter is the db connection variable. MSDN shows that as being the recordsaffected.
so try

Set rs = db.Execute(strSQL,, adCmdText)

or substitute a long variable.

Can you confirm from access that the string "Michelle" and "hart" woudl return records.

Take care

Matt

 

[MichHart]

Ok thanks Matt for all your help.

I will take a look at all these suggestions today.

Thanks again for all the help.

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[MichHart]

Matt I hope you are still viewing this thread.

I have a question that concerns all the recordsets that I am setting up..

Should I be closing each one and setting rs to nothing each time or should I do it after all are completed.??

Or is there a better way to do it.

The program is now booting me when it starts to processess those recordsets. Am I asking the program to do too much??

Is the procedure too complicated??

Thanks again for all your input, I greatly appreciate it.

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[mattKnight]

Mich,

Ok
>I have a question that concerns all the recordsets that I am setting up..

Does this mean that the code is now reaching the section of

Set rs = db.Execute("SELECT Leagues.[Index], Leagues.[ShortName] FROM Leagues WHERE Leagues.[Index] IN SELECT Teams.[Index], Teams.[LongName], Players.[CurTeam] " & " FROM Teams, Players WHERE Players.[CurTeam] = Teams.Index")
    cboLeague.Text = rs.Fields("ShortName") & vbNullString
    rs.Close
    Set rs = Nothing

This is a good start...

In general and MHO, the way you are closing and setting the SR to nothing is correct.

HOWEVER I have serious misgivings about the style of SQL join. How 'normalised' are the data tables. The query above seems to me to be really inefficient. Is there a relationship between the Player table and the teams table.

maybe using a join is a better way?

Matt

 

[MichHart]

Hi everyone:

Matt I have figured out the problem. In this line of each of my recordsets

Set rs = db.Execute(("SELECT Teams.[Index], Teams.[LongName], Players.[ColCommit] " & " FROM Teams, Players WHERE Players.[ColCommit] = Teams.Index&quot, db, adCmdText)

I had to remove the db from each of the recordsets. Just like I had to do with the previous suggestion from you.

Thanks so much for your help.

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[mattKnight]

Mich,

cboCurTeam.Text = rs.Fields("LongName") & vbNullString

really ought to be

cboCurTeam.Text = rs.Fields("LongName").value & vbNullString

MS suggest that default properties should no longer be used.
This is discussed in thread222-448036

Matt

 

[MichHart]

Thanks Matt, I will make that adjustment.

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."