passing keys or values from one subform to another by double clicking

[jmpigott]

I have searched forever trying to solve a problem that simplifies recurring info inputs to my database

the basic structure is Orders tbl main form and pricing_tbl subform and third when orders have recurring pricing structure, query pricing history for location and product, then select appropriate costing method from filtered list. This historical data sheet (subform 2) would be optional method for populating costing.

I have run across junction tables as a solution but it could potential cause problems if someone changes the historical costing methods. another way of saying this is that if I'm linked to a historical record associated with a given customer or customers then potentially all customers associated with that pricing method change.

pricing is based on base components (materials) 70% a 30% B the actual unit price is based on market indexes which change monthly.

Ideally I would like to be looking at new record (order) on the main form. Query or filter for costing methods for that plant (refinery) and product. Click or double click on the above pricing method and pass the sequence number (similar to junction method) or pass component_A, component_A_Percent, component_B, component_b_percent, etc. to subform 1 which is where you manually enter this information.

Usually I can figure out VBA but am not particulary adept with it. I find Cold Fusion simplifier because I don't have to deal with visual methods. A simple example or general direction would be greatly appreciated.

Here is my feeble attempt to store a value from the double click

Private Sub Form_DblClick(Cancel As Integer)
Dim price_seq As Integer
Me.RecordSource = "standard_pricing_tbl"
Me.intSequence.ControlSource = "Pricing_method_seq"
price_seq = Pricing_Method_Seq
End Sub

MP

[nico5038]

Hmm, complex description. I guess you're describing what we call a "Bill Of Materials" (BOM) costing method.
This requires "recursive" programming using the product structure and the costs.
Building such a (basically a tree-) structure requires testing each branch not to hold the same products, as that will create an "endless" loop.

How did you organize your Product table to enable the relation between the products ?

Nic;o)

[jmpigott]

Just to give some clarity

there is an orders table primary keys order item

transfer cost which is internal cost or internal price has a one to one relation to the orders table

One thing I should have mentioned is that there are never or rarely more than two components. Therefore one to one. I would have liked to make it more flexible with one to many but this case it should be acceptable.

historical pricing methods is just a unique subset of plant, product pricing methods it has an autonumber field providing a unique identifier

Ultimately i update the calculated price to the orders table to ensure a static price

I have yet to find the simplest method and realize this is a little clunky

My problem is generally understanding objects, states, focus and referencing them.

I did come up with an alternative method which involves adding a checkbox field to filtered historical pricing methods. returning the record value of the checked record to form by parameterizing the order item, combining it with the pricing components in an append query.

Then i would reload the form to that record and the pricing method would show up where it would be manually entered.


Again referencing the order item from the form to build the query and how I reload the form to previous state plus the pricing methods is yet to be accomplished. I have worked a lot with Access but not much automation or form usage.

I apologize if I sound vague. Tried to simplify to standard database table names while the actual tables aren't orders or order details. I can't see what I wrote the first time nor exactly what you said from this reply screen.

BOM is generally correct, though since the operative order detail is item level. It is the items that compose the order item. Apologize for the confusion ; ). Been a learning curve for me as well. SAP doesn't handle this correctly so I am creating a double entry method but trying to use SAP where it is accurate and allow a user to create a monthly P&L with minimal effort. There are about 108 order items a month and additional details so putting in one month was onerous.

thanks for the response

[nico5038]

Hmm, still some trouble to understand the processes involved.
Your initial question is rather easy to achieve as just storing the value in a "bound" field will cause Access to store the value when you close the form.
Storing the final calculated price is the way to go when the basic values (like the prices) are changing overtime. Alternatively to this you could chose for storing the prices with a start- (and/or end-) date. This will make the result verifyable, but harder to determine the price as a date is needed to extract it.

The determination of the values in subforms is with your additional checkbox field rather easy to achieve by doing a Sum on the rows having a YesNo field set to true. For this a query can be created referencing the formfield holding the Unique ID.

More difficult is the part with the different calculation methods. I get the impression the user can select the different items and your form adds them, I'm missing the real BOM property, being the different levels as items can be linked to other ("lower") items.

Nic;o)

[jmpigott]

I'll put something together definitive and concise shortly. Misplaced by USB drive so I'll have to refer to older version. See if I can past a relationship view and form view.

[nico5038]

Just open a comment here with the [Edit/Delete] link at the bottom and you can add an attachment.

Nic;o)

[jmpigott]

Nico,

I tried uploading some images but ran up against the 75k limit. I don't have a good image editor. Tried zipping a few formats and other compression, no dice. Haven't used snapshot view for a while. Will look into that.

For the moment I'll just see how far I can get then at least my question will be more specific.

Thanks for your help.

[nico5038]

Try to make a small test .mdb and (after compacting) zip it.
Just a small sample will do.

Nic;o)

[jmpigott]

Nico,

Again thanks for the options. I have made some progress in my simplified method. I can display related historical pricing using a product relationship and a refresh once the plant_Of_Origin is selected (doesn't come straight from SAP).

I created a command button which called the main form primary keys in running a query but I can tell it is passing null values. So the update query isn't working.

I'd love to share a simple sample. However, we have pretty strict security and while I came up with some relationship and form views, now I can't attach them from work. Similarly I used ODBC tables for some of the queries so it will capture any changes or additions.

So what would be the best way to pass order and order item from the main form to an update query.

I used the below as a criteria after trying to using to update the field

[Forms]![Review_and_Modify_Transactions_frm]![DOC_NO]
[Forms]![Review_and_Modify_Transactions_frm]![Item_num]

after I choose the plant of origin in the subform which is based on the transfer_cost_tbl. The record already exists it just needs the component pricing structure.


If this does not make any sense. No worries. I'm pretty sure the above method is fundamentally wrong. I need continuity from the form to the query. So I'm looking to make these parameters available to the query. Some sort of temp variable.

Private Sub Apply_Historical_Pricing_Btn_Click()
On Error GoTo Err_Apply_Historical_Pricing_Btn_Click

Dim stDocName As String

stDocName = "Apply_Historical_Pricing_Method_qry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Apply_Historical_Pricing_Btn_Click:
Exit Sub

Err_Apply_Historical_Pricing_Btn_Click:
MsgBox Err.Description
Resume Exit_Apply_Historical_Pricing_Btn_Click

End Sub


thanks. I believe the below will get me there.

''' Keywords: MOTOR DELETE
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' 8/09/96 Marty Wasznicky created

On Error GoTo RemoveMotorID_Err

Dim intSelected As Integer
Dim lngMotorID As Long, lngCustId As Long
Dim qdfMotorDelete As QueryDef
Dim errdata As gTypeErr
Dim strProcName As String * 255
strProcName = "RemoveMotorID"

''' Check if motor selection was made
intSelected = lstMotorSelect.ItemsSelected.Count
If intSelected < 1 Then
MsgBox "You must first select a Motor."
Exit Sub
Else
''' If there is a selection, store it
lngMotorID = lstMotorSelect.Column(0)
End If

''' If there is a selection, store it
lngCustId = txtId

''' Open querydef, passing it the CustID and MotorID as parameters
Set qdfMotorDelete = CurrentDb.QueryDefs("qdelCustMotor")
qdfMotorDelete.PARAMETERS!Customer = lngCustId
qdfMotorDelete.PARAMETERS!Motor = lngMotorID

''' Execute querydef
qdfMotorDelete.Execute

''' Requery listboxes
Call RefreshLists

RemoveMotorID_Exit:
Exit Sub

RemoveMotorID_Err:
Call ErrSave(errdata)
Call ErrorMsg(errdata, Trim$(strProcName), varIcon:=vbExclamation)
Resume RemoveMotorID_Exit
End Sub

Mike

[nico5038]

From code you can issue an update like:
[code=vb]
currentdb.execute ("UPDATE tblX SET DOC_NO =" & Me![DOC_NO])
[code]

Nic;o)

[jmpigott]

Nico,

using the below as a starting point

Private Sub Apply_Historical_Pricing_Btn_Click()
On Error GoTo Err_Apply_Historical_Pricing_Btn_Click

Dim stDocName As String

stDocName = "Apply_Historical_Pricing_Method_qry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Apply_Historical_Pricing_Btn_Click:
Exit Sub

Err_Apply_Historical_Pricing_Btn_Click:
MsgBox Err.Description
Resume Exit_Apply_Historical_Pricing_Btn_Click

End Sub


Where would the code you provided fit. I haven't spent the time to understand the long sample I provided.


I have checked the record of the historical pricing to be applied.
The query I created works as intended if I supply the parameter. Doc_Num is sufficient.

SQL

UPDATE Transfer_Cost_tbl, Historical_Transfer_Cost_tbl SET Transfer_Cost_tbl.Transaction_Dt = Forms!Review_and_Modify_Transactions_frm!Transacti on_Dt, Transfer_Cost_tbl.Plant_of_Origin = Historical_Transfer_Cost_tbl!Plant_of_Origin, Transfer_Cost_tbl.Pct_Mat_A = Historical_Transfer_Cost_tbl!Pct_Mat_A, Transfer_Cost_tbl.Index_A = Historical_Transfer_Cost_tbl!Index_A, Transfer_Cost_tbl.Pct_Mat_b = Historical_Transfer_Cost_tbl!Pct_Mat_b, Transfer_Cost_tbl.Index_B = Historical_Transfer_Cost_tbl!Index_B, Transfer_Cost_tbl.Discount_cpg = Historical_Transfer_Cost_tbl!Discount_cpg
WHERE (((Transfer_Cost_tbl.DOC_NUM)=[Forms]![Review_and_Modify_Transactions_frm]![DOC_NO]) AND ((Transfer_Cost_tbl.ITEM_NUM)=[Forms]![Review_and_Modify_Transactions_frm]![Item_num]) AND ((Historical_Transfer_Cost_tbl.Pricing_Select)=-1));

Hopefully, for once, this gives you the info you need.

Thanks.

Mike

[jmpigott]

I created a global variable which solved my problem.

http://www.blueclaw-db.com/download/...parameters.htm

[nico5038]

Glad you've solved your problem. The currentdb.execute would also work and require no additional variable and function, so keep that in mind too :-)

Success with your application !

Nic;o)

[jmpigott]

Nico,

Will do. After a month and half working on a patch for idiosyncratic systems and manual bookkeeping, I feel a certain degree of success. ( %

Thanks.