I'm desparate for help

[GeekyGirl]

I am new to the programming, macro side of Access and need some help. I also have some problems with 2 Forms I am working with. There are a total of 4 Issues that I need help with.

I am working with 3 tables for an inventory and small business accounting program. We just added the ability to assign Product Lines to each vendor. So there is a table for the vendor contact information, a table with all of the Product Lines entered and a blank table which will hold the lists of Product Lines specific to a vendor. This has been a real challenge to get to work properly. After struggling for a grueling week to be able to get enter the data so that the new table has the multiple Product Lines for each Vendor, I was able to create 2 separate forms. One for updating and adding vendors and one for selecting the Product Lines.

Here are the issues I am experiencing:

Issue 1: Combo Boxes
Is there a way to use all the data from a combo box instead of just the one that you designate as the Bound Column?

I originally created several combo boxes to select the various pieces of information but then I couldn't carry over all the information over from the single combo box so I created many List boxes. There has to be some way to use all the data in a combo box to be stored in different fields. Below is a list of the different groups of data that I need to be able to save into several fields:

Combo Box 1: Vendor ID, Vendor Name
Combo Box 2: Product Line ID, Product Line Name
Combo Box 3: Product Line Category, Product Line Subcategory
Note: Combo Box 2 and 3 ideally could be combined if I am able to use one Combo Box that sorts out the different fields.

Issue 2: Residual Data & Losing Data
The next problem I have is in working with the forms. Currently my second form is a set of 6 List Boxes for me to choose the Vendor and the Product Lines. My problem is that after I have created one entry and saved it, all of the highlighted fields from that record are still highlighted and when I went to select the next set of data, it wrote over the fields that were highlighted.

Is there a way to reset the form so I don't keep writn over or losing data? I created a "Refresh" button that didn't do anything.

Issue 3: Problems with Coding
I have worked with this data so much that the coding is all wrong for the file. Is there a way to run a program or something to recode the file? There are 2 problems with the coding.

1. When I open the file, I get several errors stating that a certain query could not be found or is mispelled. I have deleted all the queries, tables, etc. that I was experimenting with but it seems that there is still information pointing to those queries somewhere in the code. I tried looking but can't find it.

2. After creating a combo box, list, etc. under the properties I renamed them from the standard generic names to ones I could relate to. Now I the combo boxes,etc don't work. When I pull up the codes page, everything is still using the standard names such as "Combo72". Now that I have renamed everything, I don't know what their original name was so I could change it back. Is there away to reset these codes so that they recognize the new names?

Issue 4: Protecting Data
Is there a way to protect the data that is already entered so that it can still be selected but not edited.

Note: if there is programming that I can do to resolve these issues, please be kind to give details since programming is very new to me.

Thanks so much for your help.

 

[aleb]

Can you post your db ?

 

[pafcpilgrim]

Here some help.

Issue 1: - Yes you can

strObjectFormat = cboExportObject.Column(3, cboExportObject.Value)

Obviously replace my object names with yours.

Issue 2: -

Correct me if i'm wrong but it will overwrite because you will be on the same active record for that vendor in the table.

You would need to create a new record to add any new entries for another vendor. I can help further if this doesn't help you with this issue.

Issue 3: -

first one - you could have a subform using a query which has been deleted. Generally you will only get these errors when opening a form if a control on the form is bound to a query could be anything like a combo box, list box, etc you will just need to check the forms i'm affraid.

second one - this is common but what you need to do is find the code for each combo box and one at a time change the name to what you want it to be i.e.

Private Sub combo1_click()

End sub

to something that you want

i.e.

Private Sub cboVendors_Click()

End sub

When you have changed the name go to the control on the form right click to properties and then select event procedure for the event and it will re-locate the code for you so then everything matches up as long as the names are right fyi the control name won't need the _click bit after it just it's name cboVendors.

Next point yes you can just use the following to stope changes to any controls

form.allowedits = false

then create an update or edit button to reverse it

form.allowedits = true

then set it back by adding it to the code for the save button.

Hope this all makes sense any probs let us know.

Mike

 

[GeekyGirl]

Thank You

Mike,

Thanks for listing out the answers. It all makes sense and I'm going to go give it a try.

You are a God Send!

GeekyGirl

 

[GeekyGirl]

Do I have this right?

I just need to check if I have this correct for storing information from the combo boxes.

combo box name = ChooseVendor
Column 0 = VendorID
Column 1 = VendorName

I want to store the information in the following
Table = Product Lines
Field = VendorID
Field = VendorName

So do I enter the expression under the Properties under ControlSource?
Is this how I enter it?
strChooseVendor = cboExportChooseVendor.Column(0,cboExportVendorID, ProductLines.VendorID), (1, cboExportVendorName, ProductLines.VendorName)

My form is set up for the Product Lines table so do I still need to put the table name in the expression or would the value simply be the field name?

Thanks
Lisa

 

[pafcpilgrim]

Lisa,

The combo boxes always allow you to bound one value from it, but you can pick up using code the others. Columns stored have a reference starting at 0 not 1, which i think you have noticed.

To get what you want you are best to create a simple table called

Tbl_Vendors
-Vendor_ID (Primary Key)
- Vendor_Name
- Plus anything else you need

On your form get the combo box to pick up the table and show the information and bound it to Vendor ID.

Because of the way in which relational databases work Lisa you only need to store the Vendors ID in your other table in that record. If you then later want to get the name associated with that Vendor ID you can join the tables together in a query this saves you having to duplicate information.

If this is a little to much just say, and i can always create you a query in a dB and email it to you if you don't know what i mean.

rgds.

 

[GeekyGirl]

Used The wrong sample

I guess I used the wrong sample since the vendor ID is the key for the Vendor. Also, I understand that the column starts counting with 0.

Let me try this one.
Combo Box Name: ChoosePL
Column 0 = Product Line ID
Column 1 = Product Line Name
Column 2 = Category
Column 3 = Subcategory

My combo box is pulling this information froma table called MainPL.

After I make a choice from the pull down list, I want to save the values to a table called Product Lines. The form I am working from uses the table Product Lines.

So am I working with an expression in the expression builder or am I working with Code?

Now, in the previous message, I was told to enter this line:
strObjectFormat = cboExportObject.Column(3,cboExportObject.Value)

Do I enter this for each column as separate lines or do I string them together with a "," or ";"?

Is the term "Object" refering to the same name through out the expression so using my example would be

strChoosePLFormat = cboExportChoosePL.Column(1,cboExportChoosePL.Product Line Name)
strChoosePLFormat = cboExportChoosePL.Column(2,cboExportChoosePL.Category)

and so on.

Is this correct or am I way off base?