Runningan SQL statment

[lxh]

Hi There

I've written some SQL that selects a particular "Type of Project" the SQL is valid and works. Butr I'm not sure how to assign the outcome to an unbound textbox on a form. I'm thinking its the RunSQL command but I'm confused as to how.

MySQL = "SELECT ProjectDetails.ProjectType" & _
        " FROM ProjectDetails INNER JOIN TeamMemberInformation ON ProjectDetails.ProjectDetailsID" & _
        " = TeamMemberInformation.ProjectDetailsID WHERE "
        
MyWhere = "(((TeamMemberInformation.ProjectDetailsID) = " & Forms!TeamMember!ProjectDetailsID & " ))"

MyGroup = " GROUP BY ProjectDetails.ProjectType, TeamMemberInformation.ProjectDetailsID;"

MySQL = MySQL & MyWhere & MyGroup

Can any one tell me how I do this? or has anyone done it?

Many thanks
Lex

 

[Surjer]

Hi There

I've written some SQL that selects a particular "Type of Project" the SQL is valid and works. Butr I'm not sure how to assign the outcome to an unbound textbox on a form. I'm thinking its the RunSQL command but I'm confused as to how.

MySQL = "SELECT ProjectDetails.ProjectType" & _
        " FROM ProjectDetails INNER JOIN TeamMemberInformation ON ProjectDetails.ProjectDetailsID" & _
        " = TeamMemberInformation.ProjectDetailsID WHERE "
        
MyWhere = "(((TeamMemberInformation.ProjectDetailsID) = " & Forms!TeamMember!ProjectDetailsID & " ))"

MyGroup = " GROUP BY ProjectDetails.ProjectType, TeamMemberInformation.ProjectDetailsID;"

MySQL = MySQL & MyWhere & MyGroup

Can any one tell me how I do this? or has anyone done it?

Many thanks
Lex

Might try something like

Dim db as database
Dim rs as recordset
set db = current db

set rs = db.openrecordset(MySQL & MyWhere & MyGroup)


text1.text = rs("MyField")

 

[jfgambit]

Have a look at the following sample database. It utilizes the code you have and passes the results to a form or a listbox. It should help you.

http://www.access-programmers.co.uk/forums/showthread.php?t=62853

 

[lxh]

Text box rather than listbox

Have a look at the following sample database. It utilizes the code you have and passes the results to a form or a listbox. It should help you.

I've had a look, but it doesn't seen possible to pass the output to a textbox rather than a list box. Does any ideas?

 

[namliam]

dim rs as dao.recordset
set rs = currentdb.openrecordset(MySQL & MyWhere & MyGroup)
yourTextbox = rs!ProjectType
rs.close
set rs = nothing

that should work....

Regards

 

[lxh]

Thanks namliam

Fantastic that works a treat thanks namliam

dim rs as dao.recordset
set rs = currentdb.openrecordset(MySQL & MyWhere & MyGroup)
yourTextbox = rs!ProjectType
rs.close
set rs = nothing

that should work....

Regards