Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: How Can I Use Same Form Changing RecordSorce?

    Hi all,

    I have Form F_CashSalesHead with a subform F_CashSalesInvFoot with one-2-many relationship on their tables. Subform contains a checkbox field that I use to lock the record set (On a command button click it runs one update query to add value 1 to each checkbox to make Enable=False all the records of current invoice on the form).

    One-2-many relation ship is made on InvNum field in both tables.

    When I open F_CashSalesHead form, bcz of some code line I wrote on On Load event of F_CashSalesHead , at the beginning it give massage how many invoices are pending to lock and would you like to see. If click “Yes” to see list, it opens a small form that called F_Count_Unlocked_Invoices showing invoice numbers and unmarked checkbox which is pending to lock. This small form is based on following query,

    SELECT DISTINCTROW T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName, Sum(T_CashSalesInvFoot.Lock_Cash_Inv) AS [Sum Of Lock_Cash_Inv]
    FROM T_CashSalesInvFoot
    GROUP BY T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName
    HAVING (((Sum(T_CashSalesInvFoot.Lock_Cash_Inv))=0));

    This works fine.

    What I am looking for is, I want to use the same F_Count_Unlocked_Invoices form for Credit Sales invoice also with the same trick. Because I don’t want to create another same form and write code that help to increase size of db.

    Can it be done just by changing record source of form F_Count_Unlocked_Invoices? Or what is the way to do it?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    me.recordsource is worth a look....

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Hi Healdem,

    I tried to write following code at loading of form, didnt work.

    Dim strSQL as String
    strSQL="Select * from T_CashSalesInvFoot WHERE Lock_Credit_Inv=0"
    'Here is the problem
    Me.RecordSource.Forms!F_Count_Unlocked_CashSales.r ecordsouce = strSQL

    Extend the help please.

    With kind regards,
    Ashfaque
    Last edited by Ashfaque; 02-09-05 at 05:32. Reason: spelling

  4. #4
    Join Date
    Jan 2005
    Posts
    68
    i see two problems. i assume that the recordsource does not have an extra space in the module. for the other you have to call the subform as the control of me. Then you can set the recordsource for the generic form.
    try
    Me!F_Count_Unlocked_CashSales.Form.RecordSource = strSQL

    you can call this on the form load event, so if you want to reuse the form you can just set it at runtime.

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Frosy1,

    I tried. It assuming its a field name and produce Run-Time error '2465', " Can't find field 'F_Count_Unlocked_CashSales' reffered to in your expression.

    One more thing, how about the previous record source of the forms. Should it be empty? Bcz the form F_Count_Unlocked_CashSales based on the query I mentioned. Therefore, 2-3 bound Text boxes are on the form.

    If I apply the new method (Changing record set by code), Should I remove the previous record source of the form from its property and make those text boxes Unbound?

    Thanks again.

    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Jan 2005
    Posts
    68
    you don't need to clear the recordsource before you reset it. access will just replace it with the new one. i have struggled with the same references, and have an idea. check to see what you named the subform control on the main form. access uses the form name as default, but if you named it something different that would be the problem. the reference should be as follows:
    Me![subform control name].Form.Recordsource
    let me know if this doesn't work

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Frosty,

    As I said previously I created a command button to run a update query to add value 1 to each checkbox to make Enable=False of current invoice on the subform in future. In other words, when you open that locked invoice, fields of this invoice would be disabled.

    Consider that there are 100 Total invoices in Cash Sales header form. And each invoice contains 3 records with its subform. So it would be 300 recordset. Now think that I locked all 98 Invoices out of 100 keeping checkbox value =1 of each sub-record. So becuase a vb code on current event of the form, it will pop ump me as "There are 6 items in different invoice are pending to lock. Would you like to see now?". If click 'Yes' then a small form will open at the bottom which gives me the list of that 2 invoice numbers with status (attached a jpg file for ref.)

    Now the main question is this small form which is based on a query has 3 fields, (InvNum, CashsalesCustomer, CashLock).

    If I have to use the same small form with Credit Sales Footer form, (3 fields which now I require to appear on this small are from Credit Sales Footer which a bit different than previous Cash Sales Footer (InvNum, CreditSalesCustomer, CreditLock), dont you think I need to make those text boxes unbound on the small form?

    Second question, in VBE code,

    Dim strSQL as String
    strSQL="Select * from .......where........." ' this is ok.

    Now this strSQL has data...how it should set as a control source of that small form.

    Bcz I tried Me![subform control name].Form.Recordsource but not worked.

    Regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails CashSalesInvoice.JPG   PendingInvoiceList.JPG  

  8. #8
    Join Date
    Jan 2005
    Posts
    68
    I'm sorry that i misunderstood you a little. i thought that you were talking about a subform on your main form. to set the record source for pop up form just add the recordsource to the "Yes" button's on click event. when you reference this pop up you should use the popup's name, as it is not a part of the main form. it should look something like this:

    strSQL = ".........."
    DoCmd.OpenForm "F_Count_Unlocked_Invoices"
    Forms!F_Count_Unlocked_Invoices.RecordSource = strSQL

    I hope this works better. Let me know.

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks again Frosty1,

    Dim strSQL as String
    strSQL = "SELECT * FROM T_CashSalesInvFoot WHERE Lock_Inv=0"
    DoCmd.OpenForm "F_Count_Unlocked_Invoices"
    Forms!F_Count_Unlocked_Invoices.RecordSource = strSQL

    It worked 75%.

    The form F_Count_Unlocked_Invoices can be used with any of the invoice footer by changing footer table only.

    Now the problem is it dislays each item in the form. Example. If invoice number 7 has 5 items in its fotter table. Its show 5 times invoice number 7 with its status which is Locked=0.

    Now the data for the said form is generated thru VB code (as above) and not by the query which I was using previously used as recordsource that was giving me SUM of same invoice number from footer.

    Now how can I get sum of same InvNum and same Lock_Inv fields from footer table to be appear one time for each invoice? (Attched jpeg for ref.)

    Thanks in advance.

    With kind regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails PendingInvoiceList.JPG  

  10. #10
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Thumbs up

    Yes, I got it.

    I done it using Group By clause to my SQL and its finish.

    Thanks a lot.

    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •