Combobox, Requery table Problem

[DblDogDare]

I am getting a runtime error 438 on a requery that I can't figure out what I am doing wrong. I have a form with a combo box that I have the users entering a project name in the combo box. If the project name does not exist, I have another pop up (onLoad) form for them to enter the new project data. This subform has a save and close form button. When I close this form, I get the run time error. When I debug, it indicates the error is at the requery line. If I check the table where the combo box gets its data, the new entry is there.
I followed a previous post that I searched on from Mile-O-Phile. It was very helpful thus far. Here is my code behind what I am trying to accomplish.


Private Sub Project_Title_NotInList(NewData As String, Response As Integer)
Response = MsgBox(Me.[ProjectName] & " is not recognized in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognized Data")

If Response = vbYes Then
DoCmd.OpenForm "frmAddProject", acNormal, , , acFormAdd, acDialog, NewData
Me.[ProjectName].Requery
Response = acDataErrAdded
Else
Me.[ProjectName].Undo
Response = acDataErrContinue
End If
End Sub


It may also help to know, the combo box table is tblProject. my combo box control Source is ProjectName, so the field name and the combo box name are ProjectName. Could this naming be causing the problem. BTW, I am using Access 2000.
Any help is greatly appreciated.

 

[dcx693]

It's been a while since I've done this, but in a database I put together a while ago, I placed this code in the Close event of the pop-up form where the user enters new data:

With Forms!MainForm.cboDescription
.Undo
.Requery
.Value = Me.txtNewValue
End With

I don't think it's necessary to have the requery or the undo code running from the pop-up form, but could it be that you're getting the runtime error because you stil have a not-in-list item in your combo box as you're trying to requery it? What if you place an Undo statement before the requery? (I see that you have one in your code if the user doesn't add new data.)

The last statement in my code snippet sets the value of the original combo box to the newly entered value.

 

[DblDogDare]

Any other suggestions??

dcx693
Thanks for the reply to my post, however it didn't work. I entered your suggested code in the OnClose event of the subform. It died on the ".Value = Me.txtNewValue" (as entered). It did not show the new value in the table as it did before. The confusing thing is that with my above mentioned code, after the runtime error, I can check the table, and the new value IS in the table. For some reason, it doesn't like the Me.[ProjectName].Requery that I have in the NotInList event.... Also, if I click "NO" , to not add the new value, I still get the runtime error on the Me.[ProjectName].Undo. So, I think it has something to do with the Name of my Combo box. Maybe the brackets??? but I tried removing the brackets. I wonder?? "ProjectName" is the Control Source, While "Project Title" is the "Name" in the Properties.. Could this be the problem?? Maybe the space in "Project Title"Anyone??

 

[Rich]

Me.ProjectTitle.Undo, it's much better not to have spaces in field or control names, so just remove the space on the property sheet

 

[DblDogDare]

Still Confused

OK, I am still having difficulty. Should the .Requery point to the "Control Source", or the "Name" in the property window. I am currently pointing to the "Control Source" and the code is automatically adding the square brackets "Me.[ProjectName].Requery". When I add a new value in the combo box, I have a NotInList event that opens a form prompting for the new data (3 fields of data in the table), I have a save and close button on that form. After I press the "Save and Close Form" button, I get the error. When I debug, it highlights the .Requery line. I have tried the two suggestions above with no luck. Again, if I add a new value through my "add project" form and check the table where the combo box gets the data, the new value IS in the table.... so, half of the code is working. It errors on the requery and/or the undo, which ever option I pick. Any help is greatly appreciated as I have racked my brain trying to figure this one out.

 

[DblDogDare]

Figured it out

Ok, I have gotten it to work. For anyone else that may encounter a similar problem, I am including my change to the above code. I deleted the requery and undo statement from my 1st form. I added the following "forms![original form name]!Combo box name.Requery" to my AfterUpdate event of the pop-up FORM so that this form requeries the 1st form (original). I can now add a value to the combo box and have it displayed when I return to the combo box. So, my NotInList event is as follows;

Private Sub Project_Title_NotInList(NewData As String, Response As Integer)
Response = MsgBox(Me.[ProjectName] & " is not recognized in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognized Data")

If Response = vbYes Then
DoCmd.OpenForm "frmAddProject", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


Thank you to dcx693 and Rich for responding with their ideas.

 

[Pangloss14]

Hi,

I was wondering if you could post the code that you used for your subform. Also, curious whether your subform was adding any data in addition to the information that triggers the NotInList event.

In any case, I can get the data the triggers the NotInList event to show up in the combo box automatically but the additional data that I add in my form doesn't show up without me requerying the form. The only one I've been able to get that to work is to manually requery the form via a command button. Thanks.

John

 

[ansentry]

combo box problem

DblDogDare,

This may be well of the mark, but I use on combo boxes when the "NotInList". I only use it for single fields, so if you only require a project name to enter then this may help.

Just change to suit. Hope this helps, if you have any problems let me know. I also have a bit of code that checks for duplicates as well.Neither are mine I found them on the web and just changed them to suit.

Regards





Private Sub Driver_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Drivers Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current Database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set RS = db.OpenRecordset("tbl_Driver", dbOpenDynaset)
On Error Resume Next
RS.AddNew
RS!Driver = NewData
RS.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If


RS.Close
Set RS = Nothing
Set db = Nothing
End If
End Sub