asked on

combo Value disappears

I have a combo on a Mainform that sets the value of the combo on the subform.

I also have the onCurrent Event of the subform, setting the value of subform.combo based on the MainForm contractID. However, after entering the values in the remaining fields for the current record, when the user creates a new record and selects another value in the subform.combo, the values is empty.

What can be causing this problem?

Private Sub Form_Current()
    Dim strSQL As String
    
    If gUser = "user" And Nz(Me.RecordLock, 0) = -1 Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If
   strSQL = "Select VendorName, AgencyID, AgencyPID, ContractNumber from tblinvoice" & _
            " Where ContractNumber = " & Chr(34) & gContractID & Chr(34) & "" & _
            " GROUP BY VendorName, AgencyID, AgencyPID, ContractNumber" & _
            " ORDER BY VendorName"
    Me.cboVendorName.RowSource = strSQL
End Sub

Open in new window

Private Sub cboVendorName_AfterUpdate()
    Me.AgencyID = Me.cboVendorName.Column(1)
    Me.AgencyPID = Me.cboVendorName.Column(2)
    Me.InvoiceDate.SetFocus
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

thanks for the input, One last question.

If I am adding new value to list view the List Item Edit Form.

Is it possible to capture the value that was just typed in the combo, even though it has not been added to the list yet, so that it may be used in the edit form - preventing the need to type the value twice.

K

PatHartman

Yes. You would need to add it to the RowSource table and requery the combo. I rarely allow this though since I think it makes the users sloppy and they frequently add misspellings to the list. Of course in some cases you don't have any choice. Use the not in list event to capture the event and put your code there.

Karen Schaefer

ASKER

thanks