Re: [MS_AccessPros] Close form question

 

John,

The first query SQL

SELECT [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Last Nm], [R&D-CURRENTEMPLOYEES].[First Nm], [R&D-CURRENTEMPLOYEES].[Dept Ld], [R&D-CURRENTEMPLOYEES].[Empl Stat Cd], [R&D-CURRENTEMPLOYEES].[Person Id], [R&D-CURRENTEMPLOYEES].[Empl Fte Pct], [R&D-CURRENTEMPLOYEES].[Flsa Stat Cd], [R&D-CURRENTEMPLOYEES].[Flsa Stat Ld], EmployeeSchedules.M, EmployeeSchedules.T, EmployeeSchedules.W, EmployeeSchedules.TH, EmployeeSchedules.F, EmployeeSchedules.SAT, EmployeeSchedules.SUN, EmployeeSchedules.TRAVELREDDAY, EmployeeSchedules.MonFrom, EmployeeSchedules.MonTo, EmployeeSchedules.TuesFrom, EmployeeSchedules.TuesTo, EmployeeSchedules.WedFrom, EmployeeSchedules.WedTo, EmployeeSchedules.ThursFrom, EmployeeSchedules.ThursTo, EmployeeSchedules.FriFrom, EmployeeSchedules.FriTo, EmployeeSchedules.SatFrom, EmployeeSchedules.SatTo, EmployeeSchedules.SunFrom, EmployeeSchedules.SunTo, EmployeeSchedules.vacflag, EmployeeSchedules.Scheduletype, EmployeeSchedules.comments, EmployeeSchedules.compflag INTO SCHEDULEDATA
FROM [R&D-CURRENTEMPLOYEES] INNER JOIN EmployeeSchedules ON [R&D-CURRENTEMPLOYEES].[Person Id] = EmployeeSchedules.[Person Id]
GROUP BY [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Last Nm], [R&D-CURRENTEMPLOYEES].[First Nm], [R&D-CURRENTEMPLOYEES].[Dept Ld], [R&D-CURRENTEMPLOYEES].[Empl Stat Cd], [R&D-CURRENTEMPLOYEES].[Person Id], [R&D-CURRENTEMPLOYEES].[Empl Fte Pct], [R&D-CURRENTEMPLOYEES].[Flsa Stat Cd], [R&D-CURRENTEMPLOYEES].[Flsa Stat Ld], EmployeeSchedules.M, EmployeeSchedules.T, EmployeeSchedules.W, EmployeeSchedules.TH, EmployeeSchedules.F, EmployeeSchedules.SAT, EmployeeSchedules.SUN, EmployeeSchedules.TRAVELREDDAY, EmployeeSchedules.MonFrom, EmployeeSchedules.MonTo, EmployeeSchedules.TuesFrom, EmployeeSchedules.TuesTo, EmployeeSchedules.WedFrom, EmployeeSchedules.WedTo, EmployeeSchedules.ThursFrom, EmployeeSchedules.ThursTo, EmployeeSchedules.FriFrom, EmployeeSchedules.FriTo, EmployeeSchedules.SatFrom, EmployeeSchedules.SatTo, EmployeeSchedules.SunFrom, EmployeeSchedules.SunTo, EmployeeSchedules.vacflag, EmployeeSchedules.Scheduletype, EmployeeSchedules.comments, EmployeeSchedules.compflag
ORDER BY [R&D-CURRENTEMPLOYEES].[Last Nm], [R&D-CURRENTEMPLOYEES].[First Nm];

The second query SQL

UPDATE SCHEDULEDATA SET SCHEDULEDATA.TRAVELREDDAY = "NA"
WHERE (((SCHEDULEDATA.[Empl Fte Pct])<0.5));

The Function running after the queries.

Public Function CheckDuplicates()
Dim RS As DAO.Recordset
Dim strSql As String
Dim db As DAO.Database

Dim MsgBoxFlag As Boolean

MsgBoxFlag = False

Set db = CurrentDb

strSql = "SELECT First(tblSupervisorList.[Person Id]) AS [Person Id Field], Count(tblSupervisorList.[Person Id]) AS NumberOfDups From tblSupervisorList GROUP BY tblSupervisorList.[Person Id] HAVING (((Count(tblSupervisorList.[Person Id]))>1))"
Set RS = CurrentDb.OpenRecordset(strSql)

If RS.RecordCount > 0 Then
    MsgBoxFlag = True
    DoCmd.OpenQuery "qryFindDupliatesFortblSupervisorList", acViewNormal, acEdit
End If

strSql = "SELECT First(EmployeeSchedules.[Person Id]) AS [Person Id Field], Count(EmployeeSchedules.[Person Id]) AS NumberOfDups From EmployeeSchedules GROUP BY EmployeeSchedules.[Person Id] HAVING (((Count(EmployeeSchedules.[Person Id]))>1))"
Set RS = CurrentDb.OpenRecordset(strSql)

If RS.RecordCount > 0 Then
    MsgBoxFlag = True
    DoCmd.OpenQuery "qryFindDuplicatesForEmployeeSchedules", acViewNormal, acEdit
End If

strSql = "SELECT SCHEDULEDATA.[Person Id], SCHEDULEDATA.[Last Nm], SCHEDULEDATA.[First Nm] From SCHEDULEDATA WHERE (((SCHEDULEDATA.[Person Id]) In (SELECT [Person Id] FROM [SCHEDULEDATA] As Tmp GROUP BY [Person Id] HAVING Count(*)>1 ))) ORDER BY SCHEDULEDATA.[Person Id]"
Set RS = CurrentDb.OpenRecordset(strSql)

If RS.RecordCount > 0 Then
    MsgBoxFlag = True
    DoCmd.OpenQuery "qryFindDuplicatesForSCHEDULEDATA", acViewNormal, acEdit
End If

If MsgBoxFlag = True Then
    MsgBox "Duplicate Records Found!", vbExclamation, "Warning"
End If

'SCHEDULEDATA 'W:\DATA MANAGEMENT SERVICES\Database Mappings and Documents\Absence Database\Schedule Data in Absence database.docx

End Function

Jim Wagner

---

On Tue Jul 25 2017 10:38:41 GMT-0700 (US Mountain Standard Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim-

Make Table queries are a bad idea - you have no control over the data types Access chooses to use, and the table has no keys.

It's better to pre-define the table, then run a DELETE * followed by an INSERT.  Why is it doing an update?  Isn't the Make Table loading all the rows you need?  What's the SQL of the two queries?

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jul 25, 2017, at 7:34 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The macro is running a query that is a make table query and then an update query on the table . I am assuming that the table is not built and updated yet before the form closes. But the unload event did not work. 

Jim Wagner

---

On Tue Jul 25 2017 10:26:16 GMT-0700 (US Mountain Standard Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jim-

Try using the Unload event instead.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jul 25, 2017, at 7:20 PM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello all,

First, Thank You for everyone's help that I have received from this forum's members. I appreciate it very much. 

I have found a form on a database that we support that has a macro that runs from the On Close event. There is a macro that runs 2 queries and then a Function. When I try to put the form in design view, I receive an error message stating that a table is locked by another user. I am supposing that the table is behind the queries that are being run. How do I stop this message from appearing? the form previously was on a tabbed form and we are moving the form off of the tabbed form. I am assuming that because it was on a tabbed form, it never gave that message box. How can I suppress the message? Is there another event to put the macro or converted vba on?

Thank You

Jim Wagner

__._,_.___

---

Posted by: Jim Wagner <luvmymelody@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___