Prefix + AutoNumber as Item Code

[GinaWhipp]

Hmm, a few questions...

1. Why are you allowing new Employee's to be added from the ALL the Forms? You already have an Employees Form for that, not making much sense to me.

2. Supplier Form is incomplete

3. You specify certain fields as Required but nothing happens if I go to a new Record, no message or anything

4. Specimen Signature? Should that be Employee Signature?

5. DSPN Form incomplete

After you address the above you can move on to the other Forms.

 

[johannaellamay]

Hmm, a few questions...

1. Why are you allowing new Employee's to be added from the ALL the Forms? You already have an Employees Form for that, not making much sense to me.

2. Supplier Form is incomplete

3. You specify certain fields as Required but nothing happens if I go to a new Record, no message or anything

4. Specimen Signature? Should that be Employee Signature?

5. DSPN Form incomplete

After you address the above you can move on to the other Forms.

1. Ohhh I'm sorry. Hahaha. It's supposed to be "New Record" but I change that labels for every form. I just forgot to change it to "New DSPN" or "New Supplier". Thank you!

2. Really? I cannot seem to find what's missing. -_-

3. Hmm. Seems that you're right. But I don't understand. In my tables, I set the Required property to Yes. Access only prompts me to input on the blank fields in some cases. But it doesn't prompt me when I click some of the buttons. Why?? -_-

4. Hmm, I think they're the same but they're just called that in our Finance department.

5. I also can't see anything missing in f_DSPN. -_-

Help?

 

[GinaWhipp]

2. Not missing, look at the Lables, you have not cleaned those up

3. That's not the way Required works at the Table Level, it will take a blank record. You really want to put that at the Form Level.

4. Okay but Specimen sounds like something from Science class.

5. It's the Labels, you have not cleaned those up. They should not be the Field Names from the Table.

 

[johannaellamay]

2. Not missing, look at the Lables, you have not cleaned those up

3. That's not the way Required works at the Table Level, it will take a blank record. You really want to put that at the Form Level.

4. Okay but Specimen sounds like something from Science class.

5. It's the Labels, you have not cleaned those up. They should not be the Field Names from the Table.

2. & 5. Okay, I've fixed the labels. Thank you!

3. How do I do that?

4. Haha. I know, but for uniformity purposes, I'll just label it that because that's what we use at Finance and HR.

 

[GinaWhipp]

To validate data entry I use...

http://www.access-diva.com/vba13.html

 

[johannaellamay]

Gina, I have one important question. I thought I understood it but apparently not. So I tried to make just a sample t_Employee with Immediate supervisor because I want to create a lookup. I tried to make a q_Employee (query) so I could use that as my lookup for eImmediateSupervisor. But everytime I try to do so, Access prompts me this:

No valid fields can be found in 'q_Employee'. You may have selected a query that use the table you're adding the lookup column to. Please select a new source.

How then were able to create a lookup for eImmediateSupervisor? What is now the use of q_Employee? I couldn't understand the connection. Sorry. -_-

 

[johannaellamay]

Gina, I have one important question. I thought I understood it but apparently not. So I tried to make just a sample t_Employee with Immediate supervisor because I want to create a lookup. I tried to make a q_Employee (query) so I could use that as my lookup for eImmediateSupervisor. But everytime I try to do so, Access prompts me this:

No valid fields can be found in 'q_Employee'. You may have selected a query that use the table you're adding the lookup column to. Please select a new source.

I tried to check Object Dependencies and realized that it's not what you used as lookup for d_ImmediateSupervisor.

How then were able to create a lookup for eImmediateSupervisor?

 

[GinaWhipp]

Please post what you have as the Row Source for the Combo Box for the Immediate Supervisor.

 

[johannaellamay]

Oh, it's:

SELECT t_Employee.eEmployeeID, [t_Employee].[eEmployeeName] AS Expr1 FROM t_Employee ORDER BY [eEmployeeName] DESC;

I just copied the one you had, and it works. Just for confirmation, so what you used for the Immediate Supervisor field is a combo box, and not a lookup from a query?

 

[GinaWhipp]

That is not the one I had I used a the query itself, not the SQL of the query. Try that and see what happens.

 

[johannaellamay]

Hey GinaWhipp, I know I'm kind of stalking you right now. Haha. I just really need your help. So I kind of put the inventory database on hold because I had to make a database for HR. It's almost finished, ALMOST. Could you maybe take a look at it and let me know what you think? I'm also stuck at something and I will ask you about it later. Please?

 

[GinaWhipp]

As long as it's not tonight I can look tomorrow...

 

[gemma-the-husky]

joanna

Yeah, I get your point. But doing so would totally defeat the purpose of extracting the first letter of an Item and combining it with a number. It's just gonna add more fields and a lot more confusion.

this is not right. The developer's job is to develop a solution that just works. The way it works does not need to concern the user, and you only have to do it once.

so you can either devleo pa solution that generates a code

P001

or you can develop a solution that generates the code in 2 parts

P and 001 (actually just 1)

The second way is far more flexible. You can add the next item P and 002 with no complexity. The first way, you have to separate the P from the numbers, increment the number and put it back together. Far harder.

By the way, having a single code P001 may be a problem - what if the number goes above 999?

Either way, the user just sees P001

 

[GinaWhipp]

@Dave,

Okay, what post am I missing because I do not see where you got that from.

 

[johannaellamay]

As long as it's not tonight I can look tomorrow...

View attachment HR_X v5.1 - Copy.zip

User Name: johanna
PW: johanna

Main form is f_HRMSCommonUser

It's almost finished. But I wanted to add like tables where I can store history of (for example) employee contract dates, previous department, or previous supervisor. I looked for ways on the net and settled with Append Queries hence the many queries and many tables for history. Do you think there's another way to do this?

Also, I added a separate audit trail. Now that I think about it, I don't even know why I put it there.

The reason why I'm stuck is that I wanted to add a button so I can see the history of employees' details. However, in employee category history for example, in my report, it only shows the employee category ID, not the name of the category.

I'm really confused at this point.

 

[johannaellamay]

joanna


this is not right. The developer's job is to develop a solution that just works. The way it works does not need to concern the user, and you only have to do it once.

so you can either devleo pa solution that generates a code

P001

or you can develop a solution that generates the code in 2 parts

P and 001 (actually just 1)

The second way is far more flexible. You can add the next item P and 002 with no complexity. The first way, you have to separate the P from the numbers, increment the number and put it back together. Far harder.

By the way, having a single code P001 may be a problem - what if the number goes above 999?

Either way, the user just sees P001

Hi. I think there was a bit of a misunderstanding. I just decided to make things simple and just settle with the normal autonumbers, without prefix. I guess I should have marked this thread as solved. Thank you for your tip. I will actually still try it.

 

[GinaWhipp]

So you want to keep historical data...

In this situation I would create a Log table that stores changes made to records at the Field Level. No sense in storing the entire record if someone just changes a few pieces of information. The Log table could use the Employee's ID to link on. Then you could pull it up when needed and it's one table and not multiple Historical tables which defeats the purpose of a database and relational design.

As for you issue which the Category only showing the ID instead of the name, you need to create a query and drop the Category table in with the table and then you would drop the Category Name in the query and use that in your Report.

I also observed that one can open the database without the password and this is not good. You are storing sensitive information (or you will be) and that is a serious problem. What steps will be taken to be sure no one but HR has access to this data?

 

[johannaellamay]

So you want to keep historical data...

In this situation I would create a Log table that stores changes made to records at the Field Level. No sense in storing the entire record if someone just changes a few pieces of information. The Log table could use the Employee's ID to link on. Then you could pull it up when needed and it's one table and not multiple Historical tables which defeats the purpose of a database and relational design.

Did you see t_AuditTrail? Is that what you meant with a Log Table? I created a sort of Log Table table and added and After Update Data Macro for the fields that I want to store historical data.

As for you issue which the Category only showing the ID instead of the name, you need to create a query and drop the Category table in with the table and then you would drop the Category Name in the query and use that in your Report.

Hmm. If I'm only to use one log table, then I don't think I would need this part anymore. I just have to figure out how to filter t_AuditTrail to only show specific data that I want or something.

I also observed that one can open the database without the password and this is not good. You are storing sensitive information (or you will be) and that is a serious problem. What steps will be taken to be sure no one but HR has access to this data?

Actually, the security measures have not yet been completed. I figured, I should do that last. I know I could press Alt+F11, and I left it out on purpose just in case. I will however, disable the shortcuts later on. I will also change the options so I can eliminate the navigation button, status bar, ribbon, etc.

Did you see any other loopholes? Please let me know so I can also correct them.

 

[GinaWhipp]

1. Yes, I saw that and yes that is what I meant, it's just I don't use Macros.

2. Use the Employee's ID since you want to see the changes per Employee. That could go in the atRecordID field and use the atTableID to store which table it comes from. (In mine I use which Form it came from, easier for me to filter by)

3. Make sure you do all that on a copy of the database as you will want to have a Design Master. You should also consider splitting so you don't have to worry about moving new data over to your updated database... because you people are going to request changes.

For real security I put the backend on an SQL Server, barring that what you listed above will keep the honest person honest.

 

[johannaellamay]

1. Yes, I saw that and yes that is what I meant, it's just I don't use Macros.

2. Use the Employee's ID since you want to see the changes per Employee. That could go in the atRecordID field and use the atTableID to store which table it comes from. (In mine I use which Form it came from, easier for me to filter by)

3. Make sure you do all that on a copy of the database as you will want to have a Design Master. You should also consider splitting so you don't have to worry about moving new data over to your updated database... because you people are going to request changes.

For real security I put the backend on an SQL Server, barring that what you listed above will keep the honest person honest.

1. If you don't use macros, how do you do it then?

2. If I do this, then I will see all the employee details even the ones that did not change? You mentioned RecordID and not FieldID? I'm sorry. I really couldn't get grasp of this. I truly am confused.

3. What is a "Design Master"? Oh yeah, I forgot about splitting. Yes, of course I will split my database. Securing an SQL Server however would be too much for me. I have no idea what it is. And as of now, we're only using the database for small-scale. Do SQL servers cost money?