Vary combobox options?

[adh123]

Hi all, not sure if this is best suited here (I anticipate it needs some coding!) or in the forms section, sorry if wrong!

I have a combobox with 13 options. As the customers order is processed I would like a user to be able to update the status based on its current status.

E.g. current status of 8 - Order raised, user can select anything from 3 - finished and awaiting dispatch, to 12 - Cancelled.
Once it is marked as 3 - finished and awaiting dispatch, the accounts team can process the payment from 3 up to status 1 - paid and delivered (would also need the option of 11 & 12 as these are cancellation options).

Finally, option 13 should not be made available throughout the process. The system auto-creates an additional 'general correspondance' status (13) row in the backend when a customer is added, this is for any notes/comments which are unrelated to their order. This one should only have the option of 11-13.

I found a post on here (although I cannot find it again!) which suggested using a range of checkboxes in the backend and the combobox should look at these depending on the current selection. I started building it but have since forgotten the search term I used/lost the link...any suggestions are very much appreciated!

 

[jdraw]

Just thinking and typing while reading your post.
It seems that the status options available for selection are based on the current status of the record. So, the logic involved, in my limited understanding of your environment,
is along this general pattern:

You have known statuses, and for each status there are a fixed number of options.

If status is x then
  options are 1,2,3
elseif status is y then
  options are 3,5,7,9
elseif status is w then
  options are  4,7,8,9
else
  "a error message indicating an unexpected status..."
endif

 

[adh123]

Exactly. Each customer/job record has a current status (all start with "8", their progress along the supply chain here will result in them being given an updated status).

Would you know how I would code this? The combobox is "cmbstatus" and in the background is also the table field it links to "c_status".

EDIT: I've had a go and am not having much luck with the below:

If Me.cmbStatus.Value = "13" Then

Dim StatusGenE As String
StatusGenE = "SELECT [tblDrpEnquiries].[ID]," & _
" [tblDrpEnquiries].[DescriptionFull], " & _
"FROM tbl drpenquiries " & _
"WHERE [Status_gen_e] = -1"

Me.cmbStatus.RowSource = StatusGenE
Me.cmbStatus.Requery

Attached image may help point me in the right direction?

 

[here4real]

If all of your options for different statuses are sequential (it sounds like they are) then you can build a table that for a given status gives a beginning and ending option. Using that lookup table you should be able to build a query that pulls the starting and ending option as criteria (DLookup - you might need a function too to return the value). Let this query be the source for the combobox.

 

[jdraw]

adh,

Do you know about flow charts?
Do you have a written description of all of the conditions and possible outcomes?

You really need to sort out what possibilities exist when you are at status XX.

I have worked on other applications where you had things like:

Preapplication
Application completed
Loan Request
Loan evaluation
Loan Approval
Rejection
Withdrawal
Cancelled
Lack Of Information-Incomplete
Held - Missed Compliance
etc.
All these were related to a Status. And when a "Project" was at a certain ststus it could only move to other specific statuses --and it could be cancelled by the Office, it could be withdrawn by the client at any time.

Search for flow charting on youtube and google.

 

[adh123]

Thanks for the suggestions.

Jdraw, never used flowcharting for access so not sure how this would work? The suggestion of using a query at first glance seems to be a workable option (my view is if it is all in access vba then there is only 1 program to go to if there is an issue - as it will be me who looks after this once it is live within the company!). It has occured to me that this would also be required for the sales team and their process is a little more complex, so thought I would start with this one and see where it leads me. If anyone has any suggestions (or beats me to it!) the diagram attached would be what I am trying to achieve!

 

[jdraw]

There is an intro video here.

I'm not sure what your png is really telling me. It may have all the info, but it should be obvious -- what does it mean to you in plain English.

As for the flowchart, I was think of logic and decision-- he gives an example of automated door.

In your set up If current status is 3, I can go to 5, 6 9
When you go to a new status you update/add something and set the new status...

 

[here4real]

One table is just your users. One table should just be your options. A third table that relates your users to options (one option on each record so you would have 3 entries for user 1, 2 entries for user 2, etc. Create a query that returns the options for a given user. Let that query be the recordsource for your combobox.

 

[adh123]

Not sure I have done this correctly but a basic flowchart is attached. How do I then import/input this into access, struggling to find any reference to this from my searching?

 

[jdraw]

adh,

In very general terms

Do-Status_Checks:
Select Case e_status
   Case 1   
        Option1_routine
   Case 2  
        Option2_routine
   Case   3
        Option3_routine
   Case  4-10
        Option4_10_routine
   Case 11
        Option11_routine
   Case 12
        Option12_routine
   Case 13
        Option13_routine
   Case Else
      MsgBox "Unknown status (" & e_status ") has been encountered "
      Goto ErrorHandler	  
End Select

where
Option1_routine has the details of what you should be doing when e_status =1
Do a validation to ensure all is well for this activity then,Do x,y and z;,Update a Record with a new Date or whatever. other stuff... then set e_status to the appropriate value.

Similarly, for each of the case conditions.

 

[adh123]

Might have a workaround which has bought up a different challenge...

I have created a new table (tblDrpEnqOptions) which links the ID field from tblDrpEnquiries to a range of available options (see attached screenshot).

Current_status_short and new_status_number are both data type "number".

I have added the current status to a text box on the form this is required (this will be hidden eventually). This displays the number from tblDrpEnquiries table upon the form loading.

The combobox now looks at tblEnqOptions which then should display the available options based on the code below:

cmbe_new_status.RowSource = "Select tblDrpEnqOptions.new_status_number " & _
    "FROM tblDrpEnqOptions " & _
    "WHERE tblDrpEnqOptions.current_status_no = '" & Me.txtE_Status.Value & "' " & _
    "ORDER BY tblDrpEnqOptions.ID;"

Only problem is I get an error now when opening the combobox of 'Data type mismatch in query expression'.

If i move the code to an after update event I can view the available options in the combobox on form load (but it does not filter to the correct options, it just shows all).

As far as I can tell all are numbers, would the fact that txte_status is a textbox create any issues here?

 

[gemma-the-husky]

I would have another table showing permitted status changes

eg, existing status 4, allows new statuses of 7 and 8.

then you can change it with no code change being required. just modify the status change table.

(I think that's what you have done)

 

[gemma-the-husky]

cmbe_new_status.RowSource = "Select tblDrpEnqOptions.new_status_number " & _
    "FROM tblDrpEnqOptions " & _
[B]   "WHERE tblDrpEnqOptions.current_status_no = '" & Me.txtE_Status.Value & "' " & _[/B]
    "ORDER BY tblDrpEnqOptions.ID;"

because status_no is numeric, this bit should be this. you shouldn't need the 'me's and the 'value's

"WHERE tblDrpEnqOptions.current_status_no = " & txtE_Status

 

[adh123]

Thanks, I seem to be getting somewhere.

Probably a minor issue but is not obvious to me, the combobox now updates on focus so the dropdown list reflects the options available but only shows the 'new status' column from the previous posted table image. The short/long/full description columns all appear blank?

 

[RainLover]

Your Status appears to fall into a group.

Say

6 = "A"
7 = "A"
9 = "A"

"A" should become an additional field in the source table.

If 7 is selected then the next selection is restricted to 6, 7 or 9.

This becomes a simple criteria in the Query that feeds the Combo.

Is my approach too simplistic?

 

[RainLover]

HereForReal in post number 8 is along the lines I was thinking.

 

[adh123]

Your Status appears to fall into a group.

Say

6 = "A"
7 = "A"
9 = "A"

"A" should become an additional field in the source table.

If 7 is selected then the next selection is restricted to 6, 7 or 9.

This becomes a simple criteria in the Query that feeds the Combo.

Is my approach too simplistic?

Thanks for the feedback, as in the previous post I have found a way to do this (which is as Here4Real suggested) and now having an issue with what appears in the columns of the combobox (only the ID number appears, the other selected columns are blank). If I could get some guidance as to where I am going wrong or why columns may not pull through then that would be a great help!

 

[RainLover]

What happens or goes wrong when you try your solution?

Back tomorrow.

 

[adh123]

the combobox now updates on focus so the dropdown list reflects the options available (based on the current status textbox) but only shows the 'new status' column from the previous posted table image. The short/long/full description columns all appear blank?

 

[here4real]

I disagree with the way you are going about this. It should ALL be table driven, not code driven. I am attaching a sample DB. When you change the criteria on the join in the query, the contents of the combobox will change as well.