Unable to requery a listbox

Albilly · Nov 27, 2015

Hi, I am trying to figure this one out
I need to requery a list box after the rowsource has been set to its original SQL but everytime I run my code, nothing is hapenning, this is the code I currently use
If Not Me.cboMois.Value = "" Then
'restore to default data in table paramatre_conformite
Me.cboMois.Value = ""

Me.CHOIX_ANNEE.Value = Me.CHOIX_ANNEE.OldValue
Me.CHOIX_PERIODE.Value = Me.CHOIX_PERIODE.OldValue

Me.CHOIX_DOCUMENT.RowSource = strOri

Me.CHOIX_DOCUMENT.SetFocus
Me.CHOIX_DOCUMENT.Requery 'does not seem to work !!!!
Me.Form.Requery
End If
and the original listbox query
SELECT LISTE_CONFORMITE_DETAIL.ID, CONFORMITE.NOM AS CONFORMITÉ, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.RESPONSABLE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE AS [CODE DOCUMENT], LISTE_CONFORMITE_DETAIL.DESCRIPTION_LETTRE_ANGLAIS AS [INSCRIPTION COURRIEL], [Forms]![CONFORMITE_POST]![CHOIX_DATE] AS Expr1 FROM CONFORMITE INNER JOIN LISTE_CONFORMITE_DETAIL ON CONFORMITE.ID = LISTE_CONFORMITE_DETAIL.ID_CONFORMITE WHERE ((([Forms]![CONFORMITE_POST]![CHOIX_DATE]) Between CDate([VALID_DE]) And CDate([VALID_A])) AND ((Choose([Forms]![CONFORMITE_POST]![CHOIX_SPECIFIQUE],True,[Forms]![CONFORMITE_POST]![CHOIX_CONFORMITE]=[LISTE_CONFORMITE_DETAIL]![ID_CONFORMITE],False))=True) AND ((Choose([Forms]![CONFORMITE_POST]![CHOIX_PERIODE],Nz([Q1]),Nz([Q2]),Nz([Q3]),Nz([Q4])))=True)) ORDER BY CONFORMITE.NOM, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE;

I know it is far from clear but this is what I got from another developper
Originallym when the form load, everything works even after I change value from either a drop down or radio button group, , Once I select a new valur from another dropbox, I replace the list box wit ha new SQL with specific passed parameters (works OK because this process is to be kept apart from the original process) nothing hapen

can anyone help me understand why I cannot make it work ?

Thanks

gemma-the-husky · Nov 27, 2015

I wouldn't use .value
Is the month (mois) text or numeric

anyway, it may be null, rather than "", in which case your test won't work.

try one of these variations, depending on whether the month is text or numeric

If Not nz(Me.cboMois,"") = "" Then
If Not nz(Me.cboMois,0) = 0 Then

note also that you may not have old values for the controls. the old values are only available while a record is dirty.

you don't need to setfocus to requery

eg this, but I am not sure whether the code will do what you want.
Add a breakpoint, and step through it would be the best.

Code:

 If Not Nz(Me.cboMois,"") = "" Then
     'restore to default data in table paramatre_conformite
     Me.cboMois = ""

     Me.CHOIX_ANNEE = Me.CHOIX_ANNEE.OldValue
     Me.CHOIX_PERIODE = Me.CHOIX_PERIODE.OldValue

     Me.CHOIX_DOCUMENT.RowSource = strOri

     Me.CHOIX_DOCUMENT.Requery 'does not seem to work !!!!
     Me.Form.Requery
End If

Albilly · Nov 27, 2015

the if statement and the .oldValue works as expected, the real problem is when I need to refresh the listbox after the listbox rowsource is set back to it's original SQL string

AccessBlaster · Nov 27, 2015

Have you tried:

Code:

Me.Requery

instead of

Code:

Me.Form.Requery

Albilly · Nov 27, 2015

yes, both and no results

CJ_London · Nov 27, 2015

I don't see any code assigning a value to strOri

suggest you set option explicit at the top of the module and then compile to ensure all variables are correctly named and available

Albilly · Nov 27, 2015

Hi,

already done and my strOri is set at the form_open()
Private Sub Form_Open(CANCEL As Integer)
'set default rowsource from choix_document
strOri = Me.CHOIX_DOCUMENT.RowSource

'pick original val for later process
intYr = Me.CHOIX_ANNEE.Value
intPer = Me.CHOIX_PERIODE.Value
End Sub

CJ_London · Nov 27, 2015

I still don't see where you have declared strOri - if it is only in the form open event, it is local to that event

Albilly · Nov 27, 2015

Does this where clause make any sense ?
I think that might be the problem, rusty on Access queries

WHERE 3/31/2015 BETWEEN CDate(LISTE_CONFORMITE_DETAIL.VALID_DE) And CDate(LISTE_CONFORMITE_DETAIL.VALID_A)

CJ_London · Nov 27, 2015

no idea without context - how is it relevant to your post

Albilly · Nov 27, 2015

this is the SQL of the rowsource of the list box

SELECT LISTE_CONFORMITE_DETAIL.VALID_DE, LISTE_CONFORMITE_DETAIL.VALID_A, LISTE_CONFORMITE_DETAIL.ID, CONFORMITE.NOM AS CONFORMITÉ, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.RESPONSABLE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE AS [CODE DOCUMENT],LISTE_CONFORMITE_DETAIL.DESCRIPTION_LETTRE_ANGLAIS AS [INSCRIPTION COURRIEL] FROM CONFORMITE INNER JOIN LISTE_CONFORMITE_DETAIL ON CONFORMITE.ID = LISTE_CONFORMITE_DETAIL.ID_CONFORMITE WHERE 3/31/2015 BETWEEN CDate(LISTE_CONFORMITE_DETAIL.VALID_DE) And CDate(LISTE_CONFORMITE_DETAIL.VALID_A) ORDER BY CONFORMITE.NOM, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE;

this is the where clausse is coming from and I realize there is no record being returned by it,m looks like this is my problem

CJ_London · Nov 27, 2015

I'm now totally confused

This latest sql is not what you originally posted as the original listbox query.

Are you now saying this new sql is the original sql? Or is this a different problem?

You haven't answered my question about declaring strOri, please answer that before continuing

Albilly · Nov 27, 2015

Sorry about the confusion,
when the form is open it pick up the rowsource from the listbox control
Private Sub Form_Open(CANCEL As Integer)
'set default rowsource from choix_document
strOri = Me.CHOIX_DOCUMENT.RowSource

'pick original val for later process
intYr = Me.CHOIX_ANNEE.Value
intPer = Me.CHOIX_PERIODE.Value

End Sub,
this is the string in the control rowsource:
SELECT LISTE_CONFORMITE_DETAIL.ID, CONFORMITE.NOM AS CONFORMITÉ, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.RESPONSABLE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE AS [CODE DOCUMENT],
LISTE_CONFORMITE_DETAIL.DESCRIPTION_LETTRE_ANGLAIS AS [INSCRIPTION COURRIEL],
FROM CONFORMITE INNER JOIN LISTE_CONFORMITE_DETAIL ON CONFORMITE.ID = LISTE_CONFORMITE_DETAIL.ID_CONFORMITE
WHERE
((([Forms]![CONFORMITE_POST]![CHOIX_DATE]) Between CDate([VALID_DE]) And CDate([VALID_A]))
AND ((Choose([Forms]![CONFORMITE_POST]![CHOIX_SPECIFIQUE],True,[Forms]![CONFORMITE_POST]![CHOIX_CONFORMITE]=[LISTE_CONFORMITE_DETAIL]![ID_CONFORMITE],False))=True)
AND ((Choose([Forms]![CONFORMITE_POST]![CHOIX_PERIODE],Nz([Q1]),Nz([Q2]),Nz([Q3]),Nz([Q4])))=True))
ORDER BY CONFORMITE.NOM, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE;

(the above is the strOri)

If the user is selecting a value from a cboMois
I change the listbox rowsource and I proces it:
Private Sub cboMois_Change()
On Error GoTo Errsub
'set new rowsource
strNew = "SELECT LISTE_CONFORMITE_DETAIL.ID, CONFORMITE.NOM AS CONFORMITÉ, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.RESPONSABLE," & _
" LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE AS [CODE DOCUMENT],LISTE_CONFORMITE_DETAIL.DESCRIPTION_LETTRE_ANGLAIS AS [INSCRIPTION COURRIEL]" & _
" FROM CONFORMITE INNER JOIN LISTE_CONFORMITE_DETAIL ON CONFORMITE.ID = LISTE_CONFORMITE_DETAIL.ID_CONFORMITE" & _
" WHERE ((Choose([Forms]![CONFORMITE_POST]![CHOIX_SPECIFIQUE], True, [Forms]![CONFORMITE_POST]![CHOIX_CONFORMITE] = [LISTE_CONFORMITE_DETAIL]![ID_CONFORMITE], False)) = True)" & _
" AND LISTE_CONFORMITE_DETAIL.MOIS = '" & Me.cboMois & "'" & _
" ORDER BY CONFORMITE.NOM, LISTE_CONFORMITE_DETAIL.DESTINATAIRE, LISTE_CONFORMITE_DETAIL.DOCUMENT_CODE;"

'war user about all criteria
If MsgBox("Cette sélection mensuelle va ignorer les autres paramètres." & vbCrLf & "Voulez-vous continuer ?", vbYesNo, "Sélection mensuelle seulement") = vbYes Then
'code here to process
'set param to null

Me.CHOIX_ANNEE.Value = ""
' intPer = Me.CHOIX_PERIODE.Value
' Me.CHOIX_PERIODE.Enabled = False
' Me.CHOIX_PERIODE = ""
Me.CHOIX_DOCUMENT.RowSource = strNew
Me.CHOIX_DOCUMENT.Requery

Call UpdateList
Else
Me.cboMois.Value = Null
End If

ExitSub:
Exit Sub
Errsub:
MsgBox "Erreur: (" & Err.Number & ") " & Err.DESCRIPTION, vbCritical
Resume ExitSub
Resume
End Sub
this sub is working the way it should be , no problem
now I the user want to select a specific year instead, which it is combine to a quarter, this is what I do:
Private Sub CHOIX_ANNEE_AfterUpdate()
'Dim intPer As Integer, lgnYr As Long
'Dim strSQL As String, strYr As String, strDate As String
'Dim rst As DAO.Recordset
'Recalc
'check if cboMois has change
If Not Me.cboMois.Value = "" Then
'restore to default data in table paramatre_conformite
Me.cboMois.Value = ""

Me.CHOIX_ANNEE.Value = Me.CHOIX_ANNEE.OldValue
Me.CHOIX_PERIODE.Value = Me.CHOIX_PERIODE.OldValue
Me.CHOIX_DOCUMENT.RowSource = strOri

Me.CHOIX_DOCUMENT.SetFocus
Me.CHOIX_DOCUMENT.Requery 'does not seem to work !!!!
Me.Form.Requery
End If

Call UpdateList
End Sub

and this is where the fun start, somehow the strOri does not return anything anymore, basically, the where clause once printed out looks like the following:
WHERE 3/31/2015 Between CDate(LISTE_CONFORMITE_DETAIL.VALID_DE) And CDate(LISTE_CONFORMITE_DETAIL.VALID_A)

Now I just remember that a date requires a # sign and when i add the # it returns nothing, right now I and debugging it using 1 date only > #3/31/2015# on a column I know there are date higner than that and trhe en result is stil lthe same , it returns nothing and I beleive strongly this is the cause of my problem the date fields even if the datatype is datetime

The_Doc_Man · Nov 27, 2015

I cannot recall the context in which I saw this actually happen, and it doesn't look like it could happen in your case, but... Is there a chance the new row source exactly matches the old row source (i.e. even though you reloaded the row source, nothing changed?) I believe Access can get confused about that case.

Also, if the listbox is visible, there are ways to step through the collection of rows in the listbox to set the .Selected property to false.

Albilly · Nov 27, 2015

Many thanks for the help averyone, I just manage to make it work, the solution is for me to hardcode the listbox rowsource everytime I need to make change and requery it instead of passing the SQL string thru variables, now it is working fine

Regards

CJ_London · Nov 27, 2015

that says to me what I have been trying to make clear - your strOri variable has not been declared as a public variable- so it can be shared between the two procedures

Unable to requery a listbox

Albilly

Registered User.

gemma-the-husky

Super Moderator

Albilly

Registered User.

AccessBlaster

Be careful what you wish for

Albilly

Registered User.

CJ_London

Super Moderator

Albilly

Registered User.

CJ_London

Super Moderator

Albilly

Registered User.

CJ_London

Super Moderator

Albilly

Registered User.

CJ_London

Super Moderator

Albilly

Registered User.

The_Doc_Man

Immoderate Moderator

Albilly

Registered User.

CJ_London

Super Moderator

Users who are viewing this thread