Form that populates fields automatically

[bentley7]

I am having difficulty with my form when I try to add fields that can populate automatically. I've attached a sample database to explain what I am trying to do.

I have a table named table1 that contains 5 fields and a form named form1 that contains these items as well. I am looking to have my form appear as such:

Program code: user selects one of the choices
Program code desc: prefills from program code selection made
Segment: prefills from program code selection made
Class code: prefills from program code selection made
Class code desc: prefills from program code selection made

This seems like it would be easy to do, but I can't seem to figure it out. Any help would be appreciated.

 

[BarryMK]

Your form has no data from your table. I used the new query wizard to create a select query on your table and in Form Design View I clicked in the desktop and selected Properties then clicked the drop down V in Record Source and select the query. Then I used the wizard to create a Combo Box on your form linked to Program Code.

Take a look and try to replicate what I did as an exercise.

However I think your one table database design will leave you with little flexibility and leave you problems later on. Perhaps some of the experts here may be able to guide you on how to normalize your database.

 

[bentley7]

Thanks for your response. I understand what you did, but still have another question for you. The sample database that I have created doesn't have all of my data in it, and the form I am creating actually has a lot of information on it. I had it set up so that the data that was created in the form that I had created was stored in a separate table...and that table was indicated in the record source. In your solution, you have me changing that table to a query. Is there still a way that I can save everything into that other table without changing everything around?

 

[BarryMK]

Using a table as the recordsource for a form is much less flexible than using a query. For instance you might wish to pull different data through in fiuture or filter it further. Queries are very versatile and can do much of your work for you.

If you must store these data in a sepatrate table just base your query for the form on that table.

 

[bentley7]

Thanks for your help, but I still do not think it is working as I'd like. I've attached a new database and here is what I am trying to do.

I've created the query like you suggested. I have two tables...table 1 is the data that I'd like to look up (so this is what I would like to appear in my "Program Code" combo box). I would like to store all of the data into table 2. In other words, the only things I would like to appear in my drop-down (combo box) are the 5 lines that appear in table 1.

I'm not sure if this is how you were thinking about it or not, but I could have easily just not understood how to do it.

Thanks for your help.

 

[BarryMK]

You'll need to explain what you want to achieve as I believe you're going to run into fundamental problems as it is. What is the actual purpose of your database and what is it you want your form to do?


It looks to me as if you need to split the data into a couple of related tables as you're storing duplicate information.

You have 3 different areas Class, Program and Segment (unless segment belongs with one of the other two) each of which should be in their own table and only joined when you create a record.

Without knowing what you want it to do we'll just get tied in knots!