List Box in Form

[jonnymenthol]

Hello,

I have a List Box in my DB on a form, which when the form is opened, the list box displays the date relating to the records. (Date, Person etc)

How can I get it so that when I open the form, ONLY the records with a date within the current month are shown.

Thanks in advance.

J.

 

[Mile-O]

Set the criteria of the query specified as the listbox's RowSource to do this.

 

[jonnymenthol]

Thanks

Thanks Very much for that. I appreciate it.

J.

 

[jonnymenthol]

Tried It

Hello,

I have done as you suggested, and it works fine from within the query builder.
However, it doesn't work when I open the form.

I have a feeling it is because when the form it opened, it is already set to do the following :

Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
strsql = "SELECT ID,Person,Task,Area,DateFrom,DateTo FROM TblMain WHERE 1 = 1"
If Len(TxtDateFrom) > 0 Then
strsql = strsql & " AND DateFrom like '*" & TxtDateFrom & "*'"
End If
If Len(TxtPerson) > 0 Then
strsql = strsql & " AND Person like '*" & TxtPerson & "*'"
End If
If Len(TxtTask) > 0 Then
strsql = strsql & " AND Task like '*" & TxtTask & "*'"
End If
If Len(TxtDateTo) > 0 Then
strsql = strsql & " AND DateTo like '*" & TxtDateTo & "*'"
End If

strsql = strsql & " Order by Person;"
Lstcustomers.RowSource = strsql
Call Countrecords
LblDateTime.Caption = Time
PersonSort.Visible = True
TaskSort.Visible = False
AreaSort.Visible = False
DateFromSort.Visible = False
DateToSort.Visible = False
Call CmdClear_Click
End Sub

I hope this makes sense, if not then please let me know.

Thanks.

J.

 

[Mile-O]

Private Sub Form_Open(Cancel As Integer)
Dim strsql As String
strsql = "SELECT ID,Person,Task,Area,DateFrom,DateTo FROM TblMain WHERE 1 = 1"
If Len(TxtDateFrom) > 0 Then
strsql = strsql & " AND DateFrom like '*" & TxtDateFrom & "*'"
End If
If Len(TxtPerson) > 0 Then
strsql = strsql & " AND Person like '*" & TxtPerson & "*'"
End If
If Len(TxtTask) > 0 Then
strsql = strsql & " AND Task like '*" & TxtTask & "*'"
End If
If Len(TxtDateTo) > 0 Then
strsql = strsql & " AND DateTo like '*" & TxtDateTo & "*'"
End If

strsql = strsql & " Order by Person;"
Lstcustomers.RowSource = strsql
Call Countrecords
LblDateTime.Caption = Time
PersonSort.Visible = True
TaskSort.Visible = False
AreaSort.Visible = False
DateFromSort.Visible = False
DateToSort.Visible = False
Call CmdClear_Click
End Sub

1. You would be better with the Form_Load event;
2. What is WHERE 1 = 1 ?
3. DateFrom like '*" & TxtDateFrom & "*'" (Dates are not Strings)
4. So change to = "#" & CDate(Me.TxtDateFrom) & "#"
5. Use Me. for all objects on your form (like in the point above)

 

[jonnymenthol]

Query

1. You would be better with the Form_Load event;
2. What is WHERE 1 = 1 ?
3. DateFrom like '*" & TxtDateFrom & "*'" (Dates are not Strings)
4. So change to = "#" & CDate(Me.TxtDateFrom) & "#"
5. Use Me. for all objects on your form (like in the point above)

Hi,

I'll answer these in turn the best I can (apologies, but I'm really new to this, so please bear with me)

1) I tried this, but I got a message saying "The expression On Load you entered as the event property setting produced the following error : procedure declaration does not match description of event or procedure having the same name."

I then choose ok to this, the form comes up (with the correct records), then I get the same message again, but relating to the on timer instead of the on load (on timer only populates a lable with todays date and time)

2) WHERE 1=1 is used because on this list form I have 3 boxes at the top. (criteria boxes if you like). I put my name in one of these boxes, then it filters the list box to only show my records. Then I filer further by choosing a certain person, and it shows my records with this person.

Without the WHERE 1=1, it would filter out my records, but when I then chose a person, it showed others people's records with that person aswell.

3, 4 and 5) I will be dealing with these later as they are part of another problems altogether.

I hope this helps.

thanks.

 

[jonnymenthol]

Looking again.

Hi,

I'll answer these in turn the best I can (apologies, but I'm really new to this, so please bear with me)

1) I tried this, but I got a message saying "The expression On Load you entered as the event property setting produced the following error : procedure declaration does not match description of event or procedure having the same name."

I then choose ok to this, the form comes up (with the correct records), then I get the same message again, but relating to the on timer instead of the on load (on timer only populates a lable with todays date and time)

2) WHERE 1=1 is used because on this list form I have 3 boxes at the top. (criteria boxes if you like). I put my name in one of these boxes, then it filters the list box to only show my records. Then I filer further by choosing a certain person, and it shows my records with this person.

Without the WHERE 1=1, it would filter out my records, but when I then chose a person, it showed others people's records with that person aswell.

3, 4 and 5) I will be dealing with these later as they are part of another problems altogether.

I hope this helps.

thanks.

Having looked again, I think I can see (sort of) where the problem lies.

My line which is on the Form_Open is :

strsql = "SELECT ID,Person,Task,Area,DateFrom,DateTo FROM TblMain WHERE 1 = 1"

I think I need it to be the sql from the row source. (SELECT ID, Person, Task, Area, DateFrom, DateTo FROM TblResourceMain WHERE (((Month([DateFrom])) Like Month(Date())))")
However when I put this in, it still doesn't work - Does anyone have any idea why ?

Thanks.

J.

 

[Mile-O]

First off, why are you using Like with a date? Like is an operator for pattern matching within a string. The Month() function returns an integer so saying Like 6 means nothing. If you are not using a wildcard to pattern match a string then always use the euals (=) operator.

The only criteria you need to pick up the current month is:

Between DateSerial(Year(Date()), Month(Date()), 1) And DateAdd("m", 1, DateSerial(Year(Date()), Month(Date()), 1)) - 1

 

[jonnymenthol]

I thought the like may have been causing a problem, so I changed it to =. However, it still didn't work.

I think I can see what your suggestion is doing, but where would I put this criteria ?

Thanks.

J.

 

[Mile-O]

In the criteria for the Date field in the query.

 

[Mile-O]

Also, I just realised your table is called TblMain - that doesn't sound as if you have properly normalised data.

 

[jonnymenthol]

Thanks

Hi Mile-O,

Firstly, my query consists of the 5 fields : Person,Task,Area,DateFrom,DateTo and an expression for Month([Date From]).

The criteria for this Month expression is : =Month (Date()).

Secondly, not sure what you mean about normalised data.
I just have one main table, which all entries are posted to when the entry form is saved.

Is there a better way ?

J.

 

[Mile-O]

Based on that one table, you should have four tables in total.

I'm not going to explain normalisation as it has been described, suggested, and hammered into people's heads numerous times on this forum and, luckily, it has a Search function to save us writing it out again and again and again and.... Google, also, returns just shy of a million results.

I will say, however, that each table in a database should represent an entity. Your one table models nothing. You have a field called Person - a name is best broken into forename & surname and, should you query on that or wish to change something (a task name/ an area) then you have to go through each record and change the text. Properly normalised and you'll change it once in its relevant table and will be laughing...

One table for persons, one for areas, one for tasks, and the one you have now whatever it is meant to be.

There shouldn't be any such thing as a "main table" - just tables that relate to each other.

If you don't want to understand normalisation (and it is in your best interest if designing a database - always best to get the best and most robust table structure designed before you even thing of making forms and the queries you bind them to) then you only need this SQL:

SELECT Person, Task, Area, DateFrom, DateTo
FROM TblMain
WHERE DateFrom Between DateSerial(Year(Date()), Month(Date()), 1) And DateAdd("m", 1, DateSerial(Year(Date()), Month(Date()), 1)) - 1
ORDER BY DateFrom;

 

[jonnymenthol]

Normalisation

Thanks for that.

1) The sql doesn't work when I put it in my form code, I get a message saying compile error: expected: end of statement, and the "m" is highlighted.

2) thanks for the info on normalisation, I'm very interested in learning more about it so I will definitely look it up on the search when I have a moment.

One quick question though, this DB is used to store different requests made by a team of 10 people. For example, I may request the use of another member of staff (say to help me write a document). I will request this time for next Monday for 3 hours. I will also log what my request is relating to.

If I don't have a main table, and have 4/5 tables instead relating to each thing, Person, Area etc. How will the records be stored so I can access them in the future ?

Is it a case that the data is then pulled from each of the different tables ?

Thanks.

J.

 

[Mile-O]

1) The sql doesn't work when I put it in my form code, I get a message saying compile error: expected: end of statement, and the "m" is highlighted.

Don't put the SQL in code in the first place. Everytime you create an SQL statement in VBA it creates a whole new query which means that space is taken up and the database gets bigger. WHen the subroutine finishes the query is then deleted but the space it takes up is not removed from the database. THis is database bloat and, if you create unnecessary queries in code, you'll find your database reaching some interesting sizes. To reduce bloat (which is more common in development) then you have to compact your database regularly. If you simply create a normal query and use the SQL I gave you, you can save that 'QueryDef'. Then all the code you'll need is a simple setting of the listbox's RowSource:

Me.MyListBox.RowSource = "MyQuery"

As an aside, the SQL balked because there was a " within the string. The editor thought that that was the end of the string. WHen you need a " inside a string, you a required to double it up ""

One quick question though, this DB is used to store different requests made by a team of 10 people. For example, I may request the use of another member of staff (say to help me write a document). I will request this time for next Monday for 3 hours. I will also log what my request is relating to.

If I don't have a main table, and have 4/5 tables instead relating to each thing, Person, Area etc. How will the records be stored so I can access them in the future ?

Is it a case that the data is then pulled from each of the different tables ?

I'm guessing that you currently have your table immediately bound to a form. Forms should be bound to queries so, yes, the info is pulled from different tables. The trick is to store the primary key of another table in your table and, when querying you can pull anything from another table if these two are connected.

As I said, once you read up on the subject then it should all fall into place.