Feeding a Combo Box from another combo box (1 Viewer)

[atrium]

I have two combo box and their source is the same table
ComboBox 1
Control Source = PartNo
Row Source =
SELECT Parts.PartsId, Parts.PartNumber, Parts.Description
FROM Parts
WHERE (((Parts.PartNumber)<>" "))
ORDER BY Parts.PartNumber;

ComboBox 2
ComboBox 1
Control Source = Description
Row Source =
SELECT Parts.PartsId, Parts.PartNumber, Parts.Description
FROM Parts
WHERE (((Parts.Description)<>" "))
ORDER BY Parts.Description;

After Update event on Description

Private Sub DescriptionCbo_AfterUpdate()
   If Len(Me.PartNo & vbNullString) = 0 Then
      Me.PartNumberCbo = " "
   End If

   If Len(Me.DescriptionCbo.Column(2) & vbNullString) > 0 Then
      
       Me.PartNumberCbo = Me.DescriptionCbo.Column(1)
       Me.ValidOrdFld = 1
   Else
      MsgBox "Please enter a Description"
      Me.DescriptionCbo.SetFocus
   End If
  
   If Len(Me.QuantityFld & vbNullString) = 0 Then
      MsgBox "Please enter a quantity"
      Me.QuantityFld.SetFocus
      Exit Sub
   End If
End Sub

After Update on PartNo

Private Sub PartNumberCbo_AfterUpdate()
   If Len(Me.PartNumberCbo.Column(1) & vbNullString) = 0 Then
      Dim LResponse As Integer
      LResponse = MsgBox(" Do you want to leave the Part No blank?", vbYesNo, "Continue")
      If LResponse = vbYes Then
         ' OK Leave it blank
         Me.OrderNoFld = Forms![OrdersFrm]![OrderNoFld]
         Exit Sub
      Else
         Me.PartNumberCbo.SetFocus
      End If
   Else
      Me.ValidOrdFld = 1
      ' If the part number has been selected and ther description = Null then move the desciption into the description field - no
      Me.DescriptionCbo = DLookup("Description", "Parts", "[PartsId] = " & Me.PartNumberCbo.Column(0))
    
   End If
   Me.OrderNoFld = Forms![OrdersFrm]![OrderNoFld]
End Sub

I want the user to be able to put the part no in and it will also populate the description field. They can also select the description and it will populate the corresponding PartNo field

When I select the Description and action the after Update event the corresponding PartNo is not going into the PartNumberCbo field

Any help would be greatly appreciated

 

[Uncle Gizmo]

If I understand your question correctly you want to select something in one combobox and have related text show in another combobox.

That is quite a common requirement however it's not normally done with combo boxes, it's normally done with a combo box and a text box or even several text boxes. Have a look at my blog on my Nifty Access website here, look at video number 2

Combo Box - Nifty Access

Combo Boxes Combo Boxes are the most fascinating and useful "Controls" you can have on your Form. They have Many uses. Combo Boxes can be manipulated in all sorts of ways without using VBA. Once you've mastered the combo-box, and once you have mastered Queries, the building of queries in the...

www.niftyaccess.com

 

[theDBguy]

Hi. Sounds like you are storing redundant data, which is against normalization rules. All you need is the ID field of your parts table, and you should be able to get both the part number and description from that. You only need one Combobox.

 

[atrium]

I reverted to only one combo box and it's now OK

Thanks theDBguy

 

[theDBguy]

I reverted to only one combo box and it's now OK

Thanks theDBguy

Glad to hear you got it sorted out. Good luck with your project.