Need help with a NotInList problem

[marsha D. Carpenter]

I posted this message yesterday and have not received a response. Someone please assist. I feel really stupid, I just figured out how to post a thread for the third time... so......

My problem is with the notinlist event of a combobox which I have on a form.
When this event occurs, I display another form which I used to capture several fields. Once the fields are captured and edited, I insert the record into the database, close the capture form and return back to the main form. The problem is that the notinlist event keeps occuring cause the data is not stored in the list. I get the following error when I use the following statement in an attempt to update the list with the new data.

The statement in error is:
Me!cboGetContacts.Requery

I tried to requery the data and receive the following error:

Run-time error 2118
"You must save the current field before you run the requery action"

Someone please help me! I am just learning this Access stuff over again after having not worked in 4 years!
Thanks in advance!

 

[CraigDolphin]

The code in your not in list event ought to be similar to the following:

Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to add this item to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "YourPopupFormNameHere", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
MsgBox "Please choose something from the list.", vbInformation
Response = acDataErrContinue
End If

There is no need to perform an additional requery if you return the argument acDataErrAdded. Substitute the name of your other form in the appropriate section.

 

[Banana]

and as matter of fact, you don't need to requery; it's done automatically when you issue acDataErrAdded

 

[marsha D. Carpenter]

Very Similiar

Thanks for your response! I hope this is not too much code. I just printed the forum etiquette instructions and will read them at home this evening.

The code in the notinList event is as follows:

Private Sub cboGetContacts_NotInList(NewData As String, response As Integer)
Dim strSQL As String
Dim i As Integer
Dim msg As String
Dim CR As String
Dim result
Dim glbUserID

'comehere
glbUserName = fOSUserName

'Exit this sub if the combo box is cleared
'glbDistrict = DLookup("DI", "tblDistricts", "[DIN] = '" & glbDistrict & "'")
glbEmployeeID = DLookup("Employee_ID", "tblEmployees", "[username] = '" & glbUserName & "'")
glbDistrict = Me.cboGetDistricts
If NewData = "" Then Exit Sub
msg = "'" & NewData & "' is not currently in your list of District Coordinators." & vbCr & vbCr
msg = msg & "Do you want to add it?"
i = MsgBox(msg, vbQuestion + vbYesNo, "District Coordinator not on your List....")
If i = vbYes Then
'**Open the frmAddACoordinator to collect the data to be added
'** format the data and pass it to the add coordinator form
DoCmd.OpenForm "frmAddACoordinator", , , , acAdd, acDialog, NewData & ";" & "'" & glbDistrict & "'"
sqlStringA = "SELECT tblContacts.Employee_ID, tblContacts.FullName,tblContacts.WorkPhone, tblContacts.Emailname,"
sqlStringA = sqlStringA + "tblContacts.District FROM tblContacts INNER JOIN tblDistricts ON "
sqlStringA = sqlStringA + "tblcontacts.District = tblDistricts.DI "
sqlStringA = sqlStringA + "WHERE (((tblContacts.District)= " + DistrictID + "));"

Me!cboGetContacts.RowSource = sqlStringA

Me!cboGetContacts.Requery
Me!cboGetContacts.Dropdown
Me![cboGetContacts].Visible = True
Me![lblContacts].Visible = True

' response = acDataErrContinue
End If

result = DLookup("[fullname]", "tblContacts", "[FullName]='" & strString & "'")


End Sub



The code in the form which captures the data is as follows:
Private Sub cmdSave_Click()
'**Ensure that all data is valid
'**
Dim lngLen As Long, lngX As Long
Dim strUserName As String

If IsNull(Me!txtEmailName) Then
MsgBox "Email Name is Required!", vbOK, "Please Enter a Valid Email Name"
'Cancel = True
Exit Sub
End If
'** Either HPMS or TRM or both must be selected
If Not (Me.chkHPMS) And Not (Me.chkTRM) Then
MsgBox "Coordinator mut be HPMS or TRM or Both. Please check at least one!", vbOK, "Please Select a Program"
' Cancel = True
Exit Sub
End If
If IsNull(Me.txtWorkPhone) Then
MsgBox "Please enter a phone number", vbOK, "Enter a Phone Number"
' Cancel = True
Exit Sub
End If

Dim intOptions As Integer

MsgBox " Record for this person does not exist - Now creating a new Record.", vbInformation, "Record Being Added"
email = Me.txtEmailName
WorkPhone = Me.txtWorkPhone
EmployeID = Me.EMPLOYEE_ID
FName = Me.txtFname
LName = Me.txtLName
FullName = Me.txtFullName
email = Me.txtEmailName

'** check HPMS and TRM flags - cannot store null values via insert
If IsNull(Me.chkHPMS) Then
HPMS = 0
Else
HPMS = Me.chkHPMS
End If
If IsNull(Me.chkTRM) Then
TRM = 0
Else
TRM = Me.chkTRM
End If


strMsg = "Insert into tblContacts ([EMPLOYEE_ID], [FULLNAME],[Fname], [Lname], [EmailName], [District], [WorkPhone], [TRM], [HPMS]) "
strMsg = strMsg & "Values('" & EmployeID & "','" & FullName & "','" & FName & "','" & LName & "','" & email & "','" & District & "','" & WorkPhone
strMsg = strMsg & "'," & TRM & "," & HPMS & ");"
CurrentDb.Execute strMsg, dbFailOnError
response = acDataErrAdded

strMsg = "A Record has been Added for " & FullName & " in District " & District
response = MsgBox(strMsg, vbInformation, "Record Added")

response = DLookup("[fullname]", "tblContacts", "[FullName]='" & FullName & "'")
DoCmd.Close acForm, "frmAddACoordinator"

End Sub

 

[CraigDolphin]

That's a lot of code going on there and I am unsure what the purpose of most of it is. The code I already gave you should suffice to open your frmAddACoordinator form when a new entry is entered into the combobox. When that form closes (presumably after the new person has been added to the table), the response = acDataErrAdded line will requery the control source of your combobox. Why you are rewriting the sql of the control's row source when all you're doing is adding a new person to a table is beyond me. I also have no idea what you're doing with all the global variables in the not in list event. I would imagine those would be better set in the after update event.

Here it is again with your form name specified instead of the generic.

Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to add this item to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmAddACoordinator ", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
MsgBox "Please choose something from the list.", vbInformation
Response = acDataErrContinue
End If

 

[marsha D. Carpenter]

It Works!

I removed most of the extraneous code and now it works! Thanks for your assistance. I can take constructive criticism! I have not developed any software for 4 years and now I am under the gun to develop! Thanks again

 

[CraigDolphin]

Glad you got it working And I did not mean to sound critical...only to convey that I did not understand what all the rest of the code was doing there (you may have had reasons for it that I did not understand).