How can user enter info on one-to-many query/form?

[bllgnn]

I have a form based on a query that shows PartID, UnitsIn, UnitsOut and, a calculated field, UnitsOnHand from a query with tblParts and tblTransactions with a one-to-many join on PartID.

The form shows the data on one row per PartID.

I want the user to be able to enter the actual physical inventory count on the form, beside the PartsOnHand field, in a text box and have the form show the difference between the UnitsOnHand and the ActualCount.

I could transfer it to a spreadsheet, but I want to use the user data later in the program.

I am a newbie who has done a little DAO, but this has me stumped.

Can anyone please tell me how to do this?

 

[willir]

bllgnn

Main form / subform is perfect for this. In fact, depending on design, a contineous subform works.

 

[bllgnn]

willir

Thanks. I'm not sure what you mean. What whould be on the parent form and what would would be on the child form?

 

[willir]

Assuming your main form is based on tblTransactions and your subform uses a contineous form based on tblParts. The primary key for Transactions is TransID. The Parts record has a primary key PartsID, and a foreign key TransID. (Do I have that right?)

Build your main form on tblTransactions - use of the wizard is fine. Leave space at the bottom. We will call this form frmTransactions.

Build your subform based on tblParts. Do use the wizard, and select the "Tabular" form. We will call this form sbfrmParts.

Have the frmTransactions open in design mode. Click and drag the sbfrmParts from the database window to a spot on the frmTransactions.

Adjust the placement of the subform.

Make sure the properties window is open. ("View" -> "Properties"). Select some location on the main form, and then select the embedded subform. Select the data tab on the property window.

Link Child Dields: TransID
Link Master Dields: TransID

The primary field on the main form and the foreign key on the child form will be the same.

Now when you create / retrieve a transaction, all assoicated Parts records will be create / displayed on the subform.

Richard

 

[bllgnn]

willim

Thanks. Your main/subform would be great for viewing all the parts in a transaction.

In my reference to the one-to-many join, I mean all the transactions with the same PartID are being added together to create one row of data with PartID, Sum Of PartsIn, Sum Of PartsOut and PartsOnHand:[Sum of PartsIn]-[Sum Of PartsOut].

Even in a subform, it won't let me add data to the transaction record, because the fields are a sum of records.

I want to allow the user to input data in a text box close to the data on the row showing transactions in order to allow the program to calculate the difference between the PartsOnHand field and the user field.

Should there be another table? Another query?

Is this possible to do in Access?

 

[willir]

bllgnn

Yes, most is possible, and usually with more than one solution.

But your situation is a little different than epxected. (I have to make assumptions when providing direction - sometimes I am right and sometimes, not) I was basing my response on your statment that you have a one-to-many (1:M).

It would help, if I knew what you were attempting, and your table structure - hard to advise if the info is thin.

I will say one thing. With a 1:M in a query, you usually can only edit on one side or the other. Secondly, for a 1:M, records on the "many" side depend on the "1" side - you need an invoice header before creating an invoice detail; you need a family name before you can have a family.

The following are links to documentation on relationships...

http://r937.com/relational.html

http://www.abcdataworks.com/fundamentals.htm

Richard

 

[bllgnn]

willir,

Yes, and that is why I came to this great forum for advice.

I have described, in simplist terms, what I want to do,

Maybe someone else has some ideas?

 

[willir]

bllgnn

Simplest as possible...

tblParts
PartID - primary key
ActualCount
UnitsOnHand (calculated field)


tblTransactions
TransID - primary key
PartID - foreign key
UnitsIn
UnitsOut

Okay, I revisted your posting, and I am going to assume the above is your design. This makes more sense with the "many" side on the Transaction table.

Since you can only "edit" on side of a relationship, having the transaction table and the parts table in the same query might make entry a little awkward.

Consider the following...
Have two contineous subforms in a form - one for parts and one for transactions. You may be able to display the caculated quantity on hand in the parts subform, or may have to depict this on the main form.

In the Parts subform, when the end user clicks on the record for a specific part, the follownig happens...
- the transaction subform is updated to reflect transactions for the current part.
- the calculated quantity on hand is calculated and displayed. I am hoping that this would work on the subform.
- the end user can then enter the actual count.

Does this sound like it would meet your needs?

Richard

 

[bllgnn]

Richard,

That is the road I will follow if my current way fails.

As you said earlier, there is usually more than one solution.

I realized I was dealing with a snaphot of transactions that cannot be edited, so I created a new table from the snaspshot, added a field for user input and and then let the user edit the ActualValue field on a form. It will get me by for now, and I think i can make that happen automatically, using DAO.

Thanks again,

Bill