Pickup Field name from the form

[Phudsen]

Hi,

I we have a Main Library and 3 branches. Let's say that we have a book called "MS Access Unleased", we have 11 copies:

6 Main Library
2 Branch 1
1 Branch 2
2 Branch 3

In my table I have book info fields in addition to the following fields:

Qty
MainLib
Branch1
Branch2
Branch3


When the book is out from Branch1 I want to remove it from Qty and from Branch1 so it becomes

Qty 10
6 MainLib
1 Branch1
1 Branch2
2 Branch3

The branches are fields in the table, they are numeric. In the checkout form I can select from where was the book checked out, so what I want is a query to read the value of the form field named "Location" in which one of the branches will be selected.

In the query grid, the first line is Field: If I do not select from the table I get Expr 1 automatically.

How can I put the value from the Form as a field name in the Field line in the grid without getting Expr 1.

I tried putting this in the Field line of the grid:
Forms![FormName]!Location
It is adding Expr 1: before it.

If I select MainLib, it will be as if I selected MainLib from the table.

I know the setup is wrong, but I got this from a previous employee with. The database has more than 80,000 books.

What happens now is that I can delete from the quantity, but cant delete from the Location.

Thanks

 

[PHV]

You may try something like this:
UPDATE yourTable
SET Qty = Qty - 1
,MainLib = MainLib - IIf(Forms![FormName]![Location]='MainLib', 1, 0)
,Branch1 = Branch1 - IIf(Forms![FormName]![Location]='Branch1', 1, 0)
,Branch2 = Branch2 - IIf(Forms![FormName]![Location]='Branch2', 1, 0)
,Branch3 = Branch3 - IIf(Forms![FormName]![Location]='Branch3', 1, 0)
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Phudsen]

Thank you PHV for your reply.

What if the quantity taken more than one?

So, there is no way to put something in the Field line in the Query grid instead of the field name? It is like having a dynamic query.

If not, I think it is easier to be done via VBA on form level.

Thanks a lot PHV

 

[PHV]

UPDATE yourTable
SET Qty = Qty - [Forms]![FormName]![Quantity]
,MainLib = MainLib - IIf([Forms]![FormName]![Location]='MainLib', [Forms]![FormName]![Quantity], 0)
,Branch1 = Branch1 - IIf([Forms]![FormName]![Location]='Branch1', [Forms]![FormName]![Quantity], 0)
,Branch2 = Branch2 - IIf([Forms]![FormName]![Location]='Branch2', [Forms]![FormName]![Quantity], 0)
,Branch3 = Branch3 - IIf([Forms]![FormName]![Location]='Branch3', [Forms]![FormName]![Quantity], 0)
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Phudsen]

Yes PHV, this will help, thanks a lot, it is fine now.

Just curious. You know in the criteria you can simply put
[Forms]![FormName]![Location]..etc and the criteria will pick up the value in that form field.

Can't we do that in the Field line in the query grid in order to have the field name given by a form?

Thanks a lot for your help "As Usual"

Paulin

 

[PHV]

In a query parameters are only for values, not for keyword nor table name nor dield name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244