 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2005Prob with mainform cbo & subform
Hi I have a form (frm_RECEIPTS) which records a cash receipts transaction. The subform (sbfrm_RECDETAILS) lists invoices paid. Both forms linked by TRANS_NO. I have a combo box (cbo_DEBTOR) on frm_RECEIPTS, which controls a combo box (cbo_REF) on the subform using the following code in the AfterUpdate event: Forms!frm_RECEIPTS.sbfrm_RECDETAILS.Form!cbo_REF.RowSource = "SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE As Date, AMOUNT As Amount, OUTSTANDING As Outstanding, NARRATIVE, TYPE, DEBTOR_IDX FROM tbl_MSTRACC WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = " & Me!DEBTOR_IDX Forms!frm_RECEIPTS.sbfrm_RECDETAILS.Form!cbo_REF.Requery This seems to work fine as cbo_REF is showing the correct invoices according to whichever Debtor is chosen in cbo_DEBTOR. My problem is that whenever I open the form, the subform remains blank until I rechoose the debtor (which does show correctly when the form is opened), and then the invoice details appear. When I move to the next record, again the subform is blank until I rechoose the debtor, and the invoices lines reappear. However, going back to the first record, the subform is blank again. I can't seem to get the subform details to appear and remain. Can anyone help me here? Detailed help would be good as I'm a novice at Access. Thanking you in advance Winsa Winsa, You are using code to manipulate the Row Source of the cbo_REF combobox on the subform. This is quite a complicated way of achieving a simple purpose, and the question remains as to what, if anything, is the Row Source of the combobox before a selection is made in the cbo_DEBTOR combobox. I would just make a query to use as the Row Source for the cbo_REF combobox, and use the reference to the main form control (I couldn't see where DEBTOR_IDX came into the picture) in the criteria of the query. Then you don't need all that code. But anyway, it sems to me that this is probably not related to the problem you have outlined. Do you have some other code somewhere that is manipulating the Record Source property of the subform, or the Source Object property of the subform control? Maybe some code on the Current event of the main form?  SignatureSteve Schapel, Microsoft Access MVP > Hi > [quoted text clipped - 27 lines] > Thanking you in advance > Winsa Winsa, Ah ok, maybe this is it... Maybe you are using the cbo_REF control in the Link Child Fields property setting of the subform. And maybe there is no Row Source for the combobox at all until it is assigned by the code on After Update of the cbo_DEBTOR combobox, so therefore can not display a value when the form opens at a new record. Does this sound like it might be the case? If so, my earlier suggestion about the query might help. SignatureSteve Schapel, Microsoft Access MVP > Winsa, > [quoted text clipped - 12 lines] > Object property of the subform control? Maybe some code on the Current > event of the main form? Hi Steve Thanks for your suggestions. Firstly, the rowsource of cbo_DEBTOR is another query based on Debtor Number and Name, where Debtor Number is the bound column. From this, cbo_REF should show only those invoices that belong to the chosen Debtor Number. This is where the DEBTOR_IDX (Debtor Number) comes in. >Do you have some other code somewhere that is manipulating the Record Source property of the subform, or the Source Object property of the subform control? I'm not sure I understand you correctly, but I don't have any other code manipulating the Record Source of the subform. There is a Record Source that is a query based on the underlying table for the subform if that is what you mean? No, I'm not using cbo_REF control in the Link Childs Field property. I tried that, but Access told me that I was using an invalid column. The link field is the Receipt Transaction Number (TRANS_NO). But yes, I think you are correct in that there may be no rowsource for cbo_REF until cbo_DEBTOR is assigned, therefore I'm not getting any data until that is assigned. If I understand correctly what you are saying, that I just use a query for cbo_REF, and then in the Link fields property, I enter DEBTOR_IDX instead of TRANS_NO, will that give me the results I'm after? Therefore the query that I have in the AfterUpdate Event should just go straight into the recordsource of cbo_REF and hopefully that will fix it?!?! Winsa > Winsa, > [quoted text clipped - 22 lines] > > Object property of the subform control? Maybe some code on the Current > > event of the main form? Forgot to add, could it be a problem that DEBTOR_IDX is not actually a field in the subform?? Perhaps this is where I'm getting the error when I try to use it as a link field??? Winsa > Hi > [quoted text clipped - 27 lines] > Thanking you in advance > Winsa Winsa, Well, there is a princple that needs to be considered here. There is a relationship between the data in the main form's record source, and the data in the subform's record source. That relationship is based on the two sets of data having a field or fields in common. This field is what you must use as the Link Master Fields and Link Child Fields properties of the subform. On the basis of what you have told us so far, I don't know what fields these are, but you need to work it out on this basis, there's not really any flexibility in this. And yes, the subform will need to have a record source (table or query) assigned which will return the correct records at the time that the form is opened, and at the time that you navigate from one main form record to another. And similarly, the cbo_REF combobox will need to have a row source assigned which will return the correct list at the time that the form is opened, and at the time that you navigate from one main form record to another. If you are assigning the row source of the combobox in code on a main form event, you have to consider what the row source is before the code assigns/modifies it. That's why I was suggesting using a query for the combobox's row source, rather than coded SQL. SignatureSteve Schapel, Microsoft Access MVP > Forgot to add, could it be a problem that DEBTOR_IDX is not actually a field > in the subform?? Perhaps this is where I'm getting the error when I try to > use it as a link field??? > > Winsa Hi Steve I sort of understand what you are saying. I understand that the master and child need a link field, and I have that, but it's not related to the Debtor field. I'm only trying to use that field from the main form to show the correct invoices in the combo on the subform. Storing the Debtor in the subform is redundant, that's why the subform does not have this field. I tried just using a normal query for the subform combo box, but it then shows up all invoices, not just the ones related to the Debtor chosen on the main form. I have no idea where to go from here, or even what I should be considering. Thanks Winsa. > Winsa, > [quoted text clipped - 23 lines] > > > > Winsa Winsa, Can you post back with the SQL view of the "normal query" that you are trying for the subform combobox's row source? SignatureSteve Schapel, Microsoft Access MVP > Hi Steve > [quoted text clipped - 12 lines] > Thanks > Winsa. Hi Steve Here is the SQL of the "normal query". It's pretty much just the same as what I posted before, but just not in code. SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE As Date, AMOUNT As Amount, OUTSTANDING As Outstanding, NARRATIVE, TYPE, DEBTOR_IDX FROM tbl_MSTRACC WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 I'm not sure how to include the DEBTOR_IDX in the WHERE clause. I don't know how to reference a control on a form in SQL, if you can do that at all. This query just gives me the list of all invoices, not just the ones pertaining to the chosen debtor in the main form. Thanks heaps for your help! Winsa > Winsa, > [quoted text clipped - 17 lines] > > Thanks > > Winsa. Winsa, SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX FROM tbl_MSTRACC WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = [Forms]![frm_RECEIPTS]![DEBTOR_IDX] SignatureSteve Schapel, Microsoft Access MVP > Hi Steve > [quoted text clipped - 14 lines] > Thanks heaps for your help! > Winsa Hi Steve That's what I thought the SQL would be, but I get errors, Error in WHERE clause near '!' Unable to parse query text Is this because I'm working with an adp and not a normal mdb?? I tried (DEBTOR_IDX = '[Forms]![RECEIPTS]![DEBTOR_IDX]'), but it wouldn't show anything. Any other ideas? Thanks Winsa > Winsa, > [quoted text clipped - 22 lines] > > Thanks heaps for your help! > > Winsa Winsa, When do you get an error? What happens when you try to open the query datasheet? Can you copy/paste the exact SQL from the query that causes the error? SignatureSteve Schapel, Microsoft Access MVP > Hi Steve > [quoted text clipped - 12 lines] > Thanks > Winsa Hi Steve Sorry for the delayed reply, I haven't been at work for a few days. I entered in the SQL exactly as you had suggested in the Record Source Build Code section of cbo_REF. When I went to run the query, by clicking on "!", it came up with the error (I put the whole message in quotes): " SQL Syntax Error Encountered The following errors were encountered whilst parsing the contents of the SQL pane: Error in WHERE clause near '!'. Unable to parse query text. " and Access changed the SQL to read: SELECT MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX FROM tbl_MSTRACC WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND OUTSTANDING <> 0 AND DEBTOR_IDX = [Forms] ! [frm_RECEIPTS] ! [DEBTOR_IDX] Anymore ideas? Thanks for all your help. Winsa > Winsa, > [quoted text clipped - 18 lines] > > Thanks > > Winsa Winsa, I guess you mean the Row Source not Record Source? My first guess is that you have copied the line returns in the SQL that were put into my post by your newsreader. Why not try to make the query in query design view? Make a query based on the tbl_MSTRACC table, add the MST_TRANS_IDX, MSTRACC_REF AS Ref, TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX fields to the query design grid, and put your Criteria in as follows: In the criteria of the TYPE field put... "INV" Or "CRE" In the Criteria of the OUTSTANDING field put... <>0 And in the criteria of the DEBTOR_IDX field put... [Forms]![frm_RECEIPTS]![DEBTOR_IDX] See if that works. You can then see the SQL view of this query via the View menu. SignatureSteve Schapel, Microsoft Access MVP > Hi Steve > [quoted text clipped - 26 lines] > > Winsa Hi Steve Yes, sorry I did meant Row Source, not Record Source! I tried creating the query from scratch, and this is the SQL I got: SELECT MST_TRANS_IDX, MSTRACC_REF, TRANS_DATE, AMOUNT, OUTSTANDING, NARRATIVE, TYPE, DEBTOR_IDX FROM dbo.TESTMSTRACC WHERE (TYPE = 'INV' OR TYPE = 'CRE') AND (OUTSTANDING <> 0) AND (DEBTOR_IDX = '[Forms]!frm_RECEIPTS!DEBTOR_IDX') Unfortunately this retrieved nothing at all!! Both the query I tried and the cbo_REF comes up empty. All that cbo_REF contains is the column headings and nothing else. I have found another way to make it work, albeit more of a long winded way. I'm using the same code to populate the Row Source of cbo_REF in the ON CURRENT event of the main form as well as in the AFTER UPDATE Event of cbo_DEBTOR. This seems to give me the desired results. Thanks heaps for your suggestions and much valued help. Pity it wouldn't work for whatever reason! Thanks again! Winsa > Winsa, > [quoted text clipped - 47 lines] > > > > Winsa |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.