Autopopulate a Criteria in a Report

[rkrause]

I have a report that pulls up a criteria for a location. my locations are pretty detail for example A'ls Body Shop. But if you dont type that fully the way it is in the table it wont pull up any data. Is there a way in my criteria in my query, so that if they type an "A" that it brings up Al's Body Shop, ect...

 

[pbaldy]

If you're using something like:

[Enter blah]

in your criteria then no. I would have the query get the criteria from a form, using a combo box that got its selections from your locations table. Then your criteria is:

Forms!FormName.ComboName

 

[rkrause]

When I do it like that, i get the same thing, just [Forms]![frmLocation].[Combo0] that in the box instead of [Enter a Vendor]...

 

[pbaldy]

Is the form open when the query runs? It needs to be.

 

[rkrause]

Yes it was open, it still not working. I have the form open. Run the query and nothing comes up for results.

 

[pbaldy]

Can you post the db? First, make sure you don't have a different bound column in the combo than you expect in the query. In other words, the combo bound to an ID field but the query looking for the name.

 

[rkrause]

How can i post the DB on here? its bout 24MB? Do i need the combo box pointing to the field in the query or the field in the table?

 

[rkrause]

Another thing is, when i have the form closed i get the criteria to come up like this [Forms]![frmLocation].[Combo0], but when i have the FORM open i get just my date criteria.

When it comes up like this [Forms]![frmLocation].[Combo0] and i type in what im lookin for it works, but if i have the form open, and i type in my date i get no results.

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[rkrause]

IM using access 2007, i cant find
TOOLS > DATABASE UTILITIES > COMPACT AND REPAIR

 

[pbaldy]

In 2007:

Microsoft Office Button | Manage | Compact and Repair Database

 

[rkrause]

here it is. the report im using is called detail by vendor. thats the query name too. Form name is called frmLocation

 

[pbaldy]

First, make sure you don't have a different bound column in the combo than you expect in the query. In other words, the combo bound to an ID field but the query looking for the name.

Your combo is bound to the ID field, but the query is looking for the name.

Personally, I'd also put 2 textboxes on the form for the dates and have the query get them from the form too.

 

[rkrause]

Ok How do i fix this? and add the dates?

 

[pbaldy]

Well, either change the bound column of the combo to the name column or change the field in the query to the ID field. I now see that you don't populate the ID field, so I guess option 1 is your only choice. Realistically, you should be storing the location ID rather than the name.

As to adding the dates, the same way you did the location. Add 2 textboxes and point the criteria to them.

 

[rkrause]

Seems easy enough, but in the properties it says bound column, and i have a 1 in there? how do i know what to change it to? Or how do i change the bound column?

 

[pbaldy]

The bound column property is related to the SQL string that is the combo's rowsource. You want the number corresponding to the column containing the desired data.

 

[rkrause]

so if i have only 2 columns in the table im using, shouldnt i be ok to put a 2 in there? And it still dont work?

 

[pbaldy]

I changed the bound column to 2 and it works for me. Because you have no other controls on that form, make sure you select a location by clicking on it, otherwise the combo won't actually be updated. In a production app you'd be clicking on a button to open a report or something, and that wouldn't be an issue.

 

[rkrause]

so after you changed it to 2, whatwere the steps you did? have the form open and run the report? what was the criteria for that field in the querey?