• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3707
  • Last Modified:

vba access, populate listbox from SQL query,

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
lhl60
Asked:
lhl60
  • 8
  • 5
1 Solution
 
jruheCommented:
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
 
lhl60Author Commented:
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
 
mbizupCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
mbizupCommented:
Also, what does the function Reset_Edit_Fields do?  

Does it affect the rowsource in any way?
0
 
mbizupCommented:
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
 
lhl60Author Commented:

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
 
mbizupCommented:
<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
 
mbizupCommented:
<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
 
lhl60Author Commented:


<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
 
mbizupCommented:
<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
 
mbizupCommented:
<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
 
lhl60Author Commented:
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
 
lhl60Author Commented:
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
 
mbizupCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now