Solved

vba access, populate listbox from SQL query,

Posted on 2009-05-05
14
3,310 Views
Last Modified: 2013-11-28
Hi There
I'm a relative novice to VBA

I'm trying  to fill a list box, from a ODBC mysql db
I want to set the Rowsource to the query.
The Rowsourcetype is set to table/Query
I had it working just as intended , but then I added some extra text to the SQL statement, and suddenly. the query string is quietly cut of, as if there is a max length of VBA strings.

the sql  string is striped to
"SELECT program.id, FORMAT(Program.Dato,'DD/MMM-YYYY') as Dato, FORMAT(Program.Tid,'HH:MM') as Kl, Program.Titel, Program.Pris, Censurliste.Censur, Speciel.speciel FROM Program,speciel INNER JOIN Censurliste ON Program.censurid = Censurliste.ID INNER


Can any body see what's going on, what don't I know about VBA ?
btw: executing the full statement manually, works, so the SQL syntax is ok but VBA is doing something beyond my understanding.



'program_liste, Årliste  and måned_liste are listboxes on my form
 

Public Sub update_program_liste()

Dim sql As String

Dim monthid As Integer

Dim yearid As Integer
 

    

yearid = Me.Årliste

monthid = Me.måned_liste
 

sql = "SELECT program.id, FORMAT(Program.Dato,'DD/MMM-YYYY') as Dato, FORMAT(Program.Tid,'HH:MM') as Kl, Program.Titel, Program.Pris, Censurliste.Censur, Speciel.speciel " & _

"FROM Program INNER JOIN Censurliste ON Program.censurid = Censurliste.ID " & _

"INNER JOIN speciel ON Program.specialID=speciel.Id " & _

"WHERE (((Program.yearID)= " & yearid & ") AND ((Program.månedID)= " & monthid & "));"
 
 

program_liste.RowSource = sql

program_liste.Requery

reset_edit_fields

End Sub

Open in new window

0
Comment
Question by:lhl60
  • 8
  • 5
14 Comments
 
LVL 4

Expert Comment

by:jruhe
ID: 24303987
I don't know what the max length of a string is but it seems it cut off at a very suspicious 250 characters.  Here are two things to try:

Don't use sql.  just say

program_liste.rowsource = "SELECT program.id, FORMAT(. . .

If that doesn't work, you may need to shorten the string length.  You don't need to mention the table name if the field name is unique.  So for example, take "FORMAT(Program.Dato, 'dd/mmm-yyyy')".  If Dato doesn't exist in Censurliste, you can just use "FORMAT(Dato, 'dd/mmm-yyyy')"

Let us know how it goes,

Joy
0
 
LVL 4

Author Comment

by:lhl60
ID: 24304082
you are on the right track.

I tried what your suggested, and stripped the table name from the unique fields.
But still the resulting string is stripped to exactly the same length. Suspicious !!!

Is there really a string length limitation in VBA?

Do you know of a way to use a predefined Query, and still be able to manipulate the variables in the select string ?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24304258
Is there additional code/ functionality that you have not described here?

For example, is this string being witten to a table at any point?

I'm asking, because I modified your code, hard-coding Year and Month ID, and it ran fine.

I added a debug.Print statement, which shows the full SQL string, and it's length (with my hard-coded values, that is 347).   It sets the rowsource of a listbox I set up on a sample form just fine, maintaining all of the characters.

I'm using Access 2003, and it seems like something else (not string length) is at issue.

I've posted my modified version in the code snippet.

At what point and how do you notice that the data has been truncated?


Public Sub update_program_liste()

Dim sql As String

Dim monthid As Integer

Dim yearid As Integer

 

    

'yearid = Me.Årliste

'monthid = Me.måned_liste

 

sql = "SELECT program.id, FORMAT(Program.Dato,'DD/MMM-YYYY') as Dato, FORMAT(Program.Tid,'HH:MM') as Kl, Program.Titel, Program.Pris, Censurliste.Censur, Speciel.speciel " & _

"FROM Program INNER JOIN Censurliste ON Program.censurid = Censurliste.ID " & _

"INNER JOIN speciel ON Program.specialID=speciel.Id " & _

"WHERE (((Program.yearID)= " & 2008 & ") AND ((Program.månedID)= " & 3 & "));"

 

 Debug.Print sql & vbCrLf & Len(sql)

 

 

'program_liste.RowSource = sql

'program_liste.Requery

'reset_edit_fields

Forms!frmMain.lstBox.RowSource = sql

End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 24304269
Also, what does the function Reset_Edit_Fields do?  

Does it affect the rowsource in any way?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24304454
Btw, in answer to your question about limitations:

String: About 64K characters
Rowsource or Recordsource Property: 32K characters

I got the info about rowsource/recordsource properties from here:
http://www.databasedev.co.uk/access_specifications.html

There are further limitations regarding SQL strings, but unless this query is MUCH bigger than what you have shown here, I don't think you are hitting those limits.
0
 
LVL 4

Author Comment

by:lhl60
ID: 24304603

thanks.
I didn't know the debug.print, being used to c++ I was looking at the watch window that apparently have some limitations.

The immediate window show a valid sql statement, that works,  when used manually

So string stripping is not my problem

But my list box is still not updated.
I tried a request that for sure yields a 2 row result with 7 fields,, but nothing is shown in my list box.
I guess this is an other question but do you have any ideas?
Reset_Edit_Fields just initialize some text boxes and doesnt touch the listbox



0
 
LVL 61

Expert Comment

by:mbizup
ID: 24305049
<The immediate window show a valid sql statement, that works, when used manually>

What happens when you copy/paste that SQL into the rowsource property of the listbox?

<But my list box is still not updated.>
My first guess is that the conditions specified in the SQL criteria (WHERE and Join Clauses) are not present in the data - producing no records as a result.

<I tried a request that for sure yields a 2 row result with 7 fields>
But if you tried this using the query as produced by this code, then the problem is likely to be something else.

Where have you placed this sub (is it in your form's code or a seperate module?), and how/where are you calling it?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 61

Expert Comment

by:mbizup
ID: 24305078
<I guess this is an other question >

And don't worry about that at this point.  IMO,  this is the same question "what is wrong with the code that populates the listbox"?
0
 
LVL 4

Author Comment

by:lhl60
ID: 24305239


<What happens when you copy/paste that SQL into the rowsource property of the listbox?>

When I copy it manually to the list box property, it complains that there is a
"syntax error because there is a missing operator in the request "program.censurid= censurliste.Id INNER JOIN speciel ON program.special.ID= speciel Id"


<I tried a request that for sure yields a 2 row result with 7 fields>
<But if you tried this using the query as produced by this code, then the problem is likely to be something else.>

I guess so 1 it works in the Mysql Query browser and yield two rows


Where have you placed this sub (is it in your form's code or a seperate module?), and how/where are you calling it?

It place in the form code in the general section, it's called from the program_liste.click() handler
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24305407
<program.special.ID= speciel Id>

Try the copy/paste again...  It looks like the periods are mixed up in what you posted.  this would definitely give you an error.  As far as I can tell, the output from the code for that part should have been:

program.specialID= speciel.Id
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24305438
<I guess so 1 it works in the Mysql Query browser and yield two rows>
Do you get the same results from the Access query builder?
0
 
LVL 4

Author Comment

by:lhl60
ID: 24305568
I guess the mising "."  was just a typo here, it is present in the request

the Query builder have the same complains

SELECT program.id, FORMAT(Dato,'DD/MMM-YYYY') as Dato, FORMAT(Tid,'HH:MM') as Kl, Titel, Pris, Censurliste.Censur, Speciel.speciel FROM Program INNER JOIN Censurliste ON Program.censurid = Censurliste.ID INNER JOIN speciel ON Program.specialID=speciel.Id WHERE (((Program.yearID)= 16) AND ((Program.månedID)= 5));


it claims there is a syntax error :
 Program.censurid = Censurliste.ID INNER JOIN speciel ON Program.specialID=speciel.Id

Maybe I need some kind of separators ,.() or something ?


0
 
LVL 4

Accepted Solution

by:
lhl60 earned 0 total points
ID: 24305685
Got it working....
I played a little with the query builder.
ands got the followin gworking result

SELECT program.id, FORMAT(Dato,'DD/MMM-YYYY') as Dat, FORMAT(Tid,'HH:MM') as Kl, Titel, Pris, Censurliste.Censur, Speciel.speciel FROM (Program INNER JOIN Censurliste ON Program.censurid = Censurliste.ID) INNER JOIN speciel ON Program.specialID=speciel.Id WHERE (((Program.yearID)= 16) AND ((Program.månedID)= 5));

note the  (..) after FROM to the 2.nd JOIN, a little difficult to see why that should be necessary ???
also the Dato AS Dato  created some  circular stuff, (understandable)
thanks a lot for all your help.
I like this forum, quick a qualified responses

-Lars


0
 
LVL 61

Expert Comment

by:mbizup
ID: 24306785
Good job...

I should have spotted that too.

I haven't worked with MySQL, but Access is picky about parentheses when your SQL statement has multiple joins.

Since you posted an independent answer, you can close the question by accepting your own post.

EE's guidelines for this are in the help pages:
http://www.experts-exchange.com/help.jsp?hi=407

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now