form based on query

[Angello Pimental]

I have a form based on a query. On the form there are two combo boxes which are used as the criteria for the query.

My problem is that I can select the values in the two comboboxes but nothing happens.
I have tried to use refresh, but to no avail.

Any suggestions as to what I am doing wrong?

THnx

p.s. The results of the search(query) are displayed on the same form (continuous form property)

 

[Abby N]

Code the comboboxs After Update event with:

Me.Requery

That should be all you need.

~Abby

 

[Angello Pimental]

AbbyN,
THanks for the reply, but the me.requery is not working for me.

My problem may be that the purpose of the form/query is for the users to select a month and year, and for all records containing the selected values to appear.
So basically the selections from two comboboxes need to be joined in code to form the criteria for one date field in the query.

The query works perfectly if I put a question as the criteria, so that users are prompted to enter the month and year before the form opens. But as soon as tried to use comboboxes nothing works....

Well, any suggestions from anyone would be great,

Thnx

 

[Abby N]

Try something like this in the query's date field criteria.

>=CDate([Forms]![FormName]![cmbMonth] & "/1/" & [Forms]![FormName]![cmbYear]) And <CDate([Forms]![FormName]![cmbMonth] + 1 & "/1/" & [Forms]![FormName]![cmbYear])

You may need to add something to handle situations where one or both of the combos' are Null. But so long as they have values this should do it. Hope it helps.

~Abby

 

[Angello Pimental]

Abby,
Thanks for your help.. it is greatly appreciated.
For some reason I get the error message "The expression is typed incorrectly or is too complex to be evaluated".....

I will try to understand what the code actually does, so that maybe I can figure something out.
Thanks for the start,

Angelo

 

[Rich]

Do you mean form or subform?

 

[Angello Pimental]

Rich,

I have a form which is based on a query. And on the form there are two comboboxes that I want to be the criteria for the query.

The values should combine from the two comboboxes to make the criteria for the date field of the query.

The results are then displayed on the form, in a continuous form format.

Should I be going about this differently?
WHat do you suggest??

THanks..

[This message has been edited by Angello Pimental (edited 08-24-2001).]

 

[Chris RR]

Here's one way around this, not elegant but maybe useful.

Create a new unbound text field on the form (locked and invisible). Give it a nice name, and a control source like this:
=[cmbMonth] & "/" & [cmbYear]
Then use this new field as the criteria in your query. Be careful, you may need to accommodate leading zeroes in your months (9/01 is not the same as 09/01, sometimes...) or other flaky format things.

 

[Angello Pimental]

Thanks for the idea Chris,

Here is what I have so far, but it doesn't seem to work:

="*" & "-" & DatePart("mmm",[monthdate]) & "-" & DatePart("yy",[yeardate])

Since the date field that I will be quering is in the format 30-Aug-02, I am trying to convert the monthcmb value from August to Aug, and the yearcmb value from 2002 to 02.

If anyone has any ideas please feel free to pitch in your two cents

Thnx

 

[Chris RR]

OK, let's take a different approach...

I'd make your two entry fields on the form numeric, with input masks and default values that put months in the range of 1 through 12 and years into some appropriate range.

Then, in your query, I would create two new fields. One will be the month only from the date on the table, the other will be the year. The criteria for each is the appropriate field on your form. You won't need a wildcard (which you were trying to do on the form, but it would properly go in the query criteria).

 

[Angello Pimental]

Chris,

I am actually one step ahead of you.
I have been able to get the date: August 2001 down to Aug-01.

My problem now is how to put the wildcard in the query criteria....

THnx

 

[D-Fresh]

You have it right by using the asterisk(*) but you have to use the Like operator instead of the equals(=) operator. Hope this helps.

Doug

 

[Angello Pimental]

Doug,

This is the code that I have now..... as the criteria for the query.

Like "*" & "-" & DatePart("mmm",[forms].[tryrenewal].[monthdate]) & "-" & DatePart("yy",[forms].[tryrenewal].[yeardate])

But it still won't work for me, any suggestions???

 

[D-Fresh]

You have to be comparing it to a field. I'm not sure if you just left that out, but it should be

[Field_Name] Like "*" & .....

Hope this helps, because everything else looks alright.

Doug

 

[Angello Pimental]

OK,
I am still getting no love from Access.....
The code:
Like "*" & "-" & DatePart("mmm",[forms].[tryrenewal].[monthdate]) & "-" & DatePart("yy",[forms].[tryrenewal].[yeardate])

Does just not want to do anything for me.

Instead I have reverted to Chris RR's idea, I get no errors, but the subform just does not display the information. I even have a requery. And the mainform and subform are linked by two parent/child relationships...

So what the hell is going on here?
I have a feeling this is going to be a rough Friday night.... the drink is already calling me!!!!!!!!!!!

Ahhhhhh
HELP

 

[jwindon]

Ok. I think I caught something on this on another thread. Now I see what you are really wanting to do. I had assumed you were asking for the dates as input data. Sorry.

You have one problem I can see.... the date you are searching for is a Date/Time data field and you are asking for Text Field data in your form. That is a major problem. (DRINK UP!)

I'll play around for a minute.....

 

[jwindon]

Well...I figured it out, but its real messy. How would you like me to let you know. I'm still tweaking it.

The first thing I had to do was create a new field (datatype TEXT) on your main record table.

I had to create two more tables used in lookups for your form based on the query.
They were:
Field1 1
Field2 January
Field1 2
Field2 February

so forth....all datatypes were text with the Field 1 as a primary key.

Did a second lookup table just like that for years.

Field1: 00
Field2: 2000
Field1: 01
Field2: 2001

Secondly...I created an update query to update that new field to the value of your MainTable DATE field.


Now the fun part, putting those on the form....

Put on two unbound combo boxes....ControlSource for the first is the first lookup table...ControlSource for the second is the second lookup table.
Create another unbound textbox(call it MONTHTEXTCHOICE) on your form, set its value to =[Combobox1]&"/"
Create yet another unbound textbox on your form (call it YEARTEXTCHOICE) set it value to =[Combobox2]

Leave them visable for now so you can see what this is doing..

Now for the query itself.....

Put on the new field that you will create using the update query, the TEXTDATE

Also put in your field list (the top row, yes) these....

Left([TEXTDATE,2)
as the criteria for this put =Forms![QueriedFormName]![MONTHTEXTCHOICE]

Left(TEXTDATE,3)
as the criteria (Under OR!)=Forms![QueriedFormName]![MONTHTEXTCHOICE]

Right(TEXTDATE,2)
as the criteria (under the equals not the OR) put
=Forms![QueriedFormName]![YEARTEXTCHOICE]

Put a button on your form to run the query after making your choices in the combo boxes.

You get to figure out the error handling yourself man,

MY GOD! I just tried the whole thing out and IT WORKS! What a pain, but you had a big one on your hands!

Email me and tell me your thoughts. If you can't get it from all this ...then I'll email you the "quickie"database I made it in.




[This message has been edited by jwindon (edited 08-24-2001).]

 

[Angello Pimental]

JWindon,

THanks for your reply. I have had a look at your code and I understand what you are trying to do.
I will have a go at getting the code to work. Could you please e-mail me that "quickie" database to have a look at...

ampimental@hotmail.com

Thanks,

Angelo

 

[Rich]

Why not just extract the Month/Year from dates you already have stored?

 

[Angello Pimental]

Rich...

Are you suggesting using datepart to extract the month and year from all the dates in my records?

How would I code that?

Angelo