My problem is that the Access database linked to VB 2008 has duplicate items in several columns, which, when attached to comboboxes, are all displayed (eg. 1,1,1,2,2,2,3,3,3 instead of 1,2,3). I want to keep all records, which will be filtered down to a single part number in a text box....any ideas how to start please?

Use the "DISTINCT" predicate in your Query that populates the combo box:-
"SELECT DISTINCT PartNumber FROM PartNumbers ORDER BY PartNumber"



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thanks for your help GeoffS.

I've tried clicking on the combobox and adding the code you suggested, but it doesn't seem to work for me (errors are displayed).
I'm certain this is down to my total lack of knowledge where VB 2008 Express is concerned.

At the moment I have a Form that is connected to an Access database. There are 5 comboboxes. I've attached them to the datasource and specified the column, but they seem to act independently as well as showing all values from the database (including duplicates).

I need to only show single values in comboboxes....sorry, but I may need step by step, if possible - any help at all would be appreciated.

Thanks

aalto

aalto,
Create a Query in your Access Database using the Query designer to select the columns from the Source table that provide the data you want for the Combo Box. You will then need to change the designer "View" from the menu bar to show the actual SQL statement so that you can enter the "DISTINCT" word after the "SELECT".
Then in VB2008 set the data source of the combobox to the query.
Note - there are other ways to achieve this, but due to your admitted limited knowledge I would suggest that this is the easiest way for you at present. When you get a little more confident you can try creating the SQL Statement with the designer, but then copy that statement and assign it to the datasource of the combobox from within your code - this method is extremely useful when you need to build the SELECT statement "on the fly" based on values selected by your users.
EG - "SELECT OrderQuantity FROM Orders WHERE (OrderNumber = " & lngOrderID & ")"
lngOrderID being the Variable containing an Order Number that is input by your user.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

GeoffS, thanks very much for the help and great, it works....for the first combobox.
Below is outlined what I hope to achieve for the whole vb form:
I have an access database table with 10 columns, that is linked and setup as the datasource for my VB Form1. From the VB Data Source list, I've dragged the first 5 of the listed database columns onto the form as ComboBoxes, the next 3 are dragged on as 3 separate groups of RadioButtons (there are only 2 different entries in each of these 3 columns). The final 2 columns are placed as text boxes and will display data established by options selected from the previous boxes etc.

There are duplicate entries in all database columns - I need each ComboBox to show every database value, but only once (no duplicates). The process for using Form1 may be as follows:

ComboBox1 - select value
ComboBox2 - select value
ComboBox3 - select value
ComboBox4 - select value
ComboBox5 - select value

RadioButton1 - selected
RadioButton2 - unselected

RadioButton3 - unselected
RadioButton4 - selected

RadioButton5 - selected
RadioButton6 - unselected

TextBox1 - value displayed
TextBox2 - value displayed

When RadioButton1 & RadioButton2 are reached, based on the selection made in ComboBox4, it maybe that RadioButton1 is the only option available for RadioButton1 & RadioButton2.

The end user would not be able to add or modify database entries, I just want the Form to configure and display data based on the selected options.

Do you know if I'd need to set up individual queries in Access for each combobox required, or just setup all coulmns in the single query....or is there another way of doing this.

Thanks

aalto

aalto - It's a bit difficult to answer your question without knowing a bit more about the data you are trying to retrieve and what you are then going to do with it.
It sounds to me as if you are going to retrieve an object value that is based upon the selection of certain criteria, but you appear to be getting it all from one table which presumably lists the criteria of each object. This would explain why you are having trouble dealing with duplicate entries for each of the criteria. This is bad database design as you are storing information more than once. What you should do is create seperate tables for the criteria (which will then supply the lists in your ComboBoxes) and then store the criteria Index in your main table. It is then easy to create a query to find objects in the table that have all the desired criteria.
Maybe an example would help here :-
A hotel has a number of bedrooms, they vary according to the type of room, and the view from the room. You create a table "RoomTypes" with columns "RoomTypeID" which is your Primary Key, and "RoomType" which is a text description. Entries here might be 1,Single 2,Double 3,Twin
You then create a table "RoomViews" with columns "RoomViewID" which is your Primary Key, and "RoomView" which again is a text description. Entries here might be 1,Sea 2,Pool 3,Dustbins
You then create your main table - "Rooms" with columns "RoomID" which is your Primary Key, "RoomNumber",  "RoomTypeID", "RoomViewID"
where you store information on the rooms in the hotel - so one entry would look like :- 1,126,2,3
You see, storing the PrimaryKey numbers of the criteria takes up much less space than continually storing the Strings "Double" and "Dustbins"
Your ComboBoxes are set so that :-
ComboRoomTypes.DataSource = "RoomTypes"
ComboRoomTypes.DisplayMember = "RoomType"
ComboRoomTypes.ValueMember = "RoomTypeID"
and the same with the Views Combo.
Then when a user selects from the Combos retrieve the SelectedValue into a Variable :
lngRoomTypeID = ComboRoomTypes.SelectedValue

So to find all rooms of the selected type and a Sea View the query would be : "SELECT RoomNumber FROM Rooms WHERE ((RoomTypeID = " & lngRoomTypeID  & ") AND (RoomViewID = 1))"

I hope that helps a bit with what you are trying to achieve.



____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hello again GeoffS, thanks very much for your in-depth response. Yes, I believe that the table structure you've outlined is the correct way to proceed.
I've split the original table into 10 separate ones, within an Access database.
I have linked the comboboxes to the correct tables and they do show the correct drop down values, but at the moment act independently.
That's as far as I can understand. Sorry to be a pain, but could you please help me with the next step concerning the code/query....do I just double click on a combobox and start adding the code....it didin't seem to recognise the column names in my tables.

In my form I will then have 3 sets of radio buttons (as mentioned in my previous email). Each set has 2 buttons, which will be linked to a table - each representing 1 of 2 values in the corresponding table.....do you know how I set these up?
There is then a final text box which will reflect what's in the master table, containing all of the ID references for the previous tables. This text box will just display a number (hopefully calculated from the previous boxes etc).

Once again, your help would be really appreciated.

Thanks
aalto

Hi aalto
You need to put the radio buttons for each set into a GroupBox Control. This will automatically ensure that only one option is selected from each set. You can then take the results from each groupbox by getting the OptionButton.Checked value to determine which one has been selected.
It may help if you take a look at some of the walkthroughs on MSDN. You can learn a great deal about data access by running through these examples : -
http://msdn.microsoft.com/en-us/library/fxsa23t6.aspx


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hello again GeoffS

Thanks, I managed to workout how to set the buttons up by grouping, but again, my problem/lack of understanding is with how to use/link Code to items/databases.
I have looked at a number of examples from websites and ebooks, but they don't seem to be too relevant or specific....I would have thought that what I'm trying to do below would be quite a common setup.

Sorry if I'm repeating myself, but I'm in this situation:

I have 11 Access tables, 10 of which each have 2 columns (a unique ID keys and unique corresponding entry). In the 11th table, each column represents the ID Key from each of the previous 10 tables. There is a final column in table 11, which contains Part Numbers.

Table 11 contains all of the combinations possible - 1500 entries, which result in only 12 different Part Numbers. Therefore, there will be a lot of duplicate entries in table 11.

Each of the 1st 10 tables are linked to seperate Comboboxes, which contain the entries from the 2nd column of the 10 tables.

Table 11 is linked to a Textbox, which will just display a part number.

The idea is that when the form is opened, all entries displayed are blank. Any of the 10 Comboboxes can be selected and the option selected. Depeneding on what value is selected, will effect what corresponding values are displayed in the next selection available from another Combobox. This will go on until all selections have been made from all of the Comboboxes. Only then will a Part Number appear in the final Text box.

I've set up all tables in VB 2008 Express and can get them to display correctly in each Combobox and Textbox. The problem I have is linking the tables / Comboboxes so that each previous selection from a Combobox will limit the options available from the next Combobox.

Can anyone please help, I feel like I'm going around in circles?

Thanks

aalto

Hello again GeoffS

The kind of thing I'm trying to end up with is shown in the attached image, which illustrates versions with & without radio buttons (the first being the prefered solution). I don't know whether this helps to clarify things for anyone.

Thanks for staying in touch anyway

aalto

____________________________
Attached:

aalto - If you want to display only certain records in a ComboBox based upon the selection made in the previous ComboBox then you will need to handle the selection of the data in code. Simply connecting the control to the underlying table in the datasource will only return the full contents of the table.
You need to run code from the first ComboBox_Click Event that takes the selected value and then build the SQL Query in code (as I demonstrated in my earlier post)  for the next ComboBox. Then, open a DataSet based on that Query, and the next ComboBox.DataSource is then set to that DataSet.
To learn about fetching data into your application check :-
http://msdn.microsoft.com/en-us/library/ms171918.aspx
You may find that these articles major on using SQL-Server, but the theory is just the same for Access, the only (main) difference being that you cannot have Stored Procedures in Access.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thanks for the swift response GeoffS...I'll give what you suggest a go.

aalto

aalto - You may want to use the technique of putting all your data access code into a seperate module. This makes it easy to interact with the database without having to re-write code for every Form that needs to display data as you simply make a call to the DataModule.
As an example assume we want to connect to a dabase which includes a list of Customers that you want to display in a ComboBox, but you want to filter the list to dsiplay only those Customers whose business activity matches a user selection from a previous ComboBox -
Create a new vb module and call it DataModule.  Add code to open and close a Connection to the database, and to retrieve a DataSet of Customers.

Module DataModule     Dim cnn As Data.OleDb.OleDbConnection = Nothing     Private cnnOpen As Boolean = False     Private Sub OpenConnection()         Dim strCNN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\Program Files\MyDataProgram\MyDatabase.mdb"""         Try             cnn = New Data.OleDb.OleDbConnection(strCNN)             cnn.Open()             cnnOpen = True         Catch ex As Exception             cnnOpen = False         End Try     End Sub     Sub CloseConnection()         If cnnOpen Then             cnn.Close()             cnn.Dispose()             cnnOpen = False         End If     End Sub     Public Function CustomerList(intActivity as Integer) As DataSet         If Not cnnOpen Then OpenConnection()         Dim daCustomers As Data.OleDb.OleDbDataAdapter = New Data.OleDb.OleDbDataAdapter("SELECT Customers.CustomerID, Customers.CompanyName FROM Customers WHERE ((Customers.CustomerActivity)=" & intActivity & ") ORDER BY Customers.CompanyName", cnn)         daCustomers.ContinueUpdateOnError = True         Dim dsCustomers As DataSet = New DataSet         daCustomers.Fill(dsCustomers)         Return dsCustomers         dsCustomers.Dispose()         daCustomers.Dispose()     End Function End Module

In your Form, get the value of the users selection from the first ComboBox, then pass that to the DataModule to get the relevant set of Customers to display in the 2nd. ComboBox

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged         Dim intActivity As Integer = ComboBox1.SelectedValue         Me.ComboBox2.DataSource = DataModule.CustomerList(intActivity).Tables(0)         Me.ComboBox2.DisplayMember = "CompanyName"         Me.ComboBox2.ValueMember = "CustomerID"     End Sub

When your application closes call the DataModule.CloseConnection method to ensure that the connection is cleanly disposed.
NOTE - this code works for VB2005 - it should be the same for 2008 but I am not using that version.
Hope that helps a bit.

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hello GeoffS - good to hear from you again.

I'm experiencing a wee bit of overload here....apologies!

I will try and work through your suggestion, but it may take some time for me to understand it.

Anyway, I've attached an image of the tables I have, from the database (shown in VB dataset view)....the idea being to end up with Number from the Part table, displayed in a text box.

I'm not sure whether this clarifies what I'm trying to do....with no experience of VB!

Thanks again for your time and help.

aalto





____________________________
Attached:

Sorry to be a pain GeoffS

I feel quite stupid, but I really need to take things step by step.

With reference to previous emails, I have a form with the correct layout (combo boxes etc).
I have linked the access database as the datasource.

I can get the 1st Pressure combobox to display values from the Pressure table in the Access database.

From there, I'm a bit confused - I'm uncertain where to put code etc to generate the next combobox (based on the selection made from the 1st combobox).

Thanks

aalto

aalto - As I said before, you cannot display a selection from a table based on chosen criteria by simply setting the table as the data source for the ComboBox - you will always get all records from the table that way.
If I understand you correctly your first ComboBox is Pressure, and based on the selection you then want to display only certain entries from the Dia table - but that table does not appear to contain any field on which you would filter the selection. If the Combo is to display only certain diameters then you need a way to select the ones that you want to make available in the combo. For example, if a certain pressure can only pass through a hose with certain diameters, then you need 2 extra fields in the Dia table - MinPressureID and MaxPressureID, which would hold the corresponding PressureID's. Then using my DataModule example you would build the SQL Query for the DiaCombo as :-
"SELECT Dia.DiaID, Dia.Dia FROM Dia WHERE (Dia.MinPressureID  <= " & intPressureID & ") AND (Dia.MaxPressureID >= " & intPressureID & ")"
You pass the parameter  "intPressureID" from your form in the "SelectedIndexChanged" Event of Combo1 to the DataModule and then use the returned DataSet to set the DataSource, DisplayMember, and ValueMember of the 2nd. ComboBox in the same way as my previous example.
Apply that principle to all ComboBoxes, then at the end you would have a Function in the DataModule to which you pass all the chosen values and which would build the SQL Query to return the Part Number.
There is an alternative method to this where the Data "Module" would be a "Class" which would have Properties corresponding to the selection values, so you only pass the value once when setting the Property of the Class. Some might argue that this would be a "Better" way of doing things, but at this stage I think we need to keep things as simple as possible.
Hope that helps.




____________________________
multi-tasking - the ability to hang more than one app. at the same time.