Help making update query using form fields as criteria

[4l63rt0]

Hello Everyone!!!



(Sorry about my grammar not an English Speaker/Writer)

Before anything thanks for your future help

I'm barely new to access (2010) and databases. I been using it for 1 month once in a while. Right now I have a small project about an inventory database where I need to set values to a sub-form field base on a combo box selection. A friend told me to use an Update Query to set this values but I have been not able to make it, let me explain, this is the picture so far:


Main Form
tblPurchaseApproval.PA_ID (key field / Auto Increase) - TextBox
tblPurchaseApproval.PR_ID - ComboBox ------This ComboBox has a Query with all the submited forms from other form------

Sub-Form
tblItemsPurchase.ID (Key field / Auto Increase)
tblItemsPurchase.PR_ID
tblItemsPurchase.PA_ID

I'm using Master field (tblPurchaseApproval.PR_ID) an child field (tblItemsPurchase.PR_ID) to get all the fields with the same value for each tblItemsPurchase.ID in to the sub-form. Now this is the part that I'm not able to do.

I want to set tblPurchaseApproval.PA_ID value to all the tblItemsPurchase.PA_ID that I got from "Master/child Fields" before, when form is saved (Click OK button)------I assume this is when we will use update query------


Thanks!!!

[amrut]

I am not sure but I think either the Master-Child relationship is not set up correctly at the table level or form level. An update query can perform this task but not necessarily required in each case to simply enter data in subform. If the linking is correct, each new record is automatically assigned the corresponding master field value. Ignore my post if I am totally off the track.

[4l63rt0]

Thanks for the reply!

I already got the values in to the sub-form, what I want to do is set the ID value from the main form to the field(s) in to the sub-form that were brought with the master/child relation.

[amrut]

You can use UPDATE query or VBA to update those values. See https://www.fmsinc.com/MicrosoftAcce...ate-query.html

But, as the tables are linked correctly, why should you need to duplicate the ID value in sub-form. You can use a query to retrieve this field from tblPurchaseApproval based on the PA_ID field.

[4l63rt0]

Thanks again Amrut!

The reason I'm doing it that way is because we need to print report for Purchase request, purchase approvals, and purchase orders, so I have a Boolean value for each one in the Items table (tblItemsPurchase).

I'm going to try with VBA code then thanks!

[4l63rt0]

I got it, I removed Master/Child Field and I made a filter. That solve the issue.

Thanks