Seperating data by Date 2

[dmuroff]

Hello again.

I have an orders table which includes Retailer ID,Product ID, Year Purchased, and Quantity.

I would like to create a form which will display the order information by year. For example, I would like a combo box with the year and when a year is selected, it will display the order info for that particular year. Is this possible to do with just one table? Thanks!

 

[RickSpr]

Easy. Set your combo box's properties as follows:
Control Source: <leave blank>
Row Source Type: Table/Query
Row Source: SELECT DISTINCT [Year] FROM [OrdersTable] ORDER BY [Year]
After Update: [Event Procedure]

Then in the combo box's AfterUpdate event procedure, use the following code:

    If IsNull([[red]ComboBox[/red]]) Then
        Me.FilterOn = False
    Else
        Me.Filter = "Year=#" & [red]ComboBox[/red] & "#"
        Me.FilterOn = True
    End If

When the form first opens, all years are displayed. If the user selects a year from the combo box, a filter on that year will be applied. The user can also erase the contents of the combo box to remove the filter.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dmuroff]

Hi Thanks for your response but I am still having trouble. I apologize for my stupidity.

I have done all that you have said and have put this in the code:

Private Sub Combo4_AfterUpdate()
If IsNull([Combo4]) Then
Me.FilterOn = False
Else
Me.Filter = Year = "#" & Combo4 & "#"
Me.FilterOn = True
End If

End Sub

When I select a date it is telling me that there is an invalid use of null "Runtime error 94". Any ideas? Do i have to do anything special to the order table itself? or is adding the year combo box on the form sufficient? Thanks!

 

[dhookom]

I don't see a field named "Year". I think the code should be:

    If IsNull(Me.cboYear) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[Year Purchased]=" & Me.cboYear
        Me.FilterOn = True
    End If

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RickSpr]

In the statement:
Me.Filter = Year = "#" & Combo4 & "#"
you put the first quote in the wrong place. It should be before 'Year'.

Also, I just noticed- the field name isn't 'Year', it's 'Year Purchased', as Duane indicated. You need to correct that in the combo box's Row Source query, too.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dmuroff]

Ok...

I'd just like to help you both for your help! You have really saved me!

I just have one quick question now

I have used this code for the Year combo box because it is the only one that does not give me an error. It works, however, when I look at my order table it does not show the date. The filter must be working but when I add an entry the date is not taken into account. Here is the code:

Private Sub Combo4_AfterUpdate()
If IsNull([Combo4]) Then
Me.FilterOn = False
Else
Me.Filter = "Year =" & Me.Combo4
Me.FilterOn = True
End If

End Sub

Thanks!

 

[dhookom]

I don't understand what you mean by "when I look at my order table it does not show the date". Does this have anything to do with the combo box issue? I have reviewed your previous messages and don't see anything regarding a date field.

Are you referring to a table or form or what?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dmuroff]

Sorry...Date should be year.

The filter is working for year but when I add a new order on the form the year is not being added to the orders table meaning the product ID, retailer ID and quantity is added but the year is null.

Sorry for the confusion.

 

[dmuroff]

Finally...Is there a way for the filter to startup automatically? i.e. I would like the default year to be 2004 and whenever the form is open, I would like the filter to show only the 2004 orders.

I really appreciate all the help you have given me. You have really saved my ass!

If you need anything from me, don't hesitate to ask!

 

[dhookom]

How do you expect the year value to get entered? Is there a default value set or do users type this in or what? How did the value get there before?

You could set the default value of the combo box to 2004 or Year(Date()). Then set the form's record source query to have a criteria under the year column of:
Forms!frmYourForm!cboYear
You wouldn't ever get all years to display without some additional work and your code in the afterupdate would change to:
Me.Requery

BTW: "combo4" isn't a very good name for a combo box with a purpose. Also, Year is the name of a function and shouldn't be used as a field name.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RickSpr]

Does your form have a text box bound to [Year Purchased]? If not, there's no source for this field on new records (unless it has a Default Value in the table definition), so it would be set to Null.

To make the form start up with the orders for the current year, add the following code to the Form_Load event procedure:

    Dim intFilterYear As Integer
    
    intFilterYear = Year(Now())
    Me.Filter = "[Year Purchased] = " & intFilterYear
    Me.FilterOn = True
    Combo4 = intFilterYear

Don't forget to set the form's OnLoad property to "[Event Procedure]".

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[dmuroff]

Works like a charm..However, when I enter a new order the date is still not being shown in the table.
For example I enter in the Orders form:
Product ID- 5
Retailer- 23
Year- 2004 (selected in combo box)
Quantity- 100

The product ID,retailer ID, and Quantity show up as shown in the Orders table but Year is shown as '0'.

Any ideas?

 

[dhookom]

You are using the Combo box for filtering, not data entry. It can't do both. You can create another text box and set its default value to the value in the combo box.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dmuroff]

Ahhh thanks dhook!

 

[dmuroff]

Works great Rick...Thanks for your help!

Take care