469,645 Members | 1,941 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

SQL in VBA - populating listbox

I am trying to populate a listbox from another listbox on a access form. My first listbox has names of tables on a linked odbc databse. I am trying to display the tuples of the table in the second listbox based on the selection of table name in the first listbox
I am new to VBA, and am trying to do that in the following manner:
Expand|Select|Wrap|Line Numbers
  1. Private Sub List0_BeforeUpdate(Cancel As Integer)
  2.     Dim strTable As String
  3.     strTable = List0.Value
  4.  
  5.     Set db = CurrentDb()
  6.     Set rs = db.OpenRecordset _
  7.                 ("SELECT * FROM " & strTable, dbOpenDynaset)
  8.     Do While rs.EOF = False
  9.         List8.RowSource = "SELECT * FROM " & strTable
  10.         rs.MoveNext
  11.  
  12.     Loop
  13.  
  14.     rs.Close
  15.  
  16. End Sub
  17.  
  18.  
on the form I have tried changing the row source property to value list, field list and table/query. With field list it displays the field name and not the data in the tuples. When I choose value list, it shows the query itself.
Can I get some help here ? thanks.
Jun 11 '07 #1
15 25574
MMcCarthy
14,534 Expert Mod 8TB
Firstly, I am going to move this to the Access forum. Then I'll have a look at answering it.
Jun 11 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
You don't need a recordset here. Also I think you should be using the After Update event. Try the following code...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub List0_AfterUpdate()
  3. Dim strTable As String
  4.     strTable = Me.List0
  5.     Me.list8.RowSourceType = "Table/Query"
  6.     Me.list8.RowSource = "SELECT * FROM " & strTable
  7.     Me.list8.Requery
  8. End Sub
  9.  
Make sure the column count property of list8 is large enough to display all the relevant columns.
Jun 11 '07 #3
Thanks a lot !!!
My previous post was the first one here, wasn't sure where to post. I will read through the guide to post my future questions at an appropriate place. Sorry for the inconvenience, and thanks again for the timely help.
Jun 11 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
Thanks a lot !!!
My previous post was the first one here, wasn't sure where to post. I will read through the guide to post my future questions at an appropriate place. Sorry for the inconvenience, and thanks again for the timely help.
Not a problem. You will find the Posting Guidelines here.
Jun 11 '07 #5
hm, can you explain your code?
Is there more than one way of using SQL in VBA ? The one which I used, I read it in the book I am following. It is slow when it comes to retrieve large number of tuples.
Can I get a link which can explain me how to use SQL in VBA? ( I have tried many sites, but couldn't find anyone for beginners.
Also, what is the difference between "after and before update" in the procedure signature?
Jun 11 '07 #6
MMcCarthy
14,534 Expert Mod 8TB
hm, can you explain your code?
Is there more than one way of using SQL in VBA ? The one which I used, I read it in the book I am following. It is slow when it comes to retrieve large number of tuples.
Can I get a link which can explain me how to use SQL in VBA? ( I have tried many sites, but couldn't find anyone for beginners.
Also, what is the difference between "after and before update" in the procedure signature?
The code you were using retrieves a recordset of tuples either from a table or a query. You would use this if you wanted to take action on certain tuples depending on their value etc. In other words it's a way of retrieving and acting on each tuple in turn.

However, you only needed to query the data and return all results.

There are some very good tutorials in the articles section of this site. You can find a full index of them here.

You would use the After Update Event when you want to get the value from the data after it is updated and the data is saved in the database.

You would use the Before Update Event when you want to validate (or otherwise) the data entered by the user just before it is stored in the database.

Mary
Jun 11 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
To get back to the code I gave you this is based on the structure of a listbox (or combobox). A listbox can have three different row source types.

Field List (Row source is table or query)
Value List (Row source is list of values separated by ';'
Table/Query (Row source is table or query)

When the row source is a query this can be a saved query in the database or a SQL statement.

In the example I have given you the row source type of the list is a "Table/Query". The row source is a sql statement.

The Requery of the listbox is necessary to update it on the form.

Mary
Jun 11 '07 #8
Mary, thanks a lot. Appreciate your time and help.
God bless
Jun 11 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
Mary, thanks a lot. Appreciate your time and help.
God bless
You're welcome
Jun 11 '07 #10
questionit
553 512MB
Hello

I have a question regarding your previous messages.

I have done the same code but my ListBox does not get its values updated at all:

Dim strTable As String
Me.List2.RowSourceType = "Table/Query"
Me.List2.RowSource = "SELECT Heating_Choice_Description FROM test"
Me.List2.Requery
End Sub

Any idea?
Thanks

You're welcome
Jul 10 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
  1. What is strTable for?
  2. What is the event this code is in, i.e. what is triggering it?
Jul 10 '07 #12
questionit
553 512MB
I have a check-boxe /tick-box. If this is checked, this should trigger the listbox to update its values.

ignore strTable, i got it from the code given in the previous messages - but no need of it at all anyway- i would delete it

  1. What is strTable for?
  2. What is the event this code is in, i.e. what is triggering it?
Jul 10 '07 #13
MMcCarthy
14,534 Expert Mod 8TB
I have a check-boxe /tick-box. If this is checked, this should trigger the listbox to update its values.

ignore strTable, i got it from the code given in the previous messages - but no need of it at all anyway- i would delete it
So have you put this code in the after update event of the checkbox?

How are you checking the value of the checkbox?

Can I suggest you post the full procedure code which will give me more information about what you are doing.
Jul 10 '07 #14
questionit
553 512MB
My code is of 100s of line. But let me explain

The purpose is to do this:

I have these values:
1- Value1
2- Value2
3- Value3
4- Value4
Now, i have 2 check/tick boxes (Called TickA, TickB).

If TickA is checked, then i should get in my Listbox values 1-3
If TickB is checked, then i should get in my Listbox value 4 only.

No matter how it is implement, i am open to ideas !!!

I can use AfterUpdate event on Tick/Check Boxes

So have you put this code in the after update event of the checkbox?

How are you checking the value of the checkbox?

Can I suggest you post the full procedure code which will give me more information about what you are doing.
Jul 10 '07 #15
MMcCarthy
14,534 Expert Mod 8TB
My code is of 100s of line. But let me explain

The purpose is to do this:

I have these values:
1- Value1
2- Value2
3- Value3
4- Value4
Now, i have 2 check/tick boxes (Called TickA, TickB).

If TickA is checked, then i should get in my Listbox values 1-3
If TickB is checked, then i should get in my Listbox value 4 only.

No matter how it is implement, i am open to ideas !!!

I can use AfterUpdate event on Tick/Check Boxes
I don't want all the code just the relevent event procedure.

What is the name of the checkbox concerned. Make sure it is the object name and not the caption.
Jul 10 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Broder | last post: by
reply views Thread by Bill Brinkworth | last post: by
6 posts views Thread by Chris Leuty | last post: by
6 posts views Thread by P K | last post: by
2 posts views Thread by NvrBst | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.