Combo Box Lists

[SASHA_D]

Hi there,

I am hoping for some help on how to update records through forms....

I have a data base with a search form which works fine in displaying the records, however I now need the user to be able to update some fields but I am having lots of difficulty in doing this (recordset not updateable). I understand this is probably because of the database design, but not sure where exactly....

The structure (or lack of) is as follows...
I have a combo box to select a product name and product code (users only see the Name). The source for the combo box is a query-this works OK. I then have another combo box to display pack size-it only displays packs available from the product selected in the first combo. Again this seems fine.
I then have a search button which opens another form-(Products & Prices) to display all the products matching the users' search criteria.
The products & prices form works off a query which pulls information from several tables-Mainly it displays the product description, supplier product ID, price, net price and cascade.

The 'cascade' field indicates in which order we are currently using the products-e.g. Lowest priced supplier will be set at 1, second lowest 2 and so on. I need to be able to devise a system where the user is able to change the cascade field for the selected product and then return to the search form, search on another product, change the cascade, and so on

If anyone can help I would be much appreciated!

thanks in advance,

Sasha

 

[wiltshire]

Hi Sasha,

You're getting that error message because you're trying to update a field in a recordset from a query with many linked tables.

The best thing to do is keep your combo boxes but structure them so that the result is unique - and then have an update query pull the data off this form and update the 'cascade' field in the appropriate table.

Or, have a subform base on the table with the cascade field in it, and update directly on this, whichever you feel is neater.

Hope this makes sense

 

[popen_73]

If you are getting 'Recordset Not Updateable', are you sure that the control source is not a query?

 

[SASHA_D]

Hi there Wiltshire, thanks for the reply!

You will have to bare with me as I'm quite new to this stuff..

I think I get what your saying- with an update query linked to the form that updates the appropriate field.
However, still a bit unsure-basically the combination of the selections from the two combo boxes generates two fields-FFS Code & Pack Size. The FFS Code and pack size are then filters for a query-'Product list with prices'.
This query, I have found out, is quite complex-along with the FFS code and pack size (which are filtered using the form), it also contains a supplier product ID. This suplier product ID is linked to fetch any matching IDs from a Union query-'Supplier prices'-the relevant prices for the product are from here.
The union query is linked to bring back any matching Id's from a table 'tblCascade'-this is the cascade position.
Not all the products have cascades, so I would like a user to be able to add products (suplier Id's) to the cascade table, delete products and edit their current cascade positions.

Phew, can anyone get me out of this mess!

 

[wiltshire]

Yikes !!!!

Can you zip it up and attach it for me to have a look !!!

Thanks

Steve

 

[SASHA_D]

Here you go....

 

[wiltshire]

I think you've forgotten something ...................!!!!!!!!!!!!!!!

 

[SASHA_D]

I think the file might be too big, even zipped, he's my second attempt....

 

[wiltshire]

Sorry - didn't attach - there is a size restriction of 102400 bytes so maybe you could strip the database leaving only bare bones of data and try it then. (make a copy first of course, don't want to be responsible for you deleting all the files !!!)

 

[SASHA_D]

Having no luck!-Reduced data to a bare minimum, deleted all unneccessary tables, queries and forms. Deleted all reports.
-It's still too big to post!