can't enter VBA code on form 3

[THWatson]

Using Access 2000 format with Access 2003

I have a form, frmMembers based on tblMembers. I want to set up two option buttons. One, optAll, will show current members only. The other, optRemoved, will show all members who have been removed.

So on the OnClick event for optAll I want to enter (don't worry about any of the code's syntax; that's not the issue)

Me.RecordSource = "SELECT tblMembers.MemberID, tblMembers.LastName, tblMembers.FirstName, tblMembers.MiddleName, tblMembers.PreferredName, tblMembers.Partner, tblMembers.DateOfBirth, tblMembers.WeddingAnniversary, tblMembers.Address, tblMembers.Address2, tblMembers.City, tblMembers.Province, tblMembers.PostalCode, tblMembers.HomePhone, 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.PerfTill, tblMembers.Updated, tblMembers.UseForEmail, tblMembers.MembershipID, tblMembers.LOADate, tblMembers.ReinstateDate
FROM tblMembers;

But, here's what happens...
Me.RecordSource = ""
SELECT tblMembers.MemberID, tblMembers.LastName, tblMembers.FirstName, tblMembers.MiddleName, tblMembers.PreferredName, tblMembers.Partner, tblMembers.DateOfBirth, tblMembers.WeddingAnniversary, tblMembers.Address, tblMembers.Address2, tblMembers.City, tblMembers.Province, tblMembers.PostalCode, tblMembers.HomePhone, 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.PerfTill, tblMembers.Updated, tblMembers.UseForEmail, tblMembers.MembershipID, tblMembers.LOADate, tblMembers.ReinstateDate
FROM tblMembers;

It won't allow me to enter anything within the "" marks. And it won't allow me to type anything prior to the word "Select." Just beeps.

I tried rebooting the computer. No difference.

I'm puzzled.

Tom

 

[RoyVidar]

Sometimes, behaviour like this occurs because there's a timer running in an open form.

Else, I wouldn't know, except for corruption.

Roy-Vidar

 

[THWatson]

Thanks RoyVidar
There's no timer running on this form.

Currently, I am trying to pull the relevant tables and form into a new database and see what gives.

Tom

 

[THWatson]

Just pulled the relevant tables and form into a small separate database. Same deal.

Arghhh!

Tom

 

[THWatson]

I also tried putting things in a two button Option Group. Same results.

Tom

 

[Remou]

Have you tried creating a new form in the new database? It would be best to use an alias for members to cut down on the number of characters:

Me.RecordSource = "SELECT m.MemberID, m.LastName, m.FirstName, 
<...>
m.MembershipID, m.LOADate, m.ReinstateDate
FROM tblMembers m;

http://lessthandot.com

 

[randy700]

I was able to re-create your problem and fixed it like this:

Private Sub Command0_Click()
    Me.RecordSource = "SELECT MemberID, LastName, FirstName, MiddleName, PreferredName, " & _
        "Partner, DateOfBirth, WeddingAnniversary, Address, 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, PerfTill, Updated, tblMembers.UseForEmail , MembershipID, " & _
        "LOADate, ReinstateDate FROM tblMembers"
End Sub

Randy

 

[THWatson]

Remou
Yes, that works. Thanks!

Can you help me understand why?

Tom

 

[RoyVidar]

If it was all pasted as one long string, then the length of the string exceeds maximum line length in the VBA editor, I think.

Roy-Vidar

 

[RoyVidar]

I think the max length is 1023 or 1024 characters, this string seems to be 1133 characters

Roy-Vidar

 

[THWatson]

Oh, I wondered but I couldn't find a reference to a maximum length of line in the VBE editor.

When I split the lines, it works!

Tom

 

[THWatson]

Randy
You're right. I fixed it slightly differently, but thanks!

Tom