Trouble with List Boxes

[LucyL]

Hi,
I have a combo box setup which is filled on form load with the names of all the renters in the database, the code for which is as follows...

Dim connection As ADODB.connection
Set connection = New ADODB.connection

connection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = h:\NewDatabase.mdb&quot

Set rs = New ADODB.Recordset
rs.Open "select * from Renter WHERE active = 'ACTIVE'", adoconnection, adOpenDynamic, adLockOptimistic
'Populates combos with database fields
While Not rs.EOF
cboRenter.AddItem rs("Name&quot
rs.MoveNext
Wend

'Close connection and the recordset
rs.Close
Set rs = Nothing

On form load a error occurs, even though the details are stilled filled into the combo box...
"Syntax error in FROM clause".

I think it may have something to do with the action of the on click event of the combo box. When a user selects a particular renter name from the combo box, all previous dates that this renter has paid rent are, or should be filled into the list box. These details are drawn from the same table. The code for filling the listbox is as follows, where cboRenter is the name of the combo box and newado is the name of the ado setup. Also I cannot set the RecordSource of the newado as again this error appears,
"Syntax Error in From clause"
and I cannot set the DataField of the combo box since this error appears again.

Private Sub cboRenter_Click()
ListDates.Clear
Dim adoconnection As ADODB.connection
Set adoconnection = New ADODB.connection

adoconnection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = H:\NewDatabase.mdb&quot

Set rs = New ADODB.Recordset

Dim Renter As String
Dim intFound As Integer


Let Renter = cboRenter.Text
If Len(Renter) > 0 Then
newado.Refresh
newado.Recordset.MoveFirst
Let intFound = 0

Do While Not newado.Recordset.EOF And intFound < newado.Recordset.RecordCount
If (newado.Recordset.Fields(&quot;Name&quot.Value) = Renter Then

ListDates.AddItem (newado.Recordset.Fields(&quot;Letting_Start&quot.Value)
ListDates.ItemData(ListDates.ListCount - 1) = newado.Recordset.Fields(&quot;Renter_No&quot
intFound = intFound + 1
newado.Recordset.MoveNext

Else

newado.Recordset.MoveNext
End If
Loop

Else
MsgBox &quot; Select Renter name First!&quot;
End If
End Sub

Hope this isn't too confusing!
Any ideas?
Thanx!

 

[Foada]

Try this code in your Form Load Event and see if the error is gone for that event.

Dim connection As New ADODB.connection
Dim rs As New ADODB.Recordset
Dim str As String

str = &quot;Provider=Microsoft.jet.oledb.4.0;&quot; & &quot;Data Source = h:\NewDatabase.mdb&quot;
connection.ConnectionString = str
connection.Mode = adModeReadWrite
connection.Open
If rs.State = adStateOpen Then
rs.Close
End If
With rs
.ActiveConnection = connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open &quot;Select * From Renter Where Active = 'Active'&quot;
End With
'Populates combos with database fields
While Not rs.EOF
cboRenter.AddItem rs(&quot;Name&quot
rs.MoveNext
Wend

'Close connection and the recordset
rs.Close
Set rs = Nothing


If this corrects the first problem we can continue on with the second error. Anything is possible, the problem is I only have one lifetime.

 

[StewartGW]

Unless there is some specific reason you are using an ADO component I suggest the following that will do the same thing:

'Declaration

Private cn As ADODB.Connection
Private rs as ADODB.Recordset
Private strSQL as String
Private Const CONNECTION_STRING = _
&quot;Provider=Microsoft.jet.oledb.4.0; Data Source = &quot; _
&quot; h:\NewDatabase.mdb&quot

'*** Form_Load event ***
'Connection object created once
Set cn = New ADODB.Connection

cn.Open(CONNECTION_STRING)

'Only get the data you need
strSQL = &quot;SELECT name FROM renter WHERE active = 'ACTIVE'&quot;

'Defaults to a Forward only read-only cursor
Set rs = cn.Execute(strSQL)

'Populates combos with database fields
Do While Not rs.EOF
cboRenter.AddItem rs(&quot;Name&quot
rs.MoveNext
Loop

'Close connection and the recordset
rs.Close
Set rs = Nothing
'@ End Form_Load

'cboRenter_Click Event
strSQL = &quot;SELECT letting_start, renter_no FROM renter &quot; _
&quot;WHERE name = '& Trim(cboRenter.Text) & &quot;'&quot;
Set rs = cn.Execute(strSQL)
ListDates.Clear
Do While Not rs.EOF
ListDates.AddItem rs(&quot;Letting_Start&quot
ListDates.ItemData(ListDates.NewIndex) = rs(&quot;Renter_No&quot
rs.MoveNext
Loop

'*** Form Unload Event ***
Set cn = Nothing