Queries in a form

[DarkProdigy]

Hey guys;

What I'm looking to do is setup up an invoicing-type system. I want to create a form wherein there will be several list/combo boxes to select various parameter (ie. product id, accessories, materials etc). As well on this form I want to put a "submit" button that will open up a report which will have the information from the various inputs(such as a breakdown of costs and such). I think I can figure the button bit on my own, but I was wondering how I would go about utilizing the boxes. I tried using a parameter-type query but this opens a dialogue box when the form is opened which I don't want. I want the selections to be stored until the submit button is pressed.

Any suggestions on how I might be able to accomplish this?

Thanks in advance

 

[ejstefl]

Try referencing the controls on your form in your query. For example, if you had a control called txtMaterials on a form called frmInvoice, you would put the following in the criteria section of the Materials field on your query:

Like Forms!frmInvoice!txtMaterials

 

[DarkProdigy]

Ok cool, I got that now. What I would like to know next is how I would update that query while staying in the form so that when I open the report, it'll use the updated field entry from the list box. I think it would have to do with the BeforeUpdate event, but I'm not sure of the syntax

 

[ejstefl]

Let me make sure I understand what you're doing:

You have a form with some unbound controls and a button. The button launches a report.

You want to type values into those controls, and have the report be filtered by the values you type into the controls.

If you've followed my suggestion above on the query, and the query is the source of the report, all you need to do is open the report. It will automatically be updated.

 

[DarkProdigy]

that's exactly what I want to do but it doesn't work. When I open the report on its own, not through the form, a dialogue box pops up prompting you to input Forms!frmInvoice!Control or whatever, and when I put in the ID i want, the report populates properly, but when done through the form, the dialogue does not come up and the report is blank.

I'm thinking I have to do some kind of an update to the query before the button is pressed

 

[ejstefl]

You definatley no not need an update query. An update query is used to change data in a table.

This should work... what data type is the field you are trying to filter by? If it is text, are you using quotes?

 

[DarkProdigy]

The data field type is number. It's still only opening up an empty report. Does it have to do maybe that the button only "previews" the report?

 

[DarkProdigy]

I guess I should clarify what I mean by "empty report". What I mean is that the report follows the layout that I want, there's just no data under any of the headings.

I'll attach a copy of what I have so far. It's a 2000 version

 

[neileg]

The name of your control is not TankID but combo14. So the criterion in the query should be [Forms]![Invoice]![combo14]

 

[DarkProdigy]

Thank you neileg, it's working beautifully now. So simple, but things like that usually need someone else to look at. Thanks to everyone for your help.

 

[neileg]

By the way, Access does a stupid thing when it builds a form for you - it gives the controls the same name as the bound field. Thus in your other form you have a control called TankID bound to a field called TankID. This can and does cause problems in VBA. It's safer to use a naming convention such as txtTankID, cboMyCombo, etc.

Oh, the other thing is please don't post the same question more than once. It drives people nuts when they find they have duplicated the advice given elsewhere, and you want people on your side when you are asking for help.

 

[DarkProdigy]

Thanks for the tip. I know, I'm sorry for the double post, put my comp lagged when I tried to submit and created 2. Very frustrating working on dial-up.

 

[rsaepa1]

Similar problem

Hi All,
I am having a similar problem with my DB. I have attached a copy of my DB for reference. On the Daily Report Form I would like to enter a date that will filter the Daily Report Query and in turn via the Preview Daily Report cmd button. When I enter the date on my form then press the Report preview button it prompts for a perameter value.

Thanks
Ron

 

[rsaepa1]

Hi All,
I am having a similar problem with my DB. I have attached a copy of my DB for reference. On the Daily Report Form I would like to enter a date that will filter the Daily Report Query and in turn via the Preview Daily Report cmd button. When I enter the date on my form then press the Report preview button it prompts for a perameter value.

Thanks
Ron

 

[ejstefl]

Remove the underscores in your query, since your form and your control don't have them. Change the criteria to [Forms]![Daily Report Form]![Daily Report Date].

 

[rsaepa1]

Thanks, that fixed the problem. I knew that it had to be something easy. I thought that when writing code you were supposed to represent spaces as underscores.

Thanks Again
Ron

 

[ejstefl]

Its best just to avoid spaces all together when you name things.