Drop downs in vba

[stuartandwil]

A friend helped me with the code below which when packer drop down is selected adjusts the area to suit which packer was selected. Packer 1 and 3 have 10 spouts and packer 2 as 14 spouts. When I alter the drop down I can see that the selection in row source changes to suit the number of spouts in the properties box. However the spout selections do not appear in the actual drop down box. Any ideas ?
:wink:

Option Compare Database
Option Explicit

Dim packerlist As String
Dim Arealist As String
Dim packer1list
Dim packer2list
Dim packer3list
'Dim Complete

Private Sub Packer_afterupdate()
Dim pv

pv = [Packer].Value
[Area].RowSourceType = "Value List"

If pv = "No.1 Packer" Then
[Area].RowSourceType = packer1list
Else
If pv = "No.2 Packer" Then
[Area].RowSourceType = packer2list
Else
If pv = "No.3 Packer" Then
[Area].RowSourceType = packer3list
End If
End If
End If
[Area].SetFocus
[Area].Dropdown

'time stamp stuff
Start = Format(Now(), "dd/mm/yy hh:mm")
If Me.Dirty Then Me.Dirty = False

End Sub
Private Sub Remedy_AfterUpdate()
Complete = Format(Now(), "dd/mm/yy hh:mm")
If Me.Dirty Then Me.Dirty = False
End Sub

Private Sub form_load()
packerlist = "No.1 Packer;No.2 Packer;No.3 Packer;"
packer1list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;"
packer2list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;Spout 11;Spout 12;Spout 13;Spout 14;"
packer3list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;"
End Sub

 

[Corticus]

Hi stuartandwill,

Try

Option Compare Database
Option Explicit

Dim packerlist As String
Dim Arealist As String
Dim packer1list
Dim packer2list
Dim packer3list
'Dim Complete

Private Sub packer_afterupdate()
Dim pv

pv = packer.Value
packer.RowSourceType = "Value List"

Select Case pv
    Case "No.1 Packer"
        packer.RowSource = packer1list
    Case "No.2 Packer"
        packer.RowSource = packer2list
    Case "No.3 Packer"
        packer.RowSource = packer3list
End Select

'time stamp stuff
Start = Format(Now(), "dd/mm/yy hh:mm")

If Me.Dirty Then Me.Dirty = False
packer.Requery

End Sub

Private Sub Remedy_AfterUpdate()

Complete = Format(Now(), "dd/mm/yy hh:mm")
If Me.Dirty Then Me.Dirty = False

End Sub

Private Sub form_load()

packerlist = "No.1 Packer;No.2 Packer;No.3 Packer;"
packer1list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;"
packer2list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;Spout 11;Spout 12;Spout 13;Spout 14;"
packer3list = "Spout 1;Spout 2;Spout 3;Spout 4;Spout 5;Spout 6;Spout 7;Spout 8;Spout 9;Spout 10;"
packer.RowSourceType = "Value List"
packer.RowSource = packerlist

End Sub

I don't know if I'd recommend doing a list this way, I'd probably use tables and queries, but this should work.

HTH,

 

[stuartandwil]

Many thanks I will give it a try.