Form to Look Up Value and Open Hyperlink

[LavendarRabbit]

To start, I am new to Access.


I have been trying to find a solution but haven't been successful.

What I am trying to accomplish is an employee would enter in a WO, area, and their employee number in the form. When they click the search button, it would do a few things:
1. Use the WO number to look up what assembly it refers to using the WO table.
2. It would cross reference that assembly and the Area in the form to the Work Instruction List table and open the hyperlink.
I currently don't have hyperlinks in the hyperlink column, but will once I can figure out how to accomplish this.
3. The WO, Area, and Employee Number on the form, will be entered into the Employee Tracking table, with a date and time stamp.

Attached is the Access database I have started. I assume I will need a couple of Macros to achieve this.
Thank you in advance.

[pbaldy]

For starters, I'd use a combo for the WO. That lets you make sure they only enter valid numbers, and allows you to include the assembly in a second column (could be hidden).

A DLookup() or recordset can find the hyperlink, FollowHyperlink can open it.

You can execute a saved query that gets values from the form to add your record. You could also use a recordset or execute SQL from code.

[Micron]

I'd advise to not use special characters (e.g. slashes) or spaces in object names. Underscore is considered acceptable but I never use it.

Looks like you should have tables for customer, employee, WI list and maybe Area and Station tables as well. Consider reading over these linked pages with special emphasis on db normalization:

https://www.accessforums.net/showthr...773#post521773

[Micron]

and their employee number in the form

This makes me think you should have a user table as well (e.g. tblUsers). When a user opens the db you can get any of their info from that table via vba or Environ variable. Then you can set a combo box to their employee number or whatever you want to show there, thus they don't have to repeatedly find their info when they need it. The combo would allow them to also switch to another user if they need to, like if they're covering them off during vacation.

I know, a bit much at this time perhaps, but if you start with the tables you need (and they're normalized) you'll find development a bit easier.

[xps35]

The relation between WO Table and Work Instruction List is not right. You should connect the Work Instruction List to WO Table's primary key (ID). In this setup there could be multiple WO's with the same Customer/Assembly so you cannot know to which WO a Work Instruction is related.

I wonder where a Work Instruction should really be related to. It seems to me that does not depend on the customer. Maybe only on the assembly?

[Micron]

A CMMS (Computerized Maintenance Management System) was a huge part of my work at one time. A work instruction would never be tied/linked to a work order in a relationship. It could be linked to a WO template if it was for repeating a wo execution such as PM's (we called those PM Base Work Orders) but not to a wo that is going to be
a) a different number each time, or
b) contain different tasks.

A Corrective Work Order executed today may require a different set of tasks next time, thus the WI's required could be different. So I'm suggesting that if the OP's db is for wo execution, the relationship between any one executed wo and the required wi's for that wo would be built in a junction table. You wouldn't link them between wo and wi tables. Again, that is all based on my assumption that in this thread, a wo for any job is going to be a one off thing that will likely repeat in the future, albeit with a different wo number next time. In that case I suspect additional tables are required.

[LavendarRabbit]

Thank you everyone for your input. I have not been able to work on this Access Database much and am just getting back into this. As I am new to Access Database, I am working through everyone's comments. If I have more questions I will make sure to ask specific ones.