Information added to form not reflected in table

[klarae99]

I am new to this forum and still relatively new to Access (2003) and will try to explain my problem without alot of extra information to bog it down. I am trying to create an Inventory Database from scratch. Below is a list of the tables and fields relevent to this question.

tblvendor
fields for contact information (Name, Address, Etc.)
VendorName
VendorCode (a three letter code used in conjunction with the item # for reporting and pricing perposes.)

tblProductInformation
Item #(Primary Key)
Vendor Name
Vendor Code
Description
Others not related to this question.

I have created a form (frmProductInformation) to imput data into the Product Information table. The form uses a combo box so the user can select the vendor name. I have formated the form so that the vendor code automatically fills in after the user makes a vendor name selection. The rowsource for the vendor name combo box is

SELECT tblVendor.[Vendor Name], tblVendor.[Vendor Code] FROM tblVendor ORDER BY tblVendor.[Vendor Name];

the bound column is 1, the column count is 2 and the column widths are 1;0. The Vendor Code field control source is

=[Vendor Name].Column(1).

This automatically fills the Vendor code in for me on the Form but does not fill in the Vendor Code Field of the tblProductInformation, and as a result when I print a report based on that table it does not list the Vendor Code. I have tried to change the properties in tblProductInformation to have a similar lookup feature but cannot set a control source for the vendor code field. I have also tried to create a queary to use as a control source for the vendor code field in tblproductinformation but using tblproductinformation:vendor name as a source for the queay promps me for an entry when I open the table.

Any advice on this problem would be greatly appreciated. If I neglected to include some important information please let me know. Thanks for your help!

[nico5038]

Using this query for the combobox will need the bound column = 2

The bound column will be the column that's stored in the tablefield the combo is bound to.

In general we use a source like:
select ID, Description from tblX
combined with a bound column = 1 and a column width = 0
This way you don't need to specify the second column width as Access will assume it's visible for "the max".

Getting the idea ?

Nic;o)

[NeoPa]

May I say what a joy it is to see an intelligently, and thoughtfully, prepared question.
If all questions were posed as well we could get so much more done here.
A hearty welcome to theScripts :)

Let us know how you get on with this.

[klarae99]

First off thank you for your timely response and for your comments on my question, it makes me feel better about revising it three times before posting it.

However, I was confused regarding the query for the combobox refered to in your reply, but this is how I approched it.

I went to my tblProductInformation and changed the vendor code column to a combobox and used the build function to create a queary with the fields Vendor Code and Vendor Name in that order (from my tblvendor). I typed the sourceline (Select ID, Description from tblProductInformation) in the criteria of Vendor Name and then changed the bound colum to 2 and the column width to 0. I saved the revised queary and returned to the table view. All of the Vendor Codes were still blank and when I clicked on the Vendor Code Combobox the following error message poped up.

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

I then moved the Select statement to the criterea for the Vendor Code column in the queary and got the same error message when I clicked on the combo box.

I am not convinced that I put your suggested source information in the correct location. If I did I am missing some Clause in my queary to make it work. Any further clarification and additional comments would be greatly appreciated.

[Jim Doherty]

First off thank you for your timely response and for your comments on my question, it makes me feel better about revising it three times before posting it.

However, I was confused regarding the query for the combobox refered to in your reply, but this is how I approched it.

I went to my tblProductInformation and changed the vendor code column to a combobox and used the build function to create a queary with the fields Vendor Code and Vendor Name in that order (from my tblvendor). I typed the sourceline (Select ID, Description from tblProductInformation) in the criteria of Vendor Name and then changed the bound colum to 2 and the column width to 0. I saved the revised queary and returned to the table view. All of the Vendor Codes were still blank and when I clicked on the Vendor Code Combobox the following error message poped up.

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

I then moved the Select statement to the criterea for the Vendor Code column in the queary and got the same error message when I clicked on the combo box.

I am not convinced that I put your suggested source information in the correct location. If I did I am missing some Clause in my queary to make it work. Any further clarification and additional comments would be greatly appreciated.

Just my two cents worth Klara

If you look at your database structure you will see that you have VENDOR CODE and VENDOR NAME in 'two' places both in the tblVendor and also in the tblProductInformation. This is against normalization principles as the VENDOR NAME can always be looked up from tblVendor by using an inner join on tblVendor. I can visualise why you have done this because you are learning and basing your inputs using a form that inputs directly into the 'table' but actually what you are doing is creating slight problems for yourself potentially in the future particularly as with an inventory system it may well become become complex and will need to be managed correctly and so on. Now this is not to scare you to death at this early stage, it is more to do with steering you in the right direction or at least pointing out to you somethings that you might NOT already know.

Have a look at basing your form on a QUERY where the main table you wish to input ie tblProductInformation is joined with tblVendor ON Vendor Code and where you wish to see all products in tblProductsInformation including those products where a vendor might not yet be assigned. This is a called a RIGHT JOIN. Take a look at the little line that joins the two tables together and make sure that you select the option to 'show all records from tblProductInformation and only those records from tblVendor where the join fields are equal.

When you do it this way you will begin to understand that the need for the data in two places other than the 'vendor code' is completely unnecessary because you can drag the Vendors name to the query grid from the tblVendors table and still input the data. This is commonly referred to in Access as AUTOLOOKUP have read in help on it and understand its concept.

As it is over time you will have to ensure that you don't have redundant data in the tblProductinformation namely the Vendors Name which will come about if you were to 'ever' change the Vendors Code value in the tblVendors table (in other words the Vendors Name in the tblProductInformation would NOT automatically update and like I mentioned is in reality unnecessary)

I hope this helps you maybe a little as a general pointer and good luck in your build

Regards

Jim

[klarae99]

Jim,

Thank you for taking the time to access my problem and give your advice, as someone learning the program I understand that the more people I talk to the better my skills will become. I understand your reasoning behind listing only the vendor code in tblProductInformation and then linking it to tblVendor when I want to view the information. It makes me wonder how many other places I have made similar mistakes in designing my database. I have some questions regarding the queary that you suggested I designed to base my form for data imput on.

1. I will never have inventory without a vendor (the vendor is who we order our merchandise from) so is a RIGHT JOIN still the type I should be looking into?

2. My tblProductInformation lists the Item #, Description, Vendor Code, Retail Price, etc. But I have a tblSubProducts that records our transactions (# Recieved, # Sold, Location, Our Cost or Sales Discount, Etc). How will I incorporate this information into the queary to link it to the Item #?

3. I had thought that a queary was a way of asking the database a question. I currently have some forms created to imput user data into a queary (search inventory by location x for example) but you are suggesting that I use a queary to enter data, or at least using a queary to build a form that would be able to enter data. Am I understanding you correctly.

Again thank you for your imput and I will go and read up on the AUTOLOOKUP in help!

[klarae99]

I have been reading up on the Autolookup feature and I can see where it would be useful but if I am understanding it correctly (and I may not be) I would use the Vendor Code to lookup the Vendor Name in your sinario, so that only the Vendor Code needs to be in both tables. If I did this could I still create a combo box with the vendor names on my form and fill in the vendor codes. The people doing the data entery may not know the vendor codes but will have the vendor name from the invoice. I'm just trying to get a better grasp of what I am trying to do before I jump in and start changing things. Thanks for your continuing help!!

[Jim Doherty]

Jim,

Thank you for taking the time to access my problem and give your advice, as someone learning the program I understand that the more people I talk to the better my skills will become. I understand your reasoning behind listing only the vendor code in tblProductInformation and then linking it to tblVendor when I want to view the information. It makes me wonder how many other places I have made similar mistakes in designing my database. I have some questions regarding the queary that you suggested I designed to base my form for data imput on.

1. I will never have inventory without a vendor (the vendor is who we order our merchandise from) so is a RIGHT JOIN still the type I should be looking into?

2. My tblProductInformation lists the Item #, Description, Vendor Code, Retail Price, etc. But I have a tblSubProducts that records our transactions (# Recieved, # Sold, Location, Our Cost or Sales Discount, Etc). How will I incorporate this information into the queary to link it to the Item #?

3. I had thought that a queary was a way of asking the database a question. I currently have some forms created to imput user data into a queary (search inventory by location x for example) but you are suggesting that I use a queary to enter data, or at least using a queary to build a form that would be able to enter data. Am I understanding you correctly.

Again thank you for your imput and I will go and read up on the AUTOLOOKUP in help!

Ok Klara I can see you have your thinking cap on and I have to say its a real pleasure to see that in the early stages as opposed to simply diving in developing and then having to revisit things unnecessarily merely because one hasn't perhaps thought things through. We cannot always do that of course, especially when we might not have the overall knowledge of what is achievable from the outset but thats what discussion is all about.

On your first point

Q) I will never have inventory without a vendor (the vendor is who we order our merchandise from) so is a RIGHT JOIN still the type I should be looking into?

A) Absolutely - you might as well give yourself the functionality of being able to enter and view a product even though you might NOT know who the vendor is at precisely that time. To simply do a join by not defining it as a RIGHT join would mean in practice that if you entered a product via the screen form ..yes.. you could enter it in ok...BUT when you opened the form again it would not return back up for viewing because the join clause is not satisfied in order to display it on screen yet the product would be sat in the table.......if you get me

On your second point

Q) My tblProductInformation lists the Item #, Description, Vendor Code, Retail Price, etc. But I have a tblSubProducts that records our transactions (# Recieved, # Sold, Location, Our Cost or Sales Discount, Etc). How will I incorporate this information into the queary to link it to the Item #?

A) Your tblSubProducts which as you say is your transactions table then the same theory applies you create a query based on that table and link it to the required table to retrieve the relevant information. Much of this depends on your database design and where you place your fields.

On your third point

You can be excused for thinking that a query is a way to ask a question of the database that is exactly what the word implies, however in the real sense a query enables you to manipulate and insert data also.

You can test this out if you create a simple query for yourself and save it as something... then create another query and call in the first query via the 'Show table dialog' you just saved to the second query (the 'show table' dialog that pops up when you create a new query gives you tables,queries and functions that you can add to the query grid). So there you have it you can add queries into queries and so on.

So in short, and if you are not totally confused at this point you will understand that well designed forms should be based around a query because you can immediately display on the form data that resides in other tables WITHOUT having to 'fetch' that data into the form by inefficient methods.

I'll give you an example imagine your customer has a fixed business address consisting of the usual street,town,city and so on. They also might have a ship to address for a transaction at any one time that varies depending where they wish the product to be shipped to.. If the 'fixed' address is stored in the customer table and the 'Ship To' address is stored in the transaction table then ALL you need to make sense of this is the customer ID in the transaction table because you can CALL IN all that data from the customer table and display it immediately on the form AS YOU TYPE THE TRANSACTION onto the screen Access 'lookups' up everything automatically for you.

Imagine then that for ONE transaction of potentially many that that particular customer on that particular occasion wants the product shipping to their business address. Its a simple question of a button to copy the business address to the shipped to address or a checkmark that says (if I am shown as ticked then the ship to address is the same as the business address. I'm sure you can see where this is leading to?


Finally on your posting number 7 (which hit the forum as I was doing this and maybe as I was watching TV also hahaha) the point you make

"If I did this could I still create a combo box with the vendor names on my form and fill in the vendor codes."

The answer - "Of course tis purely a matter of design and making the system do what you want it to do BUT.... you do it coupled with the knowledge of knowing that you do not have redundant data hanging around thus optimising your table layouts and thereby keeping overall datasize to its minimum"

I am willing to help you on this, as I can see you have the bit between your teeth from reading your text content. Therefore if you wish I will be happy to look at your database fields and tables and structure and assist you in setting this up optimally, maybe design a few forms to give you the concept. In order to do this you would need to PM me with your email address in order for us to communicate and pass files.

Kind Regards

Jim

[klarae99]

Thank you so much for your offer, it is much more help than I expected to recieve when I posted this question.

To Everyone Out There:

I really appreciate the fact that forum members reviewed my post and responded with ideas and support. Thanks to everyone who participates in these forums; even before posting anything to this site I had referenced multiple other postings and managed to figure out much more than I ever could have if I relied soly on the guides that I had in front of me. The guides are very good at telling you what you should be able to do but when you run into the little misconnects in procedure or ideology they are unable to redirect you to the correct path. As a new member to the forum world I just wanted to let everyone who contributes know how much your help is appreciated. I imagine it can be daunting to have everyone look to you for answers and expect to get them. I was pleasently supprised by how quickly my post was answered and really appreciated that even after replying to my post members returned to view my replies to them, opening up a diologue that I could learn from. A Heartfelt Thank You!!

[NeoPa]

Klara,

Members like yourself, who are careful, intelligent and obviously prepared to help yourself, as well as help us to help you, are not that common around here (not unique - but rare). When they come along it makes a nice change to deal with them, in contrast to some others who expect more to be done for them.
All are welcome of course, but it's a little break when we don't need to keep explaining that it's not appropriate simply to let us do all the work for them.

Many questions are less complex, so this is not so relevant, but with a complex one it can get difficult dealing with some characters. Your thread is exemplary. It is a more involved one, but you've clearly gone to some effort to take as much of the burden as possible - which we all appreciate.

We also appreciate the thanks, of course. Most members post their appreciation, to be fair, but it's always appreciated.

Keep posting if you need more help :)