Queries and Dlookup (1 Viewer)

[gwunta]

I'm trying to use Dlookup to grab thes value in a field from a query. It works if I copy the values to a table however I want to avoid this if possible. Here's the code below

Private Sub Form_Load()
Dim MyDate, MyDay, MyYear, ent1, ent2, ent3, ent4, ent5
DoCmd.OpenQuery "qryCalcDifference"
DoCmd.GoToRecord , , acLast
ent1 = DLookup("Ent1", "qryCalcDifference")
ent2 = DLookup("Ent2", "qryCalcDifference")
ent3 = DLookup("Ent3", "qryCalcDifference")
ent4 = DLookup("Ent4", "qryCalcDifference")
ent5 = DLookup("Ent5", "qryCalcDifference")
If ent1 > 1.15 Or ent1 < 0.85 Or ent2 > 1.15 Or ent2 < 0.85 Or ent3 > 1.15 Or ent3 < 0.85 Or ent4 > 1.15 Or ent4 < 0.85 Or ent5 > 1.15 Or ent5 < 0.85 Then
MsgBox "PCS Diagnostics has detected there may be an error in the PCS system. Please contact Pedestrian Counting Systems.", vbExclamation, "PCS Error Detection"
End If
End Sub

Can Dlookup not be used in queries or have I got code wrong somewhere?

 

[dgm]

Hi,
You can use a query in DLookup. Does your query return more then one record? If so this may be your problem. The following is an excerpt from MS Access Help.

Remarks

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

Does your query not get it's data from a table anyway? Your code seems ok, although i'm not sure why u are opening the query. Why doesn't it work for u, does it come up with an error, or with the wrong result?

 

[The_Doc_Man]

In the code you posted,

DoCmd.GoToRecord , , acLast

is not functional. Things done from DoCmd have NOTHING AT ALL to do with the results of a DLookup. The first DoCmd (.OpenQuery) should open up the query on your screen. The second one should move the selection cursor to the last record.

If your real goal was to get selected data from the last record in the recordset, and you are ALREADY using VBA, do it more like this....


Dim rsQry as Recordset

Dim myEnt1 as .... (whatever it is)
Dim myEnt2 .... and the same for Ent3, Ent4, Ent5

Set rsQry = CurrentDB.OpenRecordset( "qryCalcDifference" )

rsQry.MoveLast

myEnt1 = rsQry!Ent1
myEnt2 = rsQry!Ent2

etc. etc.

rsQry.Close


Then follow this with your big IF clause

If myEnt1 > 1.15 Or myEnt1 < 0.85 Or myEnt2 > 1.15 Or myEnt2 < 0.85 Or .... etc. etc.

DON'T FORGET THE rsQry.Close STATEMENT. Otherwise your system will hang up on "out of resources" or "out of memory" and you will have to reboot.

 

[gwunta]

Thanks for that Doc. I haven't had the opportunity to try it yet but it makes sense. I had read dgm's reply and then it clicked that I didn't need that gotorecordlast in there. I had to work out a way of making each record in the query unique and seeing as the query only runs once a week, that by putting in the system date in the source table for the query and then using Dlookup specifying the date as the criteria, I could pick up the record I wanted.

Will let you know how it goes