Getting SQL statement to work in Access

[Guest]

I'm quite new to Access forms, and I'm having a couple of issues in trying to
get an SQL statement to work correctly.

Here's the scenario:

We have a database in MySQL that's mapped back to an Access front end using
ODBC. This all works just fiine as a basic query front end, and I'm able to
display, on the one form and for each client, their primary details (name,
DoB, gender etc), along with a browser table showing their multiple
addresses, and a secondary browser table displaying their various phone
numbers and email addresses.

What I'm wanting to do is to have a means of selecting one of the displayed
addresses and setting it as the preferred address. This would involved
setting the preferred address value in each of the other address records to
"N" as well as that in the current one to "Y", and to place a reference to
the selected address record's address_id into the client's record.

It seems to me that I should be able to place a button on the browser form,
but buttons placed there in the designer do not show when the form is
rendered with data.

Ok ... so I place the button on the primary form, and try to trigger a macro
using RunSQL with the following statement ...

"UPDATE address set pref_mail ="N" where ltrim(trim(address.contact_id)) =
trim(ltrim([Forms]![contact]![txtContactID].[Text]))"

While the statement seems to be ok, if fails because it updates 0 rows,
desptie the fact that there are 3 rows that it should be addressing.

I've tried variations on the where clause (no trims, etc) but to no avail.

Can anyobody point me to some tutorials on how to accomplish this, or on how
better to achieve this?

Many thanx in advance for your assistance in this.

 

[Guest]

have you tried setting the boolean value to 0 or 1 as you need it, not
the string character N or Y?

Thanx for that suggestion.

No, I haven't, but I'm not exactly sure how that might help.

The datatable schema (done by someone else, long gone) provides for a 3 byte
string ( char 3 in MySQL terminology) field in the field in question, and
therefore a Y or N - which is the current data that is there - should also be
able to be comfortably written back there.

My guess is that the mis-designer wanted to put a YES or No value in there
.... but in writing the frontend app he screwed up a few other things besides
this.

I'm looking for a quick and not-so-dirty way to rectify some of his errors ...