By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

SQL in VBA - populating listbox

P: 27
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
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expert Mod 10K+
P: 14,534
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

P: 27
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
Expert Mod 10K+
P: 14,534
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

P: 27
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 10K+
P: 14,534
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

P: 27
Mary, thanks a lot. Appreciate your time and help.
God bless
Jun 11 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary, thanks a lot. Appreciate your time and help.
God bless
You're welcome
Jun 11 '07 #10

100+
P: 553
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
Expert Mod 10K+
P: 14,534
  1. What is strTable for?
  2. What is the event this code is in, i.e. what is triggering it?
Jul 10 '07 #12

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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.