Including date range as a combo box.

[bbannock]

Hi everyone...I'm an access dummy, so bare with me. I'm creating a database for my boss and I've run into a small problem. I have a form which uses two combo boxes...one for employee name and one for employee ID. Once the user enters something into the box it populates the subform. Now my boss wants to include an option to search for transactions within a specific range of dates.

Right now I have a combo box for Employee Name
a combo box for Employee ID
and I'm going to need something to search for a range of dates.

I have a few questions...

1. How do I get the combo boxes to matchup ie. When I type in the Employee ID in one combo box I would like to see the name come up in the other automatically.

2. how do I allow the user to search for a range of dates on the form. I have both month and year categories included in the data...so I basically just need to know the best way to go about including this on the form.

I'm sorry this is really long and everything, but I'd really appreciate your help.

Thanks Alot,
Brandon

 

[nberryman]

Need to know more,what tables do you have? It sounds to me that you are getting yourself confused and that your basic database structure is wrong.

Tell us what you have and we should be able to offer solutions, for example you don't need two combo boxes as one will do.



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Krystoff]

Brandon,

Ok I think I can visualize what you have. Lets deal with problem number 1 first. In your table, you should only be storing the data for the employee code. You should store all data about the employee themselves into an employee table.

Ocne you are just storing the EmpID in the table, it makes it much easier to sync the two combo boxes. However you do need some type of employee table for this to work

Set the controlsource of BOTH combo boxes to the same field. EmpID in this case. Then set the Row Source type to Table/Query.

EmpID:
Row Source: SELECT EmpID, EmployeeName FROM EmployeeTbl
Column Count: 1
Bound Column: 1

EmployeeName:
Row Source: SELECT EmployeeName, EmpID FROM EmployeeTbl
Column Count: 1
Bound Column: 2

What that does is store the EmpID in the EmpID field but display it differently in the two text boxes.

**********************
Problem 2

You have the data ranges to search by so this shouldn't be a problem. All you need are 2 new text boxes on your form and to change the query that the subform is based on to include your new criteria. Plus we will make it so that if they don't include a date, it will show everything.

On your form, name the 2 textboxes MthRng and YrRng. Make sure and have the text boxes requery the subform in the After Update event of the text boxes.

Now, in the criteria in your subform for Month and Year, place this in Month(Replacing YourForm with whatever your form is named):
[Forms]![YourForm]![MthRng] OR [Forms]![YourForm]![MthRng] Is Null

In the Criteria for year put:
[Forms]![YourForm]![YrRng] OR [Forms]![YourForm]![YrRng] Is Null

That should do it, if you have any more problems, let me know and I will see if I can help.

Chris

 

[bbannock]

Thanks guys...I'm gonna give it a go and I'l get back to you...thanks for the help.

Brandon

 

[bbannock]

Everything is going really well so far...I'm just stuck on how to Make sure and have the text boxes requery the subform in the After Update event of the text boxes. I see where to do it, but do I have to write an expression or what?

thanks alot!

Brandon

 

[Krystoff]

Brandon,

The easiest way I have figured out to do it is to use some vba code.

In the after update event of the text box, place this code (click the three elipses ... at the right)

Me.YourSubformName.Requery

HTH

Chris

 

[bbannock]

Chris, you've been a huge help.

I wrote the VB code and everything else...now how do I actually enter the ranges...should I make the text boxes combo boxes?

right now i have

mthrng : Text Box
yrrng : Text Box

 

[bbannock]

Also...now that I've put in the code...when I type in the box I'm getting taken back to the VB window.

Brandon

 

[Krystoff]

Brandon,

Ok, not sure about being taken back to the VB window. Try shutting down the VB window before opening the form. If that doesn't work, can you post the code exactly as it shows in the vb window?

For the text boxes, in the format of the field put "Short Date" Also, if you really want to limit the user, place an input mask of short date on there as well. (Use the wizard for input mask and its really easy)

Glad I can be of help!

Chris

 

[bbannock]

Private Sub Combo10_AfterUpdate()
Me.Amex_per_EE_query_subform.Requery
End Sub

this is the visual basic code I wrote.

also...the data I was given comes with a month and a year column

ie. column 1 column 2
March 2003

I want the user to be able to search from march 2003 to April 2003...something like that...I do not have exact dates in the short form.

If this is too much feel free to not respond...you've done a ton already

Brandon

 

[Krystoff]

Dont worry about the help, it gets me thinking about other things than my own work

Ok, so I assume that the vba window keeps popping up which is very strange. I have never heard of that happening. If you can send the db, you can email me at krystoff@exite.com with the db (as long as its not too large) and I can look at it.

For the dates,

When I wrote the first criteria, I wasn't thinking of multiple months or years. I was thinking of looking for just one month or year at a time. There are two ways of doing this.

One: Make 2 extra text boxes and do a between in your criteria. This would make for more work on the user.

Two: Depending on how you have the data stored for month (text like March or numeric like 3) concatenate the fields Month and Year and do a between based on that field.

Let me know which way you would rather go.

Chris

 

[bbannock]

I'd say we should stick with the first one...I would send you the database, but it has some info that others aren't supposed to see...that'd probably make things alot easier.

 

[Krystoff]

If you can delete the info that other people arent supposed to see then email it that would work. Otherwise, lets muddle through as best we can!

Ok, add the two new text boxes. It will be a little more work but rename the old text boxes so you can easily distinguish them.

So the four text boxes would be SMthrng, EMthRng, SYrRng, and EYrRng.

Then your criteria for the query would look like:

Between [Forms]![YourForm]![SMthRng] and [Forms]![YourForm]![EMthRnng] OR Between [Forms]![YourForm]![SMthRng] and [Forms]![YourForm]![EMthRnng] IS NULL

Change the above to house the Start Year and the End Year and you should be set. The only problem left is the VB window popping up.

Chris

 

[bbannock]

ok...I put the between function in the query for the subform...is that right?

I also think I have to link the months up with numbers 1-12 to get them to go in order.

bye the way...the VB window is taken care of...not a problem any more.

 

[bbannock]

Chris...thank you for all your help today...I'm miles ahead of where I was. I gotta get going though...if you're around here tommorow I'm sure I'll try and pick your brain again...thanks alot for your help.

Brandon

 

[Krystoff]

Np, post here again tomorrow to make sure I see it or email me!

Chris

 

[bbannock]

Hey...If you're still out there Chris I'm stil havin an issue with getting the form to use the month/year criteria to search the database.

I created all the text boxes and labelled them correctly...and I included the between function in my criteria for the subform...I'm not sure if that was the right place to put it? As of now the form just disreguards the month and year ranges and just spits out all of the data for each person.

thanks alot for your help.

Brandon

 

[bbannock]

Also...I've created a seperate database with the same forms that i could send to you to look at...it's only 1.74 mb right now.

 

[Krystoff]

Hey Brandon,

Do you have Winzip? That email address won't accept something that large. I have a couple more that will work if you can't zip it up. Either way, send it to krys_wilson@earthlink.net as that will be a better email system.

The subform was the correct place to put the criteria. Also, the way I wrote the criteria, it should spit out everything unless you put criteria in. But why it wont filter correctly when you put the criteria in is a mystery.

Chris