SPEECHLESS IN NEW ENGLAND (3 Viewers)

[esturgesjr]

Just when I thought I'd seen everything, I get this .... the yellow highlighted field should be the field in the list circled in red. Totally unsure how this error came about. I delete the field from the form and replace it with the correct field (the errant field stays in the Field List) and as soon as I click on Save, the field changes back to Expr1011. I try selecting the field and choosing the correct field in the dropdown ... same result. I deleted the form and imported the form from a backup version in Dropbox ... when I open the backup form ... same result. What in Hades is going on here? As the post title says, I am speechless ... well, not really, but all of it would be censored! Any ideas would be welcomed!

 

[Gasman]

Export as text and reimport perhaps?
Upload enough of the DB to see the issue?

 

[Mark_]

Could be a corrupted form.
If Gasman's suggestion doesn't work, I'd suggest recreating. When you do, I'd also look at your data source to figure out what EXPR1011 is. That looks like it is referencing something that no longer exists and should be cleared out also.

Separate item you may want to look at in the very near future is moving all of your training requirements into a child table. For some reason those mandatory trainings keep getting added and never removed. Best to address that early before it becomes a real problem.

 

[esturgesjr]

Here's the SQL behind the form; didn't see anything in here that jumped out. The training is in a separate table as you'll probably see in the code. I'll try recreating the form. Thanks!

SELECT Personnel.AFD_ID, Personnel.[Last Name], Personnel.[First Name], Personnel.Email, Personnel.Phone, Personnel.DOB, Personnel.StartDate, Personnel.TermDate, Personnel.SecStartDate, Personnel.SecTermDate, Personnel.Station, Personnel.Rank, Personnel.Unit, Personnel.Position, Personnel.HomeAddress, Personnel.Status, Personnel.Rank, RT_NIMS.[Tot NIMS], RT_PhysicalsExp.[Expiration Date], RT_PhysicalsExp.[Training Certification] AS [Training Certification_PhysicalsExp], [RT_Blood Borne Cert].[Expiration Date] AS [Expiration Date_Blood Borne Cert], [RT_HazMat Cert].[Expiration Date] AS [Expiration Date_HazMat Cert], [RT_Mask FIT Test].[Expiration Date] AS [Expiration Date_Mask FIT Test], RT_RequiredTraining.[Tot Classes], RT_LiveBurns.[Tot Burns], RT_Drivers.Apparatus
FROM (((((((Personnel LEFT JOIN RT_NIMS ON Personnel.AFD_ID = RT_NIMS.ID) LEFT JOIN RT_PhysicalsExp ON Personnel.AFD_ID = RT_PhysicalsExp.ID) LEFT JOIN [RT_Blood Borne Cert] ON Personnel.AFD_ID = [RT_Blood Borne Cert].ID) LEFT JOIN [RT_HazMat Cert] ON Personnel.AFD_ID = [RT_HazMat Cert].ID) LEFT JOIN [RT_Mask FIT Test] ON Personnel.AFD_ID = [RT_Mask FIT Test].ID) LEFT JOIN RT_RequiredTraining ON Personnel.AFD_ID = RT_RequiredTraining.[Agency Personnel Id]) LEFT JOIN RT_LiveBurns ON Personnel.AFD_ID = RT_LiveBurns.ID) LEFT JOIN RT_Drivers ON Personnel.AFD_ID = RT_Drivers.ID
WHERE (((Personnel.Status)="Active" Or (Personnel.Status)="Suspended") AND ((Personnel.Rank)<>"Junior Firefighter"))
ORDER BY Personnel.[Last Name], Personnel.[First Name];

 

[isladogs]

Your SQL contains Personnel.Rank twice so one of them is renamed Expr1011

As that is the first reference to that field, Access acts correctly in trying to use it.

The error is yours not Access. Delete one of those in the SQL and it will work correctly

 

[DHookom]

While you are fixing your form, you might want to research “data normalization”. You are store data in your field names. A friend suggests you are “committing spreadsheet”.