Combo Box problem

[jspe377522]

Hi

I would like to know if, and if so how, I can stop users picking options in a combo box is another combo box has the same option selected.

For instance: I have the first combo box cboMachines which is a simple machines list, there are 20 other comboboxes cboAccessoryID1 - 20. When a user picks a machine in cboMachines only the accessories for that machine type are available in the cboAccessory boxes. I have done this with VB. I want the users to pick say Accessory 1 in cboAccessory1 then either it not be available in cboAccessory2 or have an error message displayed if they try to select it.

I hope i have explained this enough.
Cheers
James

 

[FoxProProgrammer]

Can you please explain why you have twenty combo boxes for accessories and why duplicate accessories can exist in the combo boxes. It sounds like there might be a way to eliminate duplicate accessories from appearing in the combo boxes by using an appropriate query, but I can't tell for sure unless I understand the situation better.

Thanks,

dz

 

[jspe377522]

Hi

The maximum amount of accessories available on some machines is 20. My boss doesnt want them sectioned off by type of accessory (the man is strange). He just wants to be able to pick the accessories from the lists. So i want to stop people picking the same accessory twice.

Cheers
james

 

[Gazer44]

Comments noted but wouldn't it be FAR easier to have just 2 combo's with the second one showing ONLY records based on what was selected in the first one !

If you copy the form, change it to this and then show your boss, he may agree !!

(can help with this if needed)

 

[FoxProProgrammer]

Hi James,

I must admit that I still don't understand why you need 20 combo boxes. Wouldn't this work better with two combo boxes? The second combo box would display only the accessories that are applicable to the machine selected in the first combo box. You can make the second combo box multi-select so the user can select more than one accessory in the second combo box if they want. You can make the second combo box display only the records applicable to the first combo box by setting the Rowsource of the second combo box to a query that has a WHERE clause. You would store a string in the Rowsource similar to below:

"SELECT DISTINCT accessories.accessory1, accessories.accessory2, ...etc... FROM accessories WHERE machine = '" & machineCbo.Value & "'"

In this example, machineCbo is the name of the first combo box. Note that you have to enclose the value of machineCbo in single quotes. Accessories is the name of the table that contains the accessories. Accessory1, Accessory2, etc. are the names of the fields that contain the accessories. Instead of spelling out each field, you could use * to retrieve all fields if you are retrieving all fields in the table. Also, you will need a JOIN clause if your tables are related, which they probably are. The easiest way that I have found to build the correct query string is to build the query with the query designer, select SQL view, and cut/paste the SQL to your code. Alternatively, you can name the query in the query builder and use that name in your code.

Do you think this method will work for you?

dz

 

[jspe377522]

Ok

My boss is well how can I say a very stubborn person. Thats the nice way to put it. I myself am quite new to access from a developing point of view. Originally i set up a simple Lotus Notes database that displays customer name and address etc, it shows the machine type and i want it to show all the accessories that have been fitted to a machine. That is what i am trying to achieve. However it must be visible on the form once the data has been entered without users going in to the combo boxes. I hope this explains it a bit better.

Cheers
James

 

[Gazer44]

What about using one combo box to auto fill a load of text boxes showing the required data ??

 

[jspe377522]

Yup

Sounds ok to me. Could it fill one big text box? then seperate each accessory with a comma or put each accessory on a new line in the text box? Any help would be most appreciated once again.

Cheers
James

 

[FoxProProgrammer]

Instead of using 20 text boxes, why don't you use a list box that is populated with a query similar to the one that I provided earlier? A list box would show all the accessories for a machine, one accessory per line. If the number of accessories exceeds the length of the field, Access will automatically give you scroll bars. Is this what you are looking for? A form with 20 text boxes doesn't sound like a very good way to go if I understand what you are trying to do. If a machine only has 5 accessories, 15 of the text boxes would be empty and you are wasting a lot of space on the form with useless fields.

dz

 

[jspe377522]

Ok am i just being stupid here. The form needs to show which machine a customer has or is getting. Also on the form it must be visible to lets say a read only user without clicking on the list box to see all the accessories we have fitted to the machine.

yes in some cases it can be 5 but in some cases it really can be 20.

How would you go about it from scratch? Maybe that will show me more about what you mean.

Cheers for this
James

 

[FoxProProgrammer]

You can make a list box read only if you want. All you have to do is lock it. I don't understand why there is a problem with a list box. Here's the scenario that I think will work best based on my understanding of your issue. The form will have a combo box and a list box. The combo box will be populated with machines. The user clicks a machine in the combo box and the accessories that are applicable to that machine are displayed in a list box. If the user wants to select a subset of accessories that defines another configuration of that machine, you can make the list box multi-select, and the user can select the custom configuration that they want. Then, you can use that information to display the machine and accessories that they selected, if you want. I am having trouble understanding what you are trying to do. Is this close?

dz

 

[jspe377522]

Spot on my good man. Now that sounds great. How about this. I want to have this database multiuser. So only 1 user can change the config of a customers machine.

can i have on one form the combobox and list box which is accessable only by the 1 user. then on another form a summary of that customers machine that gets populated by the combo and list box form?? Does that make sense?

Cheers
James

 

[FoxProProgrammer]

Now you've confused me again! lol

Is the data in the list box going to be used to select a configuration of a machine OR display a customer's configuration of an existing machine? Two different situations. You'd want to keep these functions separate, although the forms will look similar. The difference will be in the queries that display the information. In the case of selecting the configuration of a new machine, you would want to display all possible accessories for the user to choose from. In the case of displaying the configuration of a customer's machine, you would want your Query to select only those accessories used in each customer's machine. It will take at least one more table to do this...a table that relates to the accessories table to store each customer's particular configuration. The Query for the list box that the user will use to BUILD a new configuration will come out of the accessories table. The Query for the list box that the user will use to DISPLAY an existing configuration will come out of the customers table. Is this making any sense?

dz

 

[jspe377522]

I want to do exactly that. and yup its starting to make sense. I'm Just not sure where to start with the code and relationships etc. Don't suppose you could stear me in the right direction.

Cheers
James

 

[FoxProProgrammer]

Well, I don't know how to steer you in the right direction because I don't know what you've done so far. Have you created any tables yet? Based on what I know so far, I'd create three tables:

Machines: This table will contain [Machine ID] and [Machine Name], at a minimum with [Machine ID] being the primary key. You can also include other data that is specific to the machine such as a description, etc.

Accessories: This table will contain all accessories for any machine. You will relate the accessories that are applicable to a specific machine via a foreign key to the Machines table [Machine ID] field. This table will also contain an [Accessory ID] field (primary key) that will be used to relate to the Customers table, described below, and an [Accessory Name] field. You can also include other data for each accessory in this table such as a description, metal it is made out of, or whatever you track...

Customers: This table will relate to the Accessories table via a foreign key to the [Accessory ID] field. It can also contain information about the customer such as their name, address, phone number, point of contact, etc.

Now that you have the tables and relationships set up, let's take a look at the form.

Create a form with a combo box and list box like we discussed earlier. The form could have two buttons...one to create a new configuration, and one to display an existing configuration. If the user clicks New Configuration, you would set the Rowsource of the list box to a Query similar to the one below:

SELECT Accessories.[Accessory ID], Accessories.[Accessory Name] FROM Accessories WHERE Accessories.[Machine ID] = Machines.[Machine ID] ORDER BY Accessories.[Accessory Name]

You would make the list box two columns and hide the first column, which contains the [Accessory ID] by setting its width to 0.

If the user clicks on the Existing Configuration button, you could display a popup form where the user selects the Customer's name, or machine name, or however you want to identify the machine. Then use that information in the WHERE clause of a Query to populate the combo box and list box. If you need help with it when you get that far, check back and I'll try to help further.

dz

 

[jspe377522]

done that.HELP!!!! hehe

I have got to greating the query in rowsource of the list box as you stated and me is a wee bit lost from there.

Cheers
James

 

[FoxProProgrammer]

Without seeing your tables, fields, forms, and control names, I couldn't write your query and have any confidence that it would work. If you post the names of the tables, fields, forms, and controls, I'll try to help. I have found it helpful to create Queries with the Query Builder. You can test it by changing to Datasheet view to see if it is pulling the right information. Once the Query is working, either save it and refer to that name in your VB code, or change to SQL mode and cut and paste the SQL into a string variable in your VB code.

dz

 

[jspe377522]

Ok seems like all is going well. Except. When i go into the form I get a dialog box that says Enter Parameter Value, if i put in a machine id number the list box gives me the correct accessories for that machine. basicly the combo box isnt working i think. Would you agree?

 

[FoxProProgrammer]

Yes, it can't find the data for the WHERE clause of your Query. If you set your Machines table up with a [Machine ID] field and [Machine Name] field, and are relating [Machine ID] to the Accessories table via [Machine ID] in the Accessories table, you will need to create a two-column combo box that includes both [Machine ID] and [Machine Name]. Set the width of column 0 (the ID) to 0 so it doesn't show.

Then put this in the WHERE clause of the Query:

WHERE Machines.[Accessory ID] = comboboxname.Column(0)

If that doesn't work, please post your code, query, and any other information that I can use to help you out. I am having trouble seeing what you have there.

dz

 

[jspe377522]

have you an email address i can send you the data base