Insert new record as the ID for a cmb box "text you entered doesnt exist"

**gangel** · 09-13-2015, 07:53 AM

Hey everyone,

So i have created a code that on NotInList sends to FrmInsComp (to create a new insurance company)

After creating and click on save and close on that form I run this:

Code:

Private Sub btnSavClose_Click()Dim InID As Integer


If Me.Dirty = True Then Me.Dirty = False


InID = Me.InsCompID




Forms!FrmCust!FrmClaims.Form!cmbInsComp.Requery
Forms!FrmCust!FrmClaims.Form!cmbInsComp = InID




DoCmd.Close acForm, "frmInsComp"

So i requery the combo box so that it sees the new insurance company created, and then puts in the new records ID as the cmb box id which SHOULD show the new ins comp.

Now this works IF i use a button, and the cmb box is empty when i press it. The above code works,

However if i run the code from NotInList where there is "eraneous" text in the combo box it allscrews up

I have even tried

Code:

If NewData = "" Then Exit Sub

    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
    Me.cmbInsComp = ""
    Me.Dirty = False
    
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "FrmInsComp", , , , acAdd, acDialog

So i Clear the combo box before gpoig to the Add Form. This still gives me the same error

Where am i going wrong!

(This is my first time using notinlist)

**orange** · 09-13-2015, 08:16 AM

Can you post a copy of the db? Nothing confidential, just enough records to allow testing.

**ItsMe** · 09-13-2015, 08:22 AM

The code in the btnSavClose_Click event looks like it should work just fine. As you mentioned it does work. However, it does not work every time. This is likely because the code in your NotInList event is not perfect. The following code is similar to code I use in my DB's. Maybe it will help out.

Code:

Private Sub cmbContact_NotInList(NewData As String, Response As Integer)
 

Response = acDataErrContinue
Dim intMsg As Integer

intMsg = MsgBox("That Contact does not exist within the list." & vbCrLf & _
                "Do you want to add a new contact?", vbYesNo + vbQuestion + vbDefaultButton2, "Name Not Found")
                
If intMsg = vbYes Then
    Me.cmbContact.Text = ""
    DoCmd.OpenForm "frmContactsAdd", , , , acFormAdd
Else
    Me.cmbContact.Value = ""
End If
 
 
End Sub

**gangel** · 09-13-2015, 08:36 AM

hey pplz, Thanks ive read comments.

ItsMe, ill run through that now and then post back

**gangel** · 09-13-2015, 08:44 AM

Ok well i changed cmbinscomp = ""
to .text = ""

and.... took out me.dirty = false

and its working... lol

One minor issue is that when it adds back to the original combo box, it isnt "selected" totally. it is in the box but all the next lines of items in the combo box are showing, so the combo box is still 'open'

If i do this by Add not NotInList then the new record goes into the combobox, but the combo list isnt open still.

**ItsMe** · 09-13-2015, 09:06 AM

I am not following what you mean by 'isnt open still'. Maybe if you post your entire Not In List procedure and the close event from the other form here I will have a clearer picture.

**gangel** · 09-13-2015, 09:11 AM

Code:

Private Sub cmbInsComp_NotInList(NewData As String, Response As Integer)Dim Result
Dim Msg As String
Dim CR As String


    CR = Chr$(13)
Response = acDataErrContinue


    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub


    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
    Me.cmbInsComp.Text = ""
        
       ' If the user chose Yes, start the Customers form in data entry
       ' mode as a dialog form, passing the new company name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Customer form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "FrmInsComp", , , , acAdd, acDialog, 4
       
       Else
       
       Me.cmbInsComp.Value = ""
    End If


  
End Sub

Code:

Private Sub btnSavClose_Click()Dim InID As Integer


If Me.Dirty = True Then Me.Dirty = False


InID = Me.InsCompID


If InsCompOpt = 1 Or InsCompOpt = 4 Then
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Requery
Forms!FrmCust!FrmClaims.Form!cmbInsComp = InID
Opt = Me.InsCompID


End If


DoCmd.Close acForm, "frmInsComp"
InsCompOpt = 999
End Sub

Thanks ItsMe

**ItsMe** · 09-13-2015, 09:29 AM

Looking at that code I would expect to see the new record in the combo after FrmInsComp is closed. If it is not appearing as selected, I would suspect a problem with the one or more of the properties of the combo. For instance, <Forms!FrmCust!FrmClaims.Form!cmbInsComp = InID> is the same as the following ...

Code:

Forms!FrmCust!FrmClaims.Form!cmbInsComp.Value = InID

In order for this to work correctly, you need to have the correct column of the combo assigned to the combo's Bound Column property. The default is 1. If the Bound Column property is 1 and the first column/field within the combo's SELECT statement does not align with InID, you will get undesirable results.

Here is another way to illustrate multicolumn combos.

Forms!FrmCust!FrmClaims.Form!cmbInsComp.Column(0) = InID
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Column(1) = InID
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Column(2) = InID
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Column(3) = InID

If the Bound Column property is 1, the following three examples are synonymous.
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Column(0) = InID
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Value = InID
Forms!FrmCust!FrmClaims.Form!cmbInsComp = InID

**gangel** · 09-13-2015, 09:38 AM

Thanks ItsMe.

I obviously wasnt clear, sorry about that.

It IS in the combo box but the combo LIST is still there (the dropdown list is still showing)

Click image for larger version.

Name: Image 028.jpg
Views: 12
Size: 247.1 KB
ID: 22025

**gangel** · 09-13-2015, 09:39 AM

so it works and im happy, but its weird that it does this, as if it is being TYPED in (the words) but im not setting inscomp(1) to "its me!!!)

Im setting inscom (which as you said is inscomp(0) ) to inscompID

**ItsMe** · 09-13-2015, 10:03 AM

Originally Posted by gangel

so it works and im happy, but its weird that it does this, as if it is being TYPED in (the words) but im not setting inscomp(1) to "its me!!!)

Im setting inscom (which as you said is inscomp(0) ) to inscompID

Are you using a Value List in your combo's RowSource? My preference is to use a table or query to support the RowSource property. This way, you can make proper use of PK's and FK's. Maybe you can post the RowSource of the combo here.

**gangel** · 09-14-2015, 05:24 AM

im using a table as the rowsource.

Code:

SELECT [InsComps].[InsCompID], [InsComps].[InsCompName] FROM InsComps ORDER BY [InsCompName];

As i said, it works normally when creating a new one from my Add button, but if i do it with not in list event, it does like the above picture, which isnt end of world, just 'weird'

**ItsMe** · 09-14-2015, 07:36 AM

I think I am understanding now. If the Pop-Up form is editing more than what is in the Combo's RowSource, you will need to save the first form be fore opening the pop-up. Then, you will need to save the first form before closing the pop-up.

Code:

if me.dirty then me.dirty = false
DoCmd.OpenForm "FrmInsComp", , , , acAdd, acDialog, 4

Then in the pop-up

Code:

Forms!FrmCust!FrmClaims.Form!cmbInsComp = InID

if Forms!FrmCust!FrmClaims.Form!cmbInsComp.Dirty = True then
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Dirty = False
end if

Forms!FrmCust!FrmClaims.Form!cmbInsComp.Requery

I am not sure what Opt = Me.InsCompID is doing in your button click event. It does not seem to be doing anything. Maybe this is part of the problem, also.

.

**gangel** · 09-14-2015, 08:01 AM

opt is a global variable that WAS going to go back into the previous form and test for something, but im not doing that now. I have removed it and added your suggestions.

The

Code:

if Forms!FrmCust!FrmClaims.Form!cmbInsComp.Dirty = True then
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Dirty = False 
end if

didnt work, said it cant do that for that object!

**gangel** · 09-14-2015, 08:03 AM

i just solved the problem by setting focus to the next control after the input of the id into the combo.

lol. No idea why it was acting that way, but this works soooo i guess thats what im doing!

Final Code

Code:

If InsCompOpt = 1 Or InsCompOpt = 4 Then
Forms!FrmCust!FrmClaims.Form!cmbInsComp.Requery


Forms!FrmCust!FrmClaims.Form!cmbInsComp.Value = InID
Forms!FrmCust!FrmClaims.Form!DateOfInjury.SetFocus


End If

Insert new record as the ID for a cmb box "text you entered doesnt exist"

Thread Tools

Insert new record as the ID for a cmb box "text you entered doesnt exist"

Similar Threads

Creating an "Insert Record" Button

Odd order of events when "i" entered in search text box

Export "Query or Report" to a "Delimited Text File"

Determine if "tab" or "return" was entered by operator

Field "F1" does not exist in table (Tablename)

Posting Permissions