Executing a query in a module & passing the result to a textbox

[thewizz]

hi I would like to know if it is possible to execute a query in a module(AfterUpdate Event) & passing the result in a textbox as a default value.

if any body can help it'would be very welcommed

thewizz

 

[RoyVidar]

Hi!

Using DAO (Access 97 or if Access 2000+ set a reference to Microsoft DAO 3.N Object Library (N representing a number, probably 6))

[tt]dim db as dao.database
dim rs as dao.recordset
set db=currentdb()
set rs=db.openrecordset("qryMyQuery",dbopenforwardonly)
if not rs.bof then
me!txtControl=rs!OneOfTheFieldNames
end if
rs.close
set rs=nothing
set db=nothing[/tt]

If select statement, perhaps something like this (two new lines, replace the set rs=... line):

[tt]dim sSql as String
sSql="Select max(MyField) as MxMyField from tblSomeTable"
...
set rs=db.openrecordset(sSql,dbopenforwardonly)
...[/tt]

This should anwser the topic, I think, but it wouldn't be a default value, just a "current" value, changed whenever the After Update event fires.

HTH Roy-Vidar

 

[PaulBricker]

I'll assume that the field is a text field

Dim qdf as DAO.QueryDef
Dim rst as DAO.Recordset
Dim strSQL as String
strSQL = "Select TableName.FieldName From TableName" 'with a Where clause if you need it
Set qdf = CurrentDb.CreateQueryDef("",strSQL)'the zero length string creates a temporary querydef
Set rst = qdf.OpenRecordset()
If Not rst.EOF Then
rst.MoveFirst
End If
Forms!Formname!TextboxName = rst!FieldName
Set rst = Nothing
Set qdf = Nothing
You could use
Forms!Formname!TextboxName.DefaultValue = rst!FieldName
but this would not put the value in the field, it would just set the default value to rst!FieldName.
If you use the first expression, it works just like the default value.


Paul

 

[thewizz]

works!!!
sorry roy it didnt work but thanks As For mr. Paul Briker Great thanks !!


IT WORKS !!!
it settles the tread i think
thank you for your time

thewizz