Textbox bound to SQL?

[ppataki]

Dear All,

Is it possible (and if yes how) in Access to have a textbox that displays the result of an SQL string? (the result is one field only)

I tried it with a combobox and it works fine, the only problem is that I have to select the value manually from the dropdown list
So an alternative solution would be to somehow enable the combobox to display the result of the SQL string automatically without choosing it manually from the dropdown

Could you please advise?
Many thanks in advance!

 

[Galaxiom]

Use VBA to set the value of the textbox.
A lot can also be achieved by dexterous setting the Control Source for the text box.

 

[ppataki]

Could you please specify how I can set the value of the textbox in VBA or with the other method you mentioned?
(I know basic VBA but I dont know how I can set the value as SQL)
Thank you

 

[JANR]

Would DAO recordset work?

Aircode:

Dim rs as DAO.Recordset
Dim strSQL as String
strSQL = "SELECT....."
set rs = Currentdb.OpenRecordset(strSQL)
    With rs
       Me!sometextbox = rs.field("somefield")
    End With
rs.Close
set rs = Nothing

JR

 

[ppataki]

This should work but when I use the below code I get an error message: "Expected end of statement" for the SQL part
Could you please help?

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Avg([Result]/[turnover]) AS Expr1 FROM tbl_AnnualData WHERE (((tbl_AnnualData.CustName)=[forms].[frm_main].[custname]) AND ((tbl_AnnualData.YearData)=DMax("[yeardata]","[tbl_annualdata]") Or (tbl_AnnualData.YearData)=DMax("[yeardata]","[tbl_annualdata]")-1 Or (tbl_AnnualData.YearData)=DMax("[yeardata]","[tbl_annualdata]")-2));"
Set rs = CurrentDb.OpenRecordset(strSQL)
   With rs
 Me!Text25 = rs.Fields("expr1")
   End With
rs.Close
Set rs = Nothing

Thank you

 

[DCrake]

For a start you have not broken up your syntax into readable script, you need concatenate the scring to get the falues form the form controls, along the lines of

X = "Select [Field] From Table Where [Field]='" & Me.Textbox & "' AND [Field] = " Me.Textbox

David

 

[ppataki]

I modified it a bit as you recommended:

SELECT Avg([Result]/[turnover]) AS Expr1 FROM tbl_AnnualData WHERE (((tbl_AnnualData.CustName)=[forms].[frm_main].[custname]) AND ((tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "') Or (tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "')-1 Or (tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "')-2));

Now I get an error message that field "|" cannot be found
Any suggestions please?
Many thanks

 

[DCrake]

SELECT Avg([Result]/[turnover]) AS Expr1 FROM tbl_AnnualData WHERE (((tbl_AnnualData.CustName)=[forms].[frm_main].[custname]) AND ((tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "') Or (tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "')-1 Or (tbl_AnnualData.YearData)=DMax('" & [YearData] & "','" & [tbl_annualdata] & "')-2));

You cannot do a select to retrieve the results into a textbox. You would need to use DLookup. Suggest you create a query that contains the avg and other fields without any conditions then use the DLookup applying the where conditions there.

David

 

[ppataki]

I did what you sugegsted and it works fine now!
Many thanks!