Multiple instances of a field (Independant) on one form? (1 Viewer)

[Lyconal]

Hi Guys,

So last question I will need to ask (I think).

I have a form that I want to use for stock transactions, that will require 2 instances of the same fields (Warehouse and Quantity).
I also need this to create '2' records in the history, one for the FROM and one for the TOO.

I think I have the SQL pretty much sorted to do the 2 transactions, its just the multiple instances of the fields, that can work independantly of each other. Is this possible?

Many thanks,

 

[Mr. B]

This shoulds very much like a many-to-many relationship. You may want to revisit your database design. Many-to-many relationships require a third table to create the links.

HTH

 

[Lyconal]

I have no many-to-many relationships.

I require to transfer stock from one location to another.

Because I have multiple locations, and multiple items, I have created a table called warehouseitems, to break to many to many relationship.

I need to be able to transfer stock from one warehouse to another warehouse, and thus require the ability for both fields on a form.

Would I be best using unbound fields, and linking them to the data and then making it update with an APPEND query? (Insert 2 records to history - 1 for removal from location A and one for addition to location B?)

Cheers

 

[Mr. B]

In my opinion, if you are not going to have a third table to track the transfers, you should consider having the following fields:
FromWarehouse
FromQty
ToWarehouse
ToQty

I just do not believe that one field for each will not work.

 

[Lyconal]

I do have a third table, tblTransactionHistory - That tracks all movements (Balance in, Sales, Transfers between locations) - I just require the 2 instances of one field, to work independantly of each other.