combo box with date

[devagupt]

I need to create a combo box with todays date , yesterdays date and day befores. How do i do it? when i use the combo box wizard , it asks me to type in the values myself and i enter =Now(); =Now()-1 ; =NOW()-2 and it doesnt work
Please help.

 

[rjoubert]

Add a combo box to your form (let's call it cboDates), and cancel out of the wizard when it comes up. In the properties of your combo box, make sure the "Row Source Type" property is set to "Value List."

In the Open Event of your form, use the following code...

Me.cboDates.AddItem (Date)
Me.cboDates.AddItem (Date - 1)
Me.cboDates.AddItem (Date - 2)

 

[devagupt]

Open event of your form" I dont understand. Where should i put the code?

 

[rjoubert]

In the Design view of your form, open the form properties, select the Events tab, locate the "On Open" event, put your cursor in the box to the right of "On Open", and click on the Ellipsis (...) button. The "Choose Builder" box should come up...select "Code Builder" and click OK. This will bring you to the code window. You should see something like this...

Private Sub Form_Open(Cancel As Integer)

End Sub

...put the code in between those two lines.

 

[missinglinq]

I tried this out, rjoubert, and it doesn't work. I think it probably works in straight VB, but it bombs in VBA.

Per Access Help:

expression.AddItem(Text, Index)
expression Required. An expression that returns a CommandBarComboBox object.

Apparently that's the only way it can be used, on a combo box that's part of a CommandBarComboBox.


I dug this up from the dungeon and adapted it for devagupt's app:

1) You need to bind the Combo box bound to field you want to populate

2) Set the Combo box's Row Source Type to Value List

Then in code:

Private Sub Form_Load()
Dim strRowSrc As String
     [COLOR=red]Me.cboPicDate.RowSource = Date - 1 & ";'" & Date & "'" & ";'" & Date + 1 & "'"[/color]
  	Me.cboPicDate.Requery
End Sub

For some reason, you have to do this, otherwise when you add a new record, the combo box defaults to 12/30/99! This sets it to the current date.

Private Sub Form_Current()
	If Me.NewRecord Then
		[COLOR=red]Me.cboPicDate.DefaultValue = "'" & Date & "'"[/color]
	End If
End Sub

Be sure that the lines in red in each code segment appear one a single line in the code editor, not on two lines as it appears here.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya devagupt . . .

Try this in the [blue]OnCurrent[/blue] event of the form,

[blue]   Dim x As Integer, curDate As String, RowSrc As String
   
   For x = -2 To 0
      curDate = Format([purple][b]Date + x[/b][/purple], "mm/dd/yyyy")
      
      If RowSrc = "" Then
         RowSrc = curDate
      Else
         RowSrc = RowSrc & ";" & curDate
      End If
   Next
   
   Me!CBx1.RowSource = RowSrc[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

So sorry devagupt . . .

In my proir post [blue]CBx1[/blue] = [purple]Your ComboboxName[/purple]! . . .

See Ya! . . . . . .

 

[missinglinq]

Misread his requirements; thought he wanted today, yesterday and tomorrow! Even corrected for that, your hack is more elegant, TheAceMan1!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

missinglinq . . .

Thank You! . . . Yeahhhh I hate it when I do that!

[blue]But we are getting better![/blue]

See Ya! . . . . . .

 

[devagupt]

When i type in the below code between private sub form open and end sub , it says variable not defined. Please help.

Dim x As Integer, curDate As String, RowSrc As String

For x = -2 To 0
curDate = Format(Date + x, "mm/dd/yyyy")

If RowSrc = "" Then
RowSrc = curDate
Else
RowSrc = RowSrc & ";" & curDate
End If
Next

Me!CBx1.RowSource = RowSrc

 

[missinglinq]

Have you actually named your combo box CBx1? If not, you need to either change the name of your combobox to CBx1 or change CBx1 in the code to the name of your combo box.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PWise]

what i would do is create a table called digits with one field called digitid type number.

add 3 record to the table
1
2
3

the recordsource of the combo box should be

SELECT Date()-digitid+1 AS Expr1
FROM digits

 

[missinglinq]

Why in the world would anyone add a table to hold three values when a couple of lines of code in a sub will do?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PWise]

Beacuse about two hours after devagupt will thank everyone for there help he will be back asking what shold i do if i want to exclude weekends.

Sure you can add code

but what i will do is say add records to this table and change the query to

SELECT TOP 3 Date()-digitid+1 AS Expr1
FROM digits
WHERE (((digits.DigitID)<7) AND ((DatePart("w",Date()-[digitid]+1)) Between 2 And 6));

and when he will want to exclude holidays for this year you can join it on this table ........

 

[PWise]

and no need to reinintlise on each formcurrent

i use it to create reports with a entry for each day of the month to fill in and no need for a table with each day of tho monte ect......

 

[missinglinq]

And if pigs could fly we'd all be in trouble! I'm sure this works well for you in your application, but all devagupt wants to do is to be able to pick today, yesterday and the day before from a combo box! When answering questions on this or any forum you have to look at what's being asked by the poster and consider his level of expertise!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PWise]

All I did was give a solution that does what devagupt needs in his application, BUT is easily adaptable to many situations.

Give a man a fish feed him for one day. Teach him how to fish feed him for a lifetime

 

[TheAceMan1]

devagupt . . .

If you've actually been reading the posts here you'll see that:
[ol][li]The code is meant to go in the [blue]On Current[/blue] event.[/li]
[li]I already addressed the problem about [blue]CBx1[/blue]. Now [blue]missinglinq[/blue] has done so as well! . . .[/li][/ol]

See Ya! . . . . . .