Data Entry Problem

I am designing a database to manage staff absences. I have a number of forms 
(one for each manager) feeding one table for company reports etc. I have a 
'member of staff' field that is a comby on each form to restrict entry for 
managers to their team members only. 
My problem is that i set the data entry property to 'Yes'. This clears the 
form on open but prevents me from finding records (obviously because the form 
is empty). If i set the data entry to 'No', all records from other managers 
are showing on all managers forms also. Due to data protection i need to 
overcome this. 
What i need is 10 forms feeding one table but i need the manager to be able 
to see alll their entries but not everybody elses. has anybody any advice? 
Thanks John aka Khartoum
0
Utf
1/8/2008 10:44:00 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
597 Views

Similar Articles

[PageSpeed] 46

Instead of creating 10 forms, or using Data Entry mode, set the RecordSource 
of the form so it shows only entries for one manager.

Presumably you have tables like this:
Staff table, with fields:
    StaffID            AutoNumber   Primary key
    Surname        Text
    FirstName      Text
    ManagerID      Number          relates to the key field in manager 
table.

Absence table, with fields:
    AbsenceID      AutoNumber   Primary key
    StaffID            Number          relates to Staff.StaffID
    AbsenceDate  Date/Time       when person was absent.

And presumably you have a way to determine which MangerID is using the 
database (so you know which records to show.) If so, use the Open event 
procedure of the form to:
a) limit the form so it shows only absences for the manager's staff;
b) limit the combo so it only shows staff for this manager.

This kind of thing:

Private Sub Form_Open(Cancel As Integer)
    Dim strSql As String

    strSql = "SELECT Absence.* FROM Absence INNER JOIN Staff ON 
Absence.StaffID = Staff.StaffID WHERE ManagerID = " & SomeNumber & " ORDER 
BY AbsenceID;"
    Me.RecordSource = strSql

    strSql = "SELECT StaffID, Surname & "", "" + FirstName AS FullName FROM 
Staff WHERE ManagerID " & SomeNumber & " ORDER BY Surname, FirstName, 
StaffID;"
    Me.StaffID.RowSource = strSql
End Sub

Hints:
a) Watch the line wrap above.
b) You can mock up a query using your fields, switch to SQL View, and get an 
example of the SQL strings you need to create.
c) You cannot use a control on *this* form to get the manager: the code runs 
before the data gets loaded.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Khartoum" <Khartoum@discussions.microsoft.com> wrote in message
news:B85AA17B-277B-41FF-8A57-C4582C54E6C0@microsoft.com...
>I am designing a database to manage staff absences. I have a number of 
>forms
> (one for each manager) feeding one table for company reports etc. I have a
> 'member of staff' field that is a comby on each form to restrict entry for
> managers to their team members only.
> My problem is that i set the data entry property to 'Yes'. This clears the
> form on open but prevents me from finding records (obviously because the 
> form
> is empty). If i set the data entry to 'No', all records from other 
> managers
> are showing on all managers forms also. Due to data protection i need to
> overcome this.
> What i need is 10 forms feeding one table but i need the manager to be 
> able
> to see alll their entries but not everybody elses. has anybody any advice?
> Thanks John aka Khartoum 

0
Allen
1/8/2008 11:47:08 AM
Thanks Allen, tht has put me on the right track

"Allen Browne" wrote:

> Instead of creating 10 forms, or using Data Entry mode, set the RecordSource 
> of the form so it shows only entries for one manager.
> 
> Presumably you have tables like this:
> Staff table, with fields:
>     StaffID            AutoNumber   Primary key
>     Surname        Text
>     FirstName      Text
>     ManagerID      Number          relates to the key field in manager 
> table.
> 
> Absence table, with fields:
>     AbsenceID      AutoNumber   Primary key
>     StaffID            Number          relates to Staff.StaffID
>     AbsenceDate  Date/Time       when person was absent.
> 
> And presumably you have a way to determine which MangerID is using the 
> database (so you know which records to show.) If so, use the Open event 
> procedure of the form to:
> a) limit the form so it shows only absences for the manager's staff;
> b) limit the combo so it only shows staff for this manager.
> 
> This kind of thing:
> 
> Private Sub Form_Open(Cancel As Integer)
>     Dim strSql As String
> 
>     strSql = "SELECT Absence.* FROM Absence INNER JOIN Staff ON 
> Absence.StaffID = Staff.StaffID WHERE ManagerID = " & SomeNumber & " ORDER 
> BY AbsenceID;"
>     Me.RecordSource = strSql
> 
>     strSql = "SELECT StaffID, Surname & "", "" + FirstName AS FullName FROM 
> Staff WHERE ManagerID " & SomeNumber & " ORDER BY Surname, FirstName, 
> StaffID;"
>     Me.StaffID.RowSource = strSql
> End Sub
> 
> Hints:
> a) Watch the line wrap above.
> b) You can mock up a query using your fields, switch to SQL View, and get an 
> example of the SQL strings you need to create.
> c) You cannot use a control on *this* form to get the manager: the code runs 
> before the data gets loaded.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Khartoum" <Khartoum@discussions.microsoft.com> wrote in message
> news:B85AA17B-277B-41FF-8A57-C4582C54E6C0@microsoft.com...
> >I am designing a database to manage staff absences. I have a number of 
> >forms
> > (one for each manager) feeding one table for company reports etc. I have a
> > 'member of staff' field that is a comby on each form to restrict entry for
> > managers to their team members only.
> > My problem is that i set the data entry property to 'Yes'. This clears the
> > form on open but prevents me from finding records (obviously because the 
> > form
> > is empty). If i set the data entry to 'No', all records from other 
> > managers
> > are showing on all managers forms also. Due to data protection i need to
> > overcome this.
> > What i need is 10 forms feeding one table but i need the manager to be 
> > able
> > to see alll their entries but not everybody elses. has anybody any advice?
> > Thanks John aka Khartoum 
> 
> 
0
Utf
1/8/2008 2:37:03 PM
Reply:

Similar Artilces:

outlook 2007 no problem receiving email but problem sending
I have no problem sending and receiving emails when connected at work but from home, I can receive new messages but there is a problem sending. I get the message Sending reported error 0x80042109. Outlook cannot connect to your outgoing (SMTP) e-mail server. If you continue receiving this message contact your server administrator or ISP. Any ideas please. Thanks, Charlie Have you verified with your corporate mail admin already that the settings have been correct for usage outside of the company network and if this is indeed fully supported by your corporation? If so,...

Public Function Problems
Hi, I am very new to VBA and am attempting to use a public Function withou much luck. What I need is I have 6 test results in columns C to H and I need Public Function to add the scores up make certain divisions and outpu a mark e.g Merit for the score they achieved. I have attempted it i the code below and have chopped and changed thing so much I am no totally confused. This is the code... Code ------------------- Option Explicit Public Function Grade(cell As Range) As String Dim dblTest1 As Double Dim dblTest2 As Double Dim dblTest3 As Double Dim dblTest4 As Dou...

Outlook 2003 Outlook Today Problem
I installed Office 2003 Standard over top of Office 2000 Professional. I'm running Windows XP Home Edition, Outlook 2003 will not let me permanently customize Outlook Today. When I click on customize it presents the menus but is unable to find the graphics such as Winter, Summer, etc. It lets me select summer for instance and when I save the changes I get the summer scheme. But, when I exit Outlook and return my saved preferences are gone. There also appears to be graphic place holders on the Outlook Today screen, no 'x's just blank little squares. In the black date bar o...

merging into Word with filtered data
Hello, I'm trying to do a merge in Word using data from Excel. However, I don't want to merge using data from the whole worksheet, but just filtered data (all people from London - place is one of the fields in the worksheet. I have no idea how I might do this. Any help gratefully received. I believe you can accomplish this from Word by using "Query Options" and selecting to merge only those records you're seeking. HTH PC "Italian Pete" <ItalianPete@discussions.microsoft.com> wrote in message news:3E62FCDC-42EC-48DB-92AC-857B224F085F@microsoft.c...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Data Loss in Publisher
Hi I run a W2k network in a school. I have Office Group Policies in place forcing the default save location for Publisher documents (Office XP version) to the pupil's home folders. All my PCs are identical (ghost images). Pupils have roaming profiles (some mandatory). Some users save work in Publisher and the next time they log on to the network and open the saved file, it's contents have gone! There is nothing exceptional about the affected users' user accounts and they have a mixture of mandatory and roaming profiles. Has anyone come across this problem before? Than...

outlook sending problems
Hi, I am having problems in sending out emails using outlook 2002. From time to time, outlook reports that the mail was failed to be sent with an error code "0x80040109", and the email stays at the Outbox. However, the email receipient will still RECEIVE the email sometimes. This is really annoying as I keep resending emails when the error occurs, which creates a flooding to the receipient. I did remove all the add-in according to the only suggestion I could find through google. The problem still exists. I switched to Outlook 2003, no luck, though I don't see the error...

Any way to clear out stock pricing data before or after going to Quicken?
With all the discussion a few months ago about taking control of our own data, I had gone through the steps to look at my Money file as an Access DB, and found that of my 80MB, more than half was daily stock prices. Thats not something I really want to convert and maintain. I can't imagine that its really needed, since transactions have their own price info. Is there any way to get rid of it in Money, in Quicken, or other? Thanks In Money Plus: Portfolio > Other Tasks > More > Clear historical quotes The process may be different in earlier versions. "JDelMar&q...

Dexterity
Dear Sir/Mis I would like to know if there is anyway i can overcome the following: I've defined a library(COM) in my dictionary. The methods in that .DLL file which are defined as Strings, or take in String parameters, all show as "anonymous" datatypes with "anonymous" parameters in class browser. And therefore am not able to use em. System generates a Datatype conflict error on runtime. The DateTime datatype on the other hand works fine. NB:I've searched the MSDN, and the internet over and over. Thank you If it shows up as anonymous in the Dexterity clas...

Problem persisting toolbar state
I'm trying to use SaveBarState("BarState") and LoadBarState("BarState") to remember the state of my toolbars. This works fine for all my toolbars except 2. Checking the registry reveals that the 2 bars are not listed (at least there is no matching BarID entry). When I show them from my view menu the first toolbar (IDR_MAINFRAME) shows up docked to the right side, not where I left it. And the second toolbar (IDR_SECOND_TOOLBAR) toolbar shows up on the correct row, i.e. I moved it down to form a second row, but has moved back to its original horizontal position wh...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

mail merge problems #2
I have Vista and Office 2007. When I try to do a mail merge with Publisher and a comma-separated-values file exported from Outlook, I get the message, "An operation cannot be completed because of database engine errors." It used to work fine, but not any more. Help! There isn't a solution specific to 2007, but this article addresses your error and probably will work You receive a "The operation cannot be completed because of dialog or data base engine failures" error message when you try to perform a mail merge by using an Outlook 2003 contact folder in Publish...

address book group problem
I created a new group then (I thought) added entries using the boxes at the bottom of the small window one at a time. Now if I open my address book and click on the group, it only shows one entry. If I double click on it a new window opens with all eight ehtries which I put in. Does anybody recognise what I have done wrong? How can I get all the eight entries into the group? I need to be sure of what I am doing as if I lose the email details of these entries it will not be easy to get them again. TIA Pete It might be easier to start over, but I'll leave that up to you....

problem customizing toolbars
Hi everyone I'm trying to customize my outlook toolbars (in the new message area). When I click View > Toolbars > the customize option is greyed out. Not sure why. All I want to do is add and remove some buttons to the toolbars. I'm using Outlook 2000. Any ideas greatly appreciated. Thanks heaps Helen Place your cursor in the message first; not in the Header lines. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Helen" <anonym...

Strange outlook receiving email problem
New to this forum, hopefully you guys can help. One of my customers was having a weird issue... the whole building is on a network/domain. Everyone is set up to use outlook/ pop3. Nobody is having problems except two employees. For some reason they can't receive emails from each other. Using web mail everything works fine. Creating emails from the web mail the email is sent and received in outlook. But sending emails from outlook do not make it to the other persons outlook. Now the weird part is that these two people communicate just fine with everyone else. They can send and receive emai...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

CRM4
Hi, On CRM4, I obtain an ASP.NET error while trying to access the website of my default organization. The message error is the following : "Caller does not have enough privilege to set CallerOriginToken to the specified value". I didn't find anything on the Web... Does anyone have an idea ? Fabien Majurel I have the same problem, I am going to reinstall and see if that helps Mike "Fabien Majurel" wrote: > Hi, > > On CRM4, I obtain an ASP.NET error while trying to access the website of my > default organization. The message error is the following...

Exchange 5.5 internal site problem
Hi, I am using exch 5.5 with 2 srvrs in a single sit , with a mail content scanner enigine on a diff srvr , when i forward mail fm content scanner to first srvr , the mail flow works properly , but when i forward it to second srvr ( which was buileded later) , the recipients on 1 st server dont get internet mails . Can anyone HElp. ...

birthday reminders problem
outlook 97: my reminders are not working when i try to access them by clicking their icons in the contacts folder i get the nysterious message "Outlook could not start because a location to send or receive mail could not be found" this is doubly weird because i do not use outlook to send or receive mail (i use outlook express) and sending/receiving mail is not what i am trying to do anyway another problem - i suspect it is related - my tasks do not appear in my calendar any more. All this began after I archived my calendar. I have tried re-installing outlook but it made...

summarise data
I have data on a sheet looking like this 5/5/2004 5/5/2004 7/5/2004 7/5/2004 7/5/2004 7/5/2004 8/5/2004 9/5/2004 9/5/2004 9/5/2004 and I want to summarise this into different cells so that it look like 5/5/2004 7/5/2004 8/5/2004 9/5/2004 i.e. I want to list individual occurances of repeating dates o numbers. Any help would be much appreciated -- Message posted from http://www.ExcelForum.com Sounds like a job or a Pivot Table perhaps? http://www.ozgrid.com/Excel/excel-pivot-tables.htm ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore...

creating a program that uses data from a sensor....
Hello, I want to create an MFC program that uses parameter values passed to it from a meter (specifically the resistance in ohms of resisters in an electronic circuit). What are the hardware components that would be needed? And what MFC class(es) would I use to accomplish this? Thanks, RABMissouri2006 On 21 Oct 2006 08:10:50 -0700, "RAB" <rabmissouri@yahoo.com> wrote: >Hello, > >I want to create an MFC program that uses parameter values passed to it >from a meter (specifically the resistance in ohms of resisters in an >electronic circuit). What are the ha...

Data archive with GP Manufacturing
Has anyone had experience with any data archive with GP Manufacturing. The product from Professional Advantage does not support Manufacturing archive, at least not in the documentation. Don't have an answer for you but we will be doing the same in the next week or so. Will report back when done. "DavidInterDyn" wrote: > Has anyone had experience with any data archive with GP Manufacturing. The > product from Professional Advantage does not support Manufacturing archive, > at least not in the documentation. ...

Transfering Outlook Data to a New PC
I've just built a new PC. Currently it has nothing on it other than Windows. When I install Outlook 2003 I'll need to transfer the data from another PC onto it, including all folders, emails and attachments in them and calendar data. What's the easiest and most effective way to do this? Find the PST file and copy it to the new machine and into the appropriate directory, what? Thanks. Copy the pst(s) to the new PC There isnt an appropriate Folder, there is a default folder created when Outlook is installed. http://www.howto-outlook.com/howto/backupandrestore.htm "Marts&q...

Locking of entered data
I haev a form that currently allows you to enter new records and edit existing records. My key field is based on a number entered by the user from another system. Once the number is entered I do not want the user to be able to type over that number but still be aloud to add a new record. I tried the AllowAdditions and AllowEdits in the properties but if I set them to Yes AllowAdditions and No AllowEdits then nothing can be changed. I want them to be able to edit everything but the one field containing the number. Ideas? Glenna, The code I provided would go in the two places that I...

Email looping problem
Hi, We have recently taken over another company and I need to get the users at the new companies site to be able to receive emails in the format of our companies email e.g. both sites need to receive emails sent to joe.bloggs@xyz.com We have setup recipient policy on the new companies exchange to accept emails for our companies domain. We have also set the SMTP virtual server at each site to "forward email with unresolved recipients" to the public IP address of the other. The problem here is that when an email is sent to a user that doesn't exist both exchange servers w...