Populating a listbox

[Tony Williams]

I have a list box on a form that is populated form two tables. The rowsource
of the list box is based on this SQL
SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

That works fine. But I want to filter the list box using date parameters and
have this SQL

SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[Forms]![frmsearch3]![txtstartdate] And [Forms]![frmsearch3]![txtendate]));

The two dates are taken from two unbound text boxes on the form

I have this in the AfterUpdate of the list box
Private Sub List4_AfterUpdate()
Me.[List4].Requery
End Sub

But it doesn't work the list box is empty. What am I doing wrong?
Thanks
Tony

 

[Larry Linson]

But it doesn't work the list box is empty.

What do you _want_ it to do if the List Box is empty? What _is_ it doing if
the List Box is empty?

What am I doing wrong?

Perhaps someone will be able to assist with the answers to these questions;
if not, perhaps someone will have another clarifying question for you.

Larry Linson
Microsoft Access MVP

 

[Tony Williams]

Hi Larry. The list box shouldn't be empty as I know there are records that
fit the parameter of the text boxes. I think the SQL must be wrong.
Tony

 

[AccessVandal via AccessMonster.com]

'I have this in the AfterUpdate of the list box
'Private Sub List4_AfterUpdate()
'Me.[List4].Requery
'End Sub

Your event above, how did it update the listbox?

Your two unbound textboxes are null or empty when you first run the form.
So...you'll have a empty listbox.

You'll need to have a button to refresh the listbox.

 

[Tony Williams]

Hi! The event didn't update the list box. How do I refresh the form with a
button?
Thanks
Tony

 

[Marshall Barton]

I have a list box on a form that is populated form two tables. The rowsource
of the list box is based on this SQL
SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

That works fine. But I want to filter the list box using date parameters and
have this SQL

SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[Forms]![frmsearch3]![txtstartdate] And [Forms]![frmsearch3]![txtendate]));

The two dates are taken from two unbound text boxes on the form

I have this in the AfterUpdate of the list box
Private Sub List4_AfterUpdate()
Me.[List4].Requery
End Sub

Your AfterUpdate event is on the wrong control. You need to
Requery the list box in **both** of the text boxes
AfterUpdate events.

The list will be empty until **both** text boxes are filled
in.

 

[AccessVandal via AccessMonster.com]

You can put these into the on click event of the button.

Private Sub NameOfYourRefreshButton_OnClick()
Me.[List4].Requery
End Sub

or you can do what Marshall suggested.

Private Sub txtstartdate_AfterUpdate()
Me.[List4].Requery
End Sub

The above may not give you all or any records.

Private Sub txtendate_AfterUpdate()
Me.[List4].Requery
End Sub

Better to use "txtendate" AfterUpdate event

 

[Tony Williams]

My list box is still not being populated if I put the dates in.
This is my SQL for the list box row source

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate] And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*"))
OR (((tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtstartdate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtstartdate]) Is Null) AND
(([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate] And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*")) OR (((tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*") AND (([forms]![frmsearch3]![txtstartdate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]) AND
((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*") AND (([forms]![frmsearch3]![txtstartdate]) Is Null) AND
(([forms]![frmsearch3]![txtenddate]) Is Null));


And this is the VBA for the form

Option Compare Database
Private Sub List4_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[tblInstitution1].[ID]=" & Me.[List4]
DoCmd.OpenForm "frmInstitution", , , MyWhereCondition
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub txtenddate_AfterUpdate()
Me.[List4].Requery
End Sub

Private Sub txtname_AfterUpdate()
Me.[List4].Requery
End Sub


Private Sub txtstartdate_AfterUpdate()
Me.[List4].Requery
End Sub


I can't see where I'm going wrong here. The list box does what it should
until I put dates in there.
Help!
Tony

I have a list box on a form that is populated form two tables. The
rowsource
of the list box is based on this SQL
SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

That works fine. But I want to filter the list box using date parameters
and
have this SQL

SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[Forms]![frmsearch3]![txtstartdate] And
[Forms]![frmsearch3]![txtendate]));

The two dates are taken from two unbound text boxes on the form

I have this in the AfterUpdate of the list box
Private Sub List4_AfterUpdate()
Me.[List4].Requery
End Sub

Your AfterUpdate event is on the wrong control. You need to
Requery the list box in **both** of the text boxes
AfterUpdate events.

The list will be empty until **both** text boxes are filled
in.

 

[Marshall Barton]

Whoa there, I'm not going to unravel that monster WHERE
clause. However, a quick glance makes me question this
part:

(tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]
And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate])

Are you really trying to find records that are outside the
date range in the text boxes?

If that doesn't shake something loose, try removing the all
the criteria and then adding each subexpression back in one
at a time to see if that helps locate the problem.
--
Marsh
MVP [MS Access]

My list box is still not being populated if I put the dates in.
This is my SQL for the list box row source

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate] And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*"))
OR (((tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtstartdate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtstartdate]) Is Null) AND
(([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate] And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*")) OR (((tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*") AND (([forms]![frmsearch3]![txtstartdate]) Is Null)) OR
(((tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]) AND
((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] & "*")
AND (([forms]![frmsearch3]![txtenddate]) Is Null)) OR
(((tblInstitution1.txtInstitution) Like [Forms]![frmsearch3]![txtname] &
"*") AND (([forms]![frmsearch3]![txtstartdate]) Is Null) AND
(([forms]![frmsearch3]![txtenddate]) Is Null));


And this is the VBA for the form

Option Compare Database
Private Sub List4_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[tblInstitution1].[ID]=" & Me.[List4]
DoCmd.OpenForm "frmInstitution", , , MyWhereCondition
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

Private Sub txtenddate_AfterUpdate()
Me.[List4].Requery
End Sub

Private Sub txtname_AfterUpdate()
Me.[List4].Requery
End Sub


Private Sub txtstartdate_AfterUpdate()
Me.[List4].Requery
End Sub


I can't see where I'm going wrong here. The list box does what it should
until I put dates in there.
Help!
Tony
"Marshall Barton" wrote

I have a list box on a form that is populated form two tables. The
rowsource
of the list box is based on this SQL
SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

That works fine. But I want to filter the list box using date parameters
and
have this SQL

SELECT tbldocument.txtExpirydate, tblInstitution1.txtInstitution
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[Forms]![frmsearch3]![txtstartdate] And
[Forms]![frmsearch3]![txtendate]));

The two dates are taken from two unbound text boxes on the form

I have this in the AfterUpdate of the list box
Private Sub List4_AfterUpdate()
Me.[List4].Requery
End Sub

Your AfterUpdate event is on the wrong control. You need to
Requery the list box in **both** of the text boxes
AfterUpdate events.

The list will be empty until **both** text boxes are filled
in.

 

[AccessVandal via AccessMonster.com]

Tony,

I’m not going to check your SQL, but I’ll suggest something else.
Like something simple and easy to do.

On the form, create a button as in Part 2 of your post.

Put these in the on click event button.

'--------------------textbox filter code--------------------------------------
-
If Not IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If Not IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = “QryResult1”
Me.YourListBoxName.Requery
End If
End If

If Not IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = “QryResult2”
Me.YourListBoxName.Requery
End If
End If
If IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If Not IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = “QryResult3”
Me.YourListBoxName.Requery
End If
End If
If IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = “QryResult4”
Me.YourListBoxName.Requery
End If
End If
'----------------------end of code--------------------------------------------
---

Next, create 4 queries and name them as below or your choice.

Name of Query ---- “QryResult1”

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between [Forms]![frmsearch3]!
[txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*"));



Name of Query ---- “QryResult2”

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between [Forms]![frmsearch3]!
[txtstartdate] And [forms]![frmsearch3]![txtenddate]));

Name of Query ---- “QryResult3”

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE
(((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] & "*"));

Name of Query ---- “QryResult4”

SELECT tblInstitution1.ID, tbldocument.txtRefNbr, tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname, [txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

Be aware that I’ll be using “Between” for the Where clause of your date
filter.

Never lump multiple filter conditions into one SQL. Break them into managable
queries so that you can avoid errors.

And refer to Marshall question on,

(tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]
And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate])

And maybe I’m wrong, but your monster query seems to nullify all records.

And also check your date format. E.g. 01/05/06 = 1-May-06 or
01/05/06 = 5-Jan-06.

 

[Tony Williams]

Thanks guys, sorry for the monster SQL. Tried the answer from Access vandal
and with a bit of tweaking to suit my database I got it working just fine.
Thanks for that. It's easy to fall into the trap of overcomplicating things
when the easy solution is sometimes the answer! Something I should have
learnt at my ripe old age!
Cheers
Tony

Tony,

I'm not going to check your SQL, but I'll suggest something else.
Like something simple and easy to do.

On the form, create a button as in Part 2 of your post.

Put these in the on click event button.

'--------------------textbox filter
code--------------------------------------
-
If Not IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If Not IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = "QryResult1"
Me.YourListBoxName.Requery
End If
End If

If Not IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = "QryResult2"
Me.YourListBoxName.Requery
End If
End If
If IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If Not IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = "QryResult3"
Me.YourListBoxName.Requery
End If
End If
If IsNull(Me.[txtstartdate] And Me.[txtenddate]) Then
If IsNull(Me.[txtname]) Then
Me.YourListBoxName.RowSource = "QryResult4"
Me.YourListBoxName.Requery
End If
End If
'----------------------end of
code--------------------------------------------
---

Next, create 4 queries and name them as below or your choice.

Name of Query ---- "QryResult1"

SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname,
[txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between [Forms]![frmsearch3]!
[txtstartdate] And [forms]![frmsearch3]![txtenddate]) AND
((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] &
"*"));



Name of Query ---- "QryResult2"

SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname,
[txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between [Forms]![frmsearch3]!
[txtstartdate] And [forms]![frmsearch3]![txtenddate]));

Name of Query ---- "QryResult3"

SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname,
[txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr
WHERE
(((tblInstitution1.txtlastname) Like [Forms]![frmsearch3]![txtname] &
"*"));

Name of Query ---- "QryResult4"

SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, tblInstitution1.txtlastname,
[txtFirstname]
& " " & [txtlastname] AS Contact, forms!frmsearch3!txtstartdate AS Expr1,
forms!frmsearch3!txtenddate AS Expr2
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr =
tblInstitution1.txtRefNbr;

Be aware that I'll be using "Between" for the Where clause of your date
filter.

Never lump multiple filter conditions into one SQL. Break them into
managable
queries so that you can avoid errors.

And refer to Marshall question on,

(tbldocument.txtExpirydate)<=[forms]![frmsearch3]![txtstartdate]
And
(tbldocument.txtExpirydate)>=[forms]![frmsearch3]![txtenddate])

And maybe I'm wrong, but your monster query seems to nullify all records.

And also check your date format. E.g. 01/05/06 = 1-May-06 or
01/05/06 = 5-Jan-06.

My list box is still not being populated if I put the dates in.
This is my SQL for the list box row source