Using vba to select a particular record in a combo box is not working properly (1 Viewer)

[ross_petersen]

Hi everyone

I have four tables as follows:

• tblResourceOrders
• tblResourceOrderDetails
• tblResourceOrderDelivery
• tblResourceOrderDelDetails

The first two tables are represented on one tab of a form and the second two on another tab

When I go to the Del Details, I am aiming to insert a new record based upon the data in the Resource Order and Resource Order Details.

Here is the code:

Private Sub Form_BeforeInsert(Cancel As Integer)

    ' pick up data from frmResourceOrders (details) and insert them into the appropriate controls
  
    Dim cnn                 As ADODB.Connection
    Dim rs                  As ADODB.Recordset
  
    Dim strSQL              As String
    Dim sql                 As String
  
    Dim lngResOrderDetID    As Long
    Dim lngProdQtyType      As Long
    Dim intQty              As Integer
  
    Dim intI                As Integer

    intI = 0
  
    Set cnn = New ADODB.Connection
    Set cnn = CurrentProject.Connection
  
    Set rs = New ADODB.Recordset
  
    strSQL = "SELECT * FROM qryResOrderDelDet_Insert_Details " _
                & "WHERE ResourceOrderID = " & Me.Parent.Parent.txtResourceOrderID
              
  
    With rs
  
        .ActiveConnection = cnn
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockReadOnly
      
        .Open strSQL, , , , adCmdText
      
        If .RecordCount > 0 Then
      
            ' move to the first record returned and assign
            .MoveFirst
          
            lngResOrderDetID = ![ResourceOrderdetailID]
            lngProdQtyType = ![ProductQtyType]
            intQty = ![Qty]
      
            Me.txtResOrderDetailID = lngResOrderDetID
          
            For intI = 0 To Me.cboProductQtyType.ListCount - 1
          
                If Me.cboProductQtyType.Column(0, intI) = lngProdQtyType Then
              
                    Me.cboProductQtyType.SetFocus
                    Me.cboProductQtyType.Dropdown
                    Me.cboProductQtyType.Selected(intI) = True
                    Me.cboProductQtyType.Value = lngProdQtyType
                  
                    Exit For
              
                End If
              
            Next intI
          
            Me.txtQty = intQty
                  
        End If
      
    End With
  
    rs.Close
    cnn.Close
  
    Set rs = Nothing
    Set cnn = Nothing
  
End Sub

The problem is that up to (and including the following line) everything is fine:

If Me.cboProductQtyType.Column(0, intI) = lngProdQtyType Then

However, somewhere in the following four(4) lines, something goes wrong

Me.cboProductQtyType.SetFocus

Me.cboProductQtyType.Dropdown

Me.cboProductQtyType.Selected(intI) = True

Me.cboProductQtyType.Value = lngProdQtyType

I am expecting to see when I look at the combo box, it should show a particular value being chosen.

If I look at the immediate window, Me.cboProductQtyType.Value does have the value I am expecting. Instead all it shows when I look at the combo box once the procedure has completed is an empty box.

I am puzzled.

Can anybody throw some light on this please?

TIA

Ross

 

[June7]

Need to know properties of cboProductQtyType: RowSource, ColumnCount, BoundColumn.
If you want to provide db for analysis, follow instructions at bottom of my post.

Why do you loop through combobox items?

 

[cheekybuddha]

Where is everybody getting this code to set a combobox?

Another example here in this recent thread.

Replace this code:

' ...
            Me.txtResOrderDetailID = lngResOrderDetID
         
            For intI = 0 To Me.cboProductQtyType.ListCount - 1
         
                If Me.cboProductQtyType.Column(0, intI) = lngProdQtyType Then
             
                    Me.cboProductQtyType.SetFocus
                    Me.cboProductQtyType.Dropdown
                    Me.cboProductQtyType.Selected(intI) = True
                    Me.cboProductQtyType.Value = lngProdQtyType
                 
                    Exit For
             
                End If
             
            Next intI
         
            Me.txtQty = intQty
'...

with:

' ...
            Me.txtResOrderDetailID = lngResOrderDetID
            Me.cboProductQtyType = lngProdQtyType
            Me.txtQty = intQty
' ...

Just assign the value to the combo - everything else is taken care of.

 

[Pat Hartman]

Duh! That would be too easy

 

[cheekybuddha]

Also, why are you doing all this in the form's BeforeInsert event?

You have a local (or linked table) - what's the need for an ADODB.Recordset?

Just bind the form to qryResOrderDelDet_Insert_Details - no code required.

If it's a subform set your LinkMasterFields/LinkChildFields to txtResourceOrderID from the grandparent form