Alphabetize church Name

[The_Doc_Man]

Dick, the SQL I'm talking about is in the form's .RecordSource, not in your form-opening code. I might have been a little vague in specifying that, for which I'm sorry.

 

[gemma-the-husky]

When you say "alphabeticize", do you mean "sort"

Is the form based on a query? If so have a look at the query properties, and see if a filter or sort uses the field [id]. Maybe that field is not included in the query, and that is causing a problem. That sort of thing.

I thought you meant add capital letters to the first letter of words in all the church names.

 

[Dick7Access]

When you say "alphabeticize", do you mean "sort"

Is the form based on a query? If so have a look at the query properties, and see if a filter or sort uses the field [id]. Maybe that field is not included in the query, and that is causing a problem. That sort of thing.

I thought you meant add capital letters to the first letter of words in all the church nam

When you say "alphabeticize", do you mean "sort"

Is the form based on a query? If so have a look at the query properties, and see if a filter or sort uses the field [id]. Maybe that field is not included in the query, and that is causing a problem. That sort of thing.

I thought you meant add capital letters to the first letter of words in all the church names.

My Bad, yes sort. As of right now I can't find a query. The form record source is main table. (Probable my bad again) but I coded this 25 years ago. I wanted to hunt for SQL in data source but haven’t had time. Today my lawn mower died, took 3 hours to get bolt out of my truck hitch to get my trailer on, and gets a call they just took my wife to ER. Got a call from VA and they want to stick it up my butt. <G> (colonoscopy) Just got back from ER nothing serious as of now. Did a quick search of table, no SQL or at least I don’t know where to look.

 

[The_Doc_Man]

OK, now we are getting crazy. If your .RecordSource is a table, then no function can be involved there unless there is a computed field in the table.

The question is then going to be, "What happens in the form's _Open, _Load, and _Current events?" Because that error message says that SOMETHING is diddling with something that shouldn't be diddled with. I'm going to make a wild-eyed guess here. Does that table have a field called ID and is that field an autonumber? And if so, is there code in one of the form events that tries to assign a value to the ID field?

 

[Dick7Access]

OK, now we are getting crazy. If your .RecordSource is a table, then no function can be involved there unless there is a computed field in the table.

The question is then going to be, "What happens in the form's _Open, _Load, and _Current events?" Because that error message says that SOMETHING is diddling with something that shouldn't be diddled with. I'm going to make a wild-eyed guess here. Does that table have a field called ID and is that field an autonumber? And if so, is there code in one of the form events that tries to assign a value to the ID field?

CHID Church Id autoNumber

FairID number Long Integer

trip04Sort number Long Integer



Form nothing on current

 

[The_Doc_Man]

Dick, do a FIND within the VBA code of that form (or heck, the whole project) for the "Whole Word" ID because Access is telling you that identifier or variable or object does not exist, yet it is being referenced by that error message. Doing a FIND allows you to check or uncheck "Whole Word Only" and you want that option checked. The term ID appears in quotes and Access is pretty good about naming the things it doesn't like. So there has to be something called ID somewhere. The fact that you have a couple of ID autonumber fields means you could have made a typo for one of them at some point. I would say to also check the relationships, but that interface doesn't allow type-ins, so it wouldn't be there. If you have any macros in the project, see if any of them reference ID as a field.

 

[Dick7Access]

No rude comments. <grin> I made this over 25 years ago.

 

[Gasman]

Even I should be able to open it then?

Edit: Well I tell a lie. I get unrecognised format?

 

[LarryE]

No rude comments. <grin> I made this over 25 years ago.

It took me a while to figure out this thing, but I opened the frmChurchesAllDick form and then deleted and replaced the txtChurch textbox control with a new one. After I did that, then the sorts worked in both datasheet view and regular form view. It appears the textbox control itself was the problem. So:

1. Open frmChurchesAllDick in design mode
2. Delete the txtChurch textbox control
3. Add a new textbox control and bind it to the txtChurch field as you did previously

There are lots and lots of naming convention issues as well as other problems in this file and you don't even have the CHID field set as a Primary key in the tblCHURCHES table, but I make no further comments. If you replace the txtChurch textbox with a brand new one, I got the sorts to work.

 

[gemma-the-husky]

@Dick7Access

On your form Dicks Churches, there is a non visible text box with the control source =IIf(IsNull([ID]),"(New)","Open")

I deleted the control source, and that stopped the error. It must be something you used a long while ago.

Jenny doesn't seem to have that text box!

 

[Dick7Access]

It took me a while to figure out this thing, but I opened the frmChurchesAllDick form and then deleted and replaced the txtChurch textbox control with a new one. After I did that, then the sorts worked in both datasheet view and regular form view. It appears the textbox control itself was the problem. So:

1. Open frmChurchesAllDick in design mode
2. Delete the txtChurch textbox control
3. Add a new textbox control and bind it to the txtChurch field as you did previously

There are lots and lots of naming convention issues as well as other problems in this file and you don't even have the CHID field set as a Primary key in the tblCHURCHES table, but I make no further comments. If you replace the txtChurch textbox with a brand new one, I got the sorts to work.

Wow! amazing. What prompted you to try and replace "txtChurch" text box. I know the whole DB is a mess, but I have tried a coup of times to create a new completely new DB and import all the data but was never successful. I even offered someone payment to make one for me and they just laughed. I can't get rid of it as it's something I have to use constantly. It has over 13000 records. Thanks, as soon as I close here, I will try it on mine copy.

 

[Dick7Access]

@Dick7Access

On your form Dicks Churches, there is a non visible text box with the control source =IIf(IsNull([ID]),"(New)","Open")

I deleted the control source, and that stopped the error. It must be something you used a long while ago.

Jenny doesn't seem to have that text box!

You are so right. Back then I add stuff quickly so I could get to the task of building my ministry, and not spend time studying how to do it correctly, so ended up with all kind of junk that I didn't need. Actually, I could have managed without fixing the sort problem as it sorted anyways but gave me that error message and it's been bugging me for years. Thanks everybody AWF great place, I love it. BTW: I read many years ago how some of the UK guys got together and thought that was a good idea and over the years tried it in the US. Only success I have had was with Doc and a guy in Jacksonville, FL and he has since moved. So, try, and try again. I will be in Lewisburg, WV for two weeks in August. Any AWF people want to get together for coffee, give me a shout.

Ps. I have tried LarryE suggestion and it on my db, and it worked. Thanks LarryE

 

[LarryE]

What prompted me to try that is if you sorted the CHID field, the sorting worked, so the problem was not the form itself, so it had to be one of the other fields and the only other field that contained data was the txtCHURCH field. Actually, I have seen this problem before in other databases. It is rare, but happens. This error can be caused in hundreds of ways though. Gemmas suggestion is also valid. Get rid of that field if it doesn't do anything. The syntax is wrong anyway.

 

[Dick7Access]

What prompted me to try that is if you sorted the CHID field, the sorting worked, so the problem was not the form itself, so it had to be one of the other fields and the only other field that contained data was the txtCHURCH field. Actually, I have seen this problem before in other databases. It is rare, but happens. This error can be caused in hundreds of ways though. Gemmas suggestion is also valid. Get rid of that field if it doesn't do anything. The syntax is wrong anyway.

Thanks

 

[LarryE]

Thanks

You don't need to use datasheet view to sort anyway. You can just add 2 commandbuttons to the form. One for Sort ascending and one for sort descending. On the On Click event put the following VBA code for Ascending:

DoCmd.GoToControl "txtCHURCH"
DoCmd.RunCommand acCmdSortAscending

and for Descending:

DoCmd.GoToControl "txtCHURCH"
DoCmd.RunCommand acCmdSortDescending

Then just click the buttons to sort.

 

[Dick7Access]

You don't need to use datasheet view to sort anyway. You can just add 2 commandbuttons to the form. One for Sort ascending and one for sort descending. On the On Click event put the following VBA code for Ascending:

DoCmd.GoToControl "txtCHURCH"
DoCmd.RunCommand acCmdSortAscending

and for Descending:

DoCmd.GoToControl "txtCHURCH"
DoCmd.RunCommand acCmdSortDescending

Then just click the buttons to sort.

I don't think that will quite do it. Many times, I am looking for a church and all I have is the pastors last or first name or some other field or first name and city, or sometimes just a zip code.

 

[Gasman]

I don't think that will quite do it. Many times, I am looking for a church and all I have is the pastors last or first name or some other field or first name and city, or sometimes just a zip code.

What is wrong with a simple Find then?
Or if you need to search on more than one control, create a search Form for the most searched controls?

 

[LarryE]

I don't think that will quite do it. Many times, I am looking for a church and all I have is the pastors last or first name or some other field or first name and city, or sometimes just a zip code.

Then you should not be sorting anyway, you should be performing a search in all fields on the form for what you are looking for and finding matches. You use the DoCmd.FindRecord command to do that.

1. Add a textbox to your form and name it SearchCriteria
2. Save the form
3. Create an Event Procedure with the After Update event
4. Put the following code in the After Update event

Dim Criteria As String
Criteria = Me.SearchCriteria
DoCmd.FindRecord Criteria, acAnywhere, , acSearchAll, , acAll

You must also add a command button to find the next instance of your criteria using the DoCmd.FindNext command in the buttons On Click event:

1. Add a command button with the Caption: Find Next
2. Add the following lines in the buttons On Click event:

Dim Criteria As String
Criteria = Me.SearchCriteria
DoCmd.FindNext

 

[Dick7Access]

What is wrong with a simple Find then?
Or if you need to search on more than one control, create a search Form for the most searched controls?

yes, sometimes I do that, but other times it's quicker to just scroll. for example, sometimes I have a person that I need to find a church that is closest to their home. I sort by zip and in two seconds I have x number of churches with that zip. Sometime zip is only last number different.