Database engine doesn't recognise a field name as valid

[wedd]

Hi experts,

I have a field name called location in a table name called Administration. I've created a drop down list on a form and I've allowed the user to add a new location via a not in list VBA code...however when the user enters the new location name in the input box a message box it displays "Database engine doesn't recognise a field name as valid" even though the field name is in a table. Is there a reason why this is happening? Can this be resolved, if so how can this be done? Would yo have any examples or websites or other resources or experiences you had done to resolve this issue?


Thanks for your contributions :friends:

[HiTechCoach]

Without seeing your VBA code it will be hard to spot the issue. Please post your code.

[wedd]

Hi HiTechCoach,

here is the code:

Private Sub cboLocations_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String
On Error GoTo Err_Locations_NotInList
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Locations", dbOpenDynaset)
' Ask the user to input a new Customer ID.
Msg = "Please enter the new name of" & vbCr & "Venue."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("Location", dbText, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("Customer ID " & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.
Rs![BookingID] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![Locations] = NewData
' Save the record.
Rs.Update
' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
End If
Exit_Locations_NotInList:
Exit Sub
Err_Locations_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue
End Sub

[HiTechCoach]

What line is generating the Error?

Coudl it be "CustomerID" in this line:

Rs.FindFirst BuildCriteria("CustomerID", dbText, NewID)

[wedd]

Hi, HiTechCoach! It is possible that that line of code is generating the error. I have an access database developed in access 2010 (dbase A). However, I would like to make changes to the table names, queries, and vba coded forms to be incorporated into a different access 2010 database (dbase B). The customerID was intially called MagazineID in dbase A, but I changed the name on the tables, queries and vba code hence the error.
Are their methods I can use to make changes to an access 2010 database queries, vba forms, vba reports by changing the field names in dbase A to be incorporated or exported into another access 2010 database dbase B, that will perform the same actions on front end interfaces, i.e vba generated buttons to produce reports on click events etc ; but the only changes within the code would be the field names in the tables; and the queries, vba forms and reports; that won't cause any issues or any error messages displaying: not recognised when I've changed and incoporated the code into dbaseB based on Dbase A? I know that Microsoft have made it very difficult to copy coded forms etc from a dbase 2010 to another dbase 2010 to prevent users taking developers code...

Thanks for your suggestion!

[HiTechCoach]

I know that Microsoft have made it very difficult to copy coded forms etc from a dbase 2010 to another dbase 2010 to prevent users taking developers code...



I have to totally disagree.

If you are expecting to be able to copy and past code between database to be as easy as copying text between word documents than you really are going to have a difficult time with database programming. It just is not that easy. When you start write code for a database there is just a whole lot to learn. It has been that way long before Microsoft every create Access or any other development system.

Microsoft did not do a single thing special to make it difficult to do copy code. You would have the same issue with any programming language in any development system. Even non Microsoft development platforms.

If your object names are hard coded to a specific database (db) then it is true that it is not easy to copy the code to a different database with different object name. It is not supoosed to be by design. If you want code that can easily be copied between database then you will have to crete generic functions so that you can pass al the objects as parameters. Even then you will still need to modify or type the inital function call.

It does not matter what programing language you are sing, creating generic functions to be easy used in different database is more difficult to do. This requires someone who know how to write the code from scratch. Not someone who only knows how to copy "n" paste code that they don't understand.


TIP: dbase is a different product than Access. I think by dbase you are trying use it as an abbreviation for database. That would be db not the product name of dbase.