Solved

vba access, populate listbox from SQL query,

Posted on 2009-05-05
14
3,444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

688 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