Docmd.browseto where condition

[mjdemaris]

Hi all.



I have a Nav form (Main), with a subform (Parts) which also has a subform datasheet, (PartDS). I have a button (Discontinued) that uses the browseto command in a macro to show all the discontinued parts in the DS. Another one switches this back to all parts.

I need to add another that allows me to see which parts are in locations 1, 2 or 3. In this case, PIT is one location. I've tried using a macro as before, then I went to VBA. Both of them give me a pop up dialog box that says: Enter Parameter Value ? And under that: PIT, and a textbox for input.
If I type in the text, I get what I want. But why is this happening? I just added the location field (Warehouse) today to add in this functionality for the Parts Manager.

Thanks,

Mike.

[mjdemaris]

Well, I haven't got it to work yet. I've tried using:

"[table].[field] = " & "PIT"
"[field] = " & "PIT"
"'PIT'"
and others.

i've also tried using a string variable: strArea = "PIT"
"[table].[field] = " & strArea

but I still get the box.

It seems like a simple text/string criteria should work!

[Perceptus]

If you just use [field] instead of [table].[field] and you have multiple tables involved. Access may ask you to specify the parameter value of it when you execute the query. Can you make a query that returns all inventory for all locations? then you could use that query to drive data and filter that data accordingly.

[mjdemaris]

Well, I am using a query as the source object of the subform control. I want to be able to view all inventory, and filter down to specific storage areas (such as PIT), view discontinued items, and then change the data so the manager can check the latest quantity on hand and make adjustments to the quantities, if someone just grabbed a bunch of parts without signing them out.

The problem I ran into is that a query using Totals cannot be updated, so trying to use a calculation in the datasheet query and allowing the manager to change data does not work, yet.

So, I decided to use multiple datasheets and queries instead of just one, to see if this would help. And I still have that problem of filtering the DS down.

The code for this is:

Me.DS.SourceObject = "F-AllPartsDS"
    DoCmd.BrowseTo acBrowseToForm, "F-AllPartsDS", "F-Main.NavigationSubform>F-Parts.DS", "[T-Bins].[Warehouse] = " & areaSelect, , acFormEdit

Thanks

[mjdemaris]

Also, how would I filter a query as you suggest, and still allow it to show all inventory?

[Perceptus]

Create the query as you would normally, a select query. Then for the column you wish to filter you can enter a object reference to field which has you wish. like
Select * from tblTables where tblTables.myField = [forms]![frmBossForm].form!FieldName

then the query can run and pull in the values from that field.

There are other ways using vba that you can also use parameters but may not give you what you want.