Requery listbox problem

[CityLimit]

I have an Access2000 multi-column listbox whose RowSource is a query that I want to automatically update after the underlying table has been updated. The table data comes from a large polynomial calculation that typically generates upwards to 200 records (8 fields non-indexed). Before the calculation is run all records in the table are deleted with a DELETE.* query. Then the calculation is performed and the table is appended. All works well. Next a requery is issued for the listbox - Me!listbox.requery. If this is the first instance of the calculation (first time the form is opened) the requery works. However on subsequent calculations the requery doesn't update the listbox with the new records, the listbox simply displays the last recordset. If I push F9 the listbox will update but sometimes only after 2 or maybe 3 pushes. I've tried applying the RowSource programatically but no difference. The same problem exists if I use continuous subform or datasheet subform instead of the listbox except that if I place the cursor on a record in the subform after the programmatic requery the record changes to #Deleted. Then if I push F9 to requery, the subform is updated and all appears okay. I had this problem on a similar calculator before but worked around it by filling the listbox using the ADO GetString method however that only works with small recordsets because the listbox RowSource is limited to 2048 characters. Thanks to all for any info on this frustrating problem.

CityLimit

 

[FoxProProgrammer]

I'm not sure if this will help, but here is the process that I use to update list boxes when the source of its data changes.

Dim strQuery as String

strQuery = "SELECT...insert your query here and make sure that you concatenate single quotes around string values.

listBox.SetFocus
listBox.RowSource = strQuery
listBox.Requery

The problem may be that you aren't setting the focus to the control being updated.

 

[CityLimit]

Thanks for the suggestion but that didn't seem to help.
Any other ideas? Thanks, CityLimit

 

[FoxProProgrammer]

Have you run in debug mode to make sure that the code is executing when you think it is? In which event do you do the Requery? Can you post your specific code?

Thanks,

dz

 

[CityLimit]

Here are the three procedures associated with the problem. First the pushbutton OnClick then the DELETE records from table and finally the actual calculator. The requery is found in the OnClick procedure and is firing but the list always displays the last recordset until a manual (F9) requery is implemented. Sorry for the length of the code but thought that it best to share all. Thanks for your help.

CityLimit

Private Sub RunSysCalculator_Click()
DoCmd.Hourglass True

   intCalculatorSelect = 2  'identifies specific calculator

   Call ErasePolyResults  'deletes all previous records
                           from calculator results table
                           (tblsysPolyBattSelect)

   Call sysPolyCalculation  'performs calculation
   
   Me.sysSelectionList.SetFocus
   Me.sysSelectionList.RowSource = "qrysysPolyBattSelect"
   Me.sysSelectionList.Requery
   
DoCmd.Hourglass False

End Sub


Private Sub ErasePolyResults()
Dim i As Integer
Dim cmd As ADODB.Command
Dim strTable1 As String
Dim strTable2 As String

           'Clears (deletes) data in calculation results table
Set cmd = New ADODB.Command

If intCalculatorSelect = 1 Then
 strTable1 = "tblPolyResults"
 strTable2 = "tblPolyBattSelect"
Else
 strTable1 = "tblsysPolyResults"
 strTable2 = "tblsysPolyBattSelect"
End If

With cmd
   .ActiveConnection = cnnPoly
   .CommandType = adCmdText
   For i = 1 To 2
    If i = 1 Then
     .CommandText = _
     "DELETE * FROM " & strTable1 & ";"
    Else
     .CommandText = _
     "DELETE * FROM " & strTable2 & ";"
    End If
   .Execute  
   Next i
End With
 Set cmd = Nothing

intCalculatorSelect = 0

End Sub


Sub sysPolyCalculation()
Dim syspolyResult As Double    'Variable to hold algorithm calculation results
Dim sysopTemp As Double
Dim sysopEOD As Double
Dim ctbatt As Long          'Loop counter for active battery polynomial constants
Dim cttime As Double           'Loop counter for requested minute iterations
Dim ctpowerloop As Double  'loop counter for system parallel string iterations
Dim intMinTime As Double   'var for lower limit of discharge time range
Dim intMaxTime As Double   'var for upper limit of discharge time range
Dim arrindex As Long        'Applies Current or Power polynomial contants to algorithm
Dim sysintPolyLoop As Integer  'Loop counter for Current/Power calculations
Dim intpower As Double  'energy to power conversion

 sysopEOD = Me!SysEODVoltage.Value
 intMinTime = Me!SysMinTime  'sets lower limit of discharge time range
 intMaxTime = Me!SysMaxTime  'sets upper limit of discharge time range
 
            'Converts "optemp" to centigrade for polynomial calculation if U.S. Customary
If Me!UnitOfMeasure.Value = 1 Then  'units selected on
                                     Calculator screen
 sysopTemp = Me!SysTemperature.Value
ElseIf Me!UnitOfMeasure.Value = 2 Then
 sysopTemp = ((Me!SysTemperature.Value + 40) * 5 / 9) - 40
End If

 DoCmd.GoToControl "sysSelectionList"    'Moves focus from "Run System Calculator' pushbutton
 Me!RunSysCalculator.Enabled = False         'to allow disabling pushbutton
    
        'Establish connection to polynomial calculation results lookup table
 Set rstsysPolyResults = New ADODB.Recordset
  With rstsysPolyResults
   Set .ActiveConnection = cnnPoly
       .CursorType = adOpenKeyset
       .LockType = adLockOptimistic
       .Open "tblsysPolyResults"
  End With
           'Algorithm for calculating current and power
For ctbatt = 1 To recordcount  'One record for each active battery
 For cttime = intMinTime To intMaxTime  'requested discharge time
  For sysintPolyLoop = 1 To 2    'First pass is for current(first 12 polynomial constants)
   If sysintPolyLoop = 1 Then    'second pass is for power (second 12 polynomial constants)
    arrindex = 0
   Else
    arrindex = 12
   End If
  syspolyResult = (10 ^ (((0.0000028 * sysopTemp ^ 2 + 0.0008392 * sysopTemp - 0.0186795) _
    * ((Log(cttime)) / Log(10)) ^ 2) + ((-0.0000043 * sysopTemp ^ 2 - 0.0057009 _
    * sysopTemp + 0.1231002) * (Log(cttime) / Log(10))) + (-0.000056 * sysopTemp ^ 2 _
    + 0.013331 * sysopTemp - 0.262505))) * avarData(1, ctbatt - 1) _
    * 10 ^ ((avarData(2 + arrindex, ctbatt - 1) * sysopEOD ^ 2 _
    + avarData(3 + arrindex, ctbatt - 1) * sysopEOD + avarData(4 + arrindex, ctbatt - 1)) _
    * (Log(cttime / 60) / Log(10)) ^ 3 + (avarData(5 + arrindex, ctbatt - 1) _
    * sysopEOD ^ 2 + avarData(6 + arrindex, ctbatt - 1) * sysopEOD _
    + avarData(7 + arrindex, ctbatt - 1)) * (Log(cttime / 60) / Log(10)) ^ 2 _
    + (avarData(8 + arrindex, ctbatt - 1) * sysopEOD ^ 2 + avarData(9 + arrindex, ctbatt - 1) _
    * sysopEOD + avarData(10 + arrindex, ctbatt - 1)) * (Log(cttime / 60) / Log(10)) _
    + avarData(11 + arrindex, ctbatt - 1) * sysopEOD ^ 2 + avarData(12 + arrindex, ctbatt - 1) _
    * sysopEOD + avarData(13 + arrindex, ctbatt - 1))
  If sysintPolyLoop = 1 Then
   rstsysPolyResults.AddNew
   rstsysPolyResults!Battery = avarData(0, ctbatt - 1)
   rstsysPolyResults!Minutes = cttime
   rstsysPolyResults!Current = syspolyResult   'Algorithm results for Current
   rstsysPolyResults!Ah = syspolyResult * (cttime / 60) 'Algorithm results for amp-hours
  Else
   rstsysPolyResults!Energy = syspolyResult * Me![SysBattPerString]  'Algorithm results for Energy
   rstsysPolyResults!Power = (syspolyResult / (cttime / 60)) * Me![SysBattPerString] 'Algorithm results for Power
   intpower = ((syspolyResult / (cttime / 60)) * Me![SysBattPerString])
   For ctpowerloop = 1 To 25
    If intpower * ctpowerloop > Me![SysMaxPower] Then
     rstsysPolyResults!Power2 = (syspolyResult / (cttime / 60)) * Me![SysBattPerString] * ctpowerloop
     rstsysPolyResults!PwrStrings = ctpowerloop
     rstsysPolyResults!Current2 = rstsysPolyResults!Current * ctpowerloop
     rstsysPolyResults!Ah2 = rstsysPolyResults!Ah * ctpowerloop
     rstsysPolyResults!Energy2 = rstsysPolyResults!Energy * ctpowerloop
     Exit For
    End If
   Next ctpowerloop
   rstsysPolyResults.Update
  End If
  Next sysintPolyLoop
 Next cttime
Next ctbatt

rstsysPolyResults.Close
 Set rstsysPolyResults = Nothing

End Sub

 

[FoxProProgrammer]

That's a lot of code to go through, but I have the gist of it. Even though you have a lot of code, the basic idea is the same as applications where I have done similar things. You are filling a list box from a Query and want to show the new data when the RowSource changes.

Is sysSelectionList unbound or bound? It should be unbound.

Is anything in the RecordSource of the form? It should be empty.

Please post the SQL of the query named "qrysysPolyBattSelect" and I'll see if I see anything that might be causing the problem.

I'm just curious...is the variable "intCalculatorSelect" a global? Even though this isn't causing your problem, I don't see how you passed its value from RunSysCalculator_Click() to ErasePolyResults().

Also, you might add the following line of code after Me.sysSelectionList.Requery

Me.sysSelectionList.Value = Me.sysSelectionList.ItemData(0)

I have found that after requerying a combo box, it doesn't display anything until I assign the value to the first item in the list. I don't think that this will solve your issue, but it won't hurt unless you don't want the first item to be selected when the list initializes.

dz

 

[FoxProProgrammer]

One more question...are the button (RunSysCalculator) and list box (sysSelectionList) on the same form? I didn't want to assume anything.

 

[CityLimit]

Thanks DZ, I hope that's not too personal.

The listbox and the pushbutton are on the same form. The form and listbox are unbound. The listbox's RowSource is blank. intCalculatorSelect is Public. I tried Me.sysSelectionList.Value = Me.sysSelectionList.ItemData(0) snd although it selects the first row it does not as you said solve the problem. As you can see in the SQL below there is a reference to the form. Removing this reference does not impact the problem. Thanks again for your help.

CityLimit

Here is the SQL:

SELECT tblsysPolyResults.Battery, tblsysPolyResults.Minutes, [Forms]![frm_Calculator_Ver01]![SysBattPerString] & " x " & [PwrStrings] AS PwrStr, Round([Power2]/1000,1) AS Pwr2, Round([Energy2]/1000,1) AS Ener2, Round([Current2],1) AS Curr2, Round([Ah2],1) AS Ah22
FROM tblsysPolyResults
ORDER BY tblsysPolyResults.Minutes, Round([Power2]/1000,1)
WITH OWNERACCESS OPTION;

 

[CityLimit]

dz
I've found that if I induce a little bit of delay (3.5 seconds) the listbox will fill every time. Some kind of racing problem but I have no idea why.

citylimit