#Name? error

[THWatson]

A Kiwanis Club database, 2010 format. A form called frmMembers. The SQL behind the form is

SELECT tblMembers.MemberID, tblMembers.LastName, tblMembers.FirstName, tblMembers.MiddleName, tblMembers.PreferredName, tblMembers.Partner, tblMembers.DateOfBirth, tblMembers.WeddingAnniversary, tblMembers.HouseNbr, tblMembers.Street, tblMembers.Address2, tblMembers.City, tblMembers.Province, tblMembers.PostalCode, tblMembers.HomePhone, [b]tblMembers.CellPhone[/b], tblMembers.HomeEmail, tblMembers.Notes, tblMembers.Occupation, tblMembers.BusinessAddress, tblMembers.BusinessPostalCode, tblMembers.BusinessCity, tblMembers.BusinessProvince, tblMembers.BusinessName, tblMembers.BusinessPhone, tblMembers.BusinessFax, tblMembers.BusinessEmail, tblMembers.Status, tblMembers.YearJoined, tblMembers.Sponsor, tblMembers.HowJoined, tblMembers.TransferFrom, tblMembers.YearsInFormerClub, tblMembers.TransferTo, tblMembers.TransferDate, tblMembers.ResignedDate, tblMembers.DeceasedDate, tblMembers.PerfectAttendance, tblMembers.HighestOffice, tblMembers.LegionofHonour, tblMembers.LegionOfHonourYear, tblMembers.LastPerfectAttendance, tblMembers.UseForEmail, tblMembers.MembershipID, tblMembers.LOADate, tblMembers.ReinstateDate
FROM tblMembers
WHERE (((tblMembers.Status)='Active' Or (tblMembers.Status)='Senior' Or (tblMembers.Status)='Leave of Absence'));

When I add the bound text box control to frmMembers to show the CellPhone field data, I get a #Name? error.
I have called the control txtCellPhone.

I have worked on this for the past quite a while and can't seem to solve it.

Any assistance would be appreciated.

Thanks!!

Tom

 

[dhookom]

I would look for another control named CellPhone. Also, your WHERE condition can be simplified:

WHERE Status IN ('Active', 'Senior', 'Leave of Absence');

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
The trouble is there is no other control named CellPhone. The field was added from available fields, just the same as others.

The properties for the field back in the table are the usual, same as for HomePhone, which is also on the form and works just fine.

There is an input mask for the field. !\(999") "000\-0000;0;_

I have fought with this for quite a while, and don't quite understand why it won't display without the error. That's what's so puzzling.

Anyhoo, off for the night.

Thanks for the tip about the WHERE clause in the SQL underlying the form.

Tom

 

[THWatson]

Funny thing...I can create a new form based on the same table (tblMembers), drag the CellPhone field onto it, and no problems. But when I try to add it to the existing frmMembers, I get the #Name? error.

Here's a DropBox link that shows the form.

Link

Tom

 

[PHV]

Did you try a Compact/Repair of your database ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Yes. Actually, the database is set to do that every time it shuts down and restarts.

 

[dhookom]

The picture doesn't really tell us much. I can only guess the form is corrupt since I can't see any of the code or control properties.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

I don't know whether or not this picture will help. It does show the control's properties.

Anyway, here's the link: Link

In the meantime, I am going to take a run at recreating the form, given that it may have become corrupt.

 

[THWatson]

I found the problem!

I had code on the form's OnOpen that was as follows:

Me.RecordSource = "SELECT MemberID, LastName, FirstName, MiddleName, PreferredName, " _
& "Partner, DateOfBirth, WeddingAnniversary, HouseNbr, Street, Address2, City, Province, PostalCode, HomePhone, HomeEmail, Notes, Occupation, BusinessAddress, BusinessPostalCode, BusinessCity, BusinessProvince, BusinessName, BusinessPhone, BusinessFax, BusinessEmail, " _
& "Status, YearJoined, Sponsor, HowJoined, TransferFrom, YearsInFormerClub, TransferTo, TransferDate, ResignedDate, DeceasedDate, PerfectAttendance, HighestOffice, LegionofHonour, LegionOfHonourYear, LastPerfectAttendance, UseForEmail, " _
& "MembershipID, LOADate, ReinstateDate " _
& "FROM tblMembers " _
& "WHERE Status = 'Active' OR Status = 'Senior' OR Status = 'Leave of Absence'"

Note that it didn't not include CellPhone as one of the fields in the Record Source.

This, of course, was overriding the form's underlying SQL.

As soon as I added CellPhone to the above code, it works fine.

I will also make the suggested change to the Where clause in that code.

Sorry to trouble you with this. Don't know why I didn't think to hunt in the Code before.

Thanks very much for your patiently trying to help.

Anyway, all's well that ends well, I guess.

Cheers!

Tom

 

[Andrzejek]

Wouldn't be easier to do:
[tt]
Me.RecordSource = "SELECT * FROM tblMembers " _
& " WHERE Status IN('Active', 'Senior', 'Leave of Absence')"
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[THWatson]

Andy
That would make sense. Then if I added any additional fields to the table I wouldn't have to paw through the code and make changes.

Thanks.