Employee Database

[SkipMartin]

I am attemting to create an employee database to replace a spreadsheet used for the same purpose.

Each record has company related info and personal info.

Issue 1:

I would like to allow the manager adding new records to add info to both records in one continuous action, but it does not seem like you can append to two different tables using the same single record form. Does anyone have any advice or examples of how this can be done.

Issue 2:

I am very familiar with query and table design, but have no experience with forms or VBA. I would like to have view functions, edit functions and new record functions for the database. How do you use a popup form to filter the records that need to be edited or viewed based on the value entered by the user. ie. a manager enters his name to view all of his records or an employee id to edit or view only one record?

Thanks

 

[ghubbell]

Hi Skip,
I think the first thing you'll have to look at is the design structure of the database. I get a little spooked when I see someone ask to "duplicate" something. Perhaps you have a table with company data, and another with personnel data. If your database layout is good, and you want to do what I think you want to do (that's a mouth full), you should have another table that is "blending" this data. Perhaps you'd look at your structure and write back here to confirm exactly how it is. Then we'll move on to getting the data where you want it! Gord
ghubbell@total.net

 

[SkipMartin]

Thanks for the quick response.

All I have set up so far is the following.

Employee_tbl (All 32 Data Fields)
Employee_Dell_tbl (21 Business Related Fields)
Employee_Personal_tbl (11 Personal Fields, ID Field (For Joins)

New_Employee_frm (31 Data Fields) Currently appends to Employee_tbl (1 of the 32 Fields is Default for Oracle query indexing and is not on the form). Properties set to open to empty record for data entry only.

View_Employee_frm (Currently Same as New_Employee_frm, but properties set to view all records in read only manner)

Here is my wish list:

1. New_Employee_frm appends to both Employee_Dell_tbl and Employee_Personal_tbl fields as appropriate from one contiguous form. Force user to abandon incomplete record or save complete record prior to leaving form in order to avoid incomplete records.

2. Allow user to filter what records return for the View_Employee_frm when it opens by selecting criteria in a popup prior to the View form opening.

3. Create new edit forms, one for E_D_tbl and one for E_P_tbl that will open records to be edited baased on user filtered selecting criteria in a popup prior to the Edit form opening.

 

[ghubbell]

This looks quite "do-able", maybe a little change, but now we need to know what the relationships are from one table to another, and perhaps a hint of the types of data you're placing in each table: this way we'll figure out if the layout is the best for you! Gord
ghubbell@total.net

 

[SkipMartin]

Using ID Field for One to One Joins. The data is text and number info, pretty much what you would see an any employee or contact database/

 

[ghubbell]

Thanks Skip, I'll guess you've split the tables with the 1-1 join for a security purpose. Your New Employee form can be based off of a query that is made up of your first two tables. Any fields that you want to fill that are "duplicated" could be filled by simple commands in the "After Update event of the first field such as:
Me.Field1 = Me.Field2
You can hide the second field as you wish. You could also play with the "default values" of the second table's fields such as:
= Me.Field1
Then again in the after update event of field1 use something like this:
Me.field2.Requery

Your popup form should be based on any table that will give you data required to "feed" any form.

In the after update event for a combobox on your popup or in a command buttons' "On Click" event you'd have something like:

DoCmd.OpenForm "NameofFormToOpen", , , "[IDNumberOnForm]= " & Me.NameOfCombo

As for add/edit forms: I try to do all my work in one place 'cause I hate the maintenance. How about one form with an add or add and edit button(s). Control the visibility of these buttons depending on the user. You can go further with this and actually control fields visibility too. It seems a bit more complex at first, but in the end you only look after one thing instead of two:
If CurrentUser() = "Skip" Then
Me.CmdAdd.Visible = True
Else
Me.CmdAdd.Visible = False
End if

Absorb this and see where we should go from here... Gord
ghubbell@total.net