Refresh form after combo NotInList event 1

[wvandenberg]

Hi all,

I have a form (frmAddNewClient) that contains a combobox (cboClientName) and a subform (sfrAddNewProject). If the desired client is not in the list, the NotInList event fires and the new data is added.

Private Sub cboClientName_NotInList(NewData As String, Response As Integer)
    If AddToList(Me, "tblClients", "ClientName") Then
        Response = acDataErrAdded
    Else
        Me.cboClientName.Undo
        Response = acDataErrContinue
    End If
End Sub

This event works correctly.

I also have code in the AfterUpdate event of cboClientName so that if the desired client is in the list, it will go to the selected record.

Private Sub cboClientName_AfterUpdate()
    Dim rst As DAO.Recordset

    Set rst = Me.Recordset.Clone
    rst.FindFirst "[pkClientID] = " & str(Me.cboClientName)
    Me.Bookmark = rst.Bookmark
    Set rst = Nothing

    If Not IsNull(Me.cboClientName) Then
        Me.sfrAddNewProject.Controls("txtProjectNumber").Enabled = True
    End If
End Sub

The problem I'm having is when the user enters a new ClientName and the NotInList event fires, the form does not go to the newly added data. It just stays on the record that was selected when the user typed the new ClientName.

If I close and reopen the form, I can select the most recently added data and it will go the the record. However, if I don't close and reopen, the rst.FindFirst command produces a NoMatch=True. I've tried requering the form and it just causes the NotInList event to keep firing and I get the NewData added to my table over and over.

Can anyone suggest how I can get the form to goto the NewData added to the combobox?

Thanks,
Wendy

 

[GingerR]

Try adding the "go to the new record" code right in after the "add the new data" code. Leave it in the AfterUpdate as well, for clients that are already there.

So in the NotInList event:

Add New Data
Go to the record with ID = NewData
Make the combo box blank

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[wvandenberg]

Thanks for responding GingerR.

I tried your suggestion but after NewData is added to the table (I checked to verify), the FindFirst results in a NoMatch=True. I know the data is there but the form isn't seeing the new data that was just added to the table. If I put Me.cboClientName.Requery in after AddToList I get Run-time error '2118' You must save the current field... The combo is unbound. Is there something else I could try?

Wendy

 

[GingerR]

Ok, kinda hokey, but it works:

Can't save the combo box; and requering the entire form makes the OnNotInList fire a ton of times like you said. So I set the combo box to be whatever the first record is, just so it won't be "not in list"; then Me.Requery makes the newest record become part of the recordsource of the form; then move to the newest record; then set the combo box back to the NewData. All of this happens so fast for me the user can't tell; hopefully it's the same for you:

{ADD NEW DATA CODE}
Response = acDataErrAdded

'Set combo box to first value in combo box so the NotInList event won't keep firing

Me.cboClientName = Me.cboClientName.ItemData(1)

'Requery the form so the newest record will show up    
Me.Requery
   
'Go to newest record (Where ID = NewData)
Dim rst As Recordset
rst.FindFirst "[pkClientID] = " & NewData
Me.Bookmark = rst.Bookmark
Set rst = Nothing
    
'Set combo box back to NewData
Me.cboClientName = NewData

You might have to tweak it, I tried to fill in your field names and such, but might be a little off. I tested it, works ok for me. Let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[wvandenberg]

Thanks GingerR, works great!

Star for you.

Wendy