One form with two subforms

Wellous,
A sample of the database you are working one will make this question easier to understand and answer.

Hello Master,
As per your advise i attached the sample of my mdb file,
and you can see by opening Assign form,
then at the 2nd subform named "Income"
Go to paidItem column and select for example PartPrice from dropdown menu , then the cost will be taken from the 1st subform named "Payement" from "ItemPrice" column according to which item you selected in the income form in paiditem column.
Also one more thing; if the paiditem is repeated then i need the Cost to be taken from the Rest column not from the orignal price in form "Payement"
if you still can't understand my point pls let me know i will elaborate more,

Many thanks in advance


db2.zip

If I understood what you wanted then this code added to replace your code will work.

Private Sub Combo22_AfterUpdate()
'Me.Cost.ControlSource = "Select ([Forms]![Assign]![Payement].Form![ItemPrice])FROM ([Forms]![Assign]![Payement]) WHERE (([Forms]![Assign]![Payement].Form![Paid_paidID]) = ([Combo22])) "
'If Me.Combo22.Recordset.RecordCount = 1 Then
'Me.Cost.ControlSource = [Forms]![Assign]![Payement].Form![ItemPrice]
'Else
'Me.Cost.Value = Me.Rest
'End If
 
Dim rsRead As DAO.Recordset
Dim strReadSQL As String
Dim lngCurrPrice As Long
    'used to find the correct item price
    'based on the PaidID in subform 'Income Subform1'
    strReadSQL = "SELECT Payement.PaymentID, Payement.ProjectdetailsID, Payement.paidID AS Payement_paidID, Payement.ItemPrice, Paid.paidID AS Paid_paidID, Paid.paidItem " & _
                    "FROM Paid INNER JOIN Payement ON Paid.paidID = Payement.paidID " & _
                    "WHERE (((Payement.ProjectdetailsID)=" & [Forms]![Assign]![ProjectdetailsID] & ") AND ((Paid.paidID)=" & Forms![Assign]![Income subform1].Form![Combo22].Column(0) & "));"
    Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
        If rsRead.RecordCount = 1 Then
            lngCurrPrice = rsRead("ItemPrice")
        Else
            If rsRead.RecordCount > 1 Then
                MsgBox "Sorry, multiple item prices found, first one used.", vbCritical
                rsRead.MoveFirst
                lngCurrPrice = rsRead("ItemPrice")
            Else
                MsgBox "Sorry, no item price found. Nothing added.", vbCritical
                Exit Sub
            End If
        End If
    rsRead.Close
    'check to see if the same value was entered twice
    If Me.Combo22.OldValue = Me.Combo22.Value Then
        Me.Cost.Value = Me.Rest.Value
    Else
        If Nz(Me.Cost) = "" Then
            Me.Cost.Value = lngCurrPrice
        Else
            Me.Cost.Value = Me.Cost.Value + lngCurrPrice
        End If
    End If
    'change the focus so that the change being made saves in the current bound form
    [Forms]![Assign]![ProjectdetailsID].SetFocus
    'set the focus back to the combo box
    Me.Combo22.SetFocus
End Sub

Select allOpen in new window

Masterjojobinks,
Thank you so much for your help,
the provided code is good to bring the" ItemPrice" from the (Payement) subform, to "Cost" in (Income) subform,  but try to repeat the "paidItem" , you will find something strange that the "Cost" will be the same exactly like the first time ... for example select from PaidItem combobox  "LandPrice" first time you select it , the cost is populated correctly , if you enter an amount in "Income" also will be calculated correctly and the "Rest" is perfect...
BUT
if we repeat again entering "PartPrice" the "Cost" appear the orignal from (Payement) not the "Rest"
which it should be payed by the member...
also i didn't get any message from the msg's in the code "MsgBox "Sorry, multiple ..."
even if the records are more than 1
another point , if you select for ex. PartPrice from combobox and u came again on same item and select different item in same field for ex. "maintanancePrice" the Cost is caculated bassed on oldvalue (PartPrice) + (maintanancePrice) !! please check and tell me,

Last point ,this code  [Forms]![Assign]![ProjectdetailsID].SetFocus is giving me error saying " MS access couldn't move the focus on projectdetailsID !!!

I attached a snapshot with some explanation i hope it would be for help.
Thanks again for your assistance and support
Brgds
Wellous
untitled.GIF

Ok I took your Income sub form as each line was accumulative and not individual.
This type of imput should really be done differently and that type of change would cause an almost complete redesign of the form.  So I will try to make it work the way you have it now.
One change you need to make to the Income subform is to sort that query by date.

See if this does not get you closer

Private Sub Combo22_AfterUpdate()
'Me.Cost.ControlSource = "Select ([Forms]![Assign]![Payement].Form![ItemPrice])FROM ([Forms]![Assign]![Payement]) WHERE (([Forms]![Assign]![Payement].Form![Paid_paidID]) = ([Combo22])) "
'If Me.Combo22.Recordset.RecordCount = 1 Then
'Me.Cost.ControlSource = [Forms]![Assign]![Payement].Form![ItemPrice]
'Else
'Me.Cost.Value = Me.Rest
'End If
 
Dim rsRead As DAO.Recordset
Dim strReadSQL As String
Dim lngCurrPrice As Long
    'used to find the correct item price
    'based on the PaidID in subform 'Income Subform1'
    strReadSQL = "SELECT Payement.PaymentID, Payement.ProjectdetailsID, Payement.paidID AS Payement_paidID, Payement.ItemPrice, Paid.paidID AS Paid_paidID, Paid.paidItem " & _
                    "FROM Paid INNER JOIN Payement ON Paid.paidID = Payement.paidID " & _
                    "WHERE (((Payement.ProjectdetailsID)=" & [Forms]![Assign]![ProjectdetailsID] & ") AND ((Paid.paidID)=" & Forms![Assign]![Income subform1].Form![Combo22].Column(0) & "));"
    Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
        If rsRead.RecordCount = 1 Then
            lngCurrPrice = rsRead("ItemPrice")
        Else
            If rsRead.RecordCount > 1 Then
                MsgBox "Sorry, multiple item prices found, first one used.", vbCritical
                rsRead.MoveFirst
                lngCurrPrice = rsRead("ItemPrice")
            Else
                MsgBox "Sorry, no item price found. Nothing added.", vbCritical
                Exit Sub
            End If
        End If
    rsRead.Close
    'check to see if the same value was entered on the previous record
    strReadSQL = "SELECT Income.IncomeID, Income.AssignID, Income.EmployeeID, Income.Payment, Income.Cost, Income.InCome, [Cost]-[Income] AS Rest, Income.Invoice, Income.Cheque, Income.Date, Income.PaidID " & _
                    "FROM Income " & _
                    "WHERE (((Income.AssignID)=" & [Forms]![Assign]![AssignID] & ")) " & _
                    "ORDER BY Income.Date;"
    Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
        If rsRead.RecordCount > 0 Then
            rsRead.MoveLast
            If rsRead("PaidID") = Me.Combo22.Value Then
                Me.Cost.Value = rsRead("Rest")
            Else
                Me.Cost.Value = lngCurrPrice
            End If
        End If
    rsRead.Close
End Sub

Select allOpen in new window

Now it works fine , sometimes it doesn't work , maybe a small fix,

What shall i do to get the messages "Sorry, multiple item prices found .. or the other one?

I will give you the points dear, but kindly try to fix it ,you are too close,
 for example if select a PaidItem for third time it's not working !!
please let me know... i can also make another question with new points if you can fix it for me,

Thanks a million

You don't want to get the messages.  If you get the message saying "Sorry, multiple item prices found, first one used.", then your list of possible item prices have duplicates (never good in a database) and it is defaulting to the first one.  The second message "Sorry, no item price found. Nothing added."  indicates that the combo box and the payement subform do not have the same data (they should).

The third time selecting the same paiditem... If you did not add an income after selecting the second paiditem, that leaves the rest field as a null.  It is null because of the calculation in the query which provides data to the income subform.  After adding an income it shows meaningfull data.  You could check to see if the rest field is null after selecting the third paiditem and then popping up a message to explain the problem.

Also, if you did not add a sort value to the income subform query, you may have problems in the future with which rest field the subroutine is selecting when adding a duplicate paiditem.

See if that helps

Thank you for your updates,
For some records it works fine , for others : sometimes the cost is blank ! even if it's first time entering...
sometimes the repeated PaidItem has the orignal price not the rest,
Please tell me what sort value i need to add in the Income subform query to fix the issue or not relation?

thanks again and wish you the best

Hello Masterjojobinks, how r u today?
i think we need to add form.refersh or .requery? what do you say?

i don't know according to what exactly the forms stop bringing the cost value sometimes.. but sometimes it works.. i really don't understand, any help?

thanks a lot!
nice weekend

Masterjojobinks,

Thanks a million for all your kind support,
the issue is solved now,
just added this repeated this lines of code , i mean repeated it:

Else
        Me.Cost.Value = lngCurrPrice
  End If
    rsRead.Close

End Sub

the issue was the sort value it was stated as Date() and when you add paiditem repeated in same date it won't give you the cost from the Rest it gives you according to last date even if you made two payments in same date...... anyway this was solved by repalcing Date() with Now() , and it works perfect now... i will paste  the code again here to see the small adaptation,

You deserve the best, i will come to you with different question soon.

Option Compare Database
 
Private Sub Combo22_AfterUpdate()
 
Dim rsRead As DAO.Recordset
Dim strReadSQL As String
Dim lngCurrPrice As Long
    'used to find the correct item price
    'based on the PaidID in subform 'Income Subform1'
    strReadSQL = "SELECT Payement.PaymentID, Payement.ProjectdetailsID, Payement.paidID AS Payement_paidID, Payement.ItemPrice, Paid.paidID AS Paid_paidID, Paid.paidItem " & _
                    "FROM Paid INNER JOIN Payement ON Paid.paidID = Payement.paidID " & _
                    "WHERE (((Payement.ProjectdetailsID)=" & [Forms]![Assign]![ProjectdetailsID] & ") AND ((Paid.paidID)=" & Forms![Assign]![Income subform1].Form![Combo22].Column(0) & "));"
    Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
        If rsRead.RecordCount = 1 Then
            lngCurrPrice = rsRead("ItemPrice")
        Else
            If rsRead.RecordCount > 1 Then
                MsgBox "Sorry, multiple item prices found, first one used.", vbCritical
                rsRead.MoveFirst
                lngCurrPrice = rsRead("ItemPrice")
            Else
                MsgBox "Sorry, no item price found. Nothing added.", vbCritical
                Exit Sub
            End If
        End If
    rsRead.Close
    'check to see if the same value was entered on the previous record
    strReadSQL = "SELECT Income.IncomeID, Income.AssignID, Income.EmployeeID, Income.Payment, Income.Cost, Income.InCome, [Cost]-[Income] AS Rest, Income.Invoice, Income.Cheque, Income.Date, Income.PaidID " & _
                    "FROM Income " & _
                    "WHERE (((Income.AssignID)=" & [Forms]![Assign]![AssignID] & ")) " & _
                    "ORDER BY Income.Date;"
    Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
        If rsRead.RecordCount > 0 Then
            rsRead.MoveLast
            If rsRead("PaidID") = Me.Combo22.Value Then
                Me.Cost.Value = rsRead("Rest")
            Else
                Me.Cost.Value = lngCurrPrice
            End If
' Here is the adapation , just added this line ;-)    
    Else
        Me.Cost.Value = lngCurrPrice
        
 ' that's it           
        End If
    rsRead.Close
 
 
 
End Sub

Select allOpen in new window

thank you for your help & assistance
Happy Easter :)