Perform event procedure on all records in a query.

Where does the value [lu-rank] come from?

OM Gang

[lu-rank] comes form the table tluRank

Kev

So your code is doing this???

    Me.RankCode.Value = DLookup("SortPri", "tluRank", "[Rank] = [lu-rank]")
   Set the value of RankCode on this form to the value of SortPri in table tluRank by looking up the record where the value of field Rank in that table equals the value of field lu-rank in the same table


Does that actually work and do what you want it to?
OM Gang

Hi,

My code above works perfectly using a form. What I REALLY want is a sql statement that does the same thing by cycling through the data in a table or query, rather than having to cycle through the records on a form. This way I can have it occur in the background without user interaction.

Kev

I understand.  I am confused about how you look up the value to assign to RankCode though.  If it works it is none of my business.  You do realize that the DLookup, as you have it written, will always return the same record.

UPDATE tblPersDetails Set PhotoLoc = [PhotoLoc] & ".jpg", RankCode = DLookup("SortPri", "tluRank", "[Rank] = [lu-rank]"), RankClass = DLookup("RankClass", "tluRank", "[Rank] = [lu-rank]");

You can copy the above SQL statement and paste into a new query and then save it.  You can call this query from a code or from a macro to run whenever you wish.  To avoid messages to the user
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfTheQueryYouSavedAbove"
DoCmd.SetWarning True

OM Gang

Hi,

Not sure why you think the DLookup returns the same record each time. It cycles through all the records in my DB and compares each records Rank value to determine the Rank Class and sort Order.

I can not tell of the code works. It seems that it is only updating one record, and not all of them. I need it to cycle through all records in tblPersDetails.  

Kev

From my earlier post
Me.RankCode.Value = DLookup("SortPri", "tluRank", "[Rank] = [lu-rank]")
   Set the value of RankCode on this form to the value of SortPri in table tluRank by looking up the record where the value of field Rank in that table equals the value of field lu-rank in the same table


From VBA Help
You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

DLookup(expr, domain, [criteria])
The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.


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.

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:

Dim varX As Variant
varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")




So, in your use of the DLookup function expr is SortPri (the field you want to return), domain is tblRank, and criteria is WHERE [Rank] = [lu-rank] ------ from what you've said, lu-rank is a field in tblRank so you asking the DLookup funtion to return the value of field SortPri from tblRank for the record WHERE the value of field [Rank] is equal to the value of field [lu-rank].  Either there is one record in tblRank in which the value of [Rank] = the value of [lu-rank] in which case the DLookup will return that record, or there is no record where the value of [Rank] = the value of [lu-rank] in which case the DLookup will return Null, or the there are multiple records where the value of [Rank] = the value of [lu-rank] in which case the DLookup will return the first occurance.  In every case the DLookup will always return the same record, the same value.  Your code does not change the criteria expression for the DLookup function, it's always the same.

BTW - the SQL statement I posted will update every record in tblPersDetails

OM Gang

Hi,

I understand this bit.
*************************************
From my earlier post
Me.RankCode.Value = DLookup("SortPri", "tluRank", "[Rank] = [lu-rank]")
   Set the value of RankCode on this form to the value of SortPri in table tluRank by looking up the record where the value of field Rank in that table equals the value of field lu-rank in the same table
**************************************
My point is I am trying to get away from using a form all together. I want the update to occur as part of the function below.

I have attached a test DB with your query and some same sample data from my DB. If you note Record 1 - I have changed the rank field value to COL, therefore, once the update query is run it should change rankclass to OFFR and RankCode to 15. When I run the update query I get a warning stating that it is about to update my record, but nothing appears to occur.

The current procedure (form based) I use is also included.

Kev

Function Update_PMKEYS_ARMY_BRT_PERSDATA()
On Error GoTo ImportError_Err
'Imports Pers Data from RPT1 query ARMY_BRT_PERSDATA
    DoCmd.SetWarnings False
 
'Import new data set
    DoCmd.RunSQL "UPDATE tblPersDetails INNER JOIN tblPMKeyS_BRT_PersData ON tblPersDetails.EID = tblPMKeyS_BRT_PersData.ID SET tblPersDetails.SvcNo = tblPMKeyS_BRT_PersData!AGSN, tblPersDetails.Seniority = tblPMKeyS_BRT_PersData![Seniority Date], tblPersDetails.[Marital Status] = tblPMKeyS_BRT_PersData![Marital Status], tblPersDetails.Corps = tblPMKeyS_BRT_PersData!Family, tblPersDetails.Religion = tblPMKeyS_BRT_PersData!Religion, tblPersDetails.DOB = tblPMKeyS_BRT_PersData!Birthdate, tblPersDetails.ENAL = tblPMKeyS_BRT_PersData![Hire Date], tblPersDetails.Sex = tblPMKeyS_BRT_PersData!Sex, tblPersDetails.SecurityClearance = tblPMKeyS_BRT_PersData!Security, tblPersDetails.DefenceEmail = [tblPMKeyS_BRT_PersData]![Email ID], tblPersDetails.Rank = [tblPMKeyS_BRT_PersData]![Rank], tblPersDetails.WornRank = [tblPMKeyS_BRT_PersData]![Worn Rank];"
    DoCmd.RunSQL "UPDATE tblPersDetails SET tblPersDetails.DRNUser = getDRNUser([DefenceEmail]);"
    
'Update PMKeyS Update Info
    With Form_frmMenuMain
        .BRTPersDataBy.Value = .varCurrentUser
        .BRTPersDataUpdate.Value = Now()
    End With
 
    MsgBox "Data Transfer Complete"
    
ImportError_Exit:
    DoCmd.SetWarnings True
    Exit Function
 
ImportError_Err:
    MsgBox Error$
    Resume ImportError_Exit
End Function

Select allOpen in new window

Test.zip

You have the two fields in the DLookup criteria expression backwards.  [Rank] is a field in tblPersDetails; [lu-rank] is a field in the domain (tblRank) so it should be

DLookup("SortPri", "tluRank", "[lu-rank] = [Rank]") but also, since we are dealing with a query expression (and [lu-rank] is a string/text value)

DLookup("SortPri", "tluRank", "[lu-rank] = " & Chr(34) & [Rank] & Chr(34))

I've modified the query from your sample DB - I changed it to a Select query.  Check it out and let me know if this is what you are expecting to see.  Then we can turn it back into an Update query.

OM Gang

SELECT tblPersDetails.RankCode, tblPersDetails.RankClass, DLookUp("SortPri","tluRank","[lu-rank] =" & Chr(34) & [Rank] & Chr(34)) AS Expr1, DLookUp("RankClass","tluRank","[lu-rank]=" & Chr(34) & [Rank] & Chr(34)) AS Expr2
FROM tblPersDetails;

Select allOpen in new window

Alrighty,

I never relaise I had the fields backwards, SORRY !!
Yes, the select query is showing what I am expecting to see.

Kev

Hi,

The code worked perfectly. My apologies for any confusion I may have created in my explanations.

Kev