Requery is not working as expected.

[pmcleod]

Good day all. It's been a while.

Issue:

Form "frmLabsBuildFieldExamCards"

Unbound Text Box "txtFindName"

Unbound List Box "LstPossibleChoices" with Row Source bound to a query "SELECT [qryLabsBuildFieldExamCards].[Name] FROM qryLabsBuildFieldExamCards;"

Cmd Button with this code:

Private Sub Command10_Click()
Dim DB As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set DB = CurrentDb()

On Error Resume Next
DB.QueryDefs.Delete ("qryLabsBuildFieldExamCards")
On Error GoTo 0

where = Null

where = where & " AND [Name]Like '" + Me![txtFindName] & "*'"

Set QD = DB.CreateQueryDef("qryLabsBuildFieldExamCards", "Select * from qryLabsFieldExam " & (" where " + Mid(where, 6) & ";"))
'DoCmd.OpenQuery "qryLabsBuildFieldExamCards"
'Me!LstPossibleChoices.Requery
'Forms!frmLabsBuildFieldExamCards!LstPossibleChoices.Requery

**************************

My eventual intent is to pass the results (using Mulit-Select) to another list box "LstActualChoices". However the Requery is not working as I thought it would. the text is passed to the query as expected, but the list box does not show the filtered results.

I have to close then open the form to see the change. Looking at other strings on this form I would have expected Requery to do the trick....what am I missing?

PS I tried using it on the list box but no go.

Thanks in advance.

 

[a.sinatra]

Try adding...

Me!LstPossibleChoices.Refresh

________
Angelina Jolie Pictures

 

[pmcleod]

DoCmd.RunCommand acCmdSaveRecord did not provide the nessessary result.

 

[Mile-O]

The code below is the code you wrote above, reworked, and improved upon in (hopefully) all areas.

You'd never have seen if you were getting an error message with your code as - with the On Error Goto 0 statement - you turned off the error handler.

Try this:

Private Sub Command10_Click()

    On Error GoTo Err_Command10_Click

    Dim DB As DAO.Database
    Dim QD As DAO.QueryDef
    Dim strWhere As String
    
    Const strQuery = "qryLabsBuildFieldExamCards"
    Const strSelect = "SELECT * FROM qryLabsFieldExam "
    
    Set DB = CurrentDb()
    On Error Resume Next
    DB.QueryDefs.Delete (strQuery)
    On Error GoTo Err_Command10_Click
    
    strWhere = "WHERE [Name] Like """ & Me.txtFindName & "*"";"
    
    Set QD = DB.CreateQueryDef(strQuery, strSelect & strWhere)
    
    QD.Close
    DB.Close
    
    Me.lstPossibleChoices.Requery

Exit_Command10_Click:
    strWhere = vbNullString
    Set QD = Nothing
    Set DB = Nothing
    Exit Sub
    
Err_Command10_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Command10_Click
    
End Sub

 

[pmcleod]

Thanks for the improved code. However, the list box still does not return the values from the query.

Here is an example. I'm sure my error is a basic one - first time writing VBA and Access shall remain my excuses for now.

I have not started the rest of the toggling of choices - wanted to get this working first.

Thanks in advance.

 

[Mile-O]

I've got it working although, to be honest, there are a few design flaws in there - a reserved word as a field name ([NAME]) and a repeating group (two different exams in one table), and students in the same table as the exams.

 

[pmcleod]

Agreed. I do need to revisit the table design - I sometimes forget it's importance.

Thank you for your help. Have a great afternoon.