Coding error

[sarahdaws]

Hi

I have been able to do this code to update a table which feeds a table which provides information to a combo box.

The first part works fine, it asks if you want to add it and adds it to the underlying table"addq" but the added value does not appear in the dropdown list and therefore cannot be selected

I presume that i have managed to mess up the refresh statement

Here is the code (Access2000)

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

'Adds a question number that is not in the list to the list, if the user wishes to do so.

Dim strMessage As String
Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you wish to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Addq")

rstKeyWord.AddNew

rstKeyWord!Questionnumber = [NewData]

rstKeyWord.Update

Me.Refresh

Response = acDataErrAdded

Response = acDataErrDisplay

End If

End Sub

Thanks in anticipation

Sarah

 

[Rich]

Try
Me.Requery or Me.MyCombo.Requery

 

[fuzzygeek]

He's right

While getting the answer, he answered. Here is whyfrom the refresh help on Refresh:



The Refresh method shows only changes made to records in the current set. Since the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.

 

[sarahdaws]

I think that worked but hard to tell

Now its saying that I need to save the current field before running the requery

sarah

 

[fuzzygeek]

Puzzled

Try closing the recordset and setting it to nothing before you requery.

rstKeyWord.close
set rstKeyWord = Nothing