Problem editing a form after adjusting sorting.

[Rob Hofkens]

Hello !

I have a simple form with adresses of contacts.
I have a problem with editing the form after I adjust the sorting.

Currently I have set the record source to SELECT * FROM tblContacts ORDER BY
Name;
This works fine but I also want the city name in the sorting.
The city name is stored in a big second table with all the city names of my
country.
So I use a CityID field in the tblContacts and link it to the table
tblCities.

To get the table sorted the way I like I would need something like this SQL
:

SELECT tblContacts.*
FROM tblCities INNER JOIN tblContacts ON tblCities.CityID =
tblContacts.CityID
ORDER BY tblContacts.Name, tblCities.City;

But when I use this as the record source then the form disables all Editing
and Adding

Is there any way how I could use the sorting I want and still be able to add
or edit the tblContact through the form?

Thanks in advanced!

Rob.

 

[Anne]

If you create a query and link the two tables, sort by ContactName then by
city.
Use the query for your form.

 

[Rob Hofkens]

Hi Anne , thanx for anwering my question!

But I believe my SQL I mentioned before is the same as you suggest.
I copied & pasted it in here from the query.

SELECT tblContacts.*
FROM tblCities INNER JOIN tblContacts ON tblCities.CityID =
tblContacts.CityID
ORDER BY tblContacts.Name, tblCities.City;

When I use this query the form does sort OK but it DOESN'T allow me to
enter data anymore or even to add a record.

Or is the query I use not ok ?

Rob.

 

[Anne]

Your joins must be wrong. You want to all records from table contacts, joined
to table cities by cityID. I am not good at SQL, but it should be Select
tblContacts.* from tblContacts.

 

[Allen Browne]

Rob, open tblCities in design view.
Do you have a primary key?
Is CityID the primary key?

The query should be editable if the join is on the primary key.

If you already have both tables set up with primary key, try editing in the
query itself. If that works but the form does not, the problem is with the
form.

BTW, if you really to have a field named Name, it is going to cause you
grief. Forms (and most other things in Access) have a Name property, so
Access is going to get confused about whether you mean the name of the form,
or the contents of the Name field or control on the form.

There's a list of 1000+ of those bad names in table tblBadName you can
download from this article:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html

 

[Rob Hofkens]

Thank you both Anne & Allen !!

I adjusted the SQL and I gave the CityID field a Primary Key.
After that it worked like a charm
I am realy happy it does since I spend a lot of time figuring out what was
wrong.
Good these news groups with experience people are available !

I am also going to check the utility you have there Allen.
The last thing I want is a bad choice of user defined names for fields and
the like.

Cheers to you both !

Rob.