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 |
#81
|
|||
|
|||
Still Struggling...
Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing is just telling Access to create a clone of whatever the recordset is. You don't need to tell it the name of the table or query. -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No The last field is not shown because it is only used for sorting. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? As far as printing the record, I am just trying to circumvent some anticipated issues; one in particular is staying in my head. As I work through my issues with the db, I keep thinking about how others will use it. I can hear some of the conversations already. I did think about making a report after I created that button. The print button is convenient but I think I would rather it go to print preview. I don't know, I'm still just trying to get through all of my other issues. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. -- _________ Sean Bailey "Aria" wrote: Hi Bruce, Thanks for responding. It seemed so easy in the example. Why does everything have to turn into a problem? I'm sorry, I forgot to include column widths. I thought about that much later. Column widths =0";0.6459";0.625" List widths=1.2708" In this case it should be unbound. You aren't trying to store the value, but rather to go to the selected record. I thought it should be unbound but I've been wrong so often that now I automatically think, "You're wrong. It must be the other way." Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? Again, if you don't want to get into it, that's fine. As far as the SQL statement, I opened it again in Design view and this is what it shows: Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? Just to cover all, I checked both tables. I did not see a drop-down arrow. I assume you mean in Design View. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. I don't know why. When you open the form, it list the title. As far as printing the record, I am just trying to circumvent some anticipated issues; one in particular is staying in my head. As I work through my issues with the db, I keep thinking about how others will use it. I can hear some of the conversations already. I did think about making a report after I created that button. The print button is convenient but I think I would rather it go to print preview. I don't know, I'm still just trying to get through all of my other issues. -- Aria W. "BruceM" wrote: I can't figure why it isn't sorting, although I have one idea I will address later. Try this: SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM tblEmployees ORDER BY [LastName], [FirstName]; This doesn't really address your questions in some ways, but it is an example of how an alias (LastFirst) can be used. You didn't mention column widths, but for a three column colmbo box in which the first column is hidden then would be something like 0";1";1". In my example the column count would be 2 and the column widths 0";1". From what I can tell the rightmost visible column will fill the rest of the space in the combo box. If there is one visible column it will be the width of the combo box unless its width is greater than the combo box width. If the combo box is 2.5" wide and the visible columns are 1";1" the rightmost column will actually be 1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the rightmost column will hang over the edge of the combo box when you click the down arrow. |
#82
|
|||
|
|||
Still Struggling...
I accidentally hit post before I was done with my last response. Here is the
complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No The last field is not shown because it is only used for sorting. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? As far as printing the record, I am just trying to circumvent some anticipated issues; one in particular is staying in my head. As I work through my issues with the db, I keep thinking about how others will use it. I can hear some of the conversations already. I did think about making a report after I created that button. The print button is convenient but I think I would rather it go to print preview. I don't know, I'm still just trying to get through all of my other issues. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. -- _________ Sean Bailey "Aria" wrote: Hi Bruce, Thanks for responding. It seemed so easy in the example. Why does everything have to turn into a problem? I'm sorry, I forgot to include column widths. I thought about that much later. Column widths =0";0.6459";0.625" List widths=1.2708" In this case it should be unbound. You aren't trying to store the value, but rather to go to the selected record. I thought it should be unbound but I've been wrong so often that now I automatically think, "You're wrong. It must be the other way." Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? Again, if you don't want to get into it, that's fine. As far as the SQL statement, I opened it again in Design view and this is what it shows: Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? Just to cover all, I checked both tables. I did not see a drop-down arrow. I assume you mean in Design View. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. I don't know why. When you open the form, it list the title. As far as printing the record, I am just trying to circumvent some anticipated issues; one in particular is staying in my head. As I work through my issues with the db, I keep thinking about how others will use it. I can hear some of the conversations already. I did think about making a report after I created that button. The print button is convenient but I think I would rather it go to print preview. I don't know, I'm still just trying to get through all of my other issues. -- Aria W. "BruceM" wrote: I can't figure why it isn't sorting, although I have one idea I will address later. Try this: SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM tblEmployees ORDER BY [LastName], [FirstName]; This doesn't really address your questions in some ways, but it is an example of how an alias (LastFirst) can be used. You didn't mention column widths, but for a three column colmbo box in which the first column is hidden then would be something like 0";1";1". In my example the column count would be 2 and the column widths 0";1". From what I can tell the rightmost visible column will fill the rest of the space in the combo box. If there is one visible column it will be the width of the combo box unless its width is greater than the combo box width. If the combo box is 2.5" wide and the visible columns are 1";1" the rightmost column will actually be 1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the rightmost column will hang over the edge of the combo box when you click the down arrow. |
#83
|
|||
|
|||
Still Struggling...
Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No The last field is not shown because it is only used for sorting. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? As far as printing the record, I am just trying to circumvent some anticipated issues; one in particular is staying in my head. As I work through my issues with the db, I keep thinking about how others will use it. I can hear some of the conversations already. I did think about making a report after I created that button. The print button is convenient but I think I would rather it go to print preview. I don't know, I'm still just trying to get through all of my other issues. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. -- _________ Sean Bailey "Aria" wrote: Hi Bruce, Thanks for responding. It seemed so easy in the example. Why does everything have to turn into a problem? I'm sorry, I forgot to include column widths. I thought about that much later. Column widths =0";0.6459";0.625" List widths=1.2708" In this case it should be unbound. You aren't trying to store the value, but rather to go to the selected record. I thought it should be unbound but I've been wrong so often that now I automatically think, "You're wrong. It must be the other way." Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? Again, if you don't want to get into it, that's fine. As far as the SQL statement, I opened it again in Design view and this is what it shows: Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending |
#84
|
|||
|
|||
Still Struggling...
Guess what? It works...it works! I'm trying real hard to hold it together,
but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees |
#85
|
|||
|
|||
Still Struggling...
Looks good for the most part with just a few comments;
tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Other than the fact that TitleDescription doesn't belong here, my other question is about ClassDescription. It *is* correct to have a field in this table as a FK to tblClassifications, but it should be ClassID. The odd thing is that you say it is a long integer number, so maybe you have it correctly related to ClassID in tblClassifications. It may be correct, it's just hard to tell because you named it ClassDescription. It should be a long integer (which you say it is) and it should be related to ClassID in tblClassifications. Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? No, that is correct. You need that subform to enter titles. tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) In this table EmpID should be a FK to tblEmployees and the PK for this table should be a combination of *both* the EmpID and TitleID fields. Again, maybe you already have it that way and you just didn't specify in your post. Other than that, it looks good as far as I can tell. I won't be around this week so hopefully all will go well Good Luck -- _________ Sean Bailey "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees |
#86
|
|||
|
|||
Still Struggling...
I should have said 'Still in the VBA editor, click Debug Compile." This
is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally recognized (by application designers and such) as representing a recordset of some type. 3. Me.Bookmark--I've seen that before and have been dying to ask. What does that do? I think of a bookmark as a placeholder, but if the value isn't stored...? It is a placeholder of sorts, but it has nothing to do with the actual data that is stored in your table. Everytime you open a bound form, a unique bookmark is created for each record in that form's recordset. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:Last Name Table:tblEmployees Sort: Ascending Show: Yes Field: FirstName Table:tblEmployees Sort: Ascending Show: Yes Just want to make sure we're on the same page...I'm working in frmEmployees with subform SiteEmps, so the table you want me to look in is tblEmployees? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees |
#87
|
|||
|
|||
Still Struggling...
You know I had never been to this section of my database before. Originally,
I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 |
#88
|
|||
|
|||
Still Struggling...
I think that Macros may be considered to be something other than code, but
I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 |
#89
|
|||
|
|||
Still Struggling...
Other than the fact that TitleDescription doesn't belong here, my other
question is about ClassDescription. It *is* correct to have a field in this table as a FK to tblClassifications, but it should be ClassID. The odd thing is that you say it is a long integer number, so maybe you have it correctly related to ClassID in tblClassifications. It may be correct, it's just hard to tell because you named it ClassDescription. It should be a long integer (which you say it is) and it should be related to ClassID in tblClassifications. No, this isn't correct. Once again, you're correct. I don't know if I meant to put ClassID and somehow it got switched or what. I don't remember. I'll change it. I really appreciate your questioning what I post. It is *not* helpful if I post the wrong information. How can I then get the help I need. I'm sorry. Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? No, that is correct. You need that subform to enter titles. Ok, do I need to change the info in the combo box to accomodate the change? Because I had linked via ClassID through EmpsClass to tblClassifications... tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) In this table EmpID should be a FK to tblEmployees and the PK for this table should be a combination of *both* the EmpID and TitleID fields. Again, maybe you already have it that way and you just didn't specify in your post. Ok, let's stop here. I owe you the biggest apology. I am not posting the correct information. This is a two field composite key. I think my porblem is that I have not been sleeping well and had spent 16 hours looking for answers and trying to resolve the staff look-up problem. But still; in order not to send us all on a wild goose chase I need to get it right. Please forgive me. I'll try to do much better. I'm going to stop working on forms and such and go back to the second half of the db; locks, keys, locations and phones. I hope your week off is peaceful. -- Aria W. "Beetle" wrote: Looks good for the most part with just a few comments; tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Other than the fact that TitleDescription doesn't belong here, my other question is about ClassDescription. It *is* correct to have a field in this table as a FK to tblClassifications, but it should be ClassID. The odd thing is that you say it is a long integer number, so maybe you have it correctly related to ClassID in tblClassifications. It may be correct, it's just hard to tell because you named it ClassDescription. It should be a long integer (which you say it is) and it should be related to ClassID in tblClassifications. Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? No, that is correct. You need that subform to enter titles. tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) In this table EmpID should be a FK to tblEmployees and the PK for this table should be a combination of *both* the EmpID and TitleID fields. Again, maybe you already have it that way and you just didn't specify in your post. Other than that, it looks good as far as I can tell. I won't be around this week so hopefully all will go well Good Luck -- _________ Sean Bailey "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. What Bruce suggested should work fine. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? tblEmployees tblSiteEmps tblTitles tblClassifications tblTitlesEmps -- _________ Sean Bailey "Aria" wrote: Hi Beetle, Where is my recall button when I need it?! I hadn't seen your post before I sent off my last one. Boy, do I need to make some changes to what I did. Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in his code but I thought I was supposed to put the fields I was interested in seeing. I'll change that. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? You have to look in tblEmployees, because FirstName and LastName don't exist in any of your other tables. Your query looks fine, unless you want to show both names in the combo when it is not expanded (dopped down). Ok, I looked in tblEmployees. I didn't see anything. I do want both first and last name to show. I did as Bruce suggested with the SQL he gave me and it does show what I want. The bookmarks are only valid for the period of time that the form is open. If you close and re-open the form, those same records may have a different bookmark assigned. Interesting...thanks for the additional info; that was helpful. There is something a little odd though. I have only input 6 employee names using frmEmployees just so I can check to see if things are working. When I opened the table, 5 of the employees have a title description (Admin., teacher, etc.). The sixth and final entry shows a #...#14. When you opened which table? tblEmployees; I don't understand why that is there since I am not entering directly into the tables and I use a drop-down menu. Although you *can* print a form in Access, they are not designed to be printed and usually look like *&%! when you do. You should create a report and then use a command button on your form to print (or print preview) it. I did print the form and understand what you're saying. I didn't really care for the way it looked. The only reports I have right now are relationship diagrams. I don't have any saved macros either. How hard would it be for me to do this? Bruce gave me this: DoCmd.OpenReport "ReportName", acViewPreview in Macros under Action, I do see OpenReport. Do I select that and then under comments (?) list the above? -- Aria W. "Beetle" wrote: Several questions to address here, so comments are inline. Dim rs As Object Set rs = Me.RecordsetClone rs.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = rs.Bookmark Hmmm...programming code. You know where I am as far as skill level. I am certainly willing to try, if you don't mind the questions that are sure to come. I'll try not to inundate you with a thousand and one questions. Can I ask the following: 1. The above code goes into the AfterUpdate event as is? Yes, but you may need to correct the naming. For example, I believe you PK field is EmpID (not EmployeeID), and your combo box may have a different name. The difference is that in the first case rs is used as a sort of abbreviation for Me.RecordsetClone. Thereafter you use rs in place of Me.RecordsetClone. Dim rs as Object declares rs as an object variable. 2. Well, maybe I don't need to know, but I was wondering if Access already knows that rs is an abbreviation for Me.RecordsetClone because the = sign isn't used until the second statement. If it's more than I need to know, we can drop it. No, Access doesn't already know that rs is an abbreviation for RecordsetClone. In this line; Dim rs As Object you are declaring a variable named rs and telling Access what *type* of variable it is. In this case it is an Object type of variable. In this line; Set rs = Me.RecordsetClone you are telling Access what to assign to the rs variable you just declared. In this case you are telling it to assign a copy of the recordset (table) that your form is based on. You can declare anything you want, as long as you declare it properly so that Access knows what it is. In other words, you could (theoretically) write; Dim ClownShoes As Object Set ClownShoes = Me.RecordsetClone ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee Me.Bookmark = ClownShoes.Bookmark but that would be silly g When declaring a recordset variable, rs and rst are more or less universally |
#90
|
|||
|
|||
Still Struggling...
I found the compile button. It was under commands so I added it to the
toolbar. Thanks. I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. Not quite ready to list the table structures for this part yet...still stinging from the last time when they were all wrong. I just basically want to think on post. Ask a few questions and have you weigh in on where I'm going wrong. Are you Ok with that? I completely understand what you mean as far as having lost track. I have all my notes and posts and I still have to read up on what's going on and what transpired. Questions unrelated to 2nd half of db: 1. When I input the programmng code for the AfterUpdate event, I input cboStaff_Lookup. The line highlighted in yellow said, "Private Sub cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore after Lookup? 2.Ever since this started taking off in earnest, I have often thought that this db seems to be complicated; maybe that's just beginner's woe. I don't know because I don't have a point of reference. Both you and Beetle stated in your posts to each other in the beginning that it wasn't simple for a first app. My question is how did you know? We hadn't even really gotten into it past the original 6 tables. How does one determine complexity? Hoping this is not too much for you in one post... We know: 1. This is a staff database for a school setting. 2. We encompass 2 campuses. 3.Db emphasis is on keys because they have been problematic. 4. We have already suffered through a re-key of an entire campus, including the stadium, gyms and gates. 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.) 6. Master keys allow general access for a single campus. 7. Master keys are unique, employee specific and their allocation is severely restricted. 8. Storage, Stadium, Food Service and Gate Masters are location specific *and* follow rule #7. 9. Wing masters will only open all rooms for a specific section of the site. 10. Key assignments are based on job title, room assignment and extracurricular duties. Where we left off: 1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees (junction), tblKeys and tblKeysRequests. 2. There was a lot of discussion involving home, personal cell, district cell and room phone #s. The last decision was that home/personal cell is part of tblEmployees; district cell and room phone #s are part of tblphones. Suggestions still on the table: 1. Create tblLocations to replace tblRooms. 2. Create tblLocks to define the relationsip between keys and locks. There should be a 2 field PK involving Key ID and LocationID. 3. Create Master Key table to account for the special attributes of Master Keys. 4. Create additional look-up tables: tblCampuses, tblWings and tblLocationTypes. If your eyes haven't glazed over... You don't have to answer today; I know it's a lot. Questions/Comments: 1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key for KeyID in tblKeys. Your statement about a possible re-key and our history of such finally made me realize that this was a *lousy* idea from the beginning. I'm switching to autonumber. 2. Master keys--This is a sub-type of key, correct? You can have one key and many masters or is it many sub-types (Gate, Stadium, etc.) But you can also have one specific master that has many keys assigned. While the master is employee specific, it *is* essentially the same key that is assigned to every employee who has clearnace. It's just coded so we know in advance who we gave it to. This is a 1:M relationship? You can't have a M:M relationship with sub-types can you? Either way it will have its own PK. This is where I'm confusing myself and going around in circles. I'll stop here for now. Thanks! -- Aria W. "BruceM" wrote: I think that Macros may be considered to be something other than code, but I'm not sure. If you use the wizard the code is added automatically. You can view it, but you may not know it was added. In any case, unless you use macros, which are rather limited, you pretty much need VBA code for anything other than a very simple database. To find the Customize option I mentioned, open your database. In the database window, click the Forms tab. Click View Code, or click the Code icon on the toolbar. What you see is the VBA editor. Right click a blank spot on the toolbar or menu bar. You should see Customize, probably as the last item on the list. There are of course other ways to open the VBA editor, so choose another method if you prefer. The way you open it is not important (although I wouldn't try to customize while debugging). I will take a look at your questions, but you will need to sort of start over with your descriptions, as I have not been following very closely of late. When you do so, list only the essential fields. For instance, for tblEmployees: EmployeeID (PK - Number) LastName FirstName etc. may be enough. For tblSiteEmps, list the PK/FK field, the relationship type, and a few fields that will give the idea of how the table is used: tblSiteEmps (1:1 tblEmployees) EmpID (PK/FK) Home Phone-txt Address-txt etc. This is enough for our purposes. Give yourself a break from typing all of the details, unless for instance the fact there is a cell phone number is relevant to the problem at hand, and make it easier for me or another responder to read. Limit your description to a few typical fields, or to fields that are part of your code or that are involved in relationships. "Aria" wrote in message ... You know I had never been to this section of my database before. Originally, I had no intention of putting in any programming code. I tried doing as you suggested but when I go to Customize...Options...I don't see what you are describing. What I see in the Options tab is Personalized Toolbar and Menu but it's greyed. The only button you can push is Reset my Usage Data (?). Maybe I'm in the wrong place. I wanted to return to the 2nd half of the db. I still need to work on tables but I have questions (naturally!) Do you mind? -- Aria W. "BruceM" wrote: I should have said 'Still in the VBA editor, click Debug Compile." This is in the menu bar, not in the Tools Options dialog. I like to have that command readily available, so I added it to the toolbar. To do that, right click on the toolbar, click Customize, click the Options tab, click Debug on the left, and drag Compile from the right side to the toolbar. BTW, you could sort the row source for the Employee combo box by the concatenated (LastFirst) field. If you sort on LastName you should sort on FirstName next, in case two people have the same last name. You don't need to show the LastName and FirstName fields. "Aria" wrote in message ... Guess what? It works...it works! I'm trying real hard to hold it together, but I am so happy. sniff Thank you *so* much! OK, so let me tell you what happened. I'm going to combine parts of both your posts. Bruce M wrote: If you view the SQL in datasheet view you will see that the first column is EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup. This statement: rs.FindFirst "[EmpID] = " & Me.cboStaffLookup means "Find the first record in the RecordsetClone in which EmpID is the same as EmpID in the combo box." Beetle wrote: A combo box will only display the first visible column in its unexpanded state, so in that case you would need to concantenate the names in your query as Bruce suggested. His example query might look like this in design view; Field: EmpID Table:tblEmployees Show: Yes (The box is checked). Field:LastFirst:[LastName] & ", " & [FirstName] Table: Sort: Show: Yes Field: LastName Table:tblEmployees Sort: Ascending Show: No Aria writes: It was the combination of both your posts that allowed me to follow along closely. You explained what was happening and then said this is what it will look like. Bruce M wrote: Private Sub cboStaffLookup_AfterUpdate() End Sub The cursor should be blinking between those lines. Add the code. After you enter: Dim rs As Object press the Enter key to go to a new line (or press it twice to create some space and make the code easier to read). Add: Set rs = Me.RecordsetClone Press the Enter key again, and add the next lines of code, pressing the Enter key after each one. Scroll to the top of the code window and be sure the words Option Explicit are under Option Compare Database. Add them if they are not. If they are not, in the VBA editor click Tools Options. Click the Editor tab, and check the box Require Variable Declaration. Still in the editor, click Debug Compile. This will highlight any typos and other such errors in the code. Aria writes: Step by step instructions...how could I ask for anything better than that. The Require Variable Declaration box wasn't checked. I didn't find the Debug Compile box but it did have Auto Syntax Check box. When I ran it I got a Compile error (Error 461). I used Help for this part. When I was finished, I noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in yellow; highlighted in blue was cbo StaffLookup. I didn't notice the underscore with cboStaff_Lookup before. That did it. Bruce M wrote: I don't know what is happening with the sort order, but let's not get too many things cooking on a Friday afternoon. lol Well, on my end it may be a little late for that. One more thing...the sort order is working as it should. I can't believe my book said it could be done w/o code. I can't thank you both enough. Still trying to hold it together. Have a great weekend! -- Aria W. "Aria" wrote: Something's not right here. tblEmployees should not have a field for TitleDescription. The only place the TitleDescription field should exist is in tblTitles. Can you post your current structure for the following tables (hopefully I have the table names right)? Yes, you have the names right. I appreciate your thoroughness in making sure everything is OK. Every time you have reservations, there's usually something amiss. I just want to say, before I post the table structure, that some facts concerning our situation may have been forgotten since our original discussions. Please allow me to refresh our memories about employees, classifications and titles. 1. Our school employs both site staff (permanent) and substitutes (temporary). 2. Each employee can only have 1 classification (Admin., Certificated (teacher et. al), Classified and Substitutes). There are many employees who have the same classification. tblClassifications 1:M tblEmployees, correct? For our purposes Admin. are strictly Admin. 3. Each employee can have one or many titles. Each title can be assigned to many employees. tblTitles M:M tblTitlesEmps The structure is as follows: tblEmployees Inactive Yes/No EmpID PK Autonumber, long integer ClassDescription FK to tblClassifications (number, long integer) (This is what it *should* be. It's kind of messed up right now because of tblEmpsClass which should be deleted.) TitleDescription (gasp!Illumination... I see what you're saying. This shouldn't be here.) LN FN MI Let's go back to TitleDescription. We made that a subform within tblEmployees. Do we keep it (now that I finally have it where I want it) or do we need to do something else? tblSiteEmps 1:1 tblEmployees EmpID (PK/FK) Home Phone-txt Cell Phone-txt Address-txt City-txt State-txt ZipCode-txt EmerContactLN -txt EmerContactFN- txt EmerContactPhone - txt PlaceofEmployment - txt FamilyDr - txt MedInsurance - txt HospitalPref - txt HealthIssues - txt Medications- txt Allergies - txt DateCreated Date/Time DateModified Date/Time tblTitles TitleID PK TitleDescription tblTitlesEmps EmpID PK TitleID number, l.i.(FK to tblTitles) tblClassifications ClassID PK ClassDescriptions - txt (FK to tblClassifications) Hopefully, the rest of it is OK. Good looking out...thank you so much! -- Aria W. "Beetle" wrote: I accidentally hit post before I was done with my last response. Here is the complete response. Just ignore my last post... So this line should be Set rs = Me.tblEmployeesClone? No. It should be Set rs = Me.RecordsetClone What you're doing here is telling Access to create a copy of whatever the recordset is. You don't need to tell it the table or query name. Ok, I looked in tblEmployees. I didn't see anything. I do want both first |
Thread Tools | |
Display Modes | |
|
|