Combo Box Values

[Cyfardda]

I have a form used for making quotations. It includes a combo box for recording the quotation status. The values include "Pending", "Approved", "Quoted","Declined Client" and "Bound". For ease of maintenance, rather than define these values as a value list, I have put them in a single field table.

For audit trail reasons, I have written VBA code to lock most of the fields on the form and its tabs and subforms if something is quoted. An exception is the Quotation Status combo box. However, I want to restrict the values that may be selected in the combo box after something has been quoted. To achieve this, I made a second table as the source of the values and wrote an If statement to change the table that was the record source once something has been quoted.

Unfortunately, this has resulted in the list not appearing in the combo box. Even though all the properties appear to be OK, I only see one value and it can be deleted or modified, which shouldn't happen.

Can anybody tell me how to fix this?

 

[CEH]

First, you don't need, or want a second table, just a second query.
Take a look at this code... it should show you want you want..... This is to open a form that allows a combo to only view "Active" salesmen or all salesmen depending on what view the form is opened in..........

Private Sub Form_Open(Cancel As Integer)
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT tblSalesman.SalesmanID, tblSalesman.SalesmanName, tblSalesman.Active FROM tblSalesman WHERE (((tblSalesman.Active)=True));"

strSQL2 = "SELECT tblSalesman.SalesmanID, tblSalesman.SalesmanName FROM tblSalesman;"



If Form.AllowEdits = True Then
Me.cboSalesmanID.RowSource = strSQL1
Else
Me.cboSalesmanID.RowSource = strSQL2
End If
End Sub

I think that should point you in the right direction.

 

[Cyfardda]

Thanks for the prompt reply. However, I think I may have given the wrong impression that this was a simple form relying only on two tables. It isn't. It's a very complex form with 5 tabs, each of which contains at least one sub-form. There are about 20 tables directly linked to the form (and a lot more indirectly involved), about 50 queries and 100 pages of VBA code.

The problem I am having relates specifically to the desire to have two alternative sets of permissible values in a combo box depending upon the status of the quotation and the fact that what seemed a simple answer is generating a malfunction that I can't explain.

 

[pbaldy]

I'm not sure it's the same, but I have a car maintenance system that includes a status field for a given repair. A business requirement was that certain status changes were not allowed. In other words, I can go from status "A" to status "B", but I can't go from "B" to "A". I used the before update event of the combo to examine the two values (.OldValue and .Value),

Cancel = True

to stop the change and a message box to inform the user when they attempted an unauthorized change.