In order to standardize terminology and nomenclature throughout my database, I had to rename a bunch of tables and forms. Those are all good with no problems, and now I'm starting to rename fields. I'm basically just changing the word "Packages" to "Missions". Yesterday I renamed my main mission tracking table from tbPackages to tbMissions. No problems with that, after I updated all my queries, forms, and VBA events that are dependent on that table.
Today, I renamed the primary key field of that table from Package_ID to Mission_ID. I've replaced the word "Package_ID" with "Mission_ID" throughout all VBA modules and procedures, and I've updated all queries, forms, and reports too. All relationships are good and everything working fine. Except for my main form, fmMissions...
fmMissions consists of a subform in datasheet view that lists all the records so that users can click on a record and the info will load in the main form (just like with a splitform). Both the main form and subform use the same exact query: quMissions. My problem arises when I try to click on a record in the subform to load it in the main form or try to compile the database in VBA Editor: I get an error "Compile Error: Method or data member not found".
My VBA for the OnCurrent event of my subform is this:
The Compile Error highlights ".Mission_ID" in the rst.FindFirst line. Both forms are based off quMissions which has the Mission_ID field in it. Opening the query produces no errors as there is nothing wrong with the query; Mission_ID shows up as its own column just like it's supposed to do. I even put a text field in both the main and subform that display the Mission_ID, and both fields show the Mission_ID in form view. I've used AllenBrowne's findfield() function (http://allenbrowne.com/ser-73.html) to search for "Project_ID" and it found 0 results since I renamed everything to Mission_ID.
If I add "Msgbox Me.Parent.Recordset.Name" and "Msgbox Me.Recordset.Name" to my OnCurrent event of the subform, both shows quMissions. I've added the following code to my OnCurrent event that lists all fields for the recordset of the main form:
Sure enough, quMissions is the recordset, and Mission_ID is listed while Package_ID is nowhere to be found. Even if I set rst = me.recordset, the results are exactly the same. quMissions is recordset, Mission_ID is listed while Package_ID is not.
Now...doing a search in the Object Browser still finds "Package_ID" as "WithEvents Package_ID As AccessField" in CurrentDatabase.Form_sbfmMissions. But there's no field in my subform with that name, and I checked every single attribute for every single field and none of them reference Package_ID anywhere.
oierjlskjflkjl askdlaksdf asdlf :banghead: Okay, I just fixed the problem by simply clearing out the Record Source of the subform and then setting it back to quMissions. I watched the Package_ID object disappear from the Object Browser as I did this. Now everything is kosher. I typed all this up and fixed it in two seconds. :banghead: I should've just tried that before spending hours researching this problem. Oh well, hopefully someone finds this useful.
On another note, does anyone have any tips for efficiently renaming a table's field and then updating all forms, queries, reports, and VBA modules/procedures to reflect the new name?
Today, I renamed the primary key field of that table from Package_ID to Mission_ID. I've replaced the word "Package_ID" with "Mission_ID" throughout all VBA modules and procedures, and I've updated all queries, forms, and reports too. All relationships are good and everything working fine. Except for my main form, fmMissions...
fmMissions consists of a subform in datasheet view that lists all the records so that users can click on a record and the info will load in the main form (just like with a splitform). Both the main form and subform use the same exact query: quMissions. My problem arises when I try to click on a record in the subform to load it in the main form or try to compile the database in VBA Editor: I get an error "Compile Error: Method or data member not found".
My VBA for the OnCurrent event of my subform is this:
PHP:
Private Sub Form_Current()
Dim rst As Recordset
If Me.Parent.Name = "fmDCO_Missions" Then
If Application.CurrentProject.AllForms("sbfm_Missions").IsLoaded Then
Exit Sub
Else
Set rst = Me.Parent.Recordset
If CLng(Nz(Mission_ID, 0)) < 1 Then
Exit Sub
End If
rst.FindFirst "Mission_ID = " & Me.Mission_ID
rst.Close
End If
End If
End Sub
The Compile Error highlights ".Mission_ID" in the rst.FindFirst line. Both forms are based off quMissions which has the Mission_ID field in it. Opening the query produces no errors as there is nothing wrong with the query; Mission_ID shows up as its own column just like it's supposed to do. I even put a text field in both the main and subform that display the Mission_ID, and both fields show the Mission_ID in form view. I've used AllenBrowne's findfield() function (http://allenbrowne.com/ser-73.html) to search for "Project_ID" and it found 0 results since I renamed everything to Mission_ID.
If I add "Msgbox Me.Parent.Recordset.Name" and "Msgbox Me.Recordset.Name" to my OnCurrent event of the subform, both shows quMissions. I've added the following code to my OnCurrent event that lists all fields for the recordset of the main form:
PHP:
Debug.Print "Recordset: " & rst.Name & vbNewLine & vbNewLine & "Field Count: " & rst.Fields.Count
Dim f As DAO.Field
For Each f In rst.Fields
Debug.Print f.Name
Next
Sure enough, quMissions is the recordset, and Mission_ID is listed while Package_ID is nowhere to be found. Even if I set rst = me.recordset, the results are exactly the same. quMissions is recordset, Mission_ID is listed while Package_ID is not.
Now...doing a search in the Object Browser still finds "Package_ID" as "WithEvents Package_ID As AccessField" in CurrentDatabase.Form_sbfmMissions. But there's no field in my subform with that name, and I checked every single attribute for every single field and none of them reference Package_ID anywhere.
oierjlskjflkjl askdlaksdf asdlf :banghead: Okay, I just fixed the problem by simply clearing out the Record Source of the subform and then setting it back to quMissions. I watched the Package_ID object disappear from the Object Browser as I did this. Now everything is kosher. I typed all this up and fixed it in two seconds. :banghead: I should've just tried that before spending hours researching this problem. Oh well, hopefully someone finds this useful.
On another note, does anyone have any tips for efficiently renaming a table's field and then updating all forms, queries, reports, and VBA modules/procedures to reflect the new name?