 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2005filtered rowsource for combo
ok i have a problem here and not much time to play round. have a form bound to a table. one of the combos is bound to a field Agents. i have active and retired agents. thing is, i want to be able to retrieve all agents from the table, but to see only active in dropdown list of the combo, to asure accurate data entry. if i filter the agents table for a rowsource to get back only active in the dropdown, i cant retreive names of the rest when i go through the records in my form. obviosly they are blank for those guys who are retired, cos they are not in the dropdown. the only way i see it is to make a separate form just for dataentry,but there must be an elegant solution here. whats worse,i bet i had a same situation in access97 and no problems. now its access2000 . advice will be appreciated. > ok i have a problem here and not much time to play round. > have a form bound to a table. one of the combos is bound to a field [quoted text clipped - 8 lines] > solution here. whats worse,i bet i had a same situation in access97 and > no problems. now its access2000 . advice will be appreciated. Are you using an .adp file? There's a difference between the methods you can use in .adp & .mdb for dynamic ComboBoxes (changing RowSources). Does your ComboBox have a dynamic RowSource? The form should be bound to the table or a query that gets both active & retired agents. The ComboBox should have a RowSource that only gets the active agents. To say "obviosly they are blank for those guys who are retired, cos they are not in the dropdown" isn't obvious. If you have two separate queries (one for the Form, the other for the ComboBox) the Form should always show all the records in the table and the ComboBox should show only active agents. IOW, ideally, you'd have 2 SQL statements for the Form & the ComboBox: Form - RecordSource Property: SELECT * FROM Agents ComboBox - RowSource Property: SELECT AgentID, AgentName FROM Agents WHERE Active = True What's your Form's RecordSource property and what's your ComboBox's RowSource property?  SignatureMGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) thanks mgfoster, my two sql statements look EXACTLY like yours, i just use a query instead Agents table in first one. otherwise my logic is same as yours. is there a way to change rowsource in runtime? it should be, like any other property, but im not sure what event to chose to trigger that change. i dont know anything about dynamic rowsources. but im about to find out . im using .mdb file. ok, now i see whats dynamic rowsource. which brings me to a previous question: when to change it to show only active agents? on BeforeUpdate of that combo? and than switch back to all agents AfterUpdate? > ok, now i see whats dynamic rowsource. which brings me to a previous > question: when to change it to show only active agents? on BeforeUpdate > of that combo? > and than switch back to all agents AfterUpdate? What, exactly, are you doing w/ the ComboBox? It sounds like you're using the ComboBox to filter the Form. Is that correct? If so, the ComboBox has to be unbound & you'd use the ComboBox's AfterUpdate event to change the Form's RecordSource. E.g.: Private Sub ComboBox_AfterUpdate() Me.RecordSource = "SELECT * FROM Agents WHERE AgentID = " & _ Me!ComboBox End Sub This would show only the Agent selected in the ComboBox. The ComboBox's properties would be something like this: ControlSource: <nothing> BoundColumn: 1 RowSourceType: Table/Query RowSource: SELECT AgentID, AgentName FROM Agents ORDER BY AgentName ColumnCount: 2 ColumnWidths: 0";2" SignatureMGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 [quoted text clipped - 20 lines] > ColumnCount: 2 > ColumnWidths: 0";2" not at all, mgfoster. form is filtered before, filter not afecting that particular combo. i simply use the form to review/enter some transaction. one of the parameters of that transaction is AgentName. Agents may come and go, but deals are still active . so out of 100 deals, 25 has AgentName field of an agent who is not employed any more. i need to see those AgentNames as i am going through the records,(or when creating reports, for that matter), but when a new transaction has to be entered, i want to see only the names of currently employed, active agents in the dropdown list of the combo. So thats why i need a combo to be bound to AgentName field in Transaction table( which is a datasource for the form) . so when i filter the rowsource of the combo(select...from...where agents=active), i do get only active AgentNames, but at the same time , i cant see the name of non-active AgentName when such a transaction comes up. i explained before , when i store AgentNames in the transaction table everything is ok, but thats not really the way things should be. i need to mention that this is only a temp soluton, as in near future agent will log to start their session, and there will be no need for that combo. but now i need to have it . < SNIP > > i simply use the form to review/enter some transaction. one of the > parameters of that transaction is AgentName. Agents may come and go, [quoted text clipped - 15 lines] > agent will log to start their session, and there will be no need for > that combo. but now i need to have it . I believe I have it now: You want to see ALL agents in the ComboBox box when editing old records; for New records only the ACTIVE agents should appear in the ComboBox. If my assertion is correct then in the form's OnCurrent event you will be changing the RowSource of the ComboBox. E.g.: Private Sub Form_Current() Const SQL = "SELECT AgentID, AgentName FROM Agents " If Me.NewRecord Then Me!cboAgents.RowSource = SQL & "WHERE Active = True" Else Me!cboAgents.RowSource = SQL End If End Sub HTH, SignatureMGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) > Hash: SHA1 > [quoted text clipped - 17 lines] > > HTH, thanks mgfoster didnt know about newrecord property. i have done somrthin similar, since i have add new deal option on the swithboard. so i open form in add mode and change rowsource then. well it works if you store the name of the agent in your table that you use as a datasource, and have the field with the name in a query for a rowsource, but thats not a way it should be. i want to keep just agentID, dealID and other ids in my table, as god intended to be kept in relational database. |
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.