This Recordset is not updateable

[June7]

Do not know any other choices.

I should have mentioned that what I did was bind the form directly to the table and the form was then editable. I did not test controls bound to the Attachment fields. The Attachment field might still serve your needs, just not as you had the query structured.

[css1270]

I have never had to do any of this before and I have been using Access for a couple years and without problems. How the heck do I bind my form directly to the table?

[June7]

Set the form's RecordSource property to the table. Simply choose the table from the property dropdown list. Setting to a query should also work, it just didn't when you brought in the Attachment fields as you did.

The Attachment datatype is new to Access 2007.

[css1270]

Ok, That worked great Thanks. One last question and you can use the file I sent you. My City located in the Billing info captures the City ID and not the City. So my output is a number and not a City and this happens when I click the red "Click Here." I think this is a simpler solution, because it used to work. Thanks!

[June7]

The BillingCity field in Clients is a number type, not text, so can't save the city name unless you fix this. If you want to show city name instead of number, make the form's record source a query that joins Cities to Clients.

[css1270]

Your advice worked great and everything works as it should....except that my "Billing City" is now blank. Please see the attachment.

[June7]

Since you changed it to a text type and can no longer join to the Cities on the Autonumber key, you will have to enter the city into the field.

If you had left it as a number type could have followed my suggestion to include the Cities table in form RecordSource by joining on the number keys.

[css1270]

A few comments back you instructed me to change the record Source to my Clients table. Now you are asking me to create a query joining Clients and Cities. Which one is it? I can't do both. Using the query prevents updates and using the clients table the field Billing City goes blank.

I feel like I am now chasing my tail and that I am in a caught 22.

[June7]

In Post # 20 I pointed out options:

"The BillingCity field in Clients is a number type, not text, so can't save the city name unless you fix this. If you want to show city name instead of number, make the form's record source a query that joins Cities to Clients."

This query allows an editable RecordSource. Depends on setting the correct Jointype:
SELECT Clients.*, Cities.City, Cities.County
FROM Cities RIGHT JOIN Clients ON Cities.ID = Clients.[Billing City];

[css1270]

Your First Option: "The BillingCity field in Clients is a number type, not text, so can't save the city name unless you fix this. Doesn't Work

Your Second Option: If you want to show city name instead of number, make the form's record source a query that joins Cities to Clients."

This query allows an editable RecordSource. Depends on setting the correct Jointype:
SELECT Clients.*, Cities.City, Cities.County
FROM Cities RIGHT JOIN Clients ON Cities.ID = Clients.[Billing City]; This option still has a glitch.

Please see my attachments.
Capture1: Shows Join Type (As you have indicated above as being a Right Join) (I also tried Inner and Left Join with no different results)
Capture2: Shows the Relationship
Capture3: Shows the Record Source just as you have also indicated above
Capture4: Shows the Billing City result (Still a number and not a City)

[June7]

You need a textbox bound to the City field from Cities table, which is made available by join query. Lock that control so it is not editable. Don't want users changing the City name in Cities table.

If you want user to select city by name, make a multi-column combobox that hides the ID and shows the city name, bound field would be the BillingCity field to save the ID.

An alternative to the number primary/foreign key is to make the City field the primary key, if you are positive there is not more than one city with same name, then the actual name could be saved in the Clients table (BillingCity would be a text field in this case).

[css1270]

To all who are reading this thread. June7 after several posts made the suggestion to change the recordsource. That worked great!

However I still had the problem of my "Billing City" being inserted as the "ID" and not the "City." I resolved my problem with only one small change. Perhaps so simple the solution was overlooked.

My "Mailing City" was a lookup field to a related Table, "Cities." And the "Billing City" was simply a text box. All I needed to do was change the text box in the "Billing City" to a lookup field relating to the "Cities" table, just like the "Mailing City" was.

Problem Solved - No complicated Binding of fields and no additional relationship changes and no other code to write...... Simple. I only regret not seeing the obvious sooner.