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 |
#1
|
|||
|
|||
Combo box wizard not working as expected
I'm creating a database that needs to track many many items related to
one employee. To do this, I have had to split up the data into multiple tables, each with an ID field, with each ID field having a one-to-one relationship with the ID field in the "personal" table. On each form (which deals with one table only), I want to have a combo box assist in changing records quickly, so when you're done with one person, save the info, and then you can use the combo box to quickly go to the next record you need to deal with. I therefore need the combo box to change records based on info in the "personal" table. The problem is, the combo box wizard is not giving me the "Find a Record on My Form Based on the Value I selected in My Combo Box" option. At first, I did get the option, but could only choose fields from the form I was working with. So I tried adding fields from the "personal" table (intending to hide them if they had to stay on the form), but now I don't get the wizard option I need. Any ideas? I'm hoping to make navigation to different records in the form simpler than using the bar at the bottom that access provides. I'm not "stuck" on using a combo box, but it was the best option I could find in my Access book. Many Thanks, Amy |
#2
|
|||
|
|||
Combo box wizard not working as expected
First, one-to-one relationships are not common and are sometimes used
incorrectly. If you want to post an example of your tables/fields/relationships someone may be able to offer advice as to your structure. As far as your combo box, it should be unbound (nothing in the control source). The row source should be a query that selects, for example, the EmployeeID an a concantenated First and Last name form the personnel table i.e.; Select tblPersonnel.EmployeeID, [FirstName] & " " & [LastName] As [Employee Name] From tblPersonnel Order By tblPersonnel.LastName; The combo box should also have the following properties; Bound Column: 1 Column Count: 2 Column Widths: 0",1" (or whatever works best for you) Then, in the After Update event of the combo box you would put code like the following (this is air code, so it may need some tweaking) With Me.RecordsetClone .FindFirst "[EmployeeID]=" & Me.YourComboBox If Not .NoMatch Then Me.Bookmark = .Bookmark Else Msgbox "No records found for this employee" End If End With -- _________ Sean Bailey "LadyIlsebet" wrote: I'm creating a database that needs to track many many items related to one employee. To do this, I have had to split up the data into multiple tables, each with an ID field, with each ID field having a one-to-one relationship with the ID field in the "personal" table. On each form (which deals with one table only), I want to have a combo box assist in changing records quickly, so when you're done with one person, save the info, and then you can use the combo box to quickly go to the next record you need to deal with. I therefore need the combo box to change records based on info in the "personal" table. The problem is, the combo box wizard is not giving me the "Find a Record on My Form Based on the Value I selected in My Combo Box" option. At first, I did get the option, but could only choose fields from the form I was working with. So I tried adding fields from the "personal" table (intending to hide them if they had to stay on the form), but now I don't get the wizard option I need. Any ideas? I'm hoping to make navigation to different records in the form simpler than using the bar at the bottom that access provides. I'm not "stuck" on using a combo box, but it was the best option I could find in my Access book. Many Thanks, Amy |
#3
|
|||
|
|||
Combo box wizard not working as expected
To add some to Sean's answer, the reason you are not seeing the third option
in the wizard is that your form's recordset is a SQL statement. It needs to be either a table, or a saved query, in order for the combo box wizard to present you with the "Find a Record on My Form Based on the Value I selected in My Combo Box" option. Combo Box to Find a Record http://www.access.qbuilt.com/html/find_a_record.html I also question your design with many 1:1 relationships. You are working much harder than you should be. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Beetle" wrote: First, one-to-one relationships are not common and are sometimes used incorrectly. If you want to post an example of your tables/fields/relationships someone may be able to offer advice as to your structure. As far as your combo box, it should be unbound (nothing in the control source). The row source should be a query that selects, for example, the EmployeeID an a concantenated First and Last name form the personnel table i.e.; Select tblPersonnel.EmployeeID, [FirstName] & " " & [LastName] As [Employee Name] From tblPersonnel Order By tblPersonnel.LastName; The combo box should also have the following properties; Bound Column: 1 Column Count: 2 Column Widths: 0",1" (or whatever works best for you) Then, in the After Update event of the combo box you would put code like the following (this is air code, so it may need some tweaking) With Me.RecordsetClone .FindFirst "[EmployeeID]=" & Me.YourComboBox If Not .NoMatch Then Me.Bookmark = .Bookmark Else Msgbox "No records found for this employee" End If End With -- _________ Sean Bailey |
#4
|
|||
|
|||
Combo box wizard not working as expected
On Sun, 11 May 2008 00:07:00 -0700, Tom Wickerath AOS168b AT comcast DOT net
wrote: To add some to Sean's answer, the reason you are not seeing the third option in the wizard is that your form's recordset is a SQL statement. It needs to be either a table, or a saved query, in order for the combo box wizard to present you with the "Find a Record on My Form Based on the Value I selected in My Combo Box" option. ahhhh.... Thanks, Tom. I'd seen the wizard Apparate and Disapparate but didn't realize that was the reason. If I quit learning from this newsgroup I'll quit posting... but I really don't expect that to happen for years! -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Combo box wizard not working as expected
Hi John,
I feel the same as you...I'm constantly learning new stuff by reading threads in the various Access related newsgroups! Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "John W. Vinson" wrote: ahhhh.... Thanks, Tom. I'd seen the wizard Apparate and Disapparate but didn't realize that was the reason. If I quit learning from this newsgroup I'll quit posting... but I really don't expect that to happen for years! -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Combo box wizard not working as expected
On May 11, 3:07 am, Tom Wickerath AOS168b AT comcast DOT net wrote:
snip I also question your design with many 1:1 relationships. You are working much harder than you should be. /snip I tried putting most of everything into one table already, and ran into the number of fields limit. Using the database documenter gives me about 12 pages, which I'll email someone, but I don't think anyone wants to see here. I unfortunately am tasked on other things today keeping me from having the time to edit the 12 pages down to something usable for Usenet. For many things in this database, I have to track the date the requirement was passed, as well as what type of documentation was provided. This is for a local paramedic service, so there are a TON of requirements to track. I also have separate tables to track hire dates and position types (we have to keep a history of every position held by every employee), and one of their ID card numbers (provincially granted, and replaced with a different ID card and number if lost, for which, of course, we need a complete history for every employee). Since I'm using the 1:1 relationships to get around the 255 field limit in a table, is there a better way around that? I will try coding the combo box per Sean Bailey's suggestion, although it will have to wait until later in the week - thank you very much for that! Amy |
#7
|
|||
|
|||
Combo box wizard not working as expected
On Mon, 12 May 2008 08:40:32 -0700 (PDT), LadyIlsebet
wrote: Since I'm using the 1:1 relationships to get around the 255 field limit in a table, is there a better way around that? YES. "Fields are expensive. Records are cheap". If each record has many dates (of events of some kind??) you need two tables in a one to many relationship, with each event date *in a record by itself*. If one person has 54 reporting dates, you don't need 54 fields - you need a different table with 54 *RECORDS*, one for each event. My guess is that you will have a typical many to many relationship. You don't say what the "requirements" apply to, so I'll speculate that it's a position; if so you would need three tables: Positions PositionID information about the position as an entity Requirements RequirementID description of the requirement MetRequirements PositionID link to Positions RequirementID link to Requirements DateAttained other info about THIS position and THIS requirement -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Combo box wizard not working as expected
Ok, I *think* I get this. Can you tell it's been a decade since my
rather crappy single database course in University? Here's what I'm thinking I'd do - the training requirements apply to each person. Personal ID Name, Sex, addresses, phone numbers, etc. Training TrainingID Training Description CopyOnFileTypes CopyType CopyDescription CompletedTraining ID linked to Personal TrainingID linked to Training Date could be attained, or date this will expire - i know date is a reserved word in access CopyOnFile linked to CopyOnFileTypes table Scans IDlinked to personal individual fields for all the scanned files, using hyperlink type OASISHistory ID linked to personal OASISNumber OASISDate date that particular OASIS number issued EmpHistory ID linked to personal PositionNumber linked to Positions DateHired FluShot ID linked to personal FluType FluDate Positions PositionNumber PositionDescription Is this more along what you were describing? I'm also worried that this will make coding my forms more difficult, as well as make ad-hoc reports trickier for my end user, but I suppose I should just get started on this version of the design Thanks for the help! Amy On May 12, 12:52 pm, John W. Vinson wrote: snip "Fields are expensive. Records are cheap". If each record has many dates (of events of some kind??) you need two tables in a one to many relationship, with each event date *in a record by itself*. If one person has 54 reporting dates, you don't need 54 fields - you need a different table with 54 *RECORDS*, one for each event. /snip |
#9
|
|||
|
|||
Combo box wizard not working as expected
On Mon, 12 May 2008 10:35:54 -0700 (PDT), LadyIlsebet
wrote: Ok, I *think* I get this. Can you tell it's been a decade since my rather crappy single database course in University? Here's what I'm thinking I'd do - the training requirements apply to each person. g You're doing very well then! Just a few comments inline: Personal ID Name, Sex, addresses, phone numbers, etc. Training TrainingID Training Description CopyOnFileTypes CopyType CopyDescription I'm not sure what a "Copy" or "CopyOnFile" might be - a document? What's the primary key of this table? CompletedTraining ID linked to Personal TrainingID linked to Training Date could be attained, or date this will expire - i know date is a reserved word in access CopyOnFile linked to CopyOnFileTypes table Linked to what field? Scans IDlinked to personal individual fields for all the scanned files, using hyperlink type STOP. You're doing it again! Each scanned file should be *an individual record* in a table; the Scans table should have only one file field. If the same file might pertain to multiple students, you need a many to many relationship, modeled by a table of Files (or Scans, or whatever you're calling them), and a table of StudentFiles, linked many to one to both Personal and to Scans. OASISHistory ID linked to personal OASISNumber OASISDate date that particular OASIS number issued EmpHistory ID linked to personal PositionNumber linked to Positions DateHired Maybe DateTerminated, blank if they're still in the position, and some other fields about the person's employment (reason terminated, employer comments, etc.) FluShot ID linked to personal FluType FluDate If there's a need for other medical information, generalize this to MedicalTreatments with flu shots as just one type. Positions PositionNumber PositionDescription Is this more along what you were describing? I'm also worried that this will make coding my forms more difficult, as well as make ad-hoc reports trickier for my end user, but I suppose I should just get started on this version of the design It'll make things EASIER not harder, eventually. Your forms should have Forms for the "one" side, with multiple Subforms for the "many" side tables; and yes, your ad-hoc reports will require getting up close and cosy with the Query grid, and probably even the SQL window (the query grid is just a tool to build SQL strings). Thanks for the help! Glad to be of assistance. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Combo box wizard not working as expected
Hi Amy,
Since I'm using the 1:1 relationships to get around the 255 field limit in a table, is there a better way around that? I see that John Vinson has been helping you some today, so I'll just add a few comments here. I suggest reading the first two database design documents, written by Michael Hernandez, available he http://www.accessmvp.com/JConrad/acc...abaseDesign101 One of them is a very easy read; it is only four pages in length. Personal table Name Name is a reserved word. You should avoid using any reserved words in Access for things that you assign a name to, including fields, tables, queries, forms, reports, macros, modules, procedures (ie. subroutines and functions), constants and variables within procedures, and controls on forms and reports. Access MVP Allen Browne has a nice collection of reserved words he Problem names and reserved words in Access http://www.allenbrowne.com/Ap****ueBadWord.html Here, you should see a link for downloading his "Database Issue Checker Utility", which can quickly and easily scan your field and table names for the use of reserved words. This utility does not currently include scanning the names of controls on forms and reports for reserved words. CompletedTraining Date could be attained, or date this will expire - i know date is a reserved word in access Okay, so you're not actually going to use the field name "Date", right? For the various ID fields, I suggest a more descriptive name such as PersonelID. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "LadyIlsebet" wrote: On May 11, 3:07 am, Tom Wickerath AOS168b AT comcast DOT net wrote: snip I also question your design with many 1:1 relationships. You are working much harder than you should be. /snip I tried putting most of everything into one table already, and ran into the number of fields limit. Using the database documenter gives me about 12 pages, which I'll email someone, but I don't think anyone wants to see here. I unfortunately am tasked on other things today keeping me from having the time to edit the 12 pages down to something usable for Usenet. For many things in this database, I have to track the date the requirement was passed, as well as what type of documentation was provided. This is for a local paramedic service, so there are a TON of requirements to track. I also have separate tables to track hire dates and position types (we have to keep a history of every position held by every employee), and one of their ID card numbers (provincially granted, and replaced with a different ID card and number if lost, for which, of course, we need a complete history for every employee). Since I'm using the 1:1 relationships to get around the 255 field limit in a table, is there a better way around that? I will try coding the combo box per Sean Bailey's suggestion, although it will have to wait until later in the week - thank you very much for that! Amy |
Thread Tools | |
Display Modes | |
|
|