Split DB - combo notinlist VBA refresh problem

[tchaplin]

I have the code below with a split database (front/back-end). I enter information that isn’t in the list and get the following “The text you entered wasn’t an item in the list, select an item in the list” when selecting a combo box. The code seems to be working as the new city is entered into the table just the combo box isn’t refreshing to show or refresh to the new data

It really has me scratching my head. (I realise that haven’t closed the DB connection)
I tried requery on the form (then the error got into a loop – recurring). The data field is text, not euro, that is illustrated by Microsoft as having problems.

The underlying data has changed just not displaying it on the form.

Any suggestions would be greatly appreciated.

Thanks Todd

Private Sub ComboCity_NotInList(NewData As String, Response As Integer)
' If the new item isn't in the list it asks if you would like to add it

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the city list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown City...")
If i = vbYes Then

strSQL = NewData
' Opens the DB connection
Call OpenConnection
'calls the sub below
ExecuteSQL (strSQL)


Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


End Sub

‘In the module section

Public conDb As ADODB.Connection

Public Sub OpenConnection()

Dim strDBPath As String

strDBPath = "MarkBe.mdb"
' Initialize Connection object
Set conDb = New ADODB.Connection
With conDb
.ConnectionString = strDBPath

' Specify Microsoft Jet 4.0 Provider and then open the
' database specified in the strDBPath variable.
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath

End With

' Close Connection object and destroy object variable.
' cnnDB.Close
' Set cnnDB = Nothing
End Sub

Public Function ExecuteSQL(strSQL As String) As Recordset

Dim rst As New ADODB.Recordset

Dim cmd As ADODB.Command


Set rst = New ADODB.Recordset
Set rst.ActiveConnection = conDb
rst.CursorLocation = adUseClient
rst.CursorType = adOpenKeyset

rst.LockType = adLockOptimistic
rst.Open "tCity", , , , adCmdTable
rst.AddNew

rst!CityName = strSQL
rst.Update
rst.Requery

rst.Close

End Function

 

[Mute101]

Hi,

The first thing you should check is that [blue]Limit to [/blue] is set to No on the Combo control. I think that should sort that problem out.

Hope that sorts it out
Sim

----------------------------------------
I was once asked if I was ignorant or just apathetic. I said, "I don't know, and I don't care."
----------------------------------------

 

[randy700]

I would leave LimitToList set to Yes. Otherwise, the NotInList event will never fire. Try adding Me.ComboCity.Requery after acDataErrAdded.


Randy

 

[tchaplin]

I Agree with Randy the LimitToList needs to set to set to yes. I tried to requery the combo and a message saying "You must save the current field before you ruin the requery action”. Strange as the underlying record has been saved. I looked in the remote back end DB.
Any suggestions much appreciated
Todd

 

[tchaplin]

Still No luck
I’ve managed to get it working using DOA; however can't get it to work in the original example using ADO.

Any Ideas as to why the ADO doesn't work appreciated
thanks Todd

The DOA which works


Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim wsp As Workspace
Set wsp = DBEngine.Workspaces(0)

Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current DLSAF?"
strMsg = strMsg & "@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 = wsp.OpenDatabase("C:\WINDOWS\Desktop\Access\Markbe.mdb")

Set rs = db.OpenRecordset("tcity", dbOpenTable)


On Error Resume Next
rs.AddNew
rs!CityName = NewData
rs.Update

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

End Sub

 

[willir]

tchaplin

Regardless of ADO, DAO and SQL, the process will remain the same...
- NotInList EventProcedure determines new entry and triggers events
- New record added to table (DAO, ADO or SQL)
- Requery the combo or list box to repopulate the values.

Works every time.

If it is not working, why?
- Is the NotInList event kicking in?
- Is the new record added?
- Is the combo box requeried -- with the correct query?

Hmmm...

'....

       On Error Resume Next
        rs.AddNew
            rs!CityName = NewData
        rs.Update
       [COLOR=blue] Me.cbxAEName.Requery[/color]

I do not see any obvious requery. I added one place where you can include the command in blue...

Richard

 

[RoyVidar]

For the ADO, I'd tried issuing an .execute on the connection object in stead of opening the recordset:

[tt]conDb.execute("insert into tCity (cityname) values ('" & newdata & "');")[/tt]

Executing queries is often preferrable vs opening recordsets.

But there are sometimes timing issues, that I'm not sure how to overcome, and it might just be what you're experiencing. And DAO is supposed to be faster than ADO on native tables.

Also I see you're opening a connection to another database, If linking the tables are an option, I think that would be faster (then in ADO use the currentproject.connection in DAO the CurrentDB).

Issuing a .Requery on the combo is one thing, I don't know if issueing a DoEvents after the insert might help, then also release the recordset, in addition to closing it (set rs=nothing)

Roy-Vidar

 

[tchaplin]

Answers to above question
Yes – the NotInList event kicking in.
Yes the data in the remote table is being added
The combo-box is not showing the correct new data. There must be a logical reason.

Perhaps this is a timing thing - in the below syntax everything works bar the combo box being updated. The suppressed error message "You must save the current field before you run the requery action" – occurs when the combo is attempted to be requeryed . It does seem to be too slow to recognize that the data is saved.

Thanks once again



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


Dim strDBPath As String
Dim condb As ADODB.Connection

strDBPath = "C:\WINDOWS\Desktop\Access\Markbe.mdb"

Set condb = New ADODB.Connection
With condb
.ConnectionString = strDBPath
' Specify Microsoft Jet 4.0 Provider and then open the
' database specified in the strDBPath variable.
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With


Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = condb

rst.CursorLocation = adUseClient
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open "tCity", , , , adCmdTable


Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current DLSAF?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else


On Error Resume Next
rst.AddNew
rst!CityName = NewData
rst.Update
rst.Requery
Me.Combo5.Requery

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

End If
End If


End Sub

 

[tchaplin]

I have also tried executing SQL, unfortunately to the same result.

Thanks for your comments

 

[randy700]

I still don't see a requery of the combo box AFTER the Response = acDataErrAdded in any of your code.

I've had the same problem in the past and this is how I fixed it.

Randy