LIstbox and SQL 1

[Tronsliver]

Is it possible to progmattically attach and SQL statement to Rowsouce? For example I would like the SQL statement to select five fields form a query and use this to populate the listbox. If so an example would be appreciated.

Thanks

 

[vbajock]

Click on the little ... symbol to the right of the rowsource property. This will open the query builder, or you can just paste the Sql into the rowsource property

 

[Tronsliver]

I know it can be done via the property box but I need to do it from within a "procedure." I'll have multiple forms accessing it.

For example I'm looking for something like this

Me!ListBox.Rowsource = SQL statement

 

[vbajock]

That works too.

 

[Tronsliver]

vbaJock,

This is how I approached it but I'm getting an syntex error:

Me!ExcessListBox.RowSource = Select sidstrETS FROM qryExcess Where orgstrPrNbr = '650'

sure would appreciate your expertise

Thanks

 

[Norris68]

Are you enclosing the SQL in quotes?

Me!ExcessListBox.RowSource = "Select sidstrETS FROM qryExcess Where orgstrPrNbr = '650'"

 

[Tronsliver]

Yes I did it treated everything between the quotes as a "string" and inserted in the ist box that way.

 

[vbajock]

Your statement looks right. Here's a couple things you can do
Create a variable called StrSql

Dim StrSql as string

assign the sql to it

strsql="Select sidstrETS FROM qryExcess Where orgstrPrNbr = '650'"

put a STOP statement in

strsql="Select sidstrETS FROM qryExcess Where orgstrPrNbr = '650'
Stop
Me!ExcessListBox.RowSource = strsql

When it hits the Stop, click View, Immediate Window. Type ? strsql and look at how the computer is interpreting the string. You can cut and paste the string from the immediate window and post it back to here

 

[Tronsliver]

The compiler is interpeting it as a string and not a SQL statement. Something just isn't right with the format.

 

[vbajock]

cut and paste it so I can look at it

 

[Tronsliver]

vbaJock thanks for helping.

I'm trying something else and I think it wil work if I can figure out the formating for extending SQL code over five lines. Here is what I have but I keep getting syntex errors. If you could give me some expert advice on this I think this thing will finally work..thank you

" SELECT qryExcess.sidstrNAME_IND, qryExcess.sidstrGR_ABBR_CODE, _
& qryExcess.sidstrDUTY_POSN, qryExcess.sidstrDY_POSN_QUAL, " " _
& qryExcess.sidstrAUTH_PARA_DSG, qryExcess.sidstrAUTH_LINE_ DSG, _
& qryExcess.sidstrPMOSD_ENL , qryExcess.sidstrSMOSD_ENL, qryExcess.sidstrAMOSD_ENL _
& FROM qryExcess WHERE (((qryExcess.orgstrPrNbr)="643&quot); "

 

[vbajock]

Do it like this:
Dim Sql as string


sql="SELECT qryExcess.sidstrNAME_IND,"
sql=sql+" qryExcess.sidstrGR_ABBR_CODE, "
sql=sql+" qryExcess.sidstrDUTY_POSN,"
sql=sql+" qryExcess.sidstrDY_POSN_QUAL,"
sql=sql+" qryExcess.sidstrAUTH_PARA_DSG,"
sql=sql+" qryExcess.sidstrAUTH_LINE_DSG,"
sql=sql+" qryExcess.sidstrPMOSD_ENL ,"
sql=sql+" qryExcess.sidstrSMOSD_ENL,"
sql=sql+" qryExcess.sidstrAMOSD_ENL "
sql=sql+" FROM qryExcess WHERE (((qryExcess.orgstrPrNbr)sql=sql+" ='643')); "

Me!ExcessListBox.RowSource=sql
Note single quotes around '643'

 

[Tronsliver]

vbaJock,
If I wanted to use a variable in place of the 643 what would the difference be in the format? Reason I ask is because this number is the criteria that will be used to filter the query.

 

[vbajock]

last line:
sql=sql+" ='643')); "
would be

if the datatype of the field containing 643 is a string:

Dim varname as string
varname="643"

sql=sql+" ='"+varname+"')); "

if the datatype of the field containing 643 is numeric:
dim varname as long

varname=643

sql=sql+" ="+str$(varname)+&quot); "

(note no single quotes for numeric data)

 

[TLady]

List_Result.RowSource = "Select USERID, LNAME,FNAME from Table"

TIA

 

[TLady]

What I meant is when you populate listBox from query - do not use ME!
Format as this will do
ListBox.RowSource = "Select USERID, LNAME,FNAME from tTable"

 

[vbajock]

I think he gets that part

 

[TLady]

It doesn't hurt to remind, does it ?