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
|
|||
|
|||
Link tables or use list boxes on input form?
Hi,
Most of the Access databases I've created so far have been for my own use. I've worked directly in the data tables. I am now creating a database for a small NGO to use to track medical data for a project in Africa. I need to create input forms for the end user to enter the data and a nice menu structure etc. My question concerns the best way to limit data entry to prevent errors. For example, there is a child data form that will have fields like ClinicID Outcome StudyFood CaretakerType PriorMalnutrutionTreatment et cetera. For each of these fields, I need to limit the user's scope of choices. I had set up data tables for each item and linked the appropriate fields between the tables. When I enter data directly into the table, I am appropriately prevented from entering an invalid code. But I would like to have the ability on the input form to show a new data entry person a list of their choices. I don't see a way to tell the form to look the value up from one table but store it in another table. Would I be better off to just create list boxes on the input form? I am concerned that this might be difficult for them to maintain if they end up needing to add codes. The data entry and database maintenance will end up being done by college/medical school students who are volunteering in Malawi. I will document the database but I don't want to make it harder than necessary to maintain. Obviously I am new to the whole forms concept. So far, the only forms I've used have been switchboards to run macros for things I do often in my databases. I've never EVER used them to enter and maintain data. I'm googling but, if any of you could recommend some good websites to go to for tuturials/reference, I'd really appreciate it. Thanks so much for your help and advice. -- Ann Scharpf |
#2
|
|||
|
|||
Link tables or use list boxes on input form?
Hi Ann,
Your comment: “I don't see a way to tell the form to look the value up from one table but store it in another table.” Forgive me if the following instructions appear to be overkill but it is difficult to know what level you are at. The following is for a simple table bound to a form and you want to look up values for a field using a combo or list box populated from another table. (It can be more complex using queries etc.) In Forms Design mode, click in the little square top left of form where the ruler lines intersect and a little black square appears. Open Properties. Select Data tab. Click Drop down arrow at right of Record source. Select the table to which the form is to be bound. Now select a combo or list box on the form. (While still in design mode). Open properties. Select Data tab. Click Drop down arrow at right of Control source. Select the required field from the bound table. Go to Row Source Type: Set to Table/Query. Go to Row Source: Click Dropdown at end of field and select the table with list of valid values for field. (If more than one field in the valid values then click the 3 dots and build a query so that the field to display is at the left end (first field) of the query builder matrix.) Save the form and now when you select a value from the combo or list box, the selected value is placed in the appropriate field of the bound table. Of course the purists will say that you should set a relationship link but this is answering your specific question as simply as possible and even if you decide to set a relationship it is not a lot different. As for adding options to the table for the valid list, that can be done by opening the table or if you want to do it by forms, place it a double click event for the combo or listbox and open a form to do so. -- Regards, OssieMac "Ann Scharpf" wrote: Hi, Most of the Access databases I've created so far have been for my own use. I've worked directly in the data tables. I am now creating a database for a small NGO to use to track medical data for a project in Africa. I need to create input forms for the end user to enter the data and a nice menu structure etc. My question concerns the best way to limit data entry to prevent errors. For example, there is a child data form that will have fields like ClinicID Outcome StudyFood CaretakerType PriorMalnutrutionTreatment et cetera. For each of these fields, I need to limit the user's scope of choices. I had set up data tables for each item and linked the appropriate fields between the tables. When I enter data directly into the table, I am appropriately prevented from entering an invalid code. But I would like to have the ability on the input form to show a new data entry person a list of their choices. I don't see a way to tell the form to look the value up from one table but store it in another table. Would I be better off to just create list boxes on the input form? I am concerned that this might be difficult for them to maintain if they end up needing to add codes. The data entry and database maintenance will end up being done by college/medical school students who are volunteering in Malawi. I will document the database but I don't want to make it harder than necessary to maintain. Obviously I am new to the whole forms concept. So far, the only forms I've used have been switchboards to run macros for things I do often in my databases. I've never EVER used them to enter and maintain data. I'm googling but, if any of you could recommend some good websites to go to for tuturials/reference, I'd really appreciate it. Thanks so much for your help and advice. -- Ann Scharpf |
#3
|
|||
|
|||
Link tables or use list boxes on input form?
Thank you so much, OssieMac! This was EXACTLY what I needed. I've worked
with Access for a few years now, but my databases have been ones where we download data from another system. I've set up related tables but everything was easily handled (by me) directly in the ancillary tables. This is the first database I'm creating where the data will be entered by hand. I have already set up relationships between all the tables in the relationships screen. Is this what your're referring to when you say, "...the purists will say that you should set a relationship link?" Thanks again for your help! -- Ann Scharpf "OssieMac" wrote: Hi Ann, Your comment: “I don't see a way to tell the form to look the value up from one table but store it in another table.” Forgive me if the following instructions appear to be overkill but it is difficult to know what level you are at. The following is for a simple table bound to a form and you want to look up values for a field using a combo or list box populated from another table. (It can be more complex using queries etc.) |
Thread Tools | |
Display Modes | |
|
|