Design Question (1 Viewer)

[TVReplay99]

I am creating a Transaction database. The tblStock contains all stock items with StockNo as the primary key field. In the Transactions form the user enters the StockNo and the Description and price field are populated but locked. This, no problem.

My boss now wants to have a 'catch all' StockNo of MISC to allow the user to enter the Description and Price on their own for nonregular items in the storeroom.

Currently I don't save the description in the tblTransDetails Table and don't want to but I can't figure a way around this. I have tried this code:
If Me.StockNo.Value = "MISC" Or "FED" Or "UPS" Or "AIR" Then
Me.Desc.Enabled = True
Me.Desc.Locked = False
Me.Desc.SetFocus

End If

But if there is more than one 'MISC' line item on a transaction then the description is changed for all on the same transaction. I'm not sure I'm on the right track design wise or form wise.

Any suggestions?

Thanks all,
Brian

 

[WayneRyan]

Brian,

I'm a little confused as to why the users enter a StockNo and
a description. That info seems redundant (and more work
for the user).

Since these are "different" types of entries, then you can have
an invisible subform, linked by StockNo, and using the form's
OnCurrent and/or AfterInsert events, you could make it visible
and set focus to it.

This way your new table contains only additional info for
particular types of transactions. The rest won't see it, and
it will give you a way to track them.

One note; the info collected in the new table is likely to be
"non-standard", you won't have consistent product information.

Wayne

 

[TVReplay99]

Wayne,
Thanks for your quick response.

I'm a little confused as to why the users enter a StockNo and

For example: If we order Widget1 and Recieve Sprocket1 we don't want to enter a new StockNO for that item but still want to sell it. User enters MISC and the Description of the item. The descriptin needs to be saved for tracking and reporting reasons. Such as detailed billing.

This way your new table contains only additional info for

I am not exactuly clear on what you mean. Should I create a seperate table for the 'MISC' TransDetails?



Brian

 

[WayneRyan]

Brian,

I'd say yes, create a new table. For most transactions your
current setup appears to suit your needs.

These ambiguous transactions need a SubTable and SubForm
to allow for further clarification.

Misc might even be a box of different items. This is where the
new table comes in because it could be possible to list
each item within the box as its own line item with qty,
description and price. This would allow you to track items
with greater detail.

Just thinking about possibilities.

Wayne

 

[TVReplay99]

Wayne

Thanks, I will try the additional table for the extra transactions. I have had a couple thoughts on the Transactions form. Do you think it wuold be wise to put the Extra Trans form on a tabed form with the current Details form? or have a popup if the StockNo meets Criteria.

frmTransactions
/ \
subfrmTrans Details subfrmMiscTransDetails


Brian