combo box in form that displays last 7 days

[KevinSlater]

Hello, please could anyone tell me if is it possible to have a combo box in a form that displays just the last 7 days? (from whatever today’s current day is) I need to be able to store this date that the user selects in a query/table

(Currently I have a field named SHIFT_DATE that the user enters a date into this field, this field is bound/linked to a query)

Ie a drop down list showing the dates like this:
15/02/2006
14/02/2006
13/02/2006
12/02/2006
& so on……

ive used the following code in a query before to display records from just the last 7 days

>=DateAdd("d",-7,Date())

I thought maybe I could do something similar in a combo box but I’ve had no luck so far :-( any suggestions would be excellent. Thanks.

 

[MStef]

Hello Kevin!

Look at "DemoLast7DComboA2000.mdb".

 

[KevinSlater]

Thanks for the demo Mstef, but im using Access 97, please would you be able to convert this into 97 format or save it in access 97 format for me?
or if anyone ele could kindly convert this for me that would be excellent

 

[MStef]

Here it is!

 

[Rod D]

A slightly different approach...

Hi Kevin

I read your post to mean that you wanted the selection to be the last 7 days, whether you had corresponding data in your tables or not.

My approach was to create the dropdown options based on a function that takes the ComboBox name and the delay you wanted and populates the value list of the CB. The code is below - I have put comments in there which describes how it should be used.

I hope this works in your version, mine is 2002
Rgds
Rod


Function GetLastXDays(ctrlComboBox As ComboBox, Delay As Integer)
'This function populates the Combo Box drop-down list with the dates counting back from today
'to the number of days passed by variable Delay.

'The Row Source property of the Combo Box should be set to "Value List"
'The Value List property of the Combo Box should be left blank
'The On Got Focus Event should be set to =GetLastXDays([<ComboBoxName>],<Delay>)
'e.g. =GetLastXDays([Shift_Date],7)

'Reset the Combo Box value list otherwise function will append to any existing entries
ctrlComboBox.RowSource = ""

'Loop round Delay times, increasing the delay count - starting at 0 will give today's date
'Index value is the position in the list - this will show dates in descending order
For i = 0 To Delay - 1
ctrlComboBox.AddItem Item:=DateAdd("d", -i, Date), Index:=i
Next i

End Function

 

[KevinSlater]

Hi Mstef,
Thanks Mstef I appreciate your help but thats not quite what I wanted to achieve, because the dates need to be the current last 7 days from whenever the user selects the drop down list in the form, and there is no data in the table to begin with. That has however given be an idea for something else, ta.

Hi Rod,
Yes you have read my post correct…I would like the last 7 days without the need for corresponding data in a table.

Your description sounds like just what I’m wanting to achieve, thanks very much for taking the time to post all this & thanks for the comments, they are very helpful!.

I will try this tomorrow & will let you know how I get on.

Regards

Kevin

 

[MStef]

Hello Kevin!

Ok, I know what you need.
Here is a new "DemoLast7DComboA97.mdb".
Look at queries, Query1 is going to create "tblDateTemp".
You don't need to care about this table.
Look at Form1 (VBA), field index1 (Default Value properties).
I think it is what you need.

 

[KevinSlater]

Thanks Mstef, im looking at your example now & trying to understand how it all works, and how i can use this in my database, i have a query in my database with the field "SHIFT_DATE" that i would like the date in the drop down list that the user selects to be stored in (and this in turn stores it in the table "TABLE-SHIFT_DATES". any ideas how i can store the date in this field??, (imm having trouble but ill have another look soon and hopefully work out how to do it)

Rod d im trying to get your example working now also.

ill post back here again soon.

Thanks

Kevin

 

[KevinSlater]

Hi Mstef thanks again for your example, ive played with it but im affraid i still cant get the date i select in the cobo list to be stored in a table, & i cant find a way of doing this, any suggestions?.

Hi Rod D thanks for your help, ive got your code working so that the cobo box displays the last 7 days, but like with Mstef example i cant find a way of making it so that the date i select in the combo box is stored in a table?.

Ive attached a experienetal file with your working code Rod D, if you or anyone is willing to take a look at it for me/or modify it? that would be excellent!. ive tried using SQL code to "insert into" a table but cant get that working either.

ive keep trying to find a way of storing the date but as the combo box is unbound i dont know how to store the data in a query/table.

Anybody help me out?
(would like the date stored in the table "TABLE-SHIFT_DATE" which is in the attached file)
Kevin

 

[MStef]

Hello Kevin!

Look at "DemoLast7DComboA97.mdb", a new one.

 

[Rod D]

Sample Database updated

Hi Kevin

I have modified your file and attached it below.

What you needed to do was...
1) link the table to the form
Form properties - Record Source - set to TABLE-SHIFT_DATE

2) link the combo-box to the table field
Combo-box properties - Control Source - set to Shift_Date

You do not need the extra code that you put in the After Update event. I have removed it...

Hope this gets you going again!
Rgds
Rod

 

[KevinSlater]

Hi Rod D, thanks again for another example, this works fine in access 2000, but not in 97 and unfortunately most of the people who will use this will be using access 97, so therefore I dont think ill be able to implement this code in my database. In 97 a message pops up saying “compile error; Method or data member not found” then the code is shown, attached is a screenshot of the highlighted code in 97. If anyone knows a way around this please let me know.

Hi mstef, thanks for your second example, this seems to work fine in 97, so im trying to implement this is my database now. I do find your method a little harder to understand, but will read through it all again, seems like a clever technique though and I see how the date is being stored in the hidden/locked field.

Overall I prefer your method Rod D as it seems cleaner and easier to understand than Mstef method, and would probably prefer to use it.

Thank you both for your help, and anyone who took the time to read this post.

Kevin

 

[Rod D]

Access '97 version

I don't have A97 to test on, but this should work.
Just replace the function with the one below. Everything else remains the same.

Please let me know how you get on.
Rgds
Rod

Function GetLastXDays(ctrlComboBox As ComboBox, intDelay As Integer)
'For Access 97
‘This function populates the Combo Box drop-down list with the dates counting back from today
'to the number of days passed by variable intDelay.

'The Row Source property of the Combo Box should be set to "Value List"
'The Value List property of the Combo Box should be left blank
'The On Got Focus Event should be set to =GetLastXDays([<ComboBoxName>],<Delay>)
'e.g. =GetLastXDays([Shift_Date],7)

'Reset the Combo Box value list
strDateList= “”

'Loop round intDelay times, increasing the delay count - starting at 0 will give today's date
For i = 0 To intDelay - 1
strDateList = DateAdd("d", -i, Date) & “;” & strDatelist
Next i

‘Set the Combo box’s RowSource to the text built in the loop above
ctrlComboBox.RowSource = strDateList
End Function

 

[KevinSlater]

Thanks again for you help Rod D, ive tried the code you supplied in both Access 97 and 2000 and cant get it working in either, a error message says "compile error: syntax error" when clicking on the combo box then the code is displayed & the following line of code is highlited in red:

strDateList = DateAdd("d", -i, Date) & “;” & strDatelist

I tried to remove the & “;” part out of this line thinking perhaps its the course of the problem and it turned this line into green but still doesn’t work when clicking on the combo field list. i tried changing it to ";" also but no joy.

I will have another look later/early tomorrow and let you know if I get it to work, i guess its just that single line thats wrong, if you have any suggestions why its not working please let me know.

attached is a screenshot of the highlighted code

Thanks

Kevin

 

[Rod D]

Oh the joys of Access and Word...
I pasted the code from Word, which "intelligently" changes the quotes, although you have tried changing them, I would change them all to normal double quotes - there is a line above where the strDateList is reset as well.

Although Access does not mandate it, it may be worthwhile (and good practice) to define the variables within the function, so add the following 2 lines immediately below the Function GetLastXDays(..........) line. I doubt if it will make a difference, but should be done.

Dim strDateList as String
Dim i as Integer

I will have a chance to get to an Access 97 box tomorrow night, so will have a play and let you know how I get on.

I am sorry you seem to be having problems with this - on the face of it, it is such a simple bit of code...fear not though we will crack it...

Rod

 

[KevinSlater]

Hi Rod, yes access is great fun (well sometimes), ok ive changed them all to normal double quotes & defined the variables, it does appear to be almost working now (in 97 & 2000) however it overwrites the first record rather than creating a new record.

Yes it seems like a reasonably simple task to achieve but not quite there yet, I feel were so close now.

Also one more minor thing in access 97 the dates are listed in descending order (which is what I want anyway) but in access 2000 they are in acceding order….bit strange I think!?

Ive played about with this tonight but still cant prevent the date from appending to the first existing record, I will try again tomorrow, please let me know if you have any further suggestions, I hope we can crack this last little step soon, I appreciate all your help!.

Just in case below is the code i currently have

Function GetLastXDays(ctrlComboBox As ComboBox, intDelay As Integer)
Dim strDateList As String
Dim i As Integer

ctrlComboBox.RowSource = ""
strDateList = ""

For i = 0 To intDelay - 1
strDateList = DateAdd("d", -i, Date) & ";" & strDateList
Next i
ctrlComboBox.RowSource = strDateList
End Function

Cheers

Kevin

 

[Rod D]

Hi Kevin

Pleased to hear that the code is working.
In answer to your questions,

The sort order has changed because of the way the list is being built.
In the first example I submitted, the last parameter was a sort "index".

In this code, each pass of the loop adds the date at the front of the string. If you wanted to sort the dates the other way (which you don't), we would simply change the line to add the date to the end -
i.e. strDateList = strDatelist & “;” & DateAdd("d", -i, Date)
The reason I didn't do this is because I would have had to add another line of code to initialise the strDateList before the loop so that the first "date" would not be blank. For completeness, the code is attached below.

As for your form overwriting previous records - I must admit I am a little confused by this as the function updates the field, not the form (and thus the underlying table), so I think the problem may lay in the design of the form - could you post what you have so far so we can see ?

Cheers
Rod


Function GetLastXDays(ctrlComboBox As ComboBox, intDelay As Integer)
Dim strDateList as String
Dim i as Integer

strDateList= Date
ctrlComboBox.RowSource = ""

For i = 1 To intDelay
strDateList = strDatelist & “;” & DateAdd("d", -i, Date)
Next i

ctrlComboBox.RowSource = strDateList
End Function

 

[KevinSlater]

Hi Rod, sorry about being a little late replying, I had a busy weekend, I spent some time today trying to get this to work properly but still cant prevent it from overwriting the existing record :-( ive tried playing about with the design of the form but cant see anything wrong with this. Attached it what I currently have, if you get time to take a look that would be excellent, or anyone else who fancies taking a look for me.

Thanks for explaining how to sort the dates in differnt orders & how this works, that makes sense.

Cheers

Kevin

 

[KevinSlater]

Hi Rod, ive got the combo box to display the last 7 days and successfully update the table, now without overwriting any existing records!.

The problem did appear to lie in the design of the form, it seemed to be going into edit mode rather than add (entry) mode, I re-did the form and it now works.

Attached is the one that works in case you or anyone fancied taking a look, I will now implement this in my proper database.

Thank you for all your help, and for being patient in helping me get this fully working.

Regards

Kevin