runtime error 3021

[MichHart]

Hi everyone

I have a series of sql statements that are being executed when a name is selected from a list. These sql statements take the field from the record and apply it to the list in the combo box that the statement is being written on.

I hope that makes sense.

The program executes the majority of the statements, but it gives me a runtime 3021 error on these statements, which are identical to the others.


Set rs = db.Execute("SELECT Teams.[Index], Teams.[LongName], Players.[ColCommit], Players.[Index] " & " FROM Teams, Players WHERE Players.[Index] IN (SELECT Players.[Index] FROM Players WHERE Players.[FirstName] = " & strFN & " AND Players.[LastName] = " & strLN & &quot AND Players.[ColCommit] = Teams.[Index]&quot
cboColCom.Text = rs.Fields("LongName&quot.Value & vbNullString
rs.Close
Set rs = Nothing

Set rs = db.Execute("SELECT CollegeYears.[Index], CollegeYears.[Long], Players.[Index], Players.[ColYear] FROM CollegeYears, Players WHERE Players.[Index] IN (SELECT Players.[Index] FROM Players WHERE Players.[FirstName] = " & strFN & " AND Players.[LastName] = " & strLN & &quot AND Players.[ColYear] = CollegeYears.[Index]&quot
cboYear.Text = rs.Fields("Long&quot.Value & vbNullString
rs.Close
Set rs = Nothing

Any problems that someone can pick out of this please tell me. I don't understand why it works for some combo boxes but not others.

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

 

[zemp]

Error 3021, No current record, means that your SQL statement is not returning any records. Check to see exactly what your SQL contains when it fails.

On the statrements above I would make sure that string variables/parameters are enclosed with single quotes. In the first statement I would remove the extra ampersand as well.

Set rs = db.Execute("SELECT Teams.[Index], Teams.[LongName], Players.[ColCommit], Players.[Index] FROM Teams, Players WHERE Players.[Index] IN (SELECT Players.[Index] FROM Players WHERE Players.[FirstName] = '" & strFN & "' AND Players.[LastName] = '" & strLN & "') AND Players.[ColCommit] = Teams.[Index]&quot

Above I tremoved the extra ampersand and added single quotes. Give that a try.
Thanks and Good Luck!

zemp

 

[MichHart]

Hi zemp:

Actually I got a syntax error with your above suggestion, but I have discovered that it only happens with the field in the table is empty. I had thought that the vbnullstring would cover that.

Any suggestions is greatly appreciated.

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