Combobox

Please tell me what is wrong with this code.
I am getting a "Subscript out of range error"

Private Sub DBCombo3_Click(Area As Integer)

Dim db As Database
Dim rs As Recordset
Dim SQLString As String
Dim NameArray() As String
Dim x As Integer

On Error GoTo Error1:

Set db = dtaemployee.Database ' openDatabase '("Your Microsoft Access Database ")
SQLString = "SELECT DISTINCTROW Surname, Forname From Employee"

'This is a command that queries the database for the employee ID, Last Name and First Name

Set rs = db.OpenRecordset(SQLString)

'If the record does not exist then it tells me
If rs.BOF = True And rs.EOF = False Then
MsgBox ("Cannot find customer!")
Exit Sub
rs.Close
db.Close
Else
x = 1
For x = 1 To rs.RecordCount
NameArray(x) = rs.Fields("Surname") & "," & rs.Fields("Forname")
'Places the information into array
x = x + 1
Next x

DBCombo3.Text = NameArray(x)
rs.Close 'Close the Recordset
db.Close
End If
Exit Sub
Error1:
MsgBox Err.Description, vbCritical, Err.Number
End Sub

Thank you
Tony
[email protected]

[Mark Sreeves]

your NameArray() needs to be redimed

you also need to do a rs.movelast before doing a rs.RecordCount


------------------
Mark Sreeves
Analyst Programmer

[email protected]
A BMW Group Company


[This message has been edited by Mark Sreeves (edited 01-11-2000).]

[Mark Sreeves]

this might help:

Set rs = db.OpenRecordset(SQLString)

'If the record does not exist then it tells me
If rs.EOF Then
  MsgBox ("Cannot find customer!")

Else
  x = 0
  With rs
    While Not .EOF
      ReDim Preserve NameArray(x + 1)
      NameArray(x) = !Surname & "," & !Forname
      x = x + 1
    Wend
  End With
End If

------------------
Mark Sreeves
Analyst Programmer

[email protected]
A BMW Group Company

[netSurfer]

Plus you have x defined as your loop trigger and you are manually adding 1 to it.

For x = 1 To rs.RecordCount
NameArray(x) = rs.Fields("Surname") & "," & rs.Fields("Forname")
'Places the information into array
x = x + 1
Next x

first time x = 1, second time x = 3 because you add 1 to it and then go next which makes it 3.

And like it was mentioned, you are using RecordCount but in order to get the true record count you have to MoveLast and set it in a variable. As it happens, RecordCount = 1 (at most) so when you go X = x+1, 2 is out of range.

Also:

If rs.BOF = True And rs.EOF = False Then
MsgBox ("Cannot find customer!")
Exit Sub
rs.Close <---
db.Close <---

You won't get to these 2 lines, put Exit Sub after them. VB should throw an error at this point, it always has to me if I do it.

[This message has been edited by netSurfer (edited 01-11-2000).]