ACCESS FEED: Re: [MS_AccessPros] To query or not to query, that is the question

Kamis, 31 Desember 2015

Re: [MS_AccessPros] To query or not to query, that is the question

Dear robejohg (name?)-

If there can be multiple plans associated with a row in tblStockMovement, then you need another table:

tblStockMovementPlans

StockMovement_ID

Plan_ID

.. other fields as appropriate.

Then you need a subform for this table on your form that edits tblStockMovement - linked on StockMovement_ID. Then a subform within that to record the answers for the questions related to the plan. I'm thinking tblTheAnswers also needs Plan_ID so that you can link it directly back to tblStockMovementPlans.

I gather that tblThePlan and tblTheQuestions are pre-loaded. When you set the Plan_ID in tblStockMovementPlans, that can link to tblTheAnswers on that Plan_ID, then you pull the questions in a combo box that has a Row Source from tblTheQuestions filtered on the selected Plan. The user would select the questions one at a time, then fill in the AnsChkBx and txtTheQuestions fields. You might consider some code to ensure that all the questions related to a selected plan have an answer before you allow the user to navigate to another Plan or another StockMovement.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Dec 31, 2015, at 1:19 PM, robejohg@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to set up a form that I can "justify" the movement of inventory. I have 5 tables:

The Stock		The Plan
tblTheStock	tblStockMovement	tblThePlan	tblTheQuestions	tblTheAnswers
Stock_ID	StockMovement_ID	Plan_ID	TheQuestions_ID	TheAnswers_ID
txtStockCode	Stock_ID	txtPlanName	Plan_ID	TheQuestions_ID
Various others	txtMovementType	Various others	txtTheQuestions	AnsChkBx (Y/N/NA)
	Various others			txtTheAnswer
	Plan_1
	Plan_2
	Plan_???

The goal is:

For every StockMovement to have a minimum of 1 Plan. Would like to have the ability to have more.
frmStockMovement
- tblTheStock fields as required
- tblStockMovement fields as required
- Button(s) to open the frmThePlan to update txtTheAnswer or AnsChkBx as required.
frmThePlan would have:
- txtPlanName (This cannot be modified)
- txtStockCode (This cannot be modified)
- Various others from tblTheStock, or tblStockMovement (This cannot be modified)
- sfrm QuestionsAnswers
  - txtTheQuestions (This cannot be modified)
  - txtTheAnswer (This can be changed)
  - AnsChkBx (Y/N/NA) (This can be changed)

My problem is setting up the frmThePlan . I cannot get the sfrm QuestionsAnswers to work. Trying to get:

The Plan:	PlanName
The Stock	StockName
Various others	tblStockMovement

Subform
txtTheQuestions		txtTheAnswer	AnsChkBx
Question 1(		This	Y
Question 2		that	N
Question …..		The other	N/A