List Box Data disappearing

[GeekyGirl]

I have a form called CreatePL which is made up solely on List Boxes that the user must choose from. After all the choices are made, I have a button to save the information to a table called PL.

I noticed that after clicking on this button, that record stays active so when you try to create the next record, it is actually changing the previous record. As I was testing this a bit further, I also noticed that the one of the names in one of the list boxes got erased (I'm not sure how yet).

Is there a way to lock the information but still be able to make selections and save it to a table? When I choose the properties so that you can't edit, I loose the ability to make choices also. All of the list boxes are set to "limittolist".

 

[Mile-O]

All of the list boxes are set to "limittolist".

Listboxes don't have a LimitToList property so it's hard to know what you want when you don't know what you referring to. You probably mean comboboxes.

More information is needed on your form. Give as much as you can.

 

[Uncle Gizmo]

a button to save the information to a table called PL

Do you mean you have an unbound form? If so could you post the code behind the button.

 

[chris davies]

have you gat a save record button on your form and an add new record on the form?

 

[GeekyGirl]

Listbox, combobox - It still won't work.

There is no way to keep this short in order for you to understand. Sorry it took me this long to get back. I have trying endlessly to get this database to work. Also, I have posted on 2 other Access forums only to be lectured about doing it all wrong and not needing the third table down below instead of being offered help. I am also sorry about stating that the listbox had a limit to list. I'm aware that is for combo boxes. I have been switching between list boxes, combo boxes, queries, etc. that my head is spinning.

Maybe if I just explain what I need, someone can tell me the best way to do it. I've tried so many different ways that I don't know how to proceed.

In any event, I believe I must be having a software problem or a problem with the database because I've changed it so many times. Although if I import just the tables into a new file, I'm still having the same problems. The biggest problem is with combo boxes and lists. If I have a combo box or list with 2 or more columns, including the ID, I can only get the ID to be saved in the control field in my table. Also, I try to follow what it states in Access that they start counting with 0 in a combo box but I could only get it to work when I started counting with 1.

Here is the situation. I am working with a software developer to be able to offer their software prefilled with a specific industry's data. The program is an inventory, accounting and contact manager for small businesses. I'm in charge of getting this data into the software program. The program is written in such a way that is has a foundation of 52 access files that are joined together with programming. I am only working with 1 of those files. It is not set up for me to work directly in the program for this situation.

This is what I'm working with...
There are 3 main tables in this file with a few supporting tables such as a list of shipping companies. A new component has been added to the program so that you can search for a vendor by the products they sell. The product lines come from a master list and now need to be joined up with the vendors. The 3 tables that are involved are:

1. Vendors - which holds the vendor's contact information. There are 226 vendor names that now need the contact information filled in.
2. PLMain - is the master table of product lines to choose from. This table as the product name, category and subcategory for over 250 different product lines.
3. Product Lines - is a blank table ready to be filled. It is part of a many-to-many relationship where a vendor can each have multiple product lines and there can be multiple vendors that carry the same product line.

I figured out that I need to handle this as 2 different tables. The first table allows me to update the vendor contact information and to be able to add new vednors if needed. No problem there.

The problem comes in in trying to create the Product Line table. I got one form to work but I worry about human error with it. First, so that I wouldn't do damage to the original table, I created an exact copy of it to store the data until I append it to the good table (this step doesn't need to exist if I can get it to work without errors). The listboxes are as follows:

VendorID, VendorName, Product LineID, Product Line Name, Product Line Category and Product Line Subcategory.

I realize these all relate back to the IDs and that in a normal access situation that would be enough information. But for this software, I actually need to have the physical date by name in their respective fields.

Here are the problems with this list box. If you are not careful after saving a record, you can easily write over the record you just created. Also, data from the master list has been deleted when I tested it out. Also, there is a large room for human error because you could choose the proper Product Line Name but accidently choose the wrong Product Line Category. The Form that works is called Assign Product Lines and the data is saved to Create ProductLine Table.

I have tried a ton of combo boxes but I can't get the actual information in the table. I created one form where I had a control for each column in the combo box and then referenced the combo box columns as the control but it sits nicely on the form and not in the table. I have even tried working directly from the datasheet with lookup boxes for each field but all I get are the ID numbers and not the actual name.

I have greatly reduced that data tables so I could post the file. This file has the best since the forms work. I am happy with the Vendors form but not with the Product Lines Form. I will try to post my other file which is the one I have worked on so much. I know that there are obvious problems but it is my "dirty" file that I was using to try to create a working form.

 

[GeekyGirl]

The Ideal Form - if it worked

I thought this form was in the other files but it wasn't. This is the ideal set-up for the form I need. If I could get it to work, it would save me a lot of time. So take a look at the only form in this file.

 

[Mile-O]

There is no way to keep this short in order for you to understand.

The more information you can give then the more chance people have of understanding your problem.

I have posted on 2 other Access forums only to be lectured about doing it all wrong and not needing the third table down below instead of being offered help.

Two things here.

1. If you feel the need to post your query to other forums then please have the courtesy to say so in your posts. There's nothing more annoying than spending time helping somebody with their problem only to find that the time has been wasted because the problem has been resolved on another forum. A lot of members here are members of other forums (I for example, am on Utter Access, VBA Express, Tek Tips, Access VBA) and we don't like to see the same question posted everywhere.
2. The fact you are being lectured about how you are doing things wrong is help. They are obviously advising you that you are going about your problem in the wrong manner and offering advice on fixing your database to a normalised design which is currently isn't.

On looking at your database samples I'm left confused as to what is supposed to be going on within it. A table for product lines and then a table for creating product lines - what is that all about?

3.. Product Lines - is a blank table ready to be filled. It is part of a many-to-many relationship where a vendor can each have multiple product lines and there can be multiple vendors that carry the same product line.

Okay, you've identified a relationship but there are no relationships defined within your database. Also, two simulate a many-to-many relationship you need a junction table between the vendors table and the product table. You don't have that in your database.

The problem comes in in trying to create the Product Line table. I got one form to work but I worry about human error with it. First, so that I wouldn't do damage to the original table, I created an exact copy of it to store the data until I append it to the good table (this step doesn't need to exist if I can get it to work without errors). The listboxes are as follows:

You seriously need to rethink your design - your tables are the sum of your worries just now. Forms don't even bear thinking about at this moment. But, when you do get around to dealing with forms you should use a form/subform combination to assign multiple products to the vendors. This is the accepted method of presenting a many-to-many relationship on screen in Access and is the way smart people do it. Smart people know not to 'reinvent the wheel'.

 

[GeekyGirl]

Here's An Example

I originally tried to create a form and subform and I did have a joined relationship. That was my first round of working with this data. I also had relationships set up - I always begin there. I believe I lost the relationships when I was trying to clean up some of the files last night and streamline the file names so it wasn't such a mess. In terms of your comment about the lecturing - it doesn't help unless it is followed by a recommendation of what to do.

I understand the concept of normalizing but I can't change the software program that still needs to use these files. Once I set up the product lines with the existing vendors, this access file is no longer needed. It is only needed to set up the initial information since the Product Line component is new to the software.

If it helps here is an example of what I need to do. Again, I can't change the base tables since that is what needs to stay for the program. I also can't change how those tables are set up in terms of their keys, fields, etc. since the program needs to access the information as it is. I agree that this is a crazy way to handle this but I didn't write the program. I did a test using my own tables and could accomplish getting the data in a more effecient way with only using the ID/Keys to pull up the information. Then I tried to use these new tables, relationships, etc. in the program and it doesn't work.

Here is the example. I have put the field names from the tables in () after the table name

Vendors (Vendor ID, Vendor Name)
1, Accent Depot
2, Deluxe Designs
3, Roger's Online

MainPL (ProductLineID, PLName, ProductLineCatergory, ProductLineBrand)
1, 01-01 Scrapbook Distributor, 01- Distributors & Suppliers, 01-01 Scrapbook & Stamp Distributors
2, 04-02 Round Eyelets, 04- Embellishments, 04-02 Eyelets, Brads & Snaps
3, 04-02 Decorative Eyelets, 04- Embellishments, 04-02 Eyelets, Brads & Snaps
4, 07-00 Stickers on a Roll, 07- Stickers, 07-00 No Brand
5, 07-01 Jolee's Stickers, 07- Stickers, 07-01 3D Stickers
6, 09-06 Color Blocking Templates, 09- Templates & Stencils, 09-04 Page Layout Templates

Now I need to populate a table with a list of the vendors and their product lines. I need to have the actual names in each field in the table and not simply the ID/Key. The table would look something like this:

VendorID, VendorName, ProductLineID, ProductLineName, ProductLineCategory, ProductLineBrand

1, Accent Depot, 2, 04-02 Round Eyelets, 04- Embellishments, 04-02 Eyelets, Brads & Snaps
1, Accent Depot, 3, 04-02 Decorative Eyelets, 04- Embellishments, 04-02 Eyelets, Brads & Snaps
1, Accent Depot, 5, 07-01 Jolee's Stickers, 07- Stickers, 07-01 3D Stickers
2, Deluxe Designs, 5, 07-01 Jolee's Stickers, 07- Stickers, 07-01 3D Stickers
2, Deluxe Designs, 4, 07-00 Stickers on a Roll, 07- Stickers, 07-00 No Brand
2, Deluxe Designs, 6, 09-06 Color Blocking Templates, 09- Templates & Stencils, 09-04 Page Layout Templates
3, Roger's Online, 1, 01-01 Scrapbook Distributor, 01- Distributors & Suppliers, 01-01 Scrapbook & Stamp Distributors


Lastly, the comment about going to different forums. I have usually found that some forums are better than others simply due to the amount of people on them. I actually wasn't asking the same questions on the forums until this last post. As I was trying different ways to work with this data, I went to different forums to ask the different questions. Then once that I see that a forum is better than the others, I usually stick with them. There are always different ways to do things and sometimes it is nice to get different opinions.

 

[Mile-O]

Lastly, the comment about going to different forums. I have usually found that some forums are better than others simply due to the amount of people on them.

Just a warning though, I have seen people banned for cross-posting the same questions across forums. When I was an admin on VBA Express there was a discussion about a certain user, name of Saz, who repeatedly cross-posted across a number of known forums. In the end s/he was banned from there. Hardline, I know, but when it comes to wasting the time of others wanting to help then it is understandable.

What is this other software program created with?

 

[GeekyGirl]

I'm not sure what the programming is. ALthough I am working with the software creator to create this industry specific software, for his own protection, he has not revealed to me the way he has programmed.

If this all seems to be a hassle, I'll just come up with a different way to handle the data.