Solved List Box is limited to add values (1 Viewer)

[indras111]

Hi Everyone,

I have a listbox in a form which is supposed to be populated with rows from a table. However, the listbox is not being added values for more than 309 rows. I don't know where i am doing wrong here. Below is the code. Can you please some one help me?
Call connec
counter = 1
If IsNull(Rep_PID) = True Or Rep_PID = "" Then
str_sql = "SELECT M.*, (select count(*) from MasterFileAudit where M.[Updated Date]>=[Updated Date] and M.PID=PID) AS row_num FROM MasterFileAudit AS M ORDER BY M.PID, M.[Updated Date];"

If rs1.State = 1 Then rs1.Close
If rs2.State = 1 Then rs2.Close
rs1.Open (str_sql), con, adOpenDynamic, adLockReadOnly
rs2.Open (str_sql), con, adOpenDynamic, adLockReadOnly

Do While rs1.EOF = False
row_count = rs1![row_num]
my_pid = rs1![PID]
fld_count = rs1.Fields.Count
If row_count = 1 Then
Lst_Audit_Rep.AddItem rs1![PID] & ";'" & rs1![Employee Full Name] & "';" & rs1![Updated Date] & ";" & rs1![Updated by] & ";" & rs1![uReason]

Else
rs2.Filter = "PID=" & my_pid & " and row_num=" & row_count - 1

For fld = 5 To fld_count - 2
If rs1(fld) <> rs2(fld) Then
'rs2.Filter = "row_num=" & row_count - 1
myvalue = rs2![Date EE Inserted]
Lst_Audit_Rep.AddItem rs1![PID] & ";'" & rs1![Employee Full Name] & "';" & rs1![Updated Date] & ";'" & rs1![Updated by] & "';" & rs1![uReason] & ";'" & rs1.Fields(fld).Name & "';'" & rs2(fld) & "';'" & rs1(fld) & "';"
End If
Next fld
End If
rs1.MoveNext
counter = counter + 1
Loop
a = Lst_Audit_Rep.ListCount

 

[theDBguy]

Hi. Sounds like you're using a Value List. If so, there is a limit in the number of characters you can put in the Row Source.

 

[indras111]

Hi. Sounds like you're using a Value List. If so, there is a limit in the number of characters you can put in the Row Source.

Hi

Yes I am using Value List. How do i fix this issue? I can't change this to Table/Query because i have lot of lookups and multiple criteria in the code. You can see the code.

 

[theDBguy]

Hi

Yes I am using Value List. How do i fix this issue? I can't change this to Table/Query because i have lot of lookups and multiple criteria in the code. You can see the code.

If you want all the data, you'll have to use Table/Query. Can you use a temporary table? You can use your code to populate the temp table and then use it for your Listbox. Just a thought...

 

[indras111]

If you want all the data, you'll have to use Table/Query. Can you use a temporary table? You can use your code to populate the temp table and then use it for your Listbox. Just a thought...

Thank you. I will use this option instead. Really appreciate your quick response.

 

[theDBguy]

Thank you. I will use this option instead. Really appreciate your quick response.

Hi. You're welcome. Good luck with your project.