help required in designing an inventory form

[vandy]

Hi All,

I am struggling with a basic logic when i am designing the final transaction
form


I have 3 tables;

tblprojects:
ProjectID-autonumber pk
pno-project no
pname - project name

tblItems:
itemID - autonumber pk
itemno- item no
itemDesc
Uom-unit of measure

tbltransactions:
TransactionID-autonumber pk
PID- project id FK linked to tblproject projectID
TranItemID - itemid FK linked to tblitems itemID
DOR- Date of Receipt
UnitsReceived - Qty received
Location - location
UnitsUsed - Units Used
DOI- Date of Issue

I have created 3 forms.
Adding new projects
Adding new items
Adding new inventory order

I dont know how to link the date such that i can select a project a
particular item from the itemtable and fill in the corresponding inventory
transaction for that item.

I created 2 combo boxes to select projectno and itemno and opened a subform
for inventory transactions. The pID and TranItemID are saved as 0. how to
have linked data from all three tables synchronizing to be able to enter the
inventory transaction for that item.

Bear with me this seems to be a very fundamental and basic question but I am
a newbie and would appreciate some direction.

thanks in advance

 

[Bob Quintal]

Hi All,

I am struggling with a basic logic when i am designing the final
transaction form


I have 3 tables;

tblprojects:
ProjectID-autonumber pk
pno-project no
pname - project name

tblItems:
itemID - autonumber pk
itemno- item no
itemDesc
Uom-unit of measure

tbltransactions:
TransactionID-autonumber pk
PID- project id FK linked to tblproject projectID
TranItemID - itemid FK linked to tblitems itemID
DOR- Date of Receipt
UnitsReceived - Qty received
Location - location
UnitsUsed - Units Used
DOI- Date of Issue

I have created 3 forms.
Adding new projects
Adding new items
Adding new inventory order

I dont know how to link the date such that i can select a project
a particular item from the itemtable and fill in the corresponding
inventory transaction for that item.

I created 2 combo boxes to select projectno and itemno and opened
a subform for inventory transactions. The pID and TranItemID are
saved as 0. how to have linked data from all three tables
synchronizing to be able to enter the inventory transaction for
that item.

Bear with me this seems to be a very fundamental and basic
question but I am a newbie and would appreciate some direction.

thanks in advance

In the inventory order form, your subform should be synchronized to
the two comboboxes, using the link parent fields and link child
fields.The link parent fields in this case would not be fields, but
the comboboxes.

I'd also suggest that the inventory transactions table be
restructured, a far more versatile setup would be

tbltransactions:
TransactionID-autonumber pk
PID- project id FK linked to tblproject projectID
TranItemID - itemid FK linked to tblitems itemID
DOT- Date of Transaction
tblTransaction.Units - Qty
tblTransaction.type - receipt (+1) or Issue.(-1)

Reason, you only have one date field to sort on. which will make
things simpler and Current Inventory will be
=sum(UnitsTransacted*type)

 

[Bob Quintal]

Thanks for your reply. I have restructured the transaction table
to reflect the changes. What should be the data attribute of "
type"- as a yes/ no field or a number.

Type should be a number 1 for receiving, -1 for removing items from
inventory. That way, qty is always a positive number, but
multiplying by type gives the correct mathematical value.

In the design i have to have in my main form 2 combo boxes one for
project and the other for items

I have to have a subform which is linked to the 2 combo box with
parent child link. I did not understand this statement.
""
The link parent fields in this case would not be fields, but
the comboboxes. ""

When one creates a subform, there is a relationship set between the
parent form and the child form.. usually they are field(s) in the
parent and equivalent field(s) in the subform. In your case, you do
not need a table in the parent form, you are using comboboxes.
The link allows to show only the transactions where the part number
is the same as the part number in the combo box, and to
automatically fill in the part number when you create a new
transaction. Project is similar.

You see the link fields in the Data Properties menu for the subform
control.

Can we use a combobox to link can you give me some examples.
Thanks for your time and i will try the above.

In the inventory order form, your subform should be synchronized
to the two comboboxes, using the link parent fields and link
child fields.The link parent fields in this case would not be
fields, but the comboboxes.

I'd also suggest that the inventory transactions table be
restructured, a far more versatile setup would be

tbltransactions:
TransactionID-autonumber pk
PID- project id FK linked to tblproject projectID
TranItemID - itemid FK linked to tblitems itemID
DOT- Date of Transaction
tblTransaction.Units - Qty
tblTransaction.type - receipt (+1) or Issue.(-1)

Reason, you only have one date field to sort on. which will make
things simpler and Current Inventory will be
=sum(UnitsTransacted*type)

 

[vandy]

Thanks for your reply. I have restructured the transaction table to reflect
the changes. What should be the data attribute of " type"- as a yes/ no
field or a number.

In the design i have to have in my main form 2 combo boxes one for project
and the other for items

I have to have a subform which is linked to the 2 combo box with parent
child link. I did not understand this statement.
""
The link parent fields in this case would not be fields, but
the comboboxes. ""

Can we use a combobox to link can you give me some examples. Thanks for your
time and i will try the above.

 

[vandy]

Hi Bob,

Thanks for taking the time to reply. As i understand it in the inventory
order form the the user will enter +1 if the item is receive or -1 if it is
issued.

Coming to the 2 combo boxes. My main form is bound to the tbltransaction . I
have the child subform calling up values of tbltransaction. I dont see any
option to set the child and parent link without setting a field value from
the table. Do i have to write an sql query. I am quite new to access so any
help would be greatly appreciated. thanks for your time

Thanks for your reply. I have restructured the transaction table
to reflect the changes. What should be the data attribute of "
type"- as a yes/ no field or a number.

Type should be a number 1 for receiving, -1 for removing items from
inventory. That way, qty is always a positive number, but
multiplying by type gives the correct mathematical value.

In the design i have to have in my main form 2 combo boxes one for
project and the other for items

I have to have a subform which is linked to the 2 combo box with
parent child link. I did not understand this statement.
""
The link parent fields in this case would not be fields, but
the comboboxes. ""

When one creates a subform, there is a relationship set between the
parent form and the child form.. usually they are field(s) in the
parent and equivalent field(s) in the subform. In your case, you do
not need a table in the parent form, you are using comboboxes.
The link allows to show only the transactions where the part number
is the same as the part number in the combo box, and to
automatically fill in the part number when you create a new
transaction. Project is similar.

You see the link fields in the Data Properties menu for the subform
control.

 

[Bob Quintal]

Hi Bob,

Thanks for taking the time to reply. As i understand it in the
inventory order form the the user will enter +1 if the item is
receive or -1 if it is issued.

You can make data entry user-friendly by creating a combobox with
the values -1;"Issued";1;"Received", having 2 rows and two columns.
You can make the number column 0" wide.
Or you can use an option group, with labels Issued, Received, and
their values the numbers.

Coming to the 2 combo boxes. My main form is bound to the
tbltransaction . I have the child subform calling up values of
tbltransaction. I dont see any option to set the child and parent
link without setting a field value from the table. Do i have to
write an sql query. I am quite new to access so any help would be
greatly appreciated. thanks for your time

You do see the link master fields row in the subform's data
properties?

Ignore that it says fields, just type the name of the comboboxes,
separated by a comma or semicolon. In the link child fields, type
the names of the textboxes or fields, again with a comma or
semicolon between.

Type should be a number 1 for receiving, -1 for removing items
from inventory. That way, qty is always a positive number, but
multiplying by type gives the correct mathematical value.



When one creates a subform, there is a relationship set between
the parent form and the child form.. usually they are field(s) in
the parent and equivalent field(s) in the subform. In your case,
you do not need a table in the parent form, you are using
comboboxes. The link allows to show only the transactions where
the part number is the same as the part number in the combo box,
and to automatically fill in the part number when you create a
new transaction. Project is similar.

You see the link fields in the Data Properties menu for the
subform control.

 

[vandy]

Bob Thanks a ton. I did not know we can use a combo box to link.
Your reply was very helpful.

thanks

Hi Bob,

Thanks for taking the time to reply. As i understand it in the
inventory order form the the user will enter +1 if the item is
receive or -1 if it is issued.

You can make data entry user-friendly by creating a combobox with
the values -1;"Issued";1;"Received", having 2 rows and two columns.
You can make the number column 0" wide.
Or you can use an option group, with labels Issued, Received, and
their values the numbers.

Coming to the 2 combo boxes. My main form is bound to the
tbltransaction . I have the child subform calling up values of
tbltransaction. I dont see any option to set the child and parent
link without setting a field value from the table. Do i have to
write an sql query. I am quite new to access so any help would be
greatly appreciated. thanks for your time

You do see the link master fields row in the subform's data
properties?

Ignore that it says fields, just type the name of the comboboxes,
separated by a comma or semicolon. In the link child fields, type
the names of the textboxes or fields, again with a comma or
semicolon between.