Using one dropdown list box to display a selecting in another dropdown list box

[Mast0645]

I have a form where I have two drop down list box.
The first list box is called Transaction_Type. It contains three values: Created, Allocated and Sold

The second list box is called Product_Status. It contains 6 items: Allocated, Produced, Reworked, Shipped, To Be Produced, Unallocated.

I have a products form. When a user created more inventory they will selected in the drop down list create, then a quantity. Then I would like the status of the product to update to "Unallocated".

When the user placed an order but doesn't finish it they will choose the status of the inventory to be allocated so I would like the product status to be updated to allocated automatically.

They other status the user will choose them self and do not need to be linked to each other.

In my vba code I have tried with the OnClick and AfterUpdate sub procedures with the following code.


If Me.Transaction_Type.Value = "Created" Then
Me.Product_Status.Value = "Unallocated"
End If

If Me.Transaction_Type.Value = "Allocated" Then
Me.Product_Status.Value = "Allocated"
End If

Yes when i select "created" from the drop down list it does not change product_status to say "unallocated"

Any suggestions.

Thanks
(in using access 2007)

 

[pbaldy]

I'd use the after update event. Are you sure the bound column of the first combo contains the text "Created" rather than an ID field?

 

[Mast0645]

I am not sure if I have it bound. I am still learning access and vba code.
The two "combo boxes" are part of a subform (referencing a table, those fields are linked to other tables which populate them with drop down options).

So my main form is Products and the sub form deals with the inventory transactions of a product.

Should i have the values for the drop down list not referencing other tables but just values i type in when creating the table field property?

Does that make a difference?

 

[pbaldy]

No, getting the values for the drop down list from a table is fine, even recommended. If that table has both an ID field and a text field, you may want to test for the ID number of "Created" rather than the text. Like:

If Me.Transaction_Type.Value = 1 Then

 

[Mast0645]

I will try that thank you, ill let you know if that works or not.

 

[pbaldy]

No problem. Alternatively, you can test a different column of the combo:

If Me.Transaction_Type.Column(1) = "Created" Then

The column property is zero based, so 1 is the second column.

 

[Mast0645]

If Me.Transaction_Type.Value = 1 Then
Me.Product_Status.Value = "Unallocated"
End If


This made the code work.
Thank you so much.

 

[pbaldy]

Happy to help and welcome to the site by the way!

 

[Mast0645]

Thank you,
I also have another question. I don't know if it is possible to do.

I am using the same combo box Product_Status.

I want the user to be reminded when they leave the Product status blank. I created a new item to the list box called Status Assignment Needed.

I tried using the Form Load event and open but i dont think I am writing the code the right way.

If the combo box is null, I want the Status Assignment Needed value to be displayed from the list.

I had
If Me.Product_Status.Value = " " Then
Me.Product_Status.Value = "Status Assignment Needed"
End If

I am unsure if you can reference it item being blank (not choose) then tell it to show a value, but that is what I want it to do.

Also tried
Me.Product_Status.Value = Null Then
Me.Product_Status.Value = "Status Assignment Needed"

Me.Product_Status.Value = -1 Then
Me.Product_Status.Value = "Status Assignment Needed"




Any thoughts?
Thanks.

 

[pbaldy]

Try

If IsNull(Me.Product_Status.Value) Then

 

[bob fitz]

I think this validation code should be in the forms Before Update event rather than the forms Open or Load events as mentioned by the OP

 

[Mast0645]

I got it to do some of them but then I changed the event to Before Update and things stopped working. I switched it back to both Open, Current and Load events and now nothing updates in the combo box.

Unsure why it worked only in some boxes for a moment or two.

 

[bob fitz]

Perhaps you can show us the code you have

 

[pbaldy]

I got it to do some of them but then I changed the event to Before Update and things stopped working. I switched it back to both Open, Current and Load events and now nothing updates in the combo box.

Unsure why it worked only in some boxes for a moment or two.

They may contain a zero length string. This will test for both:

If Len(Me.Product_Status.Value & vbNullString) = 0 Then

 

[Mast0645]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Product_Status.Value) Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub

Private Sub Form_Current()
If IsNull(Me.Product_Status.Value) Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub


Private Sub Form_Load()
If IsNull(Me.Product_Status.Value) Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub


Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.Product_Status.Value) Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub
These were what I tried.

Going to try "If Len(Me.Product_Status.Value & vbNullString) = 0 Then" now



Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.Product_Status.Value & vbNullString) = 0 Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub

Private Sub Form_Load()
If Len(Me.Product_Status.Value & vbNullString) = 0 Then
Me.Product_Status.Value = "Status Assignment Needed"
End If
End Sub

Tired all with different events for the page and still nothing. Hmmm...
I have a form called Products, It has a subform Inventory
I am updated the code for the Inventory on the subform. I dont know if the location makes a difference.

Product_Status is in my Products Table. Product_Status is liked to ItemStatus table. Which list all my status types for the combo box. It does not have index numbers, the actual status are the key values.
Dont know if this background information helps.

 

[pbaldy]

If you want to stop the update you need Cancel = True:

http://www.baldyweb.com/BeforeUpdate.htm