Not in list error - 2455 run-time (1 Viewer)

[vegemite]

Hi guys,
I am grabbing some old code and I can see it working in the database I have grabbed it from but having issues with this instance.
Once I enter the value, it prompts not in list as expected, I can add it then it loops back to not on list again. If I select no I don't want to add it I get a runtime error "You entered an expression that has an invalid reference to the property". Strangely when I exit the debug my item is on the list..
Im very much learning & would appreciate if you cant be nice, don't comment about my lack of skills ;-)
FYI the client name is first name and surname

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

'Enable the user to choose if they want to add a new client
If MsgBox("Client Name is not on list, would you like to add it?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, txtOrgNameID & Chr(165) & NewData
'NewData
txtClientName.Recordset.Requery
txtClientName.Text = NewData
End If
'Clear the error and remove the entered data
Response = acDataErrContinue
Me!txtClientName.Undo
End Sub

 

[theDBguy]

Hi. It's hard to read the code, but I don't see an Else branch. Maybe try adding one.

 

[June7]

Review https://blueclawdatabase.com/notinlist-event-code/

 

[vegemite]

Hi. It's hard to read the code, but I don't see an Else branch. Maybe try adding one.

I think I did try adding it where the endif is now & putting the endif at the end. Will try again. thanks,

 

[vegemite]

I think I did try adding it where the endif is now & putting the endif at the end. Will try again. thanks,

That worked but it popped up the "item not on the list again" then when I say no its in the spot I want it?

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

'Enable the user to choose if they want to add a new client
If MsgBox("Client Name is not on list, would you like to add it?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, txtOrgNameID & Chr(165) & NewData
'NewData
txtClientName.Recordset.Requery
txtClientName.Text = NewData
Else
'Clear the error and remove the entered data
Response = acDataErrContinue
Me!txtClientName.Undo
End If
End Sub

 

[arnelgp]

you need a way to Know if the User actually saved the new record.
on your code you don't have that, so you just guess and force it:

If MsgBox("Client Name is not on list, would you like to add it?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, txtOrgNameID & Chr(165) & NewData
'NewData
'no need for this
'txtClientName.Recordset.Requery
'txtClientName.Text = NewData
'if the data is Actually saved in frmDirectory, we don't know.
'just return acDataErrAdded
Response = acDataErrAdded
Else
'Clear the error and remove the entered data
Response = acDataErrContinue
Me!txtClientName.Undo
End If
End Sub

 

[vegemite]

Awesome thank you!!!

 

[arnelgp]

you're welcome.

 

[vegemite]

you're welcome.

Thanks so much for your help with the last one. Would there be a difference if I am using a subform for the same process?
This is for a consultant working on the job - the new record would go to the same place (the directory). Ideally I update the record by getting it to record "yes" or 1 to tag them as a subconsultant. But I am having the same error as before, its just looping....

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

'Enable the user to choose if they want to add a new consultant
If MsgBox("Client Name is not on list, would you like to add it?", vbOKCancel) = vbOK Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, CmbSubconsultant & Chr(165) & NewData


'if the data is Actually saved in frmDirectory, we don't know.
'just return acDataErrAdded
Response = acDataErrAdded
Else
'Clear the error and remove the entered data
Response = acDataErrContinue
Me!CmbSubconsultant.Undo
End If
End Sub

 

[June7]

Post the code behind frmDirectory.

 

[vegemite]

Post the code behind frmDirectory.

Rather than the subform?

 

[June7]

Correct. If this code has references to subform as if it were opened independently, code will no longer work.

 

[vegemite]

The form where the consultant is being entered into is a job form. The consultant's name is added to the directory if its not there (its supposed to). It works in the first instance that @arnelgp provided above (the code is in the job form,not the directory). The only diff with this is that the consultant is a subform that sits on the job form (there can be multiple consultants). On the job form the user enters the name as one field ie Bob Brown - then if it doesn't exist in the directory it passes the Bob & Brown to the directory fields first name and surname

 

[June7]

Well, code can be applied to form or subform.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[vegemite]

Thanks @June7 Attached. Form is JobManagement. Subconsultant is the subform

 

[June7]

frmDirectory code references JobManagement, not Subconsultant. If you want one input form to serve two comboboxes on different forms, need to provide frmDirectory with parameter to know which form called it so as to know which control needs requery.

Alternative is to have calling form manage its own combobox. This is what I do. Example:

Private Sub cbxStateNum_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
If MsgBox("State Number not in database.  Add new project record?", vbYesNo + vbQuestion, "NoRecord") = vbYes Then
    If MsgBox("Do you really want to add a new project record?", vbYesNo, "ConfirmAddNewProject") = vbYes Then
        DoCmd.OpenForm "AddEditProject", acNormal, , , , acDialog, NewData
        Me.cbxStateNum = Null
        Me.cbxStateNum.Requery
        Me.cbxStateNum = NewData
        Me.lbxProjects.Requery
    Else
        Me.cbxStateNum = Null
        Me.cbxStateNum.SetFocus
    End If
Else
    Me.cbxStateNum = Null
    Me.cbxStateNum.SetFocus
End If
End Sub

 

[vegemite]

So in my case
Private Sub CmbSubconsultant_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
'Enable the user to choose if they want to add a new consultant
If MsgBox("Consultant Name is not on list, would you like to add it?", vbYesNo + vbQuestion, "NoRecord") = vbYes Then
If MsgBox("Do you really want to add a new record?", vbYesNo, "ConfirmAddNewConsultant") = vbYes Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, CmbSubconsultant & Chr(165) & NewData ^^^

Me.CmbSubconsultant = Null
Me.CmbSubconsultant.Requery
*** Me.CmbSubconsultant = NewData
Me.CmbSubconsultant.Requery
Else
Me.CmbSubconsultant = Null
Me.CmbSubconsultant.SetFocus
End If
Else
Me.CmbSubconsultant = Null
Me.CmbSubconsultant.SetFocus
End If
End Sub

It throws a runtime error where I have put the ***
-2147352567 (80020009)';
the value you entered isnt valid for this field

Is that because the intent is to split the text entered into the two fields in the directory (first name and surname) here ^^^

I will try your first suggestion as well

 

[June7]

Since you have first and last name fields concatenated in combobox RowSource, this should still work but is giving me issue as well.

In future, try to post code between CODE tags to retain indentation and readability.

 

[vegemite]

Thanks @June7 I'll keep at it & tidy up at the same time

 

[June7]

Combobox in subform filters to only items that are flagged as subconsultant. No place on frmDirectory to input that therefore NewData will not agree with any item in requeried combobox.