Combo Box to auto fill text box - problem

[mmaurigi]

I'm attempting to create a Purchase Order form. The way I have started to setup the form, I have a Combo Box that has a list of our most popular vendors. I would like to have the user be able to select the vendor from the list and based on their selection, have the vendor's address, phone no., contact name, etc. automatically fill in text boxes on the form. The information for the Combo Box is fed from a Table called VendorInfo. Currently there are only 3 vendors in the VendorInfo table. Unfortunately, it's not working 100% correctly. What's happening is this: When a vendor is selected from the Combo Box, the text boxes fill in as they should. If I close out of the form, and reopen it, the information shown in the first 4 columns for the vendor listed for ID 1 of the VendorInfo table gets replaced with the vendor information of the last vendor selected prior to closing the form. Below is the code tied to the Combo Box under the Properties, Event tab, After Update. I'm looking for some guidance on what is wrong and how I can fix it. I'm very new to creating MS Access forms and learned whatever I do know from internet searches. Appreciate anyone's assistance.

Private Sub Combo18_AfterUpdate()
Me.Text10 = Me.Combo18.Column(2)
Me.Text12 = Me.Combo18.Column(3)
Me.Text20 = Me.Combo18.Column(4)


End Sub

[orange]

Please describe all of the tables you have. Also, tell us a little more about "making a purchase". Where does Vendor fit? What about the "thing" being purchased etc.?

Also, your database life will be better served if you use meaningful names for tables and controls.

Me.Combo18 doesn't inform anyone whereas Me.cboVendor might be helpful.

[mmaurigi]

orange, thanks for replying. This database is really in its infancy. The only table I have right now is the VendorInfo table. There are 10 fields to the table:
VendorName
VendorAddress
VendorCity
VendorState
VendorZip
VendorPhone
VendorFax
VendorCell
VendorEmail
VendorContact

Currently there are only 3 records in the table.

Vendors are the companies we a buy items from.

"Making a Purchase" scenario: Our purchasing agent needs to buy 1 or more items. The intent would be that once opened, the form has the next available sequential purchase order number. The purchasing agent selects the vendor from the Combo Box that he will be buying the item(s) from. Rather than having the agent fill in the address, city, state, etc for the vendor, I want that to populate on the form based on the vendor that was selected from the ComboBox. The agent would enter a "Ship To" address and then proceed to type in the items to be purchased with their costs (ultimately would like there to be a list of items that the agent could choose from). The costs would be totaled at the bottom of the form. Form would print to a pdf and emailed to the vendor.

[orange]

Separate WHAT you are trying to accomplish from HOW you think you'll do it. It is worth the time to write a clear description (in plain English) of WHAT the business is and what the requirement is. Build a model of what you are dealing with. Test the model, and adjust as necessary. Once you have a model that matches your requirements you have a blueprint for your database. Then deal with forms, reports etc.

You may get a lot of info re database and related concepts from these videos by Dr.Daniel Soper
especially topics 1,2 and 4

You may also find this tutorial well worth the 45 minutes to 1 hour to work through it.
The Hernandez method alone is well worth reading/referencing.

Here is a sample data model that may help also.

Good luck.

[mmaurigi]

My industry is industrial water treatment. I'm trying to create a PO system to use with the company. I have a plan on how to do this. I'm having a problem getting a selection from a Combo Box fill in a few text fields on the PO form. As I explained in my initial post, the Combo Box that has a list of our most popular vendors. I would like to have the user be able to select the vendor from the list and based on their selection, have the vendor's address, phone no., contact name, etc. automatically fill in text boxes on the form. The information for the Combo Box is fed from a Table called VendorInfo. Currently there are only 3 vendors in the VendorInfo table. What's happening is this: When a vendor is selected from the Combo Box, the text boxes fill in as they should. If I close out of the form, and reopen it, the information shown in the first 4 columns for the vendor listed for ID 1 of the VendorInfo table gets replaced with the vendor information of the last vendor selected prior to closing the form. Basically was looking for a possible reason why the information in the VendorInfo table changes as described above.

[orange]

Did you watch any of the videos, or work through the tutorial?

Here is more info from Barry Williams' site.

In this General Approach we define the Steps in a structured method to design a Database, and there
is another Approach at the bottom of the page.

You can have a look at this Page to see how this Approach applies to the design of a Database for an HR Department.
The Approach defined here is aimed at beginners and experienced practitioners.
It makes some recommendations to simplify basic design decisions on key structures.
These are the Steps in a Top-Down Approach :-

2. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
3. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
4. Analyze the Things of Interest and identify the corresponding Tables.
5. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
   For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities. If you are just starting out, I suggest that you postpone this level of analysis.
6. At this point, you can produce a List of Things of Interest.
7. Establish the relationships between the Tables.
   For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
8. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
9. Identify the Static and Reference Data, such as Country Codes or Customer Types.
10. Obtain a small set of Sample Data,
    e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
    "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
11. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
12. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
13. You need to define a Primary Key for all Tables.
    For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be. Which means, of course, that it often never gets done.
14. Confirm the first draft of the Database design against the Sample Data.
15. Review the Business Rules with Users,(if you can find any Users).
16. Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
17. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
18. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

[mmaurigi]

Think I figured it out. Records were changing in the table because the combo box was unbound. Seems to be working now.