Determining data to display in a listbox 2

[DeepBlerg]

Hi,

This is a bit tricky, but I'll try to explain my problem:

I have 2 DataCombo boxes which display "date from" and "date to". The list box I'm using (not DataList because I don't think it has the Style property, correct me if I'm wrong ) needs to display records from a table in a mdb depending on the date range selected in the 2 DataCombo boxes after an OK button is pressed.

The reason I need the Style property is so users can tick certain records in the list box.

So if anyone could help me out that would be great.

Thanks.......

 

[MatsHulten]

I'm afraid that I cannot provide you with the exact code just now... But this may at least get you started.

Construct your SQL-query so that you get the data you want and then a WHERE statement like this one: &quot;WHERE (((user_.LastLogin)<#3/1/2001# and (user_.LastLogin) >#1/1/2001#))&quot; (Assuming user_.lastlogin has DateTime as datatype.)

Fill a recordset with the result of the query and loop though it, filling your listbox as you go.

Good Luck
-Mats Hulten

 

[DeepBlerg]

Hi Mats,

Thanks for your help on this. I don't really unserstand why the statement would say .LastLogin etc etc. I'm a bit new to this
But basically, this form will show records in a listbox depending on the two options selected in the data combo boxes.

 

[MatsHulten]

Lastlogin is simply the field in the table I used for the example (The syntax goes like: table.field).

==================================================================

Dim strSQL As String
Dim recordset As New ADODB.Recordset
Dim connection As New ADODB.Connection

connection.Open connectionstring 'Use a DSN or write the connectionstring here manually.

strSQL = "SELECT field1, field2, field3, datefield" & _
         "FROM table1" & _
         "WHERE (datefield > #" & cboStartDate & "#) " & _
           &quot;AND (datefield < #&quot; & cboStopDate  & &quot;#)&quot;

Set recordset = connection.Execute(strSQL)

Do While Not recordset.EOF
   list.AddItem(recordset(&quot;field1&quot;))
Loop

A bit quick and dirty, but it would be something to build on. Don't forget to put a referense to ADO.

Good Luck!
-Mats Hulten

 

[DeepBlerg]

Hi again Mats,

That code you gave me was very helpful and I implemented it into my program:

Private Sub cmdOK_Click()

strSQL = &quot;SELECT Period, Date, Description&quot; & _
&quot;FROM log_combined&quot; & _
&quot;WHERE (Date > #&quot; & cbofrom & &quot;#) &quot; & _
&quot;AND (Date < #&quot; & cboto & &quot;#)&quot;

Set rsRecordSet = connConnection.Execute(strSQL)

Do While Not rsRecordSet.EOF
lstcomb.AddItem (rsRecordSet(&quot;Period&quot)
Loop
End Sub

The only thing is when i select the dates and press OK, I get this error message:

Run-time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression 'DescriptionFROMlog_combined etc etc etc


Does this bit: &quot;WHERE (Date > #&quot; & cbofrom & &quot;#) &quot; & _
&quot;AND (Date < #&quot; & cboto & &quot;#)&quot;
check for records between cbofrom and cboto or find records depending on their alphanumerical order?

Thanks for all your help.

 

[MatsHulten]

If you do a debug.print of your strSQL just before you send it in to the DB I belive you will find that it prints ...DescriptionFROM ...
This is of course invalid and easily corrected by adding a single space (' ') to the string after the Description.
(Like this: strSQL = &quot;SELECT Period, Date, Description &quot; & _ )

-Mats

 

[DeepBlerg]

One more quick question.......
I changed it and it works now, although the listbox overloads with blank records and I have to Crtl+Alt+Del to stop it

Does this have something to do with the:
Do While Not rsRecordSet.EOF?

 

[MatsHulten]

The Do-While statement loops through each record in the recordset. Thus if your query returned 50 records, it will iterate 50 times.
If you get a number of empty records this probably depends on that you have empty records in your database.

Take the strSQL you printed with the debug.print statement and open a new query within Access, and paste it in there. Then run the question.
The result is the records returned to your recordset. You should be able to see if you get a number of empty records there. If not, there is something wrong with your code.

If you find that you get a number of empty records and don't want them, you should either modify your query (suggest you play around with Access and then cut n' paste the query to your code), or else try programattically if the field is empty.

While...
   If Trim(rsRecordSet(&quot;Period&quot;)) <> &quot;&quot; Then
       lstcomb.AddItem (rsRecordSet(&quot;Period&quot;))
   End If
Loop

-Mats

 

[DeepBlerg]

I tried that latest code you gave me but it still overflows the list box and freezes the computer. Is it because my tables in the mdb have no primary key?

 

[MatsHulten]

When does the computer freezes? Before or during you populate the listbox?

Can you post your code and I will have a look on it.

-M

 

[DeepBlerg]

The computer freezes when the listbox scrollbar becomes tiny because it overflows.

Here's the code attached to the OK button:
Private Sub cmdOK_Click()

strSQL = &quot;SELECT Period, Date, Description &quot; & _
&quot;FROM log_combined &quot; & _
&quot;WHERE (Date > #&quot; & cbofrom & &quot;#) &quot; & _
&quot;AND (Date < #&quot; & cboto & &quot;#)&quot;

Set rsRecordSet = connConnection.Execute(strSQL)

Do While Not rsRecordSet.EOF
If Trim(rsRecordSet(&quot;Period&quot) <> &quot;&quot; Then
lstcomb.AddItem (rsRecordSet(&quot;Period&quot)
End If
Loop
End Sub

Thanks

 

[Themuppeteer]

didn't you forget the
rsRecordSet.MoveNext
in you loop ?
theMuppeteer.

 

[MatsHulten]

Oooups... My fault... Sorry!
TheMuppeteer is absolutely right, it goes just before the Loop-statement.

-M

 

[DeepBlerg]

Ah good thats better! I put reRecordSet.Movenext after End If and before Loop.

It shows only the first coloumn from my database being &quot;Period&quot;, how bout if I want to show, say, 5 coloumns similar to a datagrid display?

 

[MatsHulten]

Well perhaps you want to try the datagrid...

Otherwise you can concatenate the string you send to lstcomb with the & operator.

.additem(rs(&quot;field1&quot & &quot;, &quot; & rs(&quot;field2&quot)

-M