ERROR Trapping in subform - Tricky

[benjee]

HI,

I have a subform that uses unbound controls to calculate a Subtotal from various text boxes and combo boxes.

However i am trying to implement some error trapping because i dont want certain values from each combo box being calculated together.

The basic scenario is ComboBox2 contains values x, y, and z, and ComboBox4 contains values a, b, and c. Now, associated to each value is a cost.

Now if the user selects 'a' and 'x' from each respective combo box thats fine, but some values i don't want to be calculated together i.e 'a' and 'z'. Note: its not that 'a' and 'z' cant be calculated together its just that it wouldnt make sense to the user if they were if you get my drift!

Can anyone help?

 

[bjackson]

you could use select case in the after update
event for each combo box

select case combobox2

case x
select case combobox4
case a
msgbox"bad choice"

case b
msgbox "good choice"

case c
etc etc

end select
case y

select case combobox4
case a
msgbox"bad choice"

case b
msgbox "good choice"

case c
etc etc

end select
case l,p,g,d

select case combobox4
case a
msgbox"bad choice"

case b
msgbox "good choice"

case c
etc etc

end select
end select

 

[Oldsoftboss]

You could base the second combo box on a query that only allows certain values based on what is selected in the first combo box.
I have posted a sample Db at http://users.impulse.net.au/predator/accessdownloads.htm called combosamples that might help to step you thruogh what I am talking about.
HTH
Dave

 

[benjee]

Thanks Dave and bjackson,

Just browsing through the forms from the link you sent me and i think 'How one list box changes the contents of another by a query' is the most appropriate.

Going to try that today.

 

[benjee]

Hey,

Ive come to abit of a halt on my error trapping. I can relate Dave's link to the form "How One List Box Changes the Contents of Another by query" to my subform closely but problems occur when trying to replicate it, especially as it kind of conflicts with my original unbound controls.

im not entirely sure how to implement the query part.

Ive attached a Access97 test form that gives the basic structure and operation of my form. Just to clarify (ignore the characterists of the values), if i select 'Face Brick' from the first list box, i want only 'english' and 'flemish' values to appear in the second list box.

This is because its impossible to have a facing brick with either a 'stretcher' or 'victorian' facing.

thankyou,

 

[Rich]

You need to add the PK from the first table as the foreign key in the second, and define the relationship as One to Many

 

[benjee]

Rich hi, the PK & FK issue has been sorted as you suggested. I don't want to keep bumping this but its annoying me!

My procedure and query are displayed, i think i need to add some IF statements to the procedure i.e. if i select 'Face Brick' from the first list box, i want only 'english' and 'flemish' values to appear in the second list box, but im really not sure how to do that.

Procedure: -

Private Sub List0_AfterUpdate()
If Me![List0] = "<All>" Then
Me![Combo13].RowSource = "Select [fldJointDesc],[fldCost],[flID] From [tblJoint]"
Else
Me![Combo13].RowSource = "Select [fldJointDesc],[fldCost],[flID] From [tblJoint] WHERE fldJobDesc = '" & Me![List0] & "'"
End If
End Sub

'Private Sub List0_AfterUpdate()
'Me.Recalc
'End Sub

Query: -

SELECT tblJoint.fldJointDesc, tblJoint.fldCost, tblJoint.[flID] FROM tblJoint WHERE ((tblJoint.fldJointDesc=[Forms]![Form1]![List0]));

Ive attached the latest db if you need it.

 

[Rich]

Hopefully you can see the changes made, you need a value for the FK's in the child table, the easiest way to enter them is via a form/subform arrangement.
Since face brick can have two types associated with it then your subform will have the two facings entered.
HTH

 

[benjee]

Am i on the right track? i understand what you are getting at.

I have created a subform based on the tblJoints table including the FK field.

2 things, does that mean i delete the second combo box?

and, when i try and set the Link Master and Child fields to flID it tells me i cannot build a link between unbound forms?

Attachment:-

 

[Rich]

No we're getting confused, you need some method of entering the joints for each item.
I've very quickly built the form/subform using the wizard.
Form1. I've changed the references to include the currency fields in the calculation.
I have removed the code in the after update of the first combo, it's unnecessary.

 

[Rich]

It does help if you actually attach a file

 

[benjee]

was there suppose to be an attachment to your last thread chap?

 

[Rich]

No, the one after it

 

[Oldsoftboss]

Thanks for helping Rich. The network server has been down and I haven't been able to get back
Dave