Solved

Perform event procedure on all records in a query.

Posted on 2008-06-11
12
400 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
Comment Utility
Where does the value [lu-rank] come from?

OM Gang
0
 
LVL 5

Author Comment

by:budorat
Comment Utility
[lu-rank] comes form the table tluRank

Kev
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,

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

Kev
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

9 Experts available now in Live!

Get 1:1 Help Now