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
|
|||
|
|||
What is the best way to set-up my Database?
I want to revisit a few things posted in another thread I started a while
back. Any help from anyone is appreciated. I have a main form "Suspects" and a subform "sfrmBolo". The "Suspects" form is bound to the Suspects table and the subform is bound to the Bolo table. The Suspect table contains details about a person involved in an incident, while the Bolo subform contains incident details. The Bolo subform is on a tab on the main form. The top of the main form contains person details. I also have tabs for vehicle info, a physical description, and contact history. One of the pices of information on the Bolo subform is the name of the Petitioner (details below) which is my main reason for posting. I also would like to know the best way to do the "Contact History" tab. It would need to be a listing of all records associated with that person that we could click and get details of that event (Bolo?) Allen Browne Wrote: "There are multiple ways to interface SuspectID and PetitionerID without having to show the AutoNumber values. One way is to use a combo box, where the RowSource is something like this: SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName FROM tblPetitioner ORDER BY Surname, FirstName, PetitionerID; Then set the first column to zero-width, so it stores the hidden PetitionerID value, but displays the name: Column Count 2 Column Widths 0 Bound Column 1 " I now understand about the combo boxes you mentioned for PetitionerID. My first question is this: Do I need to make a separate table tblPetitioner? If so, I assume it would need to have PetitionerID (PK Autonumber), FirstName, LastName, PhoneNumber, Department ? Assuming that table structure, Next question is: Do I need a separate form to enter the Petitioner info? Would a sub-subform or subform be better? Now, assuming it is set up this way, is it then correct to place a combo box cboPetitioner bound to the PetitionerID PK field that does the query you gave me earlier to combine the First and Last Name on the sfrmBolo subform? The question is then, do I put the query "code" in the rowsource space on the combo options, and how do I make this combo a drop-down list? Let me clarify...if the petitioner does not exist in the list in the combo, I want the add petitioner form to pop-up, so a new petitioner can be added. I do not want the user to be able to just type a garbage name in the combo, since first and last names are in separate fields. I want to prevent user error and confusion as much as possible. Last thing is I assume after setting it up this way, I would merely need to do a dlookup to pull the petitioner phone and department into the sfrmBolo subform? Am I on the right track? Thanks for your help, Derek |
#2
|
|||
|
|||
What is the best way to set-up my Database?
See answers in line below:
-- Dave Hargis, Microsoft Access MVP "dgodfrey" wrote: I want to revisit a few things posted in another thread I started a while back. Any help from anyone is appreciated. I have a main form "Suspects" and a subform "sfrmBolo". The "Suspects" form is bound to the Suspects table and the subform is bound to the Bolo table. The Suspect table contains details about a person involved in an incident, while the Bolo subform contains incident details. The Bolo subform is on a tab on the main form. The top of the main form contains person details. I also have tabs for vehicle info, a physical description, and contact history. One of the pices of information on the Bolo subform is the name of the Petitioner (details below) which is my main reason for posting. I also would like to know the best way to do the "Contact History" tab. It would need to be a listing of all records associated with that person that we could click and get details of that event (Bolo?) Allen Browne Wrote: "There are multiple ways to interface SuspectID and PetitionerID without having to show the AutoNumber values. One way is to use a combo box, where the RowSource is something like this: SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName FROM tblPetitioner ORDER BY Surname, FirstName, PetitionerID; Then set the first column to zero-width, so it stores the hidden PetitionerID value, but displays the name: Column Count 2 Column Widths 0 Bound Column 1 " I now understand about the combo boxes you mentioned for PetitionerID. My first question is this: Do I need to make a separate table tblPetitioner? If so, I assume it would need to have PetitionerID (PK Autonumber), FirstName, LastName, PhoneNumber, Department ? Yes, you do need this table. Assuming that table structure, Next question is: Do I need a separate form to enter the Petitioner info? Would a sub-subform or subform be better? A form would be best. If you are entering the petitioner in the bolo subform, you can use a combo box as described by Allen Browne above. Use the combo's Not In List event when an unknown petitioner is entered to open the petitioner form and enter his information at that time. Now, assuming it is set up this way, is it then correct to place a combo box cboPetitioner bound to the PetitionerID PK field that does the query you gave me earlier to combine the First and Last Name on the sfrmBolo subform? The question is then, do I put the query "code" in the rowsource space on the combo options, and how do I make this combo a drop-down list? Let me clarify...if the petitioner does not exist in the list in the combo, I want the add petitioner form to pop-up, so a new petitioner can be added. I do not want the user to be able to just type a garbage name in the combo, since first and last names are in separate fields. I want to prevent user error and confusion as much as possible. The query is the row source for the combo. All combos are drop downs. Without the drop down, they become a text box. As to controlling the garbage, you would do that with validation in the petitioner form. Last thing is I assume after setting it up this way, I would merely need to do a dlookup to pull the petitioner phone and department into the sfrmBolo subform? Actually, you wouldn't need to do that. If you include the phone and department in the combo's row source, you can use the After Update event of the combo box to populate those controls. Am I on the right track? Thanks for your help, Derek |
Thread Tools | |
Display Modes | |
|
|