SQl statement in VBA

[chrisjames25]

Hi I am trying to create a listbox rowsource on the fly once a command button is pressed in a form.

My problem is i literally dont understand how to write sql into vba.

SELECT Tbl_Pot_Size.ID, Tbl_Pot_Size.PotName, Qry_VarietyFilter.PotSize_ID
FROM Tbl_Pot_Size RIGHT JOIN Qry_VarietyFilter ON Tbl_Pot_Size.ID = Qry_VarietyFilter.PotSize_ID
ORDER BY Tbl_Pot_Size.ID;

NO clue where i need to put " or ' i thought i would just wrap the whole thing in "" and sorted but not such luck.

Any advice, guidance, link to usual sources would be much appreciated.

CHeers

 

[jdraw]

What is Qry_VarietyFilter ? Suggest you post the sql.

 

[CJ_London]

try

me.lstbox.rowsource="SELECT Tbl_Pot_Size.ID, Tbl_Pot_Size.PotName, Qry_VarietyFilter.PotSize_ID" & _
" FROM Tbl_Pot_Size RIGHT JOIN Qry_VarietyFilter ON Tbl_Pot_Size.ID = Qry_VarietyFilter.PotSize_ID" & _
" ORDER BY Tbl_Pot_Size.ID"

note the spaces at the start of the last two lines

 

[Uncle Gizmo]

Look at this YouTube playlist I created which demonstrates how to create VBA code and particularly Focuses on queries as queries and then as queries in the text form:-

VBA Beginner - Nifty Access

I think you will find the last two YouTube videos in the playlist:-

VBA Beginner - VBA Queries - P1 - Nifty Access
VBA Beginner - VBA Queries - P2 - Nifty Access

most relevant to your situation. You have the added advantage of all the preceding videos, which will be particularly useful for giving you the context of the last two videos...

 

[Gasman]

You might find this database a Godsend then , at least to start with.

https://www.access-programmers.co.uk/forums/showthread.php?t=293372

Create your query the query designer and then paste into the form and convert.
Also converts the other way.