How do I return a query result to form fields (1 Viewer)

[cursedeye]

I'm new to VBA

Dim db As Database
Set db = CurrentDb
Dim qry As QueryDef
Dim sSQL As String


db.QueryDefs.Delete "temp"


sSQL = "SELECT WorkOrder.FinishDate " & _
"From WorkOrder " & _
"WHERE WorkOrder.WorkOrderID =" & Me.WorkOrder

Set qry = db.CreateQueryDef("temp", sSQL)
me.Date = DoCmd.OpenQuery qry.Name

I just want to return the result to a field called Date

how do I do it?

 

[cursedeye]

I tried this as well, didn't work neither

Dim sSQL As String

sSQL = "SELECT WorkOrder.FinishDate " & _
"From WorkOrder " & _
"WHERE WorkOrder.WorkOrderID =" & Me.WorkOrder


me.Date = DoCmd.OpenQuery sSQL

 

[SOS]

This is one way (of several):

Dim sSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
Set db = CurrentDb
 
sSQL = "SELECT WorkOrder.FinishDate " & _
"From WorkOrder " & _
"WHERE WorkOrder.WorkOrderID =" & Me.WorkOrder
 
Set rst = db.OpenRecordset(sSQL)

me.Inspection_Date = rst!FinishDate
 
rst.Close
Set rst = nothing

 

[Khalid_Afridi]

Deleting of an object if it is not exist will also create an error. so SOS has given you the perfect reply.

Open a recordset with a Select statement and then get the field value in your control on the form.

me.Inspection_Date = rst!FinishDate

 

[vbaInet]

Also the DLookup() function is sufficient for this task.

 

[Khalid_Afridi]

personally i dont like the DLookup, in many cases it is very slow rather to get the data using select statement. on large data its performance is very slow.

I change all the DLookups from my database and use the Select Statement instead

 

[vbaInet]

I'm aware, I don't use them.

Most of the time you will find that posters on this forum are creating a small database. The DLookup's performance wouldn't be noticeable on relatively small dbs.

 

[Khalid_Afridi]

But the good way is to give somebody is the good comprehensive and perfect advice...

 

[vbaInet]

These functions were built to be used. It's up to the OP to decide which route he wants to follow. The options have been laid out. From using both options the OP would be able to see for him/herself which way is optimised (or if you like, faster) than the other. The DLookup is useful in so many ways.

 

[Khalid_Afridi]

...but not in my way
I am dealing with more than 2,50,000 records using Ms-access 2007 with windows XP

 

[vbaInet]

2.5 million or 250 thousand?

 

[cursedeye]

Thank you very much. I got it working