Set Value of Text Box Based on Subform

[mikeTTI]

I have a form that is used to enter/display industry levy payments.

It has a main section and two subforms, both of which are datasheets.

subform1 is based on a table and shows individual payment details.

subfrom2 is based on a query that calculates what the amount for the payments by lookign up a rate in a table and mutiplying this by the volume in the table subform 1 is based on.

The payment details are entered from "buyer generated" invoices. Subform2 simply displays what the payment amounts should be for checking purposes. This info is not stored anywhere.

the two subforms contain the same "AutoID" field.

This all works very nicely.

What I would really like to do is insert a check box in subform1 which when set to true will cause the payment amount from the matching "line" in subform2 to be copied across to subform1. This will save the users a lot of time.

I have tried two ways to do this.

An update query: -

UPDATE tblLevyReceiptsDetail SET tblLevyReceiptsDetail.LevyPaid = Forms![Levy Receipts]!sbfRateCalc.Form!LevyDue
WHERE (((tblLevyReceiptsDetail.AutoID)=[Forms]![Levy Receipts]![sbfLevyReceiptsDetail].[Form]![AutoID]));

Some Code: -

Private Sub Correct_Click()
Dim RecNum As Integer
Dim PaidVal As Currency
RecNum = Forms![Levy Receipts]![subform1].Form![AutoID]

If Me![Correct] = True Then

Forms![MainForm]![subform2].Form![AutoID].SetFocus
DoCmd.FindRecord RecNum
PaidVal = Forms![MainForm]![subform2].Form![LevyDue]
Forms![MainForm]![subform1].Form![AutoID].SetFocus
DoCmd.FindRecord RecNum
Forms![Levy Receipts]![subform1].Form![LevyPaid] = PaidVal

Else

DoCmd.Beep

End If

End Sub

Both these methods work for the 1st record in subform1 is the first record in the datasheet. If the 2nd or subsequent record in the datasheet is selected, then the SQL or Code runs for the first record anyway.

How do I make the code/query recognise which record is selected in the subform1 datasheet?

NB: When I say the 1st record in the datasheet I mean the 1st displayed for a particular record on the main form, not the first record in the table subform1 is based on.

 

[Luka]

What if you insert one more field in subform and make it display increment number of a line? That way everytime the record that has focus would be selected.

In this case when you're doing update query you should include loop (search this forum there is some code for it)

 

[CEH]

Maybe I am misunderstanding this... But you are simply trying to insert the value of one textbox to another? I use a similar method when addresses are the same.... eg... Billing address to service address.... I use a command button... but you can easily change to a checkbox.... using the "If me.checkbox = true then" statement

Private Sub cmdSame_Click()

CustDescription = JobDescription
CustAddress = JobAddress
CustCity = JobCity
CustState = JobState
CustZip = JobZip

End Sub

Of course you would need the proper path to your corresponding textboxes.

 

[mikeTTI]

Thanks for your suggestions, I have this sorted now via a DLookUp, with help from someoen much better at tis stuff than me!