Open an excel spreadsheet, skip over inactive employees in form

[sephiroth2906]

Hello all!



I have an employee database I use to keep track of all sorts of things, and there are a couple of things I would like to add to it, but am having a little trouble.

I created a table with all of the employee's information I need, and then I created a form where that information appears in a more readable format. I use the navigation buttons on the bottom to look at them one at a time, but I would like to create one where it will skip over inactive employees. I do have a checkbox for active, and while I know how to utilize it in a query, I do not know how to tell the form to skip over an unchecked box when using the arrows on the bottom.

Second, after searching attendance tracking, I have come to the conclusion that access just isn't going to cut it, so I am using excel spreadsheets. I would like to have a link I can click to just open that employee's timecard, but when I tried to put a hyperlink in the table it wasn't able to open it, so I must be misunderstanding something. I am not trying to import any information from these sheets, I just want to open them, interact with them, then close them. Then I move on to the next employee and do it all over again.

I really appreciate any help you can give me!

[pbaldy]

You can base the form on a query that excludes those employees.

Not sure what you think Access can't do; I've just written a time card application that's in testing right now. In any case, you should be able to use FollowHyperlink or Shell to open a saved Excel file. More info in VBA help.

[sephiroth2906]

The issue I have with the timecard is that what I use that works well for me is a spreadsheet with 31 days across the top and the 12 months on the left. An X indicates work, S sick, V vacation, etc. Then the days get color coded for what type of hours are used, and then there is constant split time, where someone works 5 hours and take 3 hours vacation, which gets put in as ST, color coded appropriately and then I insert a comment stating how many hours are what. I can then get a view of the entire year in a small area.

It is possible that I am just not versed enough in Access to incorprate these things into the database. Frankly, if you have suggestions, I would love to learn.

That being said, you (rightly) made me feel like an idiot. Of course I can build a form from a query! I will get right on that, so thank you!

[pbaldy]

Happy to help. It was certainly not my intention to make you feel like an idiot.

[sephiroth2906]

With all of that, I completely forgot to doublecheck:

I take it this means I cannot use the hyperlink field type to enter the link to the spreadsheet, is this correct?

I am reasonably sure I can code a link, but, the thing is that I would prefer the link to populate from the table, as it will far easier. Is there a way to accomplish that? What I want to do is cycle through each employee (which is why I needed to eliminate inactive ones) and just click that link, but have been unsuccessful thusfar.

If not, well, then, a coding I will go.

Thanks again!

[pbaldy]

I'm not entirely clear on what you're trying to do. I've never used the hyperlink data type, but I often store the path to a file in a standard text field and use FollowHyperlink to let the user open that file by clicking or double clicking in a textbox or button.

[sephiroth2906]

Let me try and explain. I currently have one table with all employee's information (name, pay, supervisor, department, appraisal dates, etc.

I have made a form that populates several of these items so I can make changes, add notes, that sort of thing. I can use the record selectors on the bottom to go through each employee one by one.

Right now, I use excel spreadsheets as timecards for attendance as mentioned above. What I would like to be able to do is utilize the record selectors on the form to go through each employee and just click on the link to their spreadsheet to enter their time. If I could enter a link on the table for each individual employee to go to their specific spreadsheet, then I could just add another spot on the form to present that link for me. While I am sure I can use FollowHyperlink to accomplish this, it looks to me that I would either have to use one spreadsheet (which I do not want to do for performance purposes) or code a button to look at which name is currently active in the form and fetch it that way, which would take more time. If I have to I have to, but I figured the hyperlink data type would make sense here.

Thanks for taking the time to help me out!

[pbaldy]

If I could enter a link on the table for each individual employee to go to their specific spreadsheet, then I could just add another spot on the form to present that link for me.

You can certainly do that, presuming the field contained a full path to the employee's spreadsheet. That would let you open a spreadsheet unique to the employee. Or, if there's a pattern to the spreadsheet names, for instance a standard part and then the employee's number, you could open it without that field:

Application.FollowHyperlink "Base path here" & "Me.EmployeeNumber & ".xls"

[sephiroth2906]

Yeah, I guess it really isn't any more work for me to do it that way then to do what I wanted to do. Thanks again! I am sure I will be back for something else!

[pbaldy]

No problem.

[sephiroth2906]

Quick aside, that actually was way LESS work then what I had intended. Thanks again!