query result into variable/textbox

[Linde]

Hai everybody,

I hope one of you can help me.
I'm trying to put a single column and single row result of an query into a textbox or string variable.

I used the code below but getting error messages and can't get it to work, Please help me..

Sub test()
Dim frst As DAO.Recordset
Dim sqlstring As String
sqlstring = "select vv.date as test from vee vv"
frst.OpenRecordset sqlstring
T_VERSIE.SetFocus
T_VERSIE.Value = frst
End Sub

In this Code T_VERSIE is a textbox.
I made it into a macro to test it, but I wil use it in a form. It's probely an easy problem but I need some help.

Harm

 

[HiTechCoach]

Try something like this:

Sub test()
Dim frst As DAO.Recordset
 Dim sqlstring As String

 sqlstring = "select vv.date as test from vv" 

set frst = CurrentDB.OpenRecordset sqlstring 

Form![YourFormName].T_VERSIE = frst!test  

End Sub

Note that the about code will return a recordset of potentially many records since there is not a Where clause. The code will return a value from the first record.

Or use a Dlookup()

 

[Linde]

Thanks,

Tryed your reaction but I'm getting a Syntax error from my access 2003.
It can't be the query result because I added a where clause with one row as a result.
wil try the other option, but I must be doing somthing wrong, anny idee of the place to look for it?

Harm

 

[HiTechCoach]

Please post your current code.

 

[Linde]

Hello Hitechcoach,

I used your code and changed it to:

Sub test2()
Dim frst As DAO.Recordset
 Dim sqlstring As String
 sqlstring = "select vv.date as test from vv where vv.id=" & Me.KL_VERSIE.Value
set frst = CurrentDB.OpenRecordset sqlstring
Form!["Ravcode bepaling"].T_VERSIE = frst!test
End Sub

I'm getting a syntax error on the "set frst = curre......"
Me.KL_VERSIE stands for a listbox. De query that is created is complete But The sub is stopt even before creating this query.

Hope this is the information your looking for.

Harm

 

[boblarson]

You're missing a set of parentheses:

set frst = CurrentDB.OpenRecordset(sqlstring)

 

[Linde]

Thanks Boblarson,

I tryed it and getting the error:
error 3061,
to few parameters, accpected ammount:1

query= "select vv.date as test from vee_versie vv where vv.id=2749"
so this is giving 1 record, in 1 column.

Anny idee?

Harm

 

[Linde]

Shenuan,

What are these links?
NOt access pages I believe

 

[boblarson]

Thanks Boblarson,

I tryed it and getting the error:
error 3061,
to few parameters, accpected ammount:1

query= "select vv.date as test from vee_versie vv where vv.id=2749"
so this is giving 1 record, in 1 column.

Anny idee?

Harm

Yeah, your select statement is not correct. Does this query pull from a table or from another query? Also what is the table/query name and you should not have a field named date (Access reserved word).

 

[HiTechCoach]

I am think kile Bob that the source vv or maybe vee_versie is a query that has form references.

It appears that you have two data source for the query (vv and vee_versie) but you are only returning fields from vv) . Is this correct?

If so, then try add the Eval() to the form reference like this:

Eval("foms!YourFormName.YourControlName")