Solved

Perform event procedure on all records in a query.

Posted on 2008-06-11
12
401 Views
Last Modified: 2013-11-27
Hi,

I currently have a form that cycles through a recordset and performs an action on each record. I am sure I can achive the same without have to use an event procedure on a form or have the form itself. I basically want to be able to update the fields using SQL or VBA so that it can occur in the background without user interaction.

The form code is below.

The data set come from

SELECT tblPersDetails.EID, tblPersDetails.PhotoLoc, tblPersDetails.Rank, tblPersDetails.RankCode, tblPersDetails.RankClass
FROM tblPersDetails
ORDER BY tblPersDetails.PhotoLoc;

Basically, I need a SQL statement to run as part of another function to update fields RankCode, RankClass and PhotoLoc.

Thanks in advance.

Kev

Private Sub cmdUpdate_DblClick(Cancel As Integer)

    Dim varform2 As String

    varform2 = "frmMntPhotos"

    Application.Echo False

    DoCmd.SetWarnings False

    

    DoCmd.GoToRecord acDataForm, varform2, acFirst
 

NextRec:

    On Error GoTo errorhandle

    

    PhotoLoc.Value = Me.EID + ".jpg"   

    Me.RankCode.Value = DLookup("SortPri", "tluRank", "[Rank] = [lu-rank]")

    Me.RankClass.Value = DLookup("RankClass", "tluRank", "[Rank] = [lu-rank]")
 

    DoCmd.GoToRecord acDataForm, varform2, acNext  
 

    GoTo NextRec

    

errorhandle:

    DoCmd.SetWarnings True

    Application.Echo True

    DoCmd.GoToRecord acDataForm, varform2, acFirst

End Sub

Open in new window

0
Comment
Question by:budorat
  • 6
  • 6
12 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 21761794
Where does the value [lu-rank] come from?

OM Gang
0
 
LVL 5

Author Comment

by:budorat
ID: 21763248
[lu-rank] comes form the table tluRank

Kev
0
 
LVL 28

Expert Comment

by:omgang
ID: 21763549
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
0
 
LVL 5

Author Comment

by:budorat
ID: 21763583
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 21763673
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
0
 
LVL 5

Author Comment

by:budorat
ID: 21763788
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
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 28

Expert Comment

by:omgang
ID: 21763898
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
0
 
LVL 5

Author Comment

by:budorat
ID: 21764048
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

Open in new window

Test.zip
0
 
LVL 28

Expert Comment

by:omgang
ID: 21764196
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;

Open in new window

0
 
LVL 5

Author Comment

by:budorat
ID: 21764253
Alrighty,

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

Kev
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 21764347
Here's the revised SQL for the update query

UPDATE tblPersDetails SET tblPersDetails.RankCode = DLookUp("SortPri","tluRank","[lu-rank] =" & Chr(34) & [Rank] & Chr(34)), tblPersDetails.RankClass = DLookUp("RankClass","tluRank","[lu-rank]=" & Chr(34) & [Rank] & Chr(34));

OM Gang
0
 
LVL 5

Author Closing Comment

by:budorat
ID: 31466079
Hi,

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

Kev
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now