Updating a form based on changes in another form (1 Viewer)

[AnilBagga]

I have a form 'frmMain' where I have an unbound text box 'txtDate' with a date. I use this date to run reports/update databases.

In another form 'frmCustRFQ1' I use this date to fetch a rate from a table using Dlookup (see below)in an unbound textbox called 'txtRMRate'. The rate fetched from the table is based on the value of txtDate. This value is updating as an when txtDate or RMType is changed

=IIf([RMType]='PP',DLookUp("PP","tblcustRMPriceMaster","Endcustcode='" & [tblCustRFQ1.EndCustomerCode] & "' AND
Month([forms]![frmMain]![txtDate])=month([pricedate]) AND Year([forms]![frmMain]![txtDate])= Year([pricedate])"),DLookUp("HDPE","tblcustRMPriceMaster","Endcustcode='" & [tblCustRFQ1.EndCustomerCode] & "' AND
Month([forms]![frmMain]![txtDate])=month([pricedate]) AND Year([forms]![frmMain]![txtDate])= Year([pricedate])"))

I need to store this value of 'txtRMRate' in the table which is bound to the frmRFQ. the bound control name is 'RMRate'

I added the code me.txtRMRate = me.RMRate in Afterupdate of the combobox RMType and the RMRate is updated when RMType is updated

However when I change the value of txtDate in frmMain, txtRMRate is updated but not the value RMRate. I added the code me.txtRMRate = me.RMRate in AfterUpdate and OnChange events of txtRMType but the value is not updated!

Where am I going wrong?

Adding [forms]![frmCustRFQ1].[txtRMRate] = [forms]![frmCustRFQ1].[RMRate] in Afterupdate of the txtdate control in frmMain produces an error!

 

[The_Doc_Man]

Your explanation of your problem is a bit convoluted. However, let me see if I can maybe talk my way through what I see.

You use an unbound txtDate control on one form. Another form depends on this date in some way. You want the date to be stored in the table when the date is updated in the main form, but there the date is unbound.

The problem is that there is no obvious method to immediately trigger the update. You have the ability for the txtRMRate control to detect the value in txtDate, but where txtRMRate is bound, a programmatic change to the field doesn't necessarily trigger the automatic update in THAT form. In essence, you have no event to drive anything.

Programmatic changes that don't at the same time trigger a form-save (such as DoCmd.RunCommand acSaveRecord) will not save anything. Changing a control is not enough in and of itself to change a record. If you don't do something BESIDES changing the value of txtRMRate, there is no driving event to trigger the save. Putting code to change values in the events still doesn't trigger the events. No events? Code doesn't execute when you wanted.

Does that help?

Now the other question hidden in there... this expression:

[forms]![frmCustRFQ1].[txtRMRate] = [forms]![frmCustRFQ1].[RMRate]

When the expression is executed from any event in form frmMain it will fail. Here is why (or at least, I THINK this is why...).

When you have a bound form, anywhere within that local context, all controls on that form are visible to you, and so are all of the underlying fields of the SELECT portion of that form's .RecordSource (table or query). I am going to make the wild-eyed guess that [txtRMRate] is bound to a field named [RMRate]. And on form frmCustRFQ1, that field and control are both visible. BUT I don't think that the field-name assignments are public. They are private to the form to which they apply. Which means that frmMain (having a different .RecordSource) cannot see field [RMRate]. Thus your error. I think...

I've been know to be wrong, but that is my take on the issue.

 

[AnilBagga]

Let me rephrase the requirement. My approach maybe wrong

I need all records in tblRFQ1 to store the RMRate.

RMRate source is tblCustRMRate and the RMRate is based on

a) RMType (available in tblRFQ1)

b) Date - RMRates change every month and thus the need to check the month and year of the date in frmMain

What is the best way to achieve this?

 

[Pat Hartman]

1. Forms don't store data. Tables store data
2. If you always want to use the current rate then don't store it twice. In an order entry system, you would copy the price from the product table at the time of the order because that's the price the customer paid and you never want the price on the order to be anything else. But you are saying that you always want the current rate so just leave it in the rate table and don't store it a second time.

 

[AnilBagga]

1. Forms don't store data. Tables store data
2. If you always want to use the current rate then don't store it twice. In an order entry system, you would copy the price from the product table at the time of the order because that's the price the customer paid and you never want the price on the order to be anything else. But you are saying that you always want the current rate so just leave it in the rate table and don't store it a second time.

Mr Hartman

You have a point. The CustomerRM Table has prices and dates. Therefore the RM historical data is available and I dont need to store this data in Customer RFQ table.

This DB is for price estimation

So when I run the report I can check the RM related to the date of the report and do the estimation using queries (which are still work in progress).

Till now I have not figured out how to store the results of this price estimation done. Ideally I would like to store this data like customer code/Item Code/Date/RMType/RMRate/Price Estimate

 

[Pat Hartman]

If you want to store the price when you make the record, use the AfterUpdate event of the combo used to pick the item.

Me.SavePrice = Me.cboItem.Column(2)

This assumes that the price is the THIRD column in the RowSource of the combo. So the RowSource would be
Select ItemID, ItemDesc, Price

The columns of the RowSource are a zerobased array. That is why (2) refers to the THIRD item.

 

[AnilBagga]

If you want to store the price when you make the record, use the AfterUpdate event of the combo used to pick the item.

Me.SavePrice = Me.cboItem.Column(2)

This assumes that the price is the THIRD column in the RowSource of the combo. So the RowSource would be
Select ItemID, ItemDesc, Price

The columns of the RowSource are a zerobased array. That is why (2) refers to the THIRD item.

Mr Hartman,

I am aware of this process, but my need is slightly different. I just posted a new thread on this

Solved - Designing a DB

The DB basically needs to create monthly price lists for customers. The table and queries planned are as below. These 5 tables and queries are more or less ready tblItemDetails = PK – ERPFGCode + various specifications (say TS) tblCustomerRFQ – PK – CustCode and ERPFGCode. It will have other...

www.access-programmers.co.uk