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."

 

[SonOfEmidec1100]

I vaguely recall a similar problem I had several years ago and it would probably have been vb4. An error ocurred when the code in the click event of the list box took a 'long' time to execute. I got round it by just setting a flag in the click event and checking for it within a timer cycle which actually ran the code then cleared the flag. Crude but it worked.

cjw

 

[strongm]

Do you really, really think that an interface that requires you to select from 56000 entries is a good design?

 

[MichHart]

I know, but it is what is required, so I have to deal with it!!

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

 

[THOMASNG]

Non-descriptive subject lines cause lots of people
to just skip reading the message!

 

[MichHart]

Yes I realized that after the message had been posted. I usually put a subject line in specifically.

Sorry!

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

 

[CajunCenturion]

First thing I would recommend is that you check that the value of spacepos is not zero, but that would cause different problems.

If you step thru the code, line by line, after which line does VB shutdown?
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

Ouch!

 

[MichHart]

Ok, I will try that

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

 

[MichHart]

I apologize, I am sort of new to this... I am looking for some support and suggestions. Perhaps a better way to do what I need to do??

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

 

[MichHart]

Ok I went through the program step for step and the point at which I am kicked out is here:

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)


Any suggestions would be greatly appreciated.

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

 

[MichHart]

Hi again!!

spacepos = InStr(lstPlayers.Text, " &quot'(The spacepos is 8)'

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)

This line shows the correct name and data is correct.
But the next line when i mouse over it, it shows " Item cannot be found in the collection corresponding....."


txtLstNme.Text = rs.Fields("LastName&quot & vbNullString

Can someone please help me with this.
Any suggestions please!

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

 

[mattKnight]

Is LastName a valid field name in the DB...

 

[MichHart]

Yes I have checked all the fields names for correct spelling

I am stumped here!

I have walked through the program three times now and it keeps booting me out as soon as it tries to execute the line in my above message.

I know its something to do with the SQL statement or the spacepos statement.

I just don't know enough about it to just pick out the problem.

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

 

[mattKnight]

Mich

Try these and see if we can get a result...

dim strFN as string, strLN as string
dim strSQL as string

strLN = "'" & Right(lstPlayers.Text, Len(lstPlayers.Text) - spacepos) & "'"

strFN = "'" & Left(lstPlayers, spacepos - 1) & "'"

strSQL ="select * from Players" & _ 
" where FirstName = " & strFN & _
" and LastName = " & strLN

debug.print "Last Name: "; strLN
debug.print "First Name: "; strFN
debug.print "SQL: "; strSQL

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

rs.movefirst
do while not .eof
 debug.print rs.Fields("LastName").value
 debug.print rs.Fields("FirstName").value
 rs.movenext
loop
debug.print
'then rest of code

I suspect that your query is returning no records, this will confirm whether this is true or not!

Take Care

 

[MichHart]

Hi Matt:

Ok I tried that, and it did nothing but boot me out again!

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

 

[MichHart]

I am going to step into it again and go through it line for line

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

 

[mattKnight]

>Ok I tried that, and it did nothing but boot me out again!

I am sure it did!

Please post the results of the debug.print statements

Please also indicate where it failed

debug.print rs.Fields("LastName").value

I guess

Then try replacing

strLN = "'" & Right(lstPlayers.Text, Len(lstPlayers.Text) - spacepos) & "'"

strFN = "'" & Left(lstPlayers, spacepos - 1) & "'"

with

strLN = &quot;'<SomelastName>'&quot;

strFN = &quot;'<SomeFirstName>'&quot;

where the <SomeFirstName> and <SomeLastName> are valid criteria. Then run it

If you get the SQL statement cut and paste it to the SQL Query Analyser.



Take care


Matt

 

[MichHart]

ok i stepped through it, and it is returning a value. I am getting a name.

I will run it again and see what happens.

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

 

[cl8855]

I know this isn't really helpful, but I just have to say as someone else pointed out - this really looks like a job for a web application instead of VB.