Creating a form in access with VBA

[nupur]

I need help in creating a form that can query a database and generate reports in access. For eg: if I want to know what type of products were sold in year 2010, I will select the type of product from a "product combo box" and then will select the 2010 month from "month combo box". After clicking OK or RUN it would give me a list of all products for that month. It is possible to create such query form in access with the help of VBA? I would really appreciate your advice.

Thanks a lot.

 

[Galaxiom]

It can be done without coding the query in VBA.
In a query the controls on forms can be referred to as:
Forms.formname.controlname

In the query designer use the following criteria on the datefield:

BETWEEN DateSerial(2010,[Forms].[formname].[cboMonth],1) AND DateSerial(2010,[Forms].[formname].[cboMonth]+1,1)-1

(This assumes the month is a number. You can show a month name in the combo and have the month number as the value if you like by including two columns in the combo.)

For the product field criteria:

=[Forms].[formname].[cboProduct]

You might consider replacing 2010 with a combo too so your query will still be useful in the future.

 

[nupur]

Thank you so much for you reply

 

[nupur]

It can be done without coding the query in VBA.
In a query the controls on forms can be referred to as:
Forms.formname.controlname

In the query designer use the following criteria on the datefield:

BETWEEN DateSerial(2010,[Forms].[formname].[cboMonth],1) AND DateSerial(2010,[Forms].[formname].[cboMonth]+1,1)-1

(This assumes the month is a number. You can show a month name in the combo and have the month number as the value if you like by including two columns in the combo.)

For the product field criteria:

=[Forms].[formname].[cboProduct]

You might consider replacing 2010 with a combo too so your query will still be useful in the future.

Thanks .. Just wanted to ask if with this code my displayed results would change based on my selection criteria. for eg: if I choose Jan 2010 to get a list of products for this month (which would be in a separate form or report). For looking at the results of Feb 2010, do I need to create separate result form (like separate form for each month). I am very confused as to what should be my criteria in creating a query.

 

[Galaxiom]

After you change the combo to Feburary you have to Requery the form so a little bit of code would be required.

In the AfterUpdate Event of the combo enter
Me.Requery

This kind of thing is often done with a filter instead of changing the query. For example you might load the rerords for 2010 and then apply a filter for the month.

In this case the AfterUpdate of the combo could have:

Me.Filter = datefield BETWEEN DateSerial(2010,[Forms].[formname].[cboMonth],1) AND DateSerial(2010,[Forms].[formname].[cboMonth]+1,1)-1"
Me.FilterOn = True

You will see many variations on this if you look around.

Another way of writing this is by concatenating the values from the combo. This has tha advantage of reference the form as Me so it works no matter what the context of the form.

Me.Filter = "datefield BETWEEN " &  Format(DateSerial(2010,Me.[cboMonth],1), "\#mm\/dd\/yyyy\#") & " AND " & Format(DateSerial(2010,Me.[cboMonth]+1,1)-1,"\#mm\/dd\/yyyy\#")
Me.FilterOn = True

Because the dates are concatenated they must be expressed in the SQL format. The way I have used Format and DateSerial here is a bit long winded in some circumstances. Sometimes the date string can be written directly from the year, month and day numbers.

You will also see this simpler expression:

Me.Filter = "Month(datefield) =" & Me.combo

It is far easier to write but slower to process than those that create a date range because each record must be processed to deterrmine its month. It is quite a good alternative if there are not too many records.

Note that you should have an index on the datefield in the table. It expedites the query on that field where a date range is used.

 

[Galaxiom]

The way I have used Format and DateSerial here is a bit long winded in some circumstances. Sometimes the date string can be written directly from the year, month and day numbers.

By that I mean:

Format(DateSerial(2010,Me.[cboMonth],1), "\#mm\/dd\/yyyy\#")

could be written more directly as:

"#" & Format(Me.[cboMonth],"00") & "/01/2010#"