Solved

One form with two subforms

Posted on 2009-04-06
12
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 8

Expert Comment

by:Joe Overman
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:Joe Overman
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Joe Overman
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
 
LVL 8

Expert Comment

by:Joe Overman
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:
Joe Overman 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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