Write Conflict (1 Viewer)

[Gismo]

NEVER, run an update query that will update the current record.
ALWAYS save the current record before opening a different form or report or running any query.

Thank you

Not sure how to handle this
When the product is selected from the drop down box, I need to have the Unit of measure updated to be able to do other calculations
If I can not update the UOM after the product has been selected, I a not sure how to get the UOM in place

I can not link the products table to have the UOM displayed because then the form wont be updatable

Not sure how to handle this one

 

[Minty]

Use the combo as I described in the second process to set the value for the UOM field, use a drop-down for the row source of the UOM control so you can change it to a different predefined value if you need to.

 

[Pat Hartman]

I didn't say you can't save it. I said to not do it with an update query. Look at what Minty described. Don't forget that the Columns property is a zero based array and so the first column is 0, the second is 1, etc. The columns are related to the combo's RowSource. So, include the data you want copied in that row source if you want to copy it. You don't need to make it visible unless you want the user to see it in the drop down. For example - ProdID, ProdName, UOM. Then use 0,2,0 as the Column widths to show only the ProdName. And make sure your combo's RowSource query is sorted by ProdName otherwise typeahead doesn't work properly.

Me.SomeField = Me.SomeCombo.Column(2) '''' refers to the THIRD column of the combo's RowSource.

ALSO, and more importantly, you have no need to store the UOM twice. Just use the field Me.SomCombo.Column(2) in your calculations

 

[Zedster]

Its likely that the conflict is that you are trying to write to a bound control on a form.
See my article Write Conflict Errors (isladogs.co.uk) for other possible causes & solutions

I have just experienced a write conflict on a database I designed 3 years ago that sat on a shelf unused. I too was experiencing write conflicts due to null "bit" fields in an SQL database. Never come across this before, more recently my forms have been unbound. Thanks for the link which provides an excellent explanation and solved my problem (I also learned something which is always good )

 

[isladogs]

You're welcome

 

[Zedster]

You're welcome

You are absolutely right Access really doesn't like null bit fields, my first attempt top solve it was in the linked table in Access, that too threw a write conflict. So I have to use an SQL update query in SQL Server Management Studio (SSMS) to set all nulls to 0 then I changed the design in SQL to set default to 0 and set allow nulls to no for good measure.

 

[isladogs]

The only time you can have null bit values in Access is for unbound controls where a tristate option is available.
By comparison, most databases do allow null values

Although the Access development team made a poor decision when they excluded null values in bit fields over 30 years ago, we are stuck with that decision as changing it now would break lots of older databases.

 

[Zedster]

That's why I got caught out returning to an old unreleased database that used bound forms, over the last few years I have designed all my forms unbound. A lot more work but a lot more interesting to design.

 

[isladogs]

Its several orders of magnitude more work to use unbound forms and personally not an approach I normally use (with the sole exception of a settings form). I'm always surprised when I hear experienced developers using unbound forms by default as that negates many of the advantages Access provides in terms of RAD.
It would be interesting to hear your reasons though you may get a lot of flak from some of us if you do so.

 

[ebs17]

A lot more work but a lot more interesting to design.

Programming close to the machine (assembler) would be much more interesting and exciting.

 

[Pat Hartman]

That's why I got caught out returning to an old unreleased database that used bound forms, over the last few years I have designed all my forms unbound. A lot more work but a lot more interesting to design.

Don't tell your employer how much of their money you're wasting.

Programming close to the machine (assembler) would be much more interesting and exciting.

Been there, done that, far less productive and far more work. Call me crazy. Call me lazy. I'm more into the instant gratification I get with Access. It is the most productive environment I have ever used for development. If Access is a suitable platform (it isn't for all applications), why would I ever want to not use its best features? Shouldn't you be more interested in learning how to use your tool to its best advantage?