[RESOLVED] Connecting Access to Combobox to List box to text box

[greatgerd]

Hi Everyone, I hope to get help here. I dont know much about programming. I am doing my final project regarding to structural Design - specifically Truss design using Eurocode 3. I were asked to make a program for the calculation using VB6 by my Lecturer.

First I want to create a program contain combo box which shows the type of table. from the combo box, user can choose the type of table from the access database and then one of the field (e.g Size of truss) from the table appeared in the list box - the list of all the truss sizes. then, user may choose the size of truss he want. after choosing one size from the list, the properties of the chosen size of truss will all in the text boxes such as txtHeight, txtwidth, txtarea and ect.

this is my attempt to get the database using ADO. I manage to let the combobox to drop down with the table NAME but fail get the Sizes of truss to be listed in the list box...

Private Cn As ADODB.Connection
Private rstSchema As ADODB.Recordset
Private strCn As String

Private Sub OpenDB()

Set Cn = New ADODB.Connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\ub2.mdb;" & _
"Persist Security Info=False"

Cn.Open strCn
End Sub

Private Sub Form_load()
OpenDB
Set rstSchema = Cn.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_TYPE").Value & "") = "TABLE" Then
Combo1.AddItem " " & rstSchema.Fields("TABLE_NAME").Value
End If
rstSchema.MoveNext
Loop

rstSchema.Close
Set rstSchema = Nothing

OpenDB
Set rstSchema = Cn.OpenSchema(adSchemaColumns)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_Name").Value & "") = Combo1.Text Then
List1.AddItem " " & rstSchema.Fields("COLUMN_NAME").Value
End If
rstSchema.MoveNext
Loop


rstSchema.Close
Set rstSchema = Nothing
End Sub

I got this code from other forum
http://www.vbforums.com/showthread.php?t=452910

The program just run as usual but when I had choose the table from the combobox (List box), nothing happened to the listbox (List1). I got stuck here and I couldn't continue with the textbox.

Please help...

[si_the_geek]

Welcome to VBForums

The problem is that you have put the code to fill the ListBox inside the Form_Load event, which is before the form is shown, and therefore also before anything can be selected in the ComboBox.

If you move that section of the code to the Combo1_Click event, it should work as intended.


On a separate note, you should not be re-opening the connection (by calling OpenDB) unless you have already closed it, and you should also ensure it is closed when your program has finished (I'd recommend in the Form_Unload event). If you don't follow that advice, you are likely to cause database corruption and/or locking issues.

The code to close it is like this:

  cn.Close
  Set cn = Nothing

One final issue is that as strCn is only used in one routine (OpenDB), it should be declared inside that routine (you will need to change the keyword Private to Dim), to reduce memory wastage and reduce the chances of bugs.

[greatgerd]

thanks for the welcome and the quick reply, si_the_geek


Here my modified coding.

Private Cn As ADODB.Connection
Private rstSchema As ADODB.Recordset
Dim strCn As String

Private Sub OpenDB()

Set Cn = New ADODB.Connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\ub2.mdb;" & _
"Persist Security Info=False"

Cn.Open strCn
End Sub

Private Sub Form_load()
OpenDB
Set rstSchema = Cn.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_TYPE").Value & "") = "TABLE" Then
Combo1.AddItem " " & rstSchema.Fields("TABLE_NAME").Value
End If
rstSchema.MoveNext
Loop

rstSchema.Close
Set rstSchema = Nothing


End Sub

Private Sub Combo1_CLICK()

Set rstSchema = Cn.OpenSchema(adSchemaColumns)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_NAME").Value & "") = Combo1.Text Then
List1.AddItem " " & rstSchema.Fields("COLUMN_NAME").Value
End If
rstSchema.MoveNext
Loop


rstSchema.Close
Set rstSchema = Nothing

  
End Sub

Private Sub Form_Unload()
Cn.Close
Set Cn = Nothing
End Sub

yup...my database had corupted. I just know why...
actually I had read about the closing OpenDB from the other forum and I had tried the Form_unload() event. but when I tried to run it, I dont know why it come out like this:

compile error:
Procedure declaration does not match of event or procedure having the same name


can you show me how to do it?


then I remove the Form_Unload() event and run it again. I still don't get the item listed in the listbox even with combo1_Click() event. is there any wrong here?

thanks for your time

[si_the_geek]

The error is referring to the line: Private Sub Form_Unload()

It is happening because Form_Unload should have specific parameters, which you haven't listed.

The best thing to do when creating a standard event sub/function (like Form_Load or Combo1_Click) is to let VB create it for you - use the drop-down lists at the top of the code window to select the object (on the left) and then the event (on the right). VB will then create the first and last lines of the routine for you, including any parameters etc.

If you do that this time, you can move the contents of your Form_Unload to the "real" one.


In terms of the list not filling, the issue appears to be the extra space you added to the combo:

Combo1.AddItem " " & rstSchema.Fields("TABLE_NAME").Value

I'd recommend removing that, but if for some reason you want to keep it, you can easily ignore leading spaces when reading it:

If UCase(rstSchema.Fields("TABLE_NAME").Value & "") = LTrim(Combo1.Text) Then

[greatgerd]

Wow GREAT
It work very well.

Private Cn As ADODB.Connection
Private rstSchema As ADODB.Recordset
Dim strCn As String

Private Sub OpenDB()

Set Cn = New ADODB.Connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\ub2.mdb;" & _
"Persist Security Info=False"

Cn.Open strCn
End Sub


Private Sub Combo1_Click()

Set rstSchema = Cn.OpenSchema(adSchemaColumns)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_NAME").Value & "") = Combo1.Text Then
List1.AddItem " " & rstSchema.Fields("COLUMN_NAME").Value
End If
rstSchema.MoveNext
Loop

rstSchema.Close
Set rstSchema = Nothing

End Sub

Private Sub Form_load()
OpenDB
Set rstSchema = Cn.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF
If UCase(rstSchema.Fields("TABLE_TYPE").Value & "") = "TABLE" Then
Combo1.AddItem rstSchema.Fields("TABLE_NAME").Value
End If
rstSchema.MoveNext
Loop

rstSchema.Close
Set rstSchema = Nothing


End Sub


Private Sub Form_Unload(Cancel As Integer)
  Cn.Close
  Set Cn = Nothing
End Sub

But What I realize is that only one of the Item out of 4 from the Combo1 will cause the list1 to show result. the other 3, just don't have any reaction. Why is that happening?

Another thing is that, from this code, the listed item in the list1 are not the "truss Sizes" (e.g 40 x 25, 60 x 30, and more) but all the column name (e.g height, width, area and more) without any number value.

actually the first column in the table are the truss sizes, the following columns is the (height, width, area and more). How to let the program to shows only a list of the "Truss sizes" (input of the truss sizes column) from the table first column instead of the all column names?

[si_the_geek]

But What I realize is that only one of the Item out of 4 from the Combo1 will cause the list1 to show result. the other 3, just don't have any reaction. Why is that happening?

It should be fine, but note that you only add items to the list - you don't remove anything that was there before (so after you have selected two items in the combo, you have both sets of results in the list).

To empty the list, use: List1.Clear
(at the start of Combo1_Click)

Another thing is that, from this code, ... How to let the program to shows only a list of the "Truss sizes" (input of the truss sizes column) from the table first column instead of the all column names?

You are getting the column names because that is what you specifically ask it for - to get data, you will need to use an SQL statement (specifically a Select)to open the recordset.

To do that, build your SQL statement in a string, and replace the "Set rstSchema" line with these two:

Set rstSchema = New ADODB.Recordset
rstSchema.Open strSQL, cn

Note that ideally you should specify values for the other parameters too, as shown in the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of this forum)

If you don't know what your SQL statement should be (or how to build it), take a look at the "SQL" section of the Database FAQs.

[greatgerd]

I forget to tell you. I am using Microsoft Access 2007...but I convert the database to MS 2000 which have extension of *.mdb

[greatgerd]

it working for the List1.clear.
but actually the problem is, I got 4 types of table, name - "Unequal Angles", "Equal Angles", "Channels" and "Circular". the program only react to give value when I choose "Channels". The others-No. this is Regardless of the turn they were chosen. Only item Channels will give result wether i click it the first time or later. Why is it? is it because of the database problem-corupted?

[si_the_geek]

If you aren't getting errors, the problem is either that there is no data in those tables, or that your IF statement isn't finding a match.

I suspect the latter, as you are checking the upper-case version of the table name against the 'normal' version of the combo text.. it would be best to compare upper-case against upper-case (as that way any differences in case will be ignored).

[greatgerd]

Hmm... i had made new data base now. and every item in the combo1 work as coded now.

[greatgerd]

Hi,
i managed to put the data in the column from table into list1 after choosing the item from combo1. I use

Set rstSchema = New AD0DB.Recordset
StrSQL = " Select TrussSize from EqualAngle"
rstSchema.Open StrSQL, cn, adForwardOnly, adLockReadOnly, adCmdText

but this code only react for Table EqualAngle...
what should be written to allowed all Table which are the item in combo1 to react when chosen?

HuH...
i'm using mobilephone to online now,
very hard to type all this.

[si_the_geek]

This should do it:

strSQL = " Select TrussSize from [" & Combo1.Text & "]"

(the square brackets are to avoid problems with 'bad' table names).

[greatgerd]

great, it work!

how bout this one,

Private Sub List1_Click()
List.clear
Set rstSchema = New ADODB.RecordSet
StrSQL = "Select Height, Width, Area From (" & combo1.text & ") Group by (" & List1.Text & ")"

it goes wrong, how to use the 'group by' clause to choose any truss size from the Truss Size column of the table?
this is to select the Width, Height and Area of the chosen row item : the selected TRUSS SIZE.


one more, ìs this correct, i was tryin' to let the width, height and area of the chosen Truss size in List1 to be inserted into the textbox

 Do Until rstSchema.EOF

Text1.text rstSchema.Field("Width")
Text2.text rstSchema.Field("Height")
Text1.text rstSchema.Field("Area")
rstSchema.MoveNext
Loop

[si_the_geek]

Don't use brackets around table names (you need square ones as I showed), or around values (what you need varies, see this FAQ article).

You don't want a Group By, what you want is a Where clause, eg: "... FROM tablename WHERE fieldname = value"

one more, ìs this correct, i was tryin' to let the width, height and area of the chosen Truss size in List1 to be inserted into the textbox

Two problems there... first of all, there is no point using a loop or the .MoveNext that goes with it (you only want to read one record), but you should make sure that there is at least one record to read (If Not rstSchema.EOF Then ... End If ).

The other issue is that you have left out the = when assigning the values to the textboxes.

[greatgerd]

it's my mistake, i type wrongly () instead of []. i'm typing using mobile keypad then. thanks

Private Sub List1_Click()
Set rstSchema = New ADODB.RecordSet
StrSQL = "Select Height, Width, Area From [" & combo1.text & "] Where TrussSize =  [" & List1.Text & "]"

rstSchema.Open StrSQL, Cn,AdFowardOnly, adLockReadOnly, adCmdText

If not rstSchema.EOF Then
 
Text1.text = rstSchema.Field("Width").value
Text2.text = rstSchema.Field("Height").value
Text1.text = rstSchema.Field("Area").value
end if

the WHERE clause work perfectly.

but

Run-time error '-2147217904(80040e10)':

No value given for one more required parameters.


and when i mouseover line 'if not rstSchema.EOF then' in the code, it show this...

rstSchema.EOF = <Operation is not allowed when the object is close.>

how to solve this

[si_the_geek]

The problem is that you aren't using the right markers around the value (in this case, List1.Text), see the article I linked to in my previous post.

Also note that AdFowardOnly is a typo.

[greatgerd]

I put it this way


"Select ... From ... Where TrussSize = '[" & List1.Text & "]'"

No error come out, but there's no result in the textbox.

i had put
text1.text = "" and other textboxes to clear the content right after the private sub List1_Click()

is the marker i put between value is wrong?

[dee-u]

You need to remove the brackets in your sql statement.

[greatgerd]

Cool...
Thanks A Lot

[dee-u]

Now that we've helped you, you can help us by pulling down the Thread Tools menu and selecting the Mark Thread Resolved item which will let everyone know that you have your answer. Also if someone has been particularly helpful you have the ability to affect their forum "reputation" by rating their post. Only those ratings that you give after you have 20 posts will actually count, but in all cases the person you rate will see it and know that you appreciate their help.