List box problem

[aziz rasul]

I have a list box on a tabbed control. On pressing a command button on the form and making the tab control visible, the following code is implemented

I tried placing

Me!List72.Requery

before and after the SQL statement. No affect. I then placed the same requery code elsewhere. Still no luck. Here's the whole part of the code: -

Me!TabCtl52.Visible = True

For index = 0 To tdfcount - 1
If dbs.TableDefs(index).Name = "tblMissing GL Codes Retail" Then
Set rst4 = dbs.OpenRecordset("tblMissing GL Codes Retail")
Me!List72.Requery
strSQL = "SELECT [tblMissing GL Codes Retail].OUC, [tblMissing GL Codes Retail].[GL Code], "
strSQL = strSQL & "[tblMissing GL Codes Retail].Year, [tblMissing GL Codes Retail].Period FROM "
strSQL = strSQL & "[tblMissing GL Codes Retail] ORDER BY [tblMissing GL Codes Retail].Year, [tblMissing GL Codes Retail].Period;"
Me!List72.Requery
Me!List72.RowSource = strSQL
Me!List72.Requery
Me!TabCtl52.Pages![Missing GL Codes].Caption = "Missing GL Codes"
Me!List72.Requery
Exit For
Else
Me!List72.RowSource = ""
End If
Next index

If I place a breakpointwithin the code, I am able to see all the records in the list box. However if I don't halt the code, I don't see ALL the records appearing in the list box.

Can anyone explain why this happens?

 

[AlanS]

I'm not sure what you're trying to do here. You're opening a recordset and then not using it, and you are repeatedly requerying the same listbox. Why not simply establish the values you need for the SQL statement, then set the listbox RowSource property ONCE, then requery it ONCE? You don't need to loop through all the tabledefs looking for the one you want - simply refer to it directly in the SQL statement. The following code should be all you need:

strSQL = "SELECT [tblMissing GL Codes Retail].OUC, [tblMissing GL Codes Retail].[GL Code], "
strSQL = strSQL & "[tblMissing GL Codes Retail].Year, [tblMissing GL Codes Retail].Period FROM "
strSQL = strSQL & "[tblMissing GL Codes Retail] ORDER BY [tblMissing GL Codes Retail].Year, [tblMissing GL Codes Retail].Period;"
Me!List72.RowSource = strSQL
Me!List72.Requery

 

[aziz rasul]

The listbox rowsource is being set once IF the table exists in the tabledefs collection, otherwise it is set to "". Hence the recordset IS being used if the table exists.

Assuming that the table exists, the SQL statement comes into play.

I initially tried the code you have given by rowsourcing it once and requerying it once, but it didn't work. Hence my play around by requerying multiple times in the hope that it would make it work. It didn't, hence the reason for the original post.

I'm still playing around with the code to see exactly what is causing the problem. If I isolate the code in a different db, there is no problem, it works. Hence I think the problem may be being caused by something else.

If I still have a problem, I'll re-post.