Solved

One form with two subforms

Posted on 2009-04-06
12
388 Views
Last Modified: 2013-11-05
Hello ALL,

I have a main form named "Assign" and two subs one is "Payment" and the 2nd sub is "Income"
the main form is linked to first sub with control "ProjectdetailsID" and  the 2nd subform (Income) is linked to main form with control name "AssignID" ...
- 1st subform has controls " ProjectdetailsID" , "ItemPrice" , "paidItem, "PaidID"
- 2nd subform has controls "AssignID" "PaidID", "ItemPrice","PaidPrice", "Rest", "Date",  "employeeID"

What i need now is :
in the 2nd subform (Income), Control name : "Paiditem" combobox , after update
this control contain " LandPrice,Maintanance,Waterprice, telephoneprice, credit,etc"
the code i need should take the prices from the 1st subform (Payement) where "PaidID" in the 1st subform is equal to "PaidID" in 2nd subform (Income) and to put it in "ItemPrice" all this in 2nd subform , but if the PaidID is repeated which means sometimes the memebers are paying the
Maintanance , or Landprice into installments so whenever any of the item is repeated then the Control "itemprice" should take into account that the price is not the one in the 1st subform but it's the "Rest "control in same 2nd subform (Income) where PaidID is the same,
i hope you got my points,
Wish you more success,
Wellous

 


i don't know 

i tried this :

Me.Cost.ControlSource = "Select ([Forms]![Assign]![Payement].Form![ItemPrice])FROM ([Forms]![Assign]![Payement]) WHERE (([Forms]![Assign]![Payement].Form![Paid_paidID]) = ([Combo22])) "

didn't work

i tried this one also , with errors... :-)

If Me.Combo22.Recordset.RecordCount = 1 Then

Me.Cost.ControlSource = [Forms]![Assign]![Payement].Form![ItemPrice]

Else

Me.Cost.Value = Me.Rest

End If

Open in new window

0
Comment
Question by:wellous
  • 7
  • 5
12 Comments
 
LVL 8

Expert Comment

by:masterjojobinks
Comment Utility
Wellous,
A sample of the database you are working one will make this question easier to understand and answer.
0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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
0
 
LVL 8

Expert Comment

by:masterjojobinks
Comment Utility
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

Open in new window

0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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
0
 
LVL 8

Expert Comment

by:masterjojobinks
Comment Utility
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

Open in new window

0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:masterjojobinks
Comment Utility
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
0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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
0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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
0
 
LVL 8

Accepted Solution

by:
masterjojobinks earned 250 total points
Comment Utility
Wellous,
Sorry for the delay, I was out for the weekend + friday.  Can you step through an example where it does not pull the cost (cost blank) or when the paiditem has the original price and not the rest.
These type of bugs can ge related to data or user input, I will have to evaluate each to figure it out.  If you can post me a copy of BP that will have the same data set as you (when your getting the errors) it will help me determine where the problem is.
0
 
LVL 5

Author Comment

by:wellous
Comment Utility
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

Open in new window

0
 
LVL 5

Author Closing Comment

by:wellous
Comment Utility
thank you for your help & assistance
Happy Easter :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now