Solved

One form with two subforms

Posted on 2009-04-06
12
391 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
ID: 24086689
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
ID: 24088308
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
ID: 24091229
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
ID: 24097693
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
ID: 24098997
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
ID: 24109235
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 8

Expert Comment

by:masterjojobinks
ID: 24109400
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
ID: 24116295
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
ID: 24122323
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
ID: 24129323
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
ID: 24138531
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
ID: 31567111
thank you for your help & assistance
Happy Easter :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

930 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