After successful "Not in List" form resets (1 Viewer)

[Kryst51]

I have two comboboxes on a form.

One combo-box to find a particular record on the form, and another combobox which looksup values in a different table and places the pk into the fk of the table the form is bound to.

This second combo box contains a list of vendors. My user may need to add another vendor, so I created a not-in-list for that combo-box, and used the same event to then assign that number to the record on the form like it would have done if it was originally in the list.

Now, however, the form requeries and sets back to the first available record. When I need it to still display the record it was on before the not-in-list event.

Here is the code in my second combo-box:

Private Sub cmbVendorName_NotInList(NewData As String, Response As Integer)
On Error GoTo cmbVendorName_NotInList_Err

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
 
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
 
    Msg = "Vendor Name: '" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
 
    i = MsgBox(Msg, vbQuestion + vbYesNo, "This vendor is not in  the list...")

    If i = vbYes Then
        strSQL = "INSERT INTO tblVendors([VendorName])" & _
                 " SELECT """ & NewData & """ AS VendorName;"
        'Debug.Print strSQL
        Response = acDataErrContinue
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        Response = acDataErrAdded
        DoCmd.SetWarnings True
 
    Else
        Response = acDataErrContinue
    End If
 
cmbVendorName_NotInList_Exit:
    Exit Sub
    
cmbVendorName_NotInList_Err:
MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error"
Resume cmbVendorName_NotInList_Exit
End Sub

I had a thought that I might be able to set the form back to the desired record, as when the form resets, the ID I need is still in the first combo-box (the lookup one) Although, I don't know how to do this, or if there is a better way to do what I need that doesn't require this fancy footwork, other than a "new" pop-up form for a new vendor. (Although If I have to, I will do that. )

 

[ghudson]

You can use a simple find command to get back to that record. If the value is in the first combo box then use it in your find command. If not, you can store the value as a variable to use when needed.

DoCmd.FindRecord Me![cboFirstComboBox], acEntire, True, acSearchAll, True, acCurrent, True

 

[vbaInet]

If you look through the code of the Sample DB I gave you you will find a way of retaining the value after Requerying.

I think it's in the frmAccountDetails module.

 

[Kryst51]

You can use a simple find command to get back to that record. If the value is in the first combo box then use it in your find command. If not, you can store the value as a variable to use when needed.

DoCmd.FindRecord Me![cboFirstComboBox], acEntire, True, acSearchAll, True, acCurrent, True

If I use this without a break in my not-in-list event then it just keeps popping up my message box again and again.

If I use it with a break and walk through it, it throws an error:

2046, The command or action 'Find Record' isn't available now

@vbaInet - I am looking at your example now.

 

[Kryst51]

If you look through the code of the Sample DB I gave you you will find a way of retaining the value after Requerying.

I think it's in the frmAccountDetails module.

I tried your openargs approach. I don't think I understand it enough to use it, as on my form as I am not opening a new form and I don't know how to modify it to work.

 

[vbaInet]

This is how the code goes:

        Dim strComboValue as string

' Requery the combo box and set back its text.
        With Me.[COLOR=Red][B]Combobox1[/B][/COLOR]
            .SetFocus
            strComboValue = Nz(.Text, "")
            .Value = vbNullString
            .Requery
            .SetFocus
            .Text = strComboValue
        End With

Something like this. The db was for someone on here who was having difficulty using NotInList.

Substitute the highlighted bit with the name of your combobox.

 

[Kryst51]

This is how the code goes:

        Dim strComboValue as string
 
' Requery the combo box and set back its text.
        With Me.[COLOR=red][B]Combobox1[/B][/COLOR]
            .SetFocus
            strComboValue = Nz(.Text, "")
            .Value = vbNullString
            .Requery
            .SetFocus
            .Text = strComboValue
        End With

Something like this. The db was for someone on here who was having difficulty using NotInList.

Substitute the highlighted bit with the name of your combobox.

Thanks for that! When I F8 through it it only gets to the first .Setfocus, then restarts from the beginning of the code. so that doesn't work, maybe I am putting it in the wrong place, so here is my modified code:

Private Sub cmbVendorName_NotInList(NewData As String, Response As Integer)
On Error GoTo cmbVendorName_NotInList_Err

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
    Msg = "Vendor Name: '" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    i = MsgBox(Msg, vbQuestion + vbYesNo, "This vendor is not in  the list...")
    If i = vbYes Then
        strSQL = "INSERT INTO tblVendors([VendorName])" & _
                 " SELECT """ & NewData & """ AS VendorName;"
        'Debug.Print strSQL
        Response = acDataErrContinue
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        Response = acDataErrAdded
        DoCmd.SetWarnings True
        
        Dim strComboValue As String
' Requery the combo box and set back its text.
        With Me.cmbPurchaseOrderLookup
            .SetFocus
            strComboValue = Nz(.Text, "")
            .Value = vbNullString
            Debug.Print
            .Requery
            .SetFocus
            .Text = strComboValue
        End With

    Else
        Response = acDataErrContinue
    End If
cmbVendorName_NotInList_Exit:
    Exit Sub
    
cmbVendorName_NotInList_Err:
MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error"
Resume cmbVendorName_NotInList_Exit
End Sub

 

[vbaInet]

Actually, remove both setfocus. It only applies in my db.

 

[Kryst51]

OK, removed the set focuses, and I get this error:

2185, You can't reference a property or method for a control unless the control has the focus.

Which I looked up and what I found is that .text doesn't work unlessthe control already has focus..... So it says to use .Value instead, So I do that, and the code runs without error, but the form doesn't go to the record, I think that's because setting the combo box doesn't fire the after update event so that code that looksup the new record never gets run. What can I try next?

 

[vbaInet]

I remember that from the db I created. There isn't a value until after requerying which was why I used the Text property. I will change the sample db to your way of using the Not In List and resend.

 

[Kryst51]

If I remove the null string bit, and break through the code, it does have a value. I don't know if that changes things.

 

[vbaInet]

That would be the old value. Confirm if its the right value. I would need to check it up again.

 

[Kryst51]

That would be the old value. Confirm if its the right value. I would need to check it up again.

Yep, its the right value, so it would be great if the form used that value. But it doesn't. I am so perplexed.

 

[vbaInet]

Just had a look at my db to refresh my memory. Ahhh...

So let's get rid of the convoluted code. This should do the trick:

' Requery the combo box and set back its text.
        With Me.cmbPurchaseOrderLookup
            .value = vbnullstring    
            .requery
            .Value = newdata
        End With

 

[Kryst51]

Just had a look at my db to refresh my memory. Ahhh...

So let's get rid of the convoluted code. This should do the trick:

' Requery the combo box and set back its text.
        With Me.cmbPurchaseOrderLookup
            .value = vbnullstring    
            .requery
            .Value = newdata
        End With

I tried this new code, it is trying to apply the new text to the lookup combo on my form (at least that is what I surmise when stepping through the code please correct my thinking if I am wrong), but the value of the lookup combo is a number for the PO record, not the name of the vendor. I have two combo boxes, one for the form lookup, and one for an Foreign key lookup - this code (The not in list event code) is for the foreign key lookup, and after its done I need the form to still be on the record that was picked in the form lookup. So, the code doesn't throw an error, but it doesn't go to the record in the combo box.

 

[vbaInet]

So your combo box has two columns. One is the ID (an autonumber field) which is hidden and the other column is the vendor?

 

[Kryst51]

Sorry, yes, I always use autonumber IDs as my pks.

Here is a stripped down version of my db, the form that pops up has two combo boxes, one that jumps to the record selected. The other combo box (the one I am working on the not in list) looks up values in a related table so that I can pick the vendor. But as the vendor list isn't tied to the system the rest of the company uses, vendors need to be added often, but I want there to be a safeguard against typos or whatever, or just one more confirmation that this is a new vendor. Hence the not in list. But when it adds the new vendor, it didn't add that values ID to the forms bound table, so I added code in to make it do that, and it does, it just makes the form jump to the first record, but if I select the record in the form lookup, it did the code correctly, I just don't want to have to re-select the record in the form look-up combo box.

Do you think I should be doing this a different way? Would it be more adviseable to use a pop-up form?

 

[vbaInet]

You can do it anyway you wish. However, the only reason why I used a popup form instead of just saving it is to allow users make changes to what they have just saved. For example, there may have been a typo or they mistakenly moved away from the combo, the typo gets saved.

To do it your way, all you would need to change the UPDATE method to a recordset way of updating. You need to get the ID of that vendor to be able to set the value appropriately.

I will have a look at your db in a min.

 

[Kryst51]

You can do it anyway you wish. However, the only reason why I used a popup form instead of just saving it is to allow users make changes to what they have just saved. For example, there may have been a typo or they mistakenly moved away from the combo, the typo gets saved.

To do it your way, all you would need to change the UPDATE method to a recordset way of updating. You need to get the ID of that vendor to be able to set the value appropriately.

I will have a look at your db in a min.

You have no idea how much I appreciate all of your time and help, I am in no rush, especially since I am now at home until tomorrow. If I could give you rep again I would but it won't let me.

 

[vbaInet]

You're welcome.

Just been trying to understand your setup and the problem is: When you insert into the Vendors table the PurchaseOrders table has the ID from the Vendors table as the foreign Key, so you need to also insert into the PurchaseOrders table. For this to happen you need a value for fkVendorID, which we can get, but also a PONum value. Where does that come from?