 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / October 2008recordset.clone
Hi, Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Location] = '" & Me![Combo58] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark I have this code. what code do i need to add so that i can see all the records in my subform after i select from my combo box? thanks denver To do that you need no code at all. Just set the Link Master field property of the sub-form control to Form!Combo58 and the Link Child field to Location. Note that Location must be part of the Record Source of the sub-form and that if you are using controls rather than columns you cannot use the wizard, you have to type the values in yourself. HTH John ################################## Don't Print - Save trees > Dim rs As Object > Set rs = Me.Recordset.Clone [quoted text clipped - 3 lines] > what code do i need to add so that i can see all the records in my subform > after i select from my combo box? I have try this suggestions but no luck. it doesnt sort at all after i select from my combo. the code that i have i used it to sort all records for a certain Location..it works will... what i want is a code that displays all the records i have in my subform even i have not select a location in my combo? or a code that clears my selection and displays my all my records in the sub-form.. sorry for not elaborating..but thanks this was helpful to me. > To do that you need no code at all. Just set the Link Master field property > of the sub-form control to Form!Combo58 and the Link Child field to Location. [quoted text clipped - 15 lines] > > what code do i need to add so that i can see all the records in my subform > > after i select from my combo box? Yes, that is different and would require code. I assume that your Combo58 has a Row Source something like: SELECT location_code, location_name FROM locations ORDER BY location_name If you change that to a UNION query you can add an option for 'All': SELECT location_code, location_name FROM locations UNION SELECT ' ', ' All Locations' FROM locations ORDER BY location_name Note the space in from of the description to put it at the top of the list. Then in the After Update event of the Combo you can put: Dim SQL as String SQL = "SELECT some_columns FROM some_tables" If Me.Combo58 <> " " THEN SQL = SQL & " WHERE Location = '" & Me![Combo58] & "'" End IF Me!SubFormControlName.Form.RecordSource = SQL Substituting your own column, table and control names. Note that the initial sql assigned to SQL will be the same sql that the subform is currently based on. If this includes a WHERE clause then change the WHERE in the code to AND. HTH John ################################## Don't Print - Save trees > I have try this suggestions but no luck. it doesnt sort at all after i select > from my combo. the code that i have i used it to sort all records for a [quoted text clipped - 24 lines] >>> what code do i need to add so that i can see all the records in my subform >>> after i select from my combo box? right now i have this RowSource for my Combobox SELECT [Data Entry].Location FROM [Data Entry] GROUP BY [Location]; so i change like this but it dosen't work.... SELECT ' ', ' All Locations' FROM [Data Entry].locations ORDER BY [location]; is there correct? please guide with this i am not a programmer. for my afterupdate Event i have this code.........this work will but i need to see all my records before a i made selection from my combo box....the moment i open my form with the subform it applieas already the filters... Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Location] = '" & Me![Combo58] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark i don't how to change the code from the example you give me Dim SQL as String SQL = "SELECT some_columns FROM some_tables" If Me.Combo58 <> " " THEN SQL = SQL & " WHERE Location = '" & Me![Combo58] & "'" End IF Me!SubFormControlName.Form.RecordSource = SQL because i don't know what is some_columns FROM some_tables is all about? please can you guide thru my codes i pasted here thanks anyway, John Smith denver > Yes, that is different and would require code. > [quoted text clipped - 57 lines] > >>> what code do i need to add so that i can see all the records in my subform > >>> after i select from my combo box? If you do not have a code in your table the union query would be: SELECT DISTINCT location FROM [Data Entry] UNION SELECT ' All Locations' FROM [Data Entry] ORDER BY location some_columns and some_tables were intended as place holders for the names in your database, which I do not know. Look at the query in the record source of your form and insert the column and table names from there. Alternatively if the query is very complex you could do: If Me!Combo58 = " All Locations" THEN Me.FilterOn = False Else Me.Filter = "location = '" & Me![Combo58] & "'" Me.FilterOn = True End If If you do not want the records filtered when you open the form then remove all of the FormOpen code. HTH John ################################## Don't Print - Save trees > right now i have this RowSource for my Combobox > SELECT [Data Entry].Location FROM [Data Entry] GROUP BY [Location]; [quoted text clipped - 75 lines] >>>>> what code do i need to add so that i can see all the records in my subform >>>>> after i select from my combo box? Hello John, i have this code..for my Combobox (Combo58) SELECT DISTINCT location FROM [Data Entry2] UNION SELECT ' All Locations' FROM [Data Entry2] ORDER BY location; i have this Afterupdate Dim SQL As String SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name]," & _ vbCrLf & "[Data Entry2].[Drawing Ref] , [Data Entry2].[Sheet No], [Data Entry2].Rev, [Data Entry2].Description," & _ vbCrLf & "[Data Entry2].[Drwg Typ], [Data Entry2].[Drawing Title], [Data Entry2].[Rd Line], [Data Entry2].[TR Rd Line]," & _ vbCrLf & "[Data Entry2].[TR Rd Line Date], [Data Entry2].AsBuilt, [Data Entry2].[Forecast (SubCon)]," & _ vbCrLf & "[Data Entry2].[Reviewed Red Line], [Data Entry2].[Reviewed (%)]," & _ vbCrLf & "[Data Entry2].[Forecast (SLSA)], [Data Entry2].[Reviewed Red Line(PMT)], [Data Entry2].[TR Rd Line(PMT)]," & _ vbCrLf & "[Data Entry2].[TR Rd Line(PMT) Date], [Data Entry2].[Reviewed (%) PMT], [Data Entry2].[Forecast PMT]," & _ vbCrLf & "[Data Entry2].SubContractor, [Data Entry2].CWP, [Data Entry2].[CWP Title], [Data Entry2].Index," & _ vbCrLf & "[Data Entry2].DISCIPLINE, [Data Entry2].Prefix, [Data Entry2].Size," & _ vbCrLf & "[Data Entry2].size_value, [Data Entry2].[Qty of Sheets], [Data Entry2].[Eqv_A_Size]," & _ vbCrLf & "[Data Entry2].[Earned ASize], [Data Entry2].[Physical Prog], [Data Entry2].TR, [Data Entry2].Date," & _ vbCrLf & "[Data Entry2].SubCon, [Data Entry2].Location, [Data Entry2].[Drawing Control No] FROM [Data Entry2];" If Me!Combo58 = " All Locations" Then Me.FilterOn = False Else Me.Filter = "location = '" & Me![Combo58] & " '" Me.FilterOn = True End If my problem is when i select 'All Locations' it doesnt show me all the records that i have in my subform....what codes do i miss here? thanks for any help > If you do not have a code in your table the union query would be: > [quoted text clipped - 103 lines] > >>>>> what code do i need to add so that i can see all the records in my subform > >>>>> after i select from my combo box? You seem to have a mixture of both methods here. If you are going to use a filter then you don't need to have the SQL, if you want to use the SQL then you need to append the criteria and set the recordsource. i.e.: SQL = "SELECT ... If Me!Combo58 <> " All Locations" Then SQL = SQL & " WHERE location = '" & Me![Combo58] & " '" End If Me.RecordSource = SQL OR just: If Me!Combo58 = " All Locations" Then Me.FilterOn = False Else Me.Filter = "location = '" & Me![Combo58] & " '" Me.FilterOn = True End If Incidentally, you do not need all the vbcrlf's in the declaration of the SQL, just a space between each item. Apart from that it should work provided that " All Locations" is identical in both the RowSource and the event code, especially check that the spaces match as people tend not to notice them but computers do! HTH John ################################## Don't Print - Save trees > Hello John, > [quoted text clipped - 144 lines] >>>>>>> what code do i need to add so that i can see all the records in my subform >>>>>>> after i select from my combo box? John, I try doing this but if i am going to remove the vbcrlf's i have this error.... :Expected End of Statement SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name], " [Data Entry2].[Drawing Ref] , [Data Entry2].[Sheet No], [Data Entry2].Rev, [Data Entry2].Description, "" [Data Entry2].[Drwg Typ] , [Data Entry2].[Drawing Title], [Data Entry2].[Rd Line], [Data Entry2].[TR Rd Line], " " [Data Entry2].[TR Rd Line Date] , [Data Entry2].AsBuilt, [Data Entry2].[Forecast (SubCon)], " " [Data Entry2].[Reviewed Red Line] , [Data Entry2].[Reviewed (%)], " " [Data Entry2].[Forecast (SLSA)] , [Data Entry2].[Reviewed Red Line(PMT)], [Data Entry2].[TR Rd Line(PMT)], " " [Data Entry2].[TR Rd Line(PMT) Date] , [Data Entry2].[Reviewed (%) PMT], [Data Entry2].[Forecast PMT], " " [Data Entry2].SubContractor , [Data Entry2].CWP, [Data Entry2].[CWP Title], [Data Entry2].Index, " " [Data Entry2].DISCIPLINE , [Data Entry2].Prefix, [Data Entry2].Size, " " [Data Entry2].size_value , [Data Entry2].[Qty of Sheets], [Data Entry2].[Eqv_A_Size], " " [Data Entry2].[Earned ASize] , [Data Entry2].[Physical Prog], [Data Entry2].TR, [Data Entry2].Date, " " [Data Entry2].SubCon, [Data Entry2].Location, [Data Entry2].[Drawing Control No] FROM [Data Entry2]: thanks again John.. denver > You seem to have a mixture of both methods here. If you are going to use a > filter then you don't need to have the SQL, if you want to use the SQL then [quoted text clipped - 179 lines] > >>>>>>> what code do i need to add so that i can see all the records in my subform > >>>>>>> after i select from my combo box? John, > I try doing this but if i am going to remove the vbcrlf's i have this > error.... > :Expected End of Statement > how can i work from this error or modify my codes?hope you are patience and kind in helping me with this.. > SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name], " > [Data Entry2].[Drawing Ref] , [Data Entry2].[Sheet No], [Data Entry2].Rev, [quoted text clipped - 17 lines] > [Data Entry2].SubCon, [Data Entry2].Location, [Data Entry2].[Drawing Control > No] FROM [Data Entry2]: the second thing is that i try to use for the moment the vbCrlf so that i can continue in writng my codes.. so i write this codes...but i have this Run Time Error '3142':Characters found after end of SQL statement. what is my mistakes here? >If Me!Combo58 <> " All Locations" Then SQL = SQL & " WHERE location = '" & Me![Combo58] & "'" End If Me.RecordSource = SQL thanks again John..thank you very much John.... denver *********************************************************** > > You seem to have a mixture of both methods here. If you are going to use a > > filter then you don't need to have the SQL, if you want to use the SQL then [quoted text clipped - 179 lines] > > >>>>>>> what code do i need to add so that i can see all the records in my subform > > >>>>>>> after i select from my combo box? You took out the line continuation characters as well as the vbcrlf. Your code should look like this: SQL = "SELECT [Site ID], [Site Name], [Drawing Ref], [Sheet No]," _ & " Rev, Description, [Drwg Typ], [Drawing Title], [Rd Line]," _ & " [TR Rd Line], [TR Rd Line Date], AsBuilt, [Forecast (SubCon)]," _ & " [Reviewed Red Line], [Reviewed (%)], [Forecast (SLSA)]," _ & " [Reviewed Red Line(PMT)], [TR Rd Line(PMT)], [TR Rd Line(PMT) Date]," _ & " [Reviewed (%) PMT], [Forecast PMT], SubContractor, CWP, [CWP Title]," _ & " Index, DISCIPLINE, Prefix, Size, size_value, [Qty of Sheets]," _ & " [Eqv_A_Size], [Earned ASize], [Physical Prog], TR, Date, SubCon," _ & " Location, [Drawing Control No] FROM [Data Entry2]" The cause your second error was the colon at the end of the SQL. Incidentally, if you keep your names to strict alpha-numeric, no other characters or spaces you will not need to bracket them every time that you refer to them. You should also refrain from using reserved words as names for your objects as it can get Access very confused. Potential problems that I notice in this table are Index, Size and Date. > John, >> I try doing this but if i am going to remove the vbcrlf's i have this [quoted text clipped - 224 lines] >>>>>>>>>> what code do i need to add so that i can see all the records in my subform >>>>>>>>>> after i select from my combo box? John, now i have this code after my SQL = "SELECT...... If Me!Combo58 = " All Locations" Then Me.FilterOn = False Else Me.Filter = "Location = '" & Me![Combo58] & " '" Me.FilterOn = True End If and set the Link child Field:Location Link master Field:Forms!Combo58 the problem is when i click All Locations from my Combo58 it resulted to blank SubForm. Please can you guide how i can let this works? the rest of the location after being selected from Combo58 it does work and filters. but when i choose "All Locations", it doesn't work at all, it gives me a blank subform. i have read your last reply and you said "Potential problems that I notice int his table are Index, Size and Date...What is this problem about? can you help me to fix this? thanks again John Smith...i really appreciate... denver <<<<<<>>>>>>>>><<<<<<<>>>>>******************>>>><<<<>>> > You took out the line continuation characters as well as the vbcrlf. Your > code should look like this: [quoted text clipped - 245 lines] > >>>>>>>>>> what code do i need to add so that i can see all the records in my subform > >>>>>>>>>> after i select from my combo box? Sorry for the delay in replying, I've been so busy that I've not had chance to get to the list. You only want to have master child links if you are using them to control the content of the sub form. Since you want an option of all you cannot do it that way so blank out the link fields. I must admit that I had forgotten that it was the sub form that you needed to filter so the code should have read: If Me!Combo58 = " All Locations" Then Me!SubFormControlName.Form.FilterOn = False Else Me!SubFormControlName.Form.Filter = "Location = '" & Me![Combo58] & " '" Me!SubFormControlName.Form.FilterOn = True End If replacing SubFormControlName with the actual name of your sub-form control (possibly but not necessarily the same as the name of the form in it). Sorry about that. If you use Access reserved words to name your objects (tables, columns, controls etc) you may well find access getting confused when you refer to them. For example understanding Date to be the built in function that returns today's date rather than the column in your table. This can cost you hours trying to discover why something is not doing what you expect it to. You are much better off avoiding using them. HTH John ################################## Don't Print - Save trees > John, > [quoted text clipped - 273 lines] >>>>>>>>>>>> what code do i need to add so that i can see all the records in my subform >>>>>>>>>>>> after i select from my combo box? John, Nothing to worried about that, I understand. But anyway Thank you very very much..... it really works now... Thank you i really appreciate. > Sorry for the delay in replying, I've been so busy that I've not had chance to > get to the list. [quoted text clipped - 286 lines] > >>>>>>>>>> from my combo. the code that i have i used it to sort all records for a > >>>>>>>>>> certain Location..it works will... |
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.