Btn Click(1st form) to Open 2nd form with 1st form details

[vancey]

Hi all,
Basically i have a set of records from Employees table. I have another table tblEmployeesHIPO which contains additional info about the previous table , i have created a button in the first form( frmEmployees) to open up a 2nd form (frmEmployeesHIPO). I want the 2nd form to show details like the EmployeeID, Fullname, etc from the first table (Employees) (not allowed to edit), and those additional fields which are on another table(tblEmployeesHIPO) for them to fill in. How do i go about loading the 2nd form with details from first, and fields for them to fill in and save for every single record? Hope it doesnt sound too confusing. Im very new to access, please help me.. Thanks!
Example:
EmployeeID : TEXTBOX (from 1st table) (uneditable)
Full Name: TEXTBOX (from 1st table) (uneditable)
..............
TPM Ratings: TEXTBOX (from 2nd table)(to be filled in)
Candidate's Readiness (from 2nd table)(to be filled in)
C-A-R Score: (from 2nd table)(to be filled in)
.............

I have created a button but once i clicked it, it says "no current record"and the EmployeeID doesnt show up.
i have a query on the 2nd form which is:
SELECT Employees.EmployeeID, Employees.FullName, Employees.DateOfBirth, Employees.Designation1, Employees.JobGrade, Employees.DateJoined, tblEmployeesHIPO.Gender, tblEmployeesHIPO.TPMratings, tblEmployeesHIPO.CARscore, tblEmployeesHIPO.carCapacity, tblEmployeesHIPO.carAchievement, tblEmployeesHIPO.carRelationship, tblEmployeesHIPO.carEthos, tblEmployeesHIPO.CEP, tblEmployeesHIPO.CandidateReadiness, tblEmployeesHIPO.degDescQualification, tblEmployeesHIPO.degSpecialization, tblEmployeesHIPO.degSchool, tblEmployeesHIPO.degYear, tblEmployeesHIPO.extDesignation, tblEmployeesHIPO.extEmployer, tblEmployeesHIPO.extPeriodstart, tblEmployeesHIPO.extPeriodend, tblEmployeesHIPO.intDesignation, tblEmployeesHIPO.intJobGrade, tblEmployeesHIPO.intNHGInstitution, tblEmployeesHIPO.intPeriodstart, tblEmployeesHIPO.inPeriodend, tblEmployeesHIPO.potCareerSenario1, tblEmployeesHIPO.potCareerSenario2
FROM Employees INNER JOIN tblEmployeesHIPO ON Employees.EmployeeID = tblEmployeesHIPO.EmployeeID;


Regards,
VanCe

 

[John Big Booty]

You could use the OpenArgs portion of the Docmd.OpenFrom command to pass the EmployeeID to the form being opened, an then use DLookUp to collect the other pieces of information you are after.

 

[vancey]

Hi John,

Thanks for the response, i have tried to pass the 3 info to the OpenArg:

cmdbutton to open the 2nd form:

Private Sub cmdNominate_Click()
On Error GoTo Err_cmdNominate_Click

stLinkCriteria = "[EmployeeID]=" & "'" & Me![EmployeeID] & "'"
DoCmd.OpenForm "frmEmployeesHIPO", , ,stLinkCriteria, acFormAdd, , Me.EmployeeID & "$" & Me.FullName & "$" & Me.DateOfBirth
Exit_cmdNominate_Click:
Exit Sub
Err_cmdNominate_Click:
MsgBox Err.Description
Resume Exit_cmdNominate_Click

End Sub

and retrieve the information:

Private Sub Form_Load()
Me.EmployeeID = DLookup("EmployeeID", "Employees")
Me.FullName = DLookup("FullName", "Employees")
Me.DateOfBirth = DLookup("DateOfBirth", "Employees")

End Sub

i got the error msg "You entered an expression that has an invalid reference to the RecordsetClone property." Could you help me on this? Thankss~

 

[vancey]

Hi John,

I solved the above error, i forgot to bound the form to the tblEmployeesHIPO table.

But after i clicked on the button to open the new form, it says "No current record". What does this error msg means?

and when i tried to save after keying in the other fields, it says "Index or Primary Key cannot contain a NULL value." But i thought it have a value in the previous form?

 

[John Big Booty]

Given that you are passing three pieces of information in the OpenArgs. You can use the On Load event of the form being called to break the the OpenArgs string back to it's component parts using the various available String manipulation functions and assign those parts to the appropriate controls.

 

[vancey]

Im still getting the "No current record" error msg and when i tried to save after keying in the other fields, it says "Index or Primary Key cannot contain a NULL value."

When i scroll through the records then click on the button(open 2nd form) the record doesnt relates to the main form. Please help me..

 

[John Big Booty]

In your On Load event you will need some code that looks something like;

Dim stInfo As String
stInfo = OpenArgs

If Not IsNull(stInfo) Then
     Me.EmployeeID = Left(stInfo, Instr (stInfo, "$")-1)
     stInfo = Right(stInfo, Len(Instr (stInfo, "$")
     Me.FullName = Left(stInfo, Instr (stInfo, "$")-1)
     stInfo = Right(stInfo, Len(Instr (stInfo, "$")
     Me.DateOfBirth = stInfo
End If

 

[missinglinq]

The dollar sign $ has special significance in Access, so I'd select another delimiter for starters.

Then I'd force a save before opening the second form:

[B]If Me.Dirty Then Me.Dirty = False
[/B]DoCmd.OpenForm "frmEmployeesHIPO", , ,stLinkCriteria, acFormAdd, , Me.EmployeeID & "$" & Me.FullName & "$" & Me.DateOfBirth

I think that's why you're getting the No Record message.

Linq ;0)>

 

[vancey]

Hi linq,

i changed the delimiter to a 'comma' (,) , and applied the code to the cmdbtn to open the form, but im still getting the 'no current record' error msg. is it because i have not created anything yet? May i know are the delimiter essential in passing multiple arguments?

When i tried to create one HIPO record for tat particular employee it cant be saved, saying that "index or primary key cannot be Null".

In fact, when i scroll to another employee then click the open form button, i don't get that employees' ID displayed, instead it displayed the very first record's ID.

 

[John Big Booty]

Can you post a copy of your DB ('03 version for preference)?

 

[missinglinq]

Of course you have to use a delimiter to pass multiple values! How else can Access tell where one item ends and the next one starts?

And I agree with JBB, we really need to lay our hand on your app, I think, in order to see what's happening here.

 

[vancey]

Hi John and Linq,

Sorry im so newbie in access ><

My copy of db is attached in my first post. Its 2003 version. Thanks!

 

[John Big Booty]

You are getting the error No Current Record because you have code in the On Current event and also the On Load event of the form being loaded which is trying to goto to another record.

I've fixed the little piece of code to pass the information between the forms that now works. It looks like;

    Dim stInfo As String
    stInfo = OpenArgs

    If Not IsNull(stInfo) Then
          Me.EmployeeID = Left(stInfo, InStr(stInfo, "*") - 1)
          stInfo = Right(stInfo, Len(stInfo) - InStr(stInfo, "*"))
          Me.FullName = Left(stInfo, InStr(stInfo, "*") - 1)
          stInfo = Right(stInfo, Len(stInfo) - InStr(stInfo, "*"))
          Me.DateOfBirth = stInfo
    End If

Now before you get all excited your DB is seriously de-normalised. So I would suggest you read up on Data Normalisation and become familiar with the concept and practice. This tutorial may aid in your understanding of the subject. The first thing you are going to have to do is to forget anything you know about Excel and spread sheet structure as Access is a relational Database and treating it as a spreadsheet will cause you nothing but frustration.

Once you have achieved that I would probably start with a clean slate.

 

[John Big Booty]

...sorry forgot to attached your DB

 

[vancey]

Thank you John and linq for the helps, advises and the tutorial links! i will continue to study more on normalization.

 

[vancey]

Hi John,

i went to read up about normalization but having some difficulty to understand for my side and apply to my database. Im trying now to normalize my data, but it seems to me that everything is related to Employee,

Maybe for example, i just need users to key in their department, and i do not have other department details like department code, department size etc so am i were to create a table just for department that only contains department id and name? if i were to create a table for every fields, e.g Designation, Mentor, Supervisor..... it will be like too much tables...i showed an example

I know normalization is very important, hope you will guide me on how to realise which field should go where in my case of 2 hardcopies of application form for users to fill in where there are too much fields. because i really do not understand it..

Example:

Employee table
EmployeeID
NIRC
Honorific
Surname
First Name
FullName
Email
Handphone
DOB
Nationality
OfficeDID (seems to be related to jobs, but i do not know where to put it)
Mailing Address
City
Postal Code
Passport No.

Designation table
Designation ID
Designation Name (basically i just need the name only, so i put it together in Employees table. Pls guide me if im wrong )

JobGrade table
JobGradeID
DateOfLastGrade (im not sure where to place this field to either)

Department table
Department ID
Department Name

.............and other tables like mentor, supervisor etc

Really hope that you will guide me on this, as im really confused about normalization. Really appreciate all your help and patience!

 

[John Big Booty]

What you need to think about is; Is this piece of information unique to the employee or is it something that might be shared with other employees.

In your Employee Table, you currently have things like;

• Honorific
• Nationality
• City
• Post Code

all of these things are common to all employees so should be hived off into their own tables and only the ID's should be stored in your Employee Table

• HonorificID
• NationalityID
• PostCodeID

Now you might notice that I have left City off that list and that is because the post code is directly related to the city so there is no need to store both the PostCodeID and the CityID.

One way to think about normalisation is to think of it in terms of a tree. So you start with your core piece of unique information, lets say your employee, any thing that is not unique to your employee branches off into it's own table which may well have it's own branches sprouting off and so on.

So if we start with the employee table, in that you would store;

• EmplyeeID (PK)
• Given Name
• Family Name
• GenderID (FK)
• DOB
• EmailAddress
• Postal Address
• PostcodeID (FK)
• HonorificID (FK)
• NationalityID (FK)

Now if you might want to store the employment history of your employee so we'll store that in a Sub table called Employee History, which might look something like;

• EmployeeID (FK)
• PostionID (FK)
• PositionStDate
• Comments

Probably the best thing to to before you go to Access is to sit down with a pencil and paper and draw out you table structure, and draw the links between the various tables, once you are happy with your structure then start building your tables. The main question you need to ask is; will this piece of information ever be repeated within this table? If the answer is yes then in all likelihood it should be stored in it's own table. In this way you avoid situations where the same piece of information gets entered into a table in a number of different ways for example the Honorific;

• Mister
• Mr.
• Mr
• Mstr
• and other variations and misspellings

I'm sure you can already see the problems that this would cause if you trying to group employees by their honorific.

 

[vancey]

Thanks john for the great explanation and examples, i will continue to look into it and hope to get more understandings..

 

[vancey]

Hi John, after some reading on normalization, i tried to sort up my tables and sketched my plan, relationship im still not so sure (like 1-1, 1-many etc) yet, hope you will help me take a look and see if im going the right track. Its my first time designing, pardon me if it looks absurd.

Hope the image is not so blurry.

Thanks so much, appreciate your help!

 

[John Big Booty]

Yep that's looking much better,well done

You may still find that things need adjusting, but I think you've come a long way.