Code for use in Form Navigation 1

[dab1477]

I wrote the code below in order to navigate through a form named "Production Board Data Input". I wrote this code so that I could skip some inputs based upon the Dept or Location input - dont need the all data fields for all Dept selections, but want to capture department and man-hours at a minimum. I ONLY want to enable Shift_input, total_manhours and HPO_input when I select "Maintenance". When I do select "Maintenance" from my Dept_or_location drop down menu, ALL form fields are enabled. What am I doing wrong? Similar issue when I select "Shipping". Thanks.

Private Sub Dept_or_Location_beforeupdate(Cancel As Integer)
With Me
Select Case Me.Dept_or_Location
Case "Maintenance"
.Shift_input.Enabled = True
.Date.Enabled = True
.Total_Good_pcs.Enabled = False
.Total_Reject_or_Scrap.Enabled = False
.Total_Manhours.Enabled = True
.HPO_input.Enabled = False
Case "Shipping"
.Shift_input.Enabled = True
.Date.Enabled = True
.Total_Good_pcs.Enabled = True
.Total_Reject_or_Scrap.Enabled = False
.Total_Manhours.Enabled = True
.HPO_input.Enabled = True
Case "Rough Inspection"
.Shift_input.Enabled = True
.Date.Enabled = True
.Total_Good_pcs.Enabled = True
.Total_Reject_or_Scrap.Enabled = True
.Total_Manhours.Enabled = True
.HPO_input.Enabled = True
Case "Final Finish"
.Shift_input.Enabled = True
.Date.Enabled = True
.Total_Good_pcs.Enabled = True
.Total_Reject_or_Scrap.Enabled = True
.Total_Manhours.Enabled = True
Case "Setup"
.Shift_input.Enabled = True
.Date.Enabled = True
.Total_Good_pcs.Enabled = True
.Total_Reject_or_Scrap.Enabled = True
.Total_Manhours.Enabled = True
End Select
End With
End Sub

 

[MajP]

Any chance you are falling through the select case and leaving the defaults set? In other words lets say the combo had misspelled values "shiping" or "Maintenence". This would fall through. You can check by adding.
case Else
Msgbox Me.Dept_or_Location

 

[dab1477]

No fall through, that I can find. I verifed the combo box spellings (these are a drop-down in Dept_or_Location and are located in DeptLocation table. I also added the suggested code. It returns a msgbox with the ID number from the DeptLocation table (This is the table that feeds the Dept_or_Location on the form). Thanks for the assist. I'm still looking for more help!

 

[PHV]

I'd try something like this:
Select Case Me.Dept_or_Location.Column(1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

In fact the code is "falling through". As PHV points out you are returning the bound column (which I guess is hidden). Your combo returns a value like 1,2.. So the selects are never caught. i.e. 1 will never = "Maintenance".

 

[dab1477]

BINGO! You guys are the best. I guess I don't understand "fall through". I altered the code to include "column(1)" and it works. I don't understand why it would return a hidden bound column. When I view the Dept_Or Location input, I get the opportunity to select from a menu of departments. It works, but I'd like to understand why?

Thanks again. I love success!

 

[MajP]

Often the primary key in a table is numeric such as an autonumber

departmentID DepartmentDescription otherDepartmentFields
1 Accounting
2 Shipping
3 Operations

Then a related table stores the id, but users will want a pulldown that shows "accounting" not 1. So the trick is to make the row source of the combo contain both values, but hide the display of the departmentID and show the description. When the user picks "Accounting" the value of 1 gets stored in the related table.

"Falling through" is a term often used with if thens and selects. Usually it is a logic mistake.

if x > 1 then
do something
else if x < 1 then
do something
end if

If X = 1 it basically "falls through" the check and nothing happens. That may or not be the real intent.