Access 2003 to A 2007 DAO questino

[Simon]

I have not converted to A2007 yet, I have given a user a copy of a database
built in A2003 to use who is now only using A2007.

On my main inputting form I use several combo boxes, when the user needs to
add an item to the list if they type in the new text in the Combo Box and
press enter a function is called from the NotInTheList event.

This function built using DAO to add a new item to a list (when recoginising
the item is not in the list for the the function asks if the new text should
be added to the list (separate table) and if yes is selected, it will add the
new item to list using DAO and the New Item can now be displayed on the Form.
However the message that comes up is not the message MsgBox from the
function. The VBA references show DAO 3.6 object library is checked.

Is there anything to that can be done to restore functionality - I use this
function several times to added new items to various lists (tables) to
populate several combo boxes on the main inputting form.

Thanks,

Simon

 

[Allen Browne]

Access 2007 is fine with the DAO library.

You can open an MDB in Access 2007, and it uses dao360.dll as
References("DAO"). If you use the new file format,it uses acedao.dll as
References("DAO"). So it works correctly either way.

Perhaps you can clarify what behavior you are seeing. Error message? Nothing
happens at all? Compile error? Other?

 

[Simon]

Access displays the message:

The text you are entering isn't an item in the list.
Select an item from the list, or enter text that that matches one of the
listed items.

There is an "I" balloon on the message.

Here's the code I have in the NotInList Event

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

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "New Entrant Name") Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblEntName")
rst.AddNew
rst("Name") = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If

End Sub

 

[Allen Browne]

Hmm. Several things could go wrong here.

Firstly, you have a field called Name. That's something Access regularly
gets confused about.

Secondly, the question arises about which is the combo's bound column. The
NewData comes from the BoundColumn. Check out these properties for the
combo:
- RowSource: what is it? A table? A query?
- Column Count: how many columns?
- Bound Column: which one is the bound column?
- Column Widths: is the bound column the one you see? Or is it zero-width?

Temporarily add this line to the event procedure:
Debug.Print NewData
When it fails open the Immediate Window (Ctrl+G) and see if what came out
was what you expected.

If it still doesn't look right, perhaps you could add a break point (press
F9) at the top of the procedure, and then single-step through it (pressing
F8 repeatedly), while checking out what's going on.

 

[Simon]

Thanks for your help Allen - when I visited the user tongith I found they
still had security level set to block VB code.

Simon