If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
tables not relating properly
Hi, Bruce. I reorganized my table(s) so that all referral info is in one:
Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can’t locate a named object) SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType field. Switch to datasheet view. If it is OK, switch to SQL view and note exactly what you see there. This combo box is unbound, as I see it. I can't quite make out the purpose of ReferralSourcesAll. I woud imagine Referral data something like this: tblReferralSourcesAll RefSourceID (autonumber primary key) ResourceType (Agency, etc.) Resource (Name of referral) Phone, Address, etc. RefSourceID ResourceType Resource 1 Agency CPS 2 General Dr. Welby 3 General Dr. Jekyll I have left out Phone, etc., as they don't matter for this, although you may need them. The second combo box (I will call it cboSource; note the name, for I will refer to it again soon) would have as its Row Source: SELECT tblReferralSourcesAll.Resource FROM tblReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]"All","", [Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True)) ORDER BY tblReferralSourcesAll.Resource; This assumes that you are storing the value Agency, etc. from [Resource ID Table]. The way you could put that value into tblReferralSourcesAll is to make a form based on tblReferralSourcesAll, with a combo box bound to the ResourceType field (that is, ResourceType is its ControlSource, which means that values entered into the combo box are stored in the ResourceType field). That combo box, with a column count of 1 and a column width of, say, 1", would have as its Row Source the same Row Source code as described for your first combo box. Select the ResourceType, and it will be inserted into the field. Back to your first combo box. In its After Update event you would have Me.cboSource.Requery. To add this code, open the form's property sheet: Click the combo box to select it, click View Properties, and click the Event tab. Click After Update, click the three dots on the right, and insert Me.cboSource.Requery between Private Sub and End Sub. What this does is to requery the Row Source for cboSource. That is to say, it runs the Row Source code, with the selected value as the parameter. If you selected Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value "Agency". The Row Source looks for all Resource records that have "Agency" as the ResourceType. You will probably want to put Me.cboSource.Requery into the form's Current event, so that when you arrive at a record the Resource text box will have the correct list. I hope this gets you closer to where you need to be. What is the Row Source for the second combo box (the Referral one)? Again, any code? SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral Source Label], [Resource ID Table].[Resource Type] FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID Table].[Resource ID] = ReferralSourcesAll.[Resource ID] WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake Worksheet2]![ReferralType])); Thanks!! Meredith "BruceM" wrote: What is the Row Source for the first combo box (the ReferralType one)? Does it have any After Update code or other code? I'm not sure where you are in your Access vocabulary, so at the risk of saying something you already know, click the combo box to select it, click View Properties, and Click the Event tab. If any of the Events has [Event Procedure], click the three dots to the right of the row to view the code. The code will go something like: Private Sub cboReferralType_After Update {Code here} End Sub What is the Row Source for the second combo box (the Referral one)? Again, any code? In describing the Row Source, remember that I can't see you database, so you should describe any tables and fields that appear if if is not obvious by context. For instance, I can assume that Referral is the name of the Referral, but other things may not be as clear. "MeredithS" wrote in message ... Hi, Bruce -- I'm working to implement your suggested combo boxes w/criteria but am having some coding problems, apparently -- Can you help with that? If so, what should I post?? The 2nd combo box is appearing as a parameter query for the 1st and not picking up the values ... Thanks, Meredith "BruceM" wrote: A combo box list (Row Source) may be either a Value List ("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that ReferralType is a field in a referral table (tblReferral), you could create a query (qryRefType) based on tblReferral, with just the field ReferralType. Right click the top of the query in design view (above the grid), and set Unique Values to Yes. A query uses code known as SQL behind the scenes. You can see a query's SQL by opening a query and clicking View SQL. You can also use SQL directly as a Row Source. Here is an excerpt from this web page: http://www.mvps.org/access/forms/frm0043.htm I have adapted it to your situation. SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as Bogus FROM tblReferral ORDER BY ReferralType; There are no line breaks, although some may appear in your newsreader window. This should give you a list of ReferralTypes, with (All) at the top of the list. The DISTINCT in the code means that even though a ReferralType appears in many records in tblReferral, it appears only once in the list. The combo box column count would be 1, and the column width about 1". You can set the Default Value of the combo box to "(All)" so that (All) appears even if no choice is made. You could use a Value List for the combo box Row Source, with "(All)" as one of the choices, but by using the SQL you can change or add the list of Referral Types without changing the code. The Row Source for the combo box from which you select the actual referral could be something like: SELECT tblReferral.Referral FROM tblReferral WHERE (((IIf([Forms]![frmReferral]![cboReferral]"All","", [Referral] = [Forms]![frmReferral]![cboReferral]))=True)) ORDER BY tblReferral.Referral; I have tested this code by adapting it from one of my own projects. I cannot say for sure that it will work in your project since I don't know all of the details of what you have done. One of my assumptions is that tblReferral contains fields for Referral (CPS, etc.) and ReferralType, along with whatever else you need. For a two-column combo box (with both columns visible), use a two-column Row Source query. Set the column count to 2 and the column widths to something like 1";1" However, I don't think that is what you really need here. |
#22
|
|||
|
|||
tables not relating properly
"MeredithS" wrote in message
... Hi, Bruce. I reorganized my table(s) so that all referral info is in one: Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can't locate a named object) cboReferralSource is the second combo box (the one from which you select the specific referral entity). Below you have referred to it as Referral Source. Use your actual combo box name for the requery code. By the way, you will be doing yourself a favor if you restrict Access names (tables, forms, fields, controls, etc.) to alphanumeric characters and the underscore. Surrounding the name in square brackets should eliminate the problems that can result from spaces, but why give yourself the extra hassle? Also, I think you will find the shortest possible descriptive name for a form, field, etc. is the most convenient in code. Finally, giving a control a different name than the field will eliminate some puzzling error messages that can appear if Access gets confused. No need to change anything in your current project. These are just ideas for the future. SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType field. Switch to datasheet view. If it is OK, switch to SQL view and note exactly what you see there. This combo box is unbound, as I see it. I can't quite make out the purpose of ReferralSourcesAll. I woud imagine Referral data something like this: tblReferralSourcesAll RefSourceID (autonumber primary key) ResourceType (Agency, etc.) Resource (Name of referral) Phone, Address, etc. RefSourceID ResourceType Resource 1 Agency CPS 2 General Dr. Welby 3 General Dr. Jekyll I have left out Phone, etc., as they don't matter for this, although you may need them. The second combo box (I will call it cboSource; note the name, for I will refer to it again soon) would have as its Row Source: SELECT tblReferralSourcesAll.Resource FROM tblReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]"All","", [Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True)) ORDER BY tblReferralSourcesAll.Resource; This assumes that you are storing the value Agency, etc. from [Resource ID Table]. The way you could put that value into tblReferralSourcesAll is to make a form based on tblReferralSourcesAll, with a combo box bound to the ResourceType field (that is, ResourceType is its ControlSource, which means that values entered into the combo box are stored in the ResourceType field). That combo box, with a column count of 1 and a column width of, say, 1", would have as its Row Source the same Row Source code as described for your first combo box. Select the ResourceType, and it will be inserted into the field. Back to your first combo box. In its After Update event you would have Me.cboSource.Requery. To add this code, open the form's property sheet: Click the combo box to select it, click View Properties, and click the Event tab. Click After Update, click the three dots on the right, and insert Me.cboSource.Requery between Private Sub and End Sub. What this does is to requery the Row Source for cboSource. That is to say, it runs the Row Source code, with the selected value as the parameter. If you selected Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value "Agency". The Row Source looks for all Resource records that have "Agency" as the ResourceType. You will probably want to put Me.cboSource.Requery into the form's Current event, so that when you arrive at a record the Resource text box will have the correct list. I hope this gets you closer to where you need to be. What is the Row Source for the second combo box (the Referral one)? Again, any code? SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral Source Label], [Resource ID Table].[Resource Type] FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID Table].[Resource ID] = ReferralSourcesAll.[Resource ID] WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake Worksheet2]![ReferralType])); Thanks!! Meredith "BruceM" wrote: What is the Row Source for the first combo box (the ReferralType one)? Does it have any After Update code or other code? I'm not sure where you are in your Access vocabulary, so at the risk of saying something you already know, click the combo box to select it, click View Properties, and Click the Event tab. If any of the Events has [Event Procedure], click the three dots to the right of the row to view the code. The code will go something like: Private Sub cboReferralType_After Update {Code here} End Sub What is the Row Source for the second combo box (the Referral one)? Again, any code? In describing the Row Source, remember that I can't see you database, so you should describe any tables and fields that appear if if is not obvious by context. For instance, I can assume that Referral is the name of the Referral, but other things may not be as clear. "MeredithS" wrote in message ... Hi, Bruce -- I'm working to implement your suggested combo boxes w/criteria but am having some coding problems, apparently -- Can you help with that? If so, what should I post?? The 2nd combo box is appearing as a parameter query for the 1st and not picking up the values ... Thanks, Meredith "BruceM" wrote: A combo box list (Row Source) may be either a Value List ("Excellent","Good","Fair","Poor") or a Table/Query. Assuming that ReferralType is a field in a referral table (tblReferral), you could create a query (qryRefType) based on tblReferral, with just the field ReferralType. Right click the top of the query in design view (above the grid), and set Unique Values to Yes. A query uses code known as SQL behind the scenes. You can see a query's SQL by opening a query and clicking View SQL. You can also use SQL directly as a Row Source. Here is an excerpt from this web page: http://www.mvps.org/access/forms/frm0043.htm I have adapted it to your situation. SELECT DISTINCT ReferralType FROM tblReferral UNION Select "(All)" as Bogus FROM tblReferral ORDER BY ReferralType; There are no line breaks, although some may appear in your newsreader window. This should give you a list of ReferralTypes, with (All) at the top of the list. The DISTINCT in the code means that even though a ReferralType appears in many records in tblReferral, it appears only once in the list. The combo box column count would be 1, and the column width about 1". You can set the Default Value of the combo box to "(All)" so that (All) appears even if no choice is made. You could use a Value List for the combo box Row Source, with "(All)" as one of the choices, but by using the SQL you can change or add the list of Referral Types without changing the code. The Row Source for the combo box from which you select the actual referral could be something like: SELECT tblReferral.Referral FROM tblReferral WHERE (((IIf([Forms]![frmReferral]![cboReferral]"All","", [Referral] = [Forms]![frmReferral]![cboReferral]))=True)) ORDER BY tblReferral.Referral; I have tested this code by adapting it from one of my own projects. I cannot say for sure that it will work in your project since I don't know all of the details of what you have done. One of my assumptions is that tblReferral contains fields for Referral (CPS, etc.) and ReferralType, along with whatever else you need. For a two-column combo box (with both columns visible), use a two-column Row Source query. Set the column count to 2 and the column widths to something like 1";1" However, I don't think that is what you really need here. |
#23
|
|||
|
|||
tables not relating properly
Hi, Bruce -- I read your comments and I don't think I understand which combo
box the After Update Event procedure is associated with. My understanding is that the procedure will requery the selected value of the 1st combo box; so, to me, that implies that the After Update would be associated with the 2nd combo box, but refer to the 1st. Is that correct? I changed the name of the 2nd box to ReferralSource; 1st name is Referral. Therefore, the code would be (associated w/the 2nd box): Me.cboReferral.Requery Is that right? You also said to put this code into the form's Current Event, but the referral source info is just part of a much larger form so would that still be true?? Many thanks, Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce. I reorganized my table(s) so that all referral info is in one: Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can't locate a named object) cboReferralSource is the second combo box (the one from which you select the specific referral entity). Below you have referred to it as Referral Source. Use your actual combo box name for the requery code. By the way, you will be doing yourself a favor if you restrict Access names (tables, forms, fields, controls, etc.) to alphanumeric characters and the underscore. Surrounding the name in square brackets should eliminate the problems that can result from spaces, but why give yourself the extra hassle? Also, I think you will find the shortest possible descriptive name for a form, field, etc. is the most convenient in code. Finally, giving a control a different name than the field will eliminate some puzzling error messages that can appear if Access gets confused. No need to change anything in your current project. These are just ideas for the future. SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType field. Switch to datasheet view. If it is OK, switch to SQL view and note exactly what you see there. This combo box is unbound, as I see it. I can't quite make out the purpose of ReferralSourcesAll. I woud imagine Referral data something like this: tblReferralSourcesAll RefSourceID (autonumber primary key) ResourceType (Agency, etc.) Resource (Name of referral) Phone, Address, etc. RefSourceID ResourceType Resource 1 Agency CPS 2 General Dr. Welby 3 General Dr. Jekyll I have left out Phone, etc., as they don't matter for this, although you may need them. The second combo box (I will call it cboSource; note the name, for I will refer to it again soon) would have as its Row Source: SELECT tblReferralSourcesAll.Resource FROM tblReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]"All","", [Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True)) ORDER BY tblReferralSourcesAll.Resource; This assumes that you are storing the value Agency, etc. from [Resource ID Table]. The way you could put that value into tblReferralSourcesAll is to make a form based on tblReferralSourcesAll, with a combo box bound to the ResourceType field (that is, ResourceType is its ControlSource, which means that values entered into the combo box are stored in the ResourceType field). That combo box, with a column count of 1 and a column width of, say, 1", would have as its Row Source the same Row Source code as described for your first combo box. Select the ResourceType, and it will be inserted into the field. Back to your first combo box. In its After Update event you would have Me.cboSource.Requery. To add this code, open the form's property sheet: Click the combo box to select it, click View Properties, and click the Event tab. Click After Update, click the three dots on the right, and insert Me.cboSource.Requery between Private Sub and End Sub. What this does is to requery the Row Source for cboSource. That is to say, it runs the Row Source code, with the selected value as the parameter. If you selected Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value "Agency". The Row Source looks for all Resource records that have "Agency" as the ResourceType. You will probably want to put Me.cboSource.Requery into the form's Current event, so that when you arrive at a record the Resource text box will have the correct list. I hope this gets you closer to where you need to be. What is the Row Source for the second combo box (the Referral one)? Again, any code? SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral Source Label], [Resource ID Table].[Resource Type] FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID Table].[Resource ID] = ReferralSourcesAll.[Resource ID] WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake Worksheet2]![ReferralType])); Thanks!! Meredith "BruceM" wrote: What is the Row Source for the first combo box (the ReferralType one)? Does it have any After Update code or other code? I'm not sure where you are in your Access vocabulary, so at the risk of saying something you already know, click the combo box to select it, click View Properties, and Click the Event tab. If any of the Events has [Event Procedure], click the three dots to the right of the row to view the code. The code will go something like: Private Sub cboReferralType_After Update {Code here} End Sub What is the Row Source for the second combo box (the Referral one)? Again, any code? In describing the Row Source, remember that I can't see you database, so you should describe any tables and fields that appear if if is not obvious by context. For instance, I can assume that Referral is the name of the Referral, but other things may not be as clear. "MeredithS" wrote in message ... |
#24
|
|||
|
|||
tables not relating properly
No, the other way around with the After Update. First, we need to get some
names straight. I am not looking at your database, and am having trouble keeping combo boxes and fields straight, so I will be referring to your combo boxes as cboReferralType (for selecting Agency, General, Specific, and (All) and cboReferral (for selecting the actual referral). The form on which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound to a table that presumably has other information for date, etc. This table must also have a field to store the Referral (the actual name). Actually, you should probably be storing the ReferralID rather than the name, since ReferralID is an unchanging number field (that is, the agency name may change, but its number won't, just as your SS # does not change). More names: I will leave the table name as ReferralSourcesAll. Here are its fields: ReferralID (autonumber primary key) ReferralType (Agency, etc.) Referral (CPS, etc.) Here is the code for cboReferralType: SELECT DISTINCT ReferralSourcesAll.[ReferralType] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[ReferralType]; After choosing a ReferralType in cboReferral (the first combo box), cboReferral is updated. When that happens the After Update event runs. Let's say you selected "Agency". cboReferral says to the combo box ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into your Row Source code, and run the code again." Back to the cboReferralSource, with changes based on the naming things I mentioned. SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True)) ORDER BY ReferralSourcesAll.Referral; Breaking it down: SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll As is probably clear, this is the instruction to select the Referral field from ReferralSourcesAll WHERE What follows are restrictions on the number of records. IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All" This refers to cboReferralType. That is where "All" may appear in some cases, or else the ReferralType. This is saying "If the selection in cboReferralType is "All"...(we'll get to what needs to happen if this is true or if it is false). NOTE: I changed to =. I got it backward the first time. Back to the full IIf statement: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) If cboReferralType = "All" then (first comma) do nothing, otherwise (second comma) Referral (the table field in ReferralSourcesAll) = the selection in cboReferral. "Agency" is the selection in cboReferral, so the WHERE part of the Row Source amounts to: WHERE ReferralType = Agency The full Row Source for the combo box cboReferralSource, in vernacular: SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = Agency ORDER BY (that is, sort by) the Referral field If the selection in cboReferralType is (All), look at the IIf statement again: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) cboReferralType equals (All), so the first condition ("") applies. Since "" is an empty string, there are no restrictions on the records (no WHERE condition): SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = nothing ORDER BY (that is, sort by) the Referral field The extra parentheses in the IIf statement, and the = True part, are things that the code needs. To be honest, I built most of the query with the query design grid, then looked at the SQL afterwards. So the After Update event (Me.cboReferral.Requery) of cboReferralType is telling cboReferral to limit its list of records to just the records with Agency (in my first example) in the ReferralType field. Forget about the code in the Current event. It's probably not needed, since for existing records the Referral has already been selected. "MeredithS" wrote in message ... Hi, Bruce -- I read your comments and I don't think I understand which combo box the After Update Event procedure is associated with. My understanding is that the procedure will requery the selected value of the 1st combo box; so, to me, that implies that the After Update would be associated with the 2nd combo box, but refer to the 1st. Is that correct? I changed the name of the 2nd box to ReferralSource; 1st name is Referral. Therefore, the code would be (associated w/the 2nd box): Me.cboReferral.Requery Is that right? You also said to put this code into the form's Current Event, but the referral source info is just part of a much larger form so would that still be true?? Many thanks, Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce. I reorganized my table(s) so that all referral info is in one: Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can't locate a named object) cboReferralSource is the second combo box (the one from which you select the specific referral entity). Below you have referred to it as Referral Source. Use your actual combo box name for the requery code. By the way, you will be doing yourself a favor if you restrict Access names (tables, forms, fields, controls, etc.) to alphanumeric characters and the underscore. Surrounding the name in square brackets should eliminate the problems that can result from spaces, but why give yourself the extra hassle? Also, I think you will find the shortest possible descriptive name for a form, field, etc. is the most convenient in code. Finally, giving a control a different name than the field will eliminate some puzzling error messages that can appear if Access gets confused. No need to change anything in your current project. These are just ideas for the future. SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType field. Switch to datasheet view. If it is OK, switch to SQL view and note exactly what you see there. This combo box is unbound, as I see it. I can't quite make out the purpose of ReferralSourcesAll. I woud imagine Referral data something like this: tblReferralSourcesAll RefSourceID (autonumber primary key) ResourceType (Agency, etc.) Resource (Name of referral) Phone, Address, etc. RefSourceID ResourceType Resource 1 Agency CPS 2 General Dr. Welby 3 General Dr. Jekyll I have left out Phone, etc., as they don't matter for this, although you may need them. The second combo box (I will call it cboSource; note the name, for I will refer to it again soon) would have as its Row Source: SELECT tblReferralSourcesAll.Resource FROM tblReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![ResourceType]"All","", [Resource] = [Forms]![IntakeWorksheet2]![ResourceType]))=True)) ORDER BY tblReferralSourcesAll.Resource; This assumes that you are storing the value Agency, etc. from [Resource ID Table]. The way you could put that value into tblReferralSourcesAll is to make a form based on tblReferralSourcesAll, with a combo box bound to the ResourceType field (that is, ResourceType is its ControlSource, which means that values entered into the combo box are stored in the ResourceType field). That combo box, with a column count of 1 and a column width of, say, 1", would have as its Row Source the same Row Source code as described for your first combo box. Select the ResourceType, and it will be inserted into the field. Back to your first combo box. In its After Update event you would have Me.cboSource.Requery. To add this code, open the form's property sheet: Click the combo box to select it, click View Properties, and click the Event tab. Click After Update, click the three dots on the right, and insert Me.cboSource.Requery between Private Sub and End Sub. What this does is to requery the Row Source for cboSource. That is to say, it runs the Row Source code, with the selected value as the parameter. If you selected Agency, then [Forms]![IntakeWorksheet2]![ReferralType] contains the value "Agency". The Row Source looks for all Resource records that have "Agency" as the ResourceType. You will probably want to put Me.cboSource.Requery into the form's Current event, so that when you arrive at a record the Resource text box will have the correct list. I hope this gets you closer to where you need to be. What is the Row Source for the second combo box (the Referral one)? Again, any code? SELECT [Resource ID Table].[Resource ID], ReferralSourcesAll.[Referral Source Label], [Resource ID Table].[Resource Type] FROM [Resource ID Table] INNER JOIN ReferralSourcesAll ON [Resource ID Table].[Resource ID] = ReferralSourcesAll.[Resource ID] WHERE ((([Resource ID Table].[Resource Type])=[Forms]![Intake Worksheet2]![ReferralType])); Thanks!! Meredith "BruceM" wrote: What is the Row Source for the first combo box (the ReferralType one)? Does it have any After Update code or other code? I'm not sure where you are in your Access vocabulary, so at the risk of saying something you already know, click the combo box to select it, click View Properties, and Click the Event tab. If any of the Events has [Event Procedure], click the three dots to the right of the row to view the code. The code will go something like: Private Sub cboReferralType_After Update {Code here} End Sub What is the Row Source for the second combo box (the Referral one)? Again, any code? In describing the Row Source, remember that I can't see you database, so you should describe any tables and fields that appear if if is not obvious by context. For instance, I can assume that Referral is the name of the Referral, but other things may not be as clear. "MeredithS" wrote in message ... |
#25
|
|||
|
|||
tables not relating properly
Bruce, I don't know how your brain keeps going with this stuff like it does;
mine hurts You've been very patient and I really appreciate it. My nonprofit psychiatric hospital for children and adolescents appreciates it -- or would, if they realized how much you've personally contributed to their efficiency and financial wellbeing. Seriously, thank you for all your help with this. Surely I can forge ahead from here on my own? If not, I'll wait a week or so and re-post, much further along. Hopefully won't happen. Meredith "BruceM" wrote: No, the other way around with the After Update. First, we need to get some names straight. I am not looking at your database, and am having trouble keeping combo boxes and fields straight, so I will be referring to your combo boxes as cboReferralType (for selecting Agency, General, Specific, and (All) and cboReferral (for selecting the actual referral). The form on which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound to a table that presumably has other information for date, etc. This table must also have a field to store the Referral (the actual name). Actually, you should probably be storing the ReferralID rather than the name, since ReferralID is an unchanging number field (that is, the agency name may change, but its number won't, just as your SS # does not change). More names: I will leave the table name as ReferralSourcesAll. Here are its fields: ReferralID (autonumber primary key) ReferralType (Agency, etc.) Referral (CPS, etc.) Here is the code for cboReferralType: SELECT DISTINCT ReferralSourcesAll.[ReferralType] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[ReferralType]; After choosing a ReferralType in cboReferral (the first combo box), cboReferral is updated. When that happens the After Update event runs. Let's say you selected "Agency". cboReferral says to the combo box ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into your Row Source code, and run the code again." Back to the cboReferralSource, with changes based on the naming things I mentioned. SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True)) ORDER BY ReferralSourcesAll.Referral; Breaking it down: SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll As is probably clear, this is the instruction to select the Referral field from ReferralSourcesAll WHERE What follows are restrictions on the number of records. IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All" This refers to cboReferralType. That is where "All" may appear in some cases, or else the ReferralType. This is saying "If the selection in cboReferralType is "All"...(we'll get to what needs to happen if this is true or if it is false). NOTE: I changed to =. I got it backward the first time. Back to the full IIf statement: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) If cboReferralType = "All" then (first comma) do nothing, otherwise (second comma) Referral (the table field in ReferralSourcesAll) = the selection in cboReferral. "Agency" is the selection in cboReferral, so the WHERE part of the Row Source amounts to: WHERE ReferralType = Agency The full Row Source for the combo box cboReferralSource, in vernacular: SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = Agency ORDER BY (that is, sort by) the Referral field If the selection in cboReferralType is (All), look at the IIf statement again: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) cboReferralType equals (All), so the first condition ("") applies. Since "" is an empty string, there are no restrictions on the records (no WHERE condition): SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = nothing ORDER BY (that is, sort by) the Referral field The extra parentheses in the IIf statement, and the = True part, are things that the code needs. To be honest, I built most of the query with the query design grid, then looked at the SQL afterwards. So the After Update event (Me.cboReferral.Requery) of cboReferralType is telling cboReferral to limit its list of records to just the records with Agency (in my first example) in the ReferralType field. Forget about the code in the Current event. It's probably not needed, since for existing records the Referral has already been selected. "MeredithS" wrote in message ... Hi, Bruce -- I read your comments and I don't think I understand which combo box the After Update Event procedure is associated with. My understanding is that the procedure will requery the selected value of the 1st combo box; so, to me, that implies that the After Update would be associated with the 2nd combo box, but refer to the 1st. Is that correct? I changed the name of the 2nd box to ReferralSource; 1st name is Referral. Therefore, the code would be (associated w/the 2nd box): Me.cboReferral.Requery Is that right? You also said to put this code into the form's Current Event, but the referral source info is just part of a much larger form so would that still be true?? Many thanks, Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce. I reorganized my table(s) so that all referral info is in one: Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can't locate a named object) cboReferralSource is the second combo box (the one from which you select the specific referral entity). Below you have referred to it as Referral Source. Use your actual combo box name for the requery code. By the way, you will be doing yourself a favor if you restrict Access names (tables, forms, fields, controls, etc.) to alphanumeric characters and the underscore. Surrounding the name in square brackets should eliminate the problems that can result from spaces, but why give yourself the extra hassle? Also, I think you will find the shortest possible descriptive name for a form, field, etc. is the most convenient in code. Finally, giving a control a different name than the field will eliminate some puzzling error messages that can appear if Access gets confused. No need to change anything in your current project. These are just ideas for the future. SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType |
#26
|
|||
|
|||
tables not relating properly
I learn too while I'm thinking about things like this. I can't tell you how
often I end up using something I discovered while studying somebody else's challenges. I think you would be doing yourself a favor if you use prefixes such as cbo, txt, etc. for controls such as combo boxes, text boxes, and so forth. It will help you identify things when you look at the project again in the future, and it will help in these postings. Best of luck with your project. "MeredithS" wrote in message ... Bruce, I don't know how your brain keeps going with this stuff like it does; mine hurts You've been very patient and I really appreciate it. My nonprofit psychiatric hospital for children and adolescents appreciates it -- or would, if they realized how much you've personally contributed to their efficiency and financial wellbeing. Seriously, thank you for all your help with this. Surely I can forge ahead from here on my own? If not, I'll wait a week or so and re-post, much further along. Hopefully won't happen. Meredith "BruceM" wrote: No, the other way around with the After Update. First, we need to get some names straight. I am not looking at your database, and am having trouble keeping combo boxes and fields straight, so I will be referring to your combo boxes as cboReferralType (for selecting Agency, General, Specific, and (All) and cboReferral (for selecting the actual referral). The form on which all of this happens is IntakeWorksheet2. IntakeWorksheet2 is bound to a table that presumably has other information for date, etc. This table must also have a field to store the Referral (the actual name). Actually, you should probably be storing the ReferralID rather than the name, since ReferralID is an unchanging number field (that is, the agency name may change, but its number won't, just as your SS # does not change). More names: I will leave the table name as ReferralSourcesAll. Here are its fields: ReferralID (autonumber primary key) ReferralType (Agency, etc.) Referral (CPS, etc.) Here is the code for cboReferralType: SELECT DISTINCT ReferralSourcesAll.[ReferralType] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[ReferralType]; After choosing a ReferralType in cboReferral (the first combo box), cboReferral is updated. When that happens the After Update event runs. Let's say you selected "Agency". cboReferral says to the combo box ReferralSource "OK, 'Agency' is my selected value. Plug 'Agency' into your Row Source code, and run the code again." Back to the cboReferralSource, with changes based on the naming things I mentioned. SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll WHERE (((IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]))=True)) ORDER BY ReferralSourcesAll.Referral; Breaking it down: SELECT ReferralSourcesAll.Referral FROM ReferralSourcesAll As is probably clear, this is the instruction to select the Referral field from ReferralSourcesAll WHERE What follows are restrictions on the number of records. IIf([Forms]![IntakeWorksheet2]![cboReferralType]"All" This refers to cboReferralType. That is where "All" may appear in some cases, or else the ReferralType. This is saying "If the selection in cboReferralType is "All"...(we'll get to what needs to happen if this is true or if it is false). NOTE: I changed to =. I got it backward the first time. Back to the full IIf statement: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) If cboReferralType = "All" then (first comma) do nothing, otherwise (second comma) Referral (the table field in ReferralSourcesAll) = the selection in cboReferral. "Agency" is the selection in cboReferral, so the WHERE part of the Row Source amounts to: WHERE ReferralType = Agency The full Row Source for the combo box cboReferralSource, in vernacular: SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = Agency ORDER BY (that is, sort by) the Referral field If the selection in cboReferralType is (All), look at the IIf statement again: IIf([Forms]![IntakeWorksheet2]![cboReferralType] = "All","", [Referral] = [Forms]![IntakeWorksheet2]![cboReferralType]) cboReferralType equals (All), so the first condition ("") applies. Since "" is an empty string, there are no restrictions on the records (no WHERE condition): SELECT the Referral field (all records) FROM tblReferralSourcesAll WHERE Referral = nothing ORDER BY (that is, sort by) the Referral field The extra parentheses in the IIf statement, and the = True part, are things that the code needs. To be honest, I built most of the query with the query design grid, then looked at the SQL afterwards. So the After Update event (Me.cboReferral.Requery) of cboReferralType is telling cboReferral to limit its list of records to just the records with Agency (in my first example) in the ReferralType field. Forget about the code in the Current event. It's probably not needed, since for existing records the Referral has already been selected. "MeredithS" wrote in message ... Hi, Bruce -- I read your comments and I don't think I understand which combo box the After Update Event procedure is associated with. My understanding is that the procedure will requery the selected value of the 1st combo box; so, to me, that implies that the After Update would be associated with the 2nd combo box, but refer to the 1st. Is that correct? I changed the name of the 2nd box to ReferralSource; 1st name is Referral. Therefore, the code would be (associated w/the 2nd box): Me.cboReferral.Requery Is that right? You also said to put this code into the form's Current Event, but the referral source info is just part of a much larger form so would that still be true?? Many thanks, Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce. I reorganized my table(s) so that all referral info is in one: Resource ID, Resource Type, Referral Source Label. Then I re-inserted most of your original code into 2 combo boxes, including the new On Event procedure as follows: I think the 1st box works well -- all the categories show up, including "All", but the 2nd box is still asking for a value as a parameter query... and the event procedure isn't working. See following: SELECT DISTINCT ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll UNION SELECT "(All)" as Bogus FROM ReferralSourcesAll ORDER BY ReferralSourcesAll.[Resource Type]; Combo Box #1 named Referral Private Sub Referral_AfterUpdate() Me.cboReferral Source.Requery End Sub (when I try to run this I get an error message to the effect that it can't locate a named object) cboReferralSource is the second combo box (the one from which you select the specific referral entity). Below you have referred to it as Referral Source. Use your actual combo box name for the requery code. By the way, you will be doing yourself a favor if you restrict Access names (tables, forms, fields, controls, etc.) to alphanumeric characters and the underscore. Surrounding the name in square brackets should eliminate the problems that can result from spaces, but why give yourself the extra hassle? Also, I think you will find the shortest possible descriptive name for a form, field, etc. is the most convenient in code. Finally, giving a control a different name than the field will eliminate some puzzling error messages that can appear if Access gets confused. No need to change anything in your current project. These are just ideas for the future. SELECT ReferralSourcesAll.[Referral Source Label], ReferralSourcesAll.[Resource Type] FROM ReferralSourcesAll WHERE (((IIf([Forms]![frmIntake Worksheet2]![cboReferral Type]"All","",[Referral Source Label]=[Forms]![frmIntake Worksheet2]![cboReferral Type]))=True)) ORDER BY ReferralSourcesAll.[Referral Source Label]; Combo Box #2 named Referral Source Thanks!! Meredith "BruceM" wrote: Good idea using the one table. A lookup table is fine for holding a list of values (such as referral type, or on a larger scale, cities or states) from which the user chooses. The chosen value is stored in the main table (or it may be linked, but with a single field there is probably little to be gained by doing that). In your case, a list of referral types can be held in a ReferralType table, but that value ends up being stored in individual records in the ReferralSources table, just as city or state typically is stored with the rest of a person's contact information. "MeredithS" wrote in message ... Thanks, Bruce. Let me see what I've got and I'll get back to you, probably tomorrow. The first combo box does work -- the list shows up and I can select an item from it. I think the easiest way to fix what's wrong, for starters, is to do what I should have done in the beginning and combine all the fields into one table. Not sure why I had 2 tables anyway. I'll work from that angle... Meredith "BruceM" wrote: "MeredithS" wrote in message ... Hi, Bruce -- Here's what I have. I think, at present, the only thing not working right is the All feature -- I've probably scavenged/guessed enough to screw up the original code which might have worked exactly as it was. The difference seemed to be that I had 2 tables I was drawing from and the Referral one didn't have the Referral Type as a text name, which was what I wanted to show up in the 1st combo box. So, I was trying to pull from both and I think that's what got screwed up. If this doesn't work, as I'm doing it, I can simply add a field to Table #2 and get everything in one table -- probably what I should have done in the 1st place? 2 tables: Resource ID Table (Resource ID, Resource Type) and ReferralSourcesAll (Resource ID, Referral Source Label) What is the Row Source for the first combo box (the ReferralType one)? SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; This should work, assuming the fields and table names are correct. To test, create a new query in design view. Don't select any tables, and click View SQL. Enter the code: SELECT DISTINCT [Resource Type] FROM [Resource ID Table] UNION SELECT "(All)" as Bogus FROM [Resource ID Table] ORDER BY [Resource Type]; The line breaks are just to make it easier to read and follow. Switch to datasheet view. Do you see the list? If not, create a query in design view. Add Resource ID Table, click OK, and add just the ResourceType |
Thread Tools | |
Display Modes | |
|
|