Text field to Combo Box problem

[petefc]

I have a simple form and table which had a text field I called "Supplier". During the design process I changed the field, on the form, into a Combo Box. Now that I have added 20 or so records, I notice that the "Supplier" field in my table is blank. I'm not surprised, and I can write a query to update the "Supplier" field on the 20 records so far entered. However, how do I modify the table to pick up the field entered into my Combo Box in the form ?

[orange]

Now that I have added 20 or so records,

where exactly did you add these?
You should research combobox properties-- recordsource and seek out examples of comboboxes.

[petefc]

I am using the form to add records to my database. The "Supplier" field is a combo box offering 6 choices. All the records appear in a table of course - except for the "Supplier" field - which is blank. All the other fields are correctly populated - so why not the "Supplier" ?

[orange]

Can you please show the vba or query sql that actually adds a record to the Suppliers table?

[petefc]

I am clearly not explaining myself very well. That is probasbly due to my newness to Access 2010. I have experience over 10 years ago of Access 97 before I retired but you must treat me as a newcomer - and be gentle.

I created a Table with half a dozen fields called Goods. One of the fields is called Supplier and is a text field. As far as I am aware a Form is the user interface to that table from which I can generate reports etc at some future date. So I then created a Form called Goods - with identical fields. One of them is the Supplier text field.

Because I'm lazy and don't like typing (and relish a challenge), I changed the form to show the Supplier field as a Combo Box. So far so good. Now when I enter records into the form I can select from a list of 6 suppliers. Looking at my Goods table now shows all the records entered - but no Supplier information.

If it's any value, the Data Properties of the Supplier field on my form are as follows :-

Control Source is blank
Row Source is a list of suppliers separated by semi-colons
Row Source Type is Value List
Bound Column is 1

In the design view of the form, the field Supplier shows that it is Unbound.

I hope this lengthy explanation tells you what you need to know.

It is over 10 years since I looked at vba though I still have my old manuals somewhere....

[Jacqueline]

The combo needs a field to update. Did you point the combo box to the Supplier's field within the form? You need both the combo and the field to update the table, I believe.
J

[petefc]

Thanks Jacqueline. I don't follow where you say that I ".. need both the combo and the field to update the table". It's not your fault.

I am missing something really basic here and I will search the help files and find examples - bear with me... My understanding of Combo Boxes is obviously lacking and I need to do more research.

[Jacqueline]

Usually when you are using a combo box you it is to look up records or you are filling data from one table into another table. Can you give me more detail on how you are using the combo...

For example, are you looking up a record, or are you pulling in data that you want entered into a field within the table?

An example of looking up data on a form: Let's say I have a form where I am need to look up students. I build a query that is based off my student table, I then insert a combo box in my form off the query and ask it to look up record on my form based on the combo box.

Can you tell me how you are using yours?

[petefc]

On the form I changed the Supplier field from text to combo box. The list of suppliers doesn't come from another table or a query. The list comes from the "Row Source" in the Supplier field properties.

Row Source is a list of suppliers separated by semi-colons

Since I posted this thread I am tempted to redesign the database by adding another table - called Suppliers which lists all six of them. I would hope that doing this would ensure any input to the form would be reflected in the table.

[Jacqueline]

I think you are on the right track. It sounds like you are trying to use a combo box for something is was not designed to do.

Remember when you are building a database, the intent is to not duplicate data. The only thing that should duplicate from table to table is the Primary and Secondary keys.

Think about your design, remember you will have tables which are considered One Tables, only one record per item such as an address book: Name ID (Primary Key) and address... this data needs not be duplicated and this means you only need to update it once if the need arises.

Suppliers, for instance... you would have a table that lists your Suppliers, with their relevant data, address phone, contact name etc. Next you would build a Many table that would hold everything you purchase from the supplier, parts widgets etc.

You will need a unique identifier for the Supplier, an number or something that is assigned to just that supplier like our Social Security numbers. This number will become the secondary key in the Parts table, where you will link the supplier to the various supplies you purchase from him... The primary key in the Parts table for each part must be unique, but the secondary key can be listed many times because one supplier will have many items supplied to you.

I hope I am not confusing you more. But if you spend a little time up front getting your design correct it will save you a lot of time down the line. Do some reading on database.

One of the things I did when I taught myself, was to download one the template databases from Microsoft that was close to what I needed. I then looked a the back end on how tables were designed and linked.

Hope this helps, good luck
Jacqueline

[petefc]

Many thanks Jacqueline. Before I read your post I had already had a tinker with my database. I think you are right in your comment about the combo box. I didn't know what I was doing trying to change a text field on the form into a combo box.

So, I pulled the plug on that idea and created a separate table containing my suppliers.

In my "Goods" table I then changed my supplier field into a lookup field using the lookup wizard. So, in the table the Supplier field now presents itself as a choice of six suppliers. The Display Control is combo box although that took care of itself; at no point did I actually use the term, the wizard took care of everything.

On the "Goods" form I had deleted the Supplier field altogether. Going back in to redesign it I added it back, but this time it's properties had already been fixed from the source table - ie: a choice field, or combo box if you prefer.

All's well that ends well. Thanks again for your patience and your assistance. Peter

[RayMilhon]

Sorry I didn't see this sooner but all you had to do was change the Control Source to the Supplier field that would have fixed it for you.