update field (1 Viewer)

[jd_boss_hogg]

Hi All - hoping somebody can help... i know very little about access, its only one of the many hats i have to wear. I need to perform a function and don't know where to start really. Id prefer it to be in a query or macro rather then VBA (because i understand it better).

I have a form, which displays a load of fields. Some of them are active, most of them are look-ups. When one of the values of the active fields is changed, i want to run a macro that changes the value of a lookup field.

So, when [schedule] on this form is changed, search for the record where [job#] [this form] = [job#][fulltable] and then run a macro that updates a field called "due-date" in that record.

I think I'm all lacking is how to put a "where" clause (where customer this form = customer in table) in the macro ?

Sorry, i don't know how to put it better than that, i don't have the word skills to describe stuff in Access.

 

[GaP42]

Difficult to be definitive / prescriptive as the question lacks specifics. Firstly - you have a form where you can change the value of the Scheduled control - does your form display the Job record (job#)? If so is there a due date displayed for the job? Otherwise how does Schedule relate to Job?
You then mention the customer form - what do you need to do there?

If you can improve the explanation of what you are trying to do in business terms and provide a copy of your database, with sample data it would be easier to assist.

 

[jd_boss_hogg]

So i have one master form (frmMainEntryForm) , that holds 2 sub forms (one being frmJobCardDataEntry) . These forms get their data from the same table.

so, when [frmMainEntryForm] /or/ [frmJobCardDataEntry] field called [CheckPlotApproveAt] is changed, run a macro that will set a value in
=DLookUp("Due","jb-2001","[Job No]=" & [Forms]![frmMainEntryForm]![Job No])

this is my macro code..... my problem , i think, is that "DUE" is just a lookup field, its not "in" the form itself.

Sorry if that is probably a crap explanation, i dont have the access vocabulary to explain it any other way

 

[mike60smart]

So i have one master form (frmMainEntryForm) , that holds 2 sub forms (one being frmJobCardDataEntry) . These forms get their data from the same table.

so, when [frmMainEntryForm] /or/ [frmJobCardDataEntry] field called [CheckPlotApproveAt] is changed, run a macro that will set a value in
=DLookUp("Due","jb-2001","[Job No]=" & [Forms]![frmMainEntryForm]![Job No])

this is my macro code..... my problem , i think, is that "DUE" is just a lookup field, its not "in" the form itself.
View attachment 112422

Sorry if that is probably a crap explanation, i dont have the access vocabulary to explain it any other way

Can you upload the database?

 

[Pat Hartman]

You seem to be asking how to store duplicate data. That is generally poor practice. You would typically not do what you are asking unless you needed to store a value at a point in time. A common situation is an Order. You order a wrench and the price at the time you order it is $12.48 so you want to copy the price into the order detail. That allows you to review orders in history and always see the price paid on that order. If the price of the wrench changes to $12.99, you wouldn't want that to affect old orders. However, if your company policy is that the customer always gets the benefit of a price reduction when his order doesn't ship immediately, if the price on the wrench went down to $12.25, you would have an automatic procedure that ran to update the price on all wrenches that are ordered but not yet shipped to the new, lower price.

If you didn't care that the value might change over time, the standard method is a query with a left join. You could bind that query to your form and if you changed, the FK, then the related value would change. Or if you changed the quantity, the total price would change.

I think we need a better understanding of your objective - in non technical words - to offer a rational solution.