Update Query in MS Access 2000

[ctaylor]

I have been told to write an update query on the face of a data entry form to convert pounds to feet.

I know how to run all the ODBC calls to pull part numbers, units of measure and then run a test to determine whether a data conversion should occur, then how to make the conversion happen.

My problem is in making this query update update a field on the face of the form rather than a table field.

Has anyone else done this type of operation before?

[howste]

AFAIK you can only update the table field. Maybe you can run the update query, then refresh the data displayed in the form.

[qball]

howste.

I know what a 'Thaumaturge' actually is...

Quote:

form to convert pounds to feet.

Anyway, one cannot accomplish this, as pounds is a measure of weight/force+dir and feet is distance, thus apples and foul tasting limes.

Now you have a form you display to the user. You can do this client or server side, YOU choose
User enters in pounds into pounds field and then does what? Click submit.
Submit should send form to be processes somehow, either by client side processing (javascript/...) or by server side.

Iffin client side, jabbascript function will be able to access and set any form fields.

Iffin server side, send back form with pertinent information all filled out.

[howste]

LOL, if he knows how to convert pounds to feet, then let him do it! The other part should be relatively easy in comparison.

This code should do what I suggested above:

DoCmd.OpenQuery "UpdateQueryName"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

If you don't want it to update the table immediately, you could update the value in a dummy table until you're ready to update the real table. Hmmm, did that make sense???

[ctaylor]

Thanks for your help guys.

I will give this a shot and let you know how this works.

Concerning the "pounds to feet" conversion what I need to do is convert the number of scrap pounds from a production run into the equivalent amount of feet of heat shrink tubing that could have been extruded.

Without getting into all the details I can convey that the database does the following:
1 - ERP sales order numbers are tied to product
2 - Engineering resources are called to determine product subcomponents and formulas to calculate ratios
3 - subcomponents are further broken down into subcomponents and the same calculations are created and applied to the higher level subcomponents calculated values
4 - material costing information is interpolated from ERP records from purchasing
5 - variances and margins are calculated against standard costing information generated in
6 - the database also makes calls against payroll to calculate the overhead and direct labor components for each of the subassemblies before tallying it all together with the cost of producing the final product for actual total costing considerations

As you can guess, it took me a while to get this whole thing written and it works great because the data entry person only has about 5 data fields to enter in order to generate all these calculations. Unfortunately the unit comparison for heat shrink tubing fails as standard costing is calculated in feet produced, not pounds consumed.

I want to "massage the data" so that when a sales order calls for a heat shrink product, that the amount of scrap pounds produced can be converted to an equivalent amount of tubing. My thought is that I could perform an "on exit" event on txtScrapLbs to test the text box bound to the sales order to a heat shrink pruduct and if this tests true, perform the conversion of pounds to feet before writting it to the main data entry table. If the test proves false, then the user data is written directly to the table without conversion.


Come to think of it. I should probably declare a variable in the form module, set the current value of that variable to be equal to the user entered value of txtScrapLbs. From there I can actually run calculations and assign that variable's value back to the text box bound to scrap pounds. This way I may be able to avoid the whole "update query" approach which is not working for me........I will let you know what I come up with.

[ctaylor]

Thanks for the dummy table suggestion too - I already have a report extract table to speed calculations for report generation.
I had not considered binding the data form to a temp table and then updating the main data table. My initial thought was to try and keep this as simple as possible which is why my first thought was for the form test and conversion solution.

[howste]

Pounds to feet didn't seem that odd to me because I have done similar calculations converting linear feet of wire mesh of different widths to pounds of wire to manufacture it. It sounds like you've got it under control.

[ctaylor]

OK, I am able to get the record written to a temporary table, then run queries to test and convert the data.

I think I'm stuck though in that I also want to require the sales order number to be unique. Any ideas on how to ensure that the sales order (drawn from a combo box bound to our ERP system) and written to the temp table does not allow for a duplicate sales order entry to be appended into the live data table.

Is there a way to draw data from one table and index it against another table to ensure a unique record is written to a 3rd table?

[howste]

I'm guessing that the live data table isn't an Access table. If it was, you could just set the property in the table to not allow duplicates in that field.

It might not be the best way, but you could pull all of the ERP sales order numbers into an Access table with no duplicates allowed, then try to append the new sales order number. If it gives you an error, don't append it to the live data table.

Or maybe instead of having the combo box based on the ERP data, have it based on a lookup query that excludes all sales orders that exist in both ERP and the live data table. (I hope I understand correctly the way your tables are related)

[qball]

Quote:

one cannot accomplish this

Well iffin it's pounds of raw mat to feet of finished product...DOH!

Quote:

...does not allow for a duplicate sales order entry to be appended into the live data table...