DAO to ADO transition language difficulties 1

[Bry12345]

I'm trying to transition from DAO to ADO, but am having difficulties understanding the statement differences. The code below works fine for a cbo "Not in List" event using DAO . . . I have left the old code commented out to illustrate what I am doing.

The (new) code returns an "Object variable or with block variable not set" error.

Thanks!

- - -
Private Sub Category_NotInList(NewData As String, response As Integer)

'Dim db As Database, Rs As Recordset 'old line
Dim Cnn As New ADODB.Connection 'new line
Dim Rs As ADODB.Recordset 'new line

On Error GoTo Err_Category_NotInList

Dim strMsg As String
strMsg = "'" & NewData & "' is not a valid Category. "
strMsg = strMsg & "Do you want to add the new Category to the list? "
strMsg = strMsg & "'Yes' to add or 'No' to retry!"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Category?&quot = vbNo Then
response = acDataErrContinue
Else
'Set db = CurrentDb 'old line
'Set rs = db.OpenRecordset("tblCategoryList", dbOpenDynaset) 'old line

Set Cnn = CurrentProject.Connection 'new line
Rs.Open "tblCategoryList", Cnn 'new line

Rs.AddNew
Rs!Category = NewData

Rs.Update

End If

Exit_Category_NotInList:
Exit Sub

Err_Category_NotInList:
MsgBox Err.Description
Resume Exit_Category_NotInList


End Sub
- - - -

Bry

 

[cmmrfrds]

Need to create an instance (New) of the recordset.
Dim Rs As New ADODB.Recordset 'new line
OR
Set Rs = new ADODB.Recordset


Here is a good link showing the differences in the code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

 

[Junior1544]

another thing i just noticed...

you have a variable not declared in the first line that starts with strMsg... The Variable name is NewData... just a thought...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[Bry12345]

Junior, thanks . .newdata is reserved . .it holds new values.

Cmmrfrds, thanks as well. The link you provided gave me good insight about the differences between ADO and DAO.

I was only partially successful in getting this to work under ADO. I was able to get the new value added to the lookup table, but unsuccessful in getting the form field to accept the value as 'in the list'. The code works fine in DAO, however, so I'll be glad to stick with that if i can't work thru this. Using DAO, the record is successfully added to the look list and the form field accepts the record as in the list.

Using ADO, however, I hit a 'Catch 22'. The ADO code succesfully added the record to the lookup list, however after doing so, Access fired a second 'not in list' event. I confirmed that the record had been added, and noted that saying 'yes' a second time to add the record resulted in a duplicate value warning.

I attempted to fix the second 'not in list' event with a me.field.requery, but I received an error stating that the record had to be saved first. I added code to save the record . .and then got an error stating that the record could not be saved because the item was not in the list!!@@@@!!!

I'm suspecting that with ADO, the combo box record source may have to be the recordset, but I'm not even going there!!!

I've posted the code below . .both the ADO and DAO lines are commented. If anyone has any suggestions, I'll be glad to try them! If not, I'll make do with the DAO for now.

Thanks!

- - -
Private Sub Category_NotInList(NewData As String, response As Integer)

'Dim db As Database, Rs As Recordset 'DAO
Dim Cnn As New ADODB.Connection 'ADO
Dim Rst As New ADODB.Recordset 'ADO


On Error GoTo Err_Category_NotInList

Dim strMsg As String
strMsg = "'" & NewData & "' is not a valid Category. "
strMsg = strMsg & "Do you want to add the new Category to the list? "
strMsg = strMsg & "'Yes' to add or 'No' to retry!"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Category?&quot = vbNo Then
response = acDataErrContinue
Else
'Set db = CurrentDb 'DAO
'Set rst = db.OpenRecordset("tblCategoryList", dbOpenDynaset) 'DAO


Set Cnn = CurrentProject.Connection 'ADO

Rst.Open "SELECT * FROM tblCategoryList", Cnn, adOpenKeyset, adLockOptimistic 'ADO

Rst.AddNew 'DAO and ADO
Rst!Category = NewData 'DAO and ADO

Rst.Update 'DAO and ADO

'Unsucessful attempt to save record after Me.Category.Requery forced a save
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'ADO

'Wouldn't work . . couldn't save record because it was not in the list!!!
Me.Category.Requery 'ADO

Rst.Close 'ADO

End If

Exit_Category_NotInList:
Exit Sub

Err_Category_NotInList:
MsgBox Err.Description
Resume Exit_Category_NotInList


End Sub - - - -

Bry

 

[cmmrfrds]

Here is a thought on this code.
Rst.Open "SELECT * FROM tblCategoryList", Cnn, adOpenKeyset, adLockOptimistic 'ADO

Rst.AddNew 'DAO and ADO
Rst!Category = NewData 'DAO and ADO
Rst.Update 'DAO and ADO

The cursor type may affect how this code works, although it still should be writing the data to the database after the update. You could try a dynamic cursor as that keeps the data refreshed. adOpenDynamic I don't think a keyset cursor is what you want here anyway.

I am inclined to think the update is not happening immediately by ADO in the database. ADO keeps at least one record in cache. To force a write to the database use the resync method. rs.Resync Although the dynamic cursor should do the same thing.
Leave the rest of the code as you had in the original DAO setup.

Another way to do this since you are only inserting one record in the sub routine is to use the execute method and use sql code. This way it will certainly get added to the database as you expect.

Cnn.execute "insert into tblCategoryList (field) values( etc...."

 

[Bry12345]

Cmmrfrds,

I got it . .tried changing the cursor, but that didn't have any effect.

What did work was adding 'Response = acDataErrAdded' prior to opening the recordset. Why the DAO code worked without this line, given the following explanation from MS, is beyond me.

From the MS Knowledge Base: acDataErrAdded -
Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.

Thanks again! - - - -

Bry