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
I want to store in the table all the values determined when the command button – C1 (Sr 4) is clicked. The steps foreseen are
Say there are 30 records in tblCustRFQ. When C1 is clicked, 30 records will populate in Temporary Table called tblTempQuote.
Fields in this tblTempQuote will be CustCode, Date (as in frmMain), ERPFGCode, CalculatedPrice (from the qry – Sr 5 above) and another field Quoteprice which will, by default, have the same values as CalculatedPrice and will be editable.
Using a form – frmTempQuote, I want to edit the values of Quoteprice for all these 30 records as the actual price quote could be different from the Calculated price. After this updation, a command button C2 on frmTempQuote , will save these 30 records in a new table tblCustQuote and simultaneously empty the table - tblTempQuote
Later, on a different date, if tblCustomerRFQ has 40 records, the cycle a) to c) will be repeated and the 40 new records are appended to the table tblCustQuote.
- tblItemDetails = PK – ERPFGCode + various specifications (say TS)
- tblCustomerRFQ – PK – CustCode and ERPFGCode. It will have other details like quantity. Some ERPFGCodes will be common across customers but all ERPFGCodes will be in tblItemDetails
- tblRM – this will have historical data customer wise. PK – CustCode. Other fields : Date and RMPrice. This table will again have all CustCodes in tblCustomerRFQ
- frmMain – This form will have a date which the user will fill in when rates are to be determined and a command button C1 which will initiate the price calculation for the date mentioned on the form
- qryPrice – This query will calculate the rate (CalculatedPrice) using inputs from above 3 tables and the form
I want to store in the table all the values determined when the command button – C1 (Sr 4) is clicked. The steps foreseen are
Say there are 30 records in tblCustRFQ. When C1 is clicked, 30 records will populate in Temporary Table called tblTempQuote.
Fields in this tblTempQuote will be CustCode, Date (as in frmMain), ERPFGCode, CalculatedPrice (from the qry – Sr 5 above) and another field Quoteprice which will, by default, have the same values as CalculatedPrice and will be editable.
Using a form – frmTempQuote, I want to edit the values of Quoteprice for all these 30 records as the actual price quote could be different from the Calculated price. After this updation, a command button C2 on frmTempQuote , will save these 30 records in a new table tblCustQuote and simultaneously empty the table - tblTempQuote
Later, on a different date, if tblCustomerRFQ has 40 records, the cycle a) to c) will be repeated and the 40 new records are appended to the table tblCustQuote.