The dreaded apostrophe 2

[Drisconsult]

Hello All

I am updating a university Teacher Traing School Placment program. Here student teacher's are assigned to schools. There is a large table of 450 schools. Many of the schools have apostrophes in their title such as:

City of London Girl's School

This is how they wish the school to be known. However the dreaded apostrophe is giving me a headache during the placing of a teacher to that school and then deducting one subject offer such as Art from that school. I have to remove the apostrophe to get my syncronised forms to work.

Does any anynody have a solution to the apostrophe?

Regards
Terence
London

 

[RuralGuy]

The general solution is to use the Replace() function to make 1 apostrophy into 2. Replace(YourField,"'","''")

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[JoeAtWork]

You never say why this is such a problem, but RuralGuy's post will probably point you to the solution. When apostraphes are part of the SQL statement, you double them up.

SELECT Address FROM Schools WHERE SchoolName = 'City of London Girl''s School'


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[fneily]

If your tables are set up correctly, you shouldn't be using the school's name until report time/presentation time. The school's name should be a field of a record which should have a primary key that is used for referencing and queries. So using the above example:
SELECT Address FROM Schools WHERE SchoolID = Forms![Formname]![SomeControlName]
SomeControlName would be bound to SchoolID not School name.

 

[Drisconsult]

Hello RuralGuy and fneily

My apologies for not making my problem clearer.
I have a relational query made up of three tables:

tblSTUDENT
tblSCHOOL
tblPLACEMENT

Then a form has been created on this query. On the form there are two List Boxes. One under the fields relating to the student data, the other under the fields retating to the school data.

The Student data would be for example, all students who are going to be placed into a school teaching art. The School data would show all the schools offering art as a subject.

The user then clicks on a student name, then on the approprite school and we have a marriage. Now the problem arises after a student has used up one of the offers. If the school has made three offers for art, an offer has to be deducted from the total.

This is not a problem. I have a form that is syncronised with the School field in the school table and a popup form that shows the school selected and the number of offers the school has made. The user simply deletes the 3 and inserts the 2, refreshes the form and all is well.

The problem arises when the school selected has an apostrophe in it's name. I cannot syncronise the two forms and get an error message. Hope this hasn't been too long-winded. If I could send you a screenshot of this procedure it would really help! If you have an email address of any kind I can send it to, please let me know.

Regards
Terence

 

[RuralGuy]

Any chance the tables have an AutoNumber as a PrimaryKey? fleily has the right idea. You then use the PK to manupulate the records rather than an ascii name field.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[Drisconsult]

Hello

I agree with you that I should be using the primary key, which I will now try to convince the secretaries that are responsible for teacher placement. They did want to see the names of the school rather than the primary key, which means nothing to them. But I will try and convince them.

Thank you so much for all your help in this matter, it is really appreciated. If I am successful, I will report back.

Regards
Terence

 

[JoeAtWork]

Your users don't have to know the primary key, in fact it's usually better if they don't even know it exists. You show the school name on the form, but internally you identify it with the primary key.

So for example if you have a "School" dropdown list, the first column is the primary key and it is invisible (by setting column one's width to zero). The second column has the name and is visible. You set the BoundColumn property to 0 (i.e. the first column).

So now, the value of the dropdown is the primary key, but what the user sees is the school name.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Drisconsult]

Hello JoeATWORK

I have just returned from a visit to the "Ladies" that run the School Placement program and they were delighted with the solutions provided by Ruralguy and fneily.

As you state the primary dosen't have to be seen, in fact that is how I had craated it. They are happy that the apostrophe is still there, I am happy because I have acquired a little bit more knowledge, and happier still because I know where I can return to increase that knowledge.

Thank you all
Terence
London

 

[Drisconsult]

Hello All Again

Just had a phone call from the Student Placement team asking me if I can place a URL on the form that will provide them with all school and colleges addresses in London. I have the URL but must admit I have never tried this one before. Have looked up the Adobe solution, which informs you to use text rather than the hyperlink, but got nowhere with this solution.

How would I place a Hyperlink on the form where new schools are added?

Regards
Terence

 

[Drisconsult]

Hello All

My apologies, I just did not realise how simple it was. Have successfully placed the hyperlink on the form.

Thank you all in advance.
Regards
Terence

 

[postermmxvicom]

Drisconsult,

I hate to butt in, but, I really think you should reconsider JoeAtWork's suggestion. It is very easy to implement. (I believe Access even sets it up for you this way when you run certain wizards.) I also think everyone here in this thread would agree. I can't speak for them (and I myself am a newbie), but I believe what JoeAtWork is suggesting is, in fact, standard/best practice.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[Drisconsult]

Hello Postermmxvision

Yes I did follow his suggestion and it works beautifully. I often wonder why people spend so much time helping people they never see or meet. Makes me feel good about my fellow man.

Regards
Terence

 

[postermmxvicom]

My apologies, I must have misread your reply. (I am really good at misreading things)

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[jamaarneen]

Hi Terence,

I often wonder why people spend so much time helping people they never see or meet. Makes me feel good about my fellow man.

Just to remind you: there is a perfect way to express your good feelings, by clicking on the [blue]Thank JoeAtWork
for this valuable post![/blue] button...
Just a little purple star ;-)