Unanswered: How Can I Use Same Form Changing RecordSorce?
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]
GROUP BY T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName
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?
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.
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.
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?
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
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.
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: