How to link a 1st form to 2nd form which the 2nd form is link to a table

[Santiadji]

Dear Sir,

Please help me for this,
I have two tables, for example the 1st table's name is 'employee' and the 2nd
table's name is 'InspectorResult'. The employee table have fields: employee
name, badge no, division, title, etc.
The InspectorResult table have fields: employee name, badge no, division,
PaperTestResult, & PraticalTestResult.
I make a form which control source to InspectorResult table. I will fill
manually to fields PaperTestResult & PraticalTestResult. But for fields
employee name, badge no & division I want it to be filled automatically by
link to employee table, And will be input to InspectorResult table, cause I
need to make query from the InspectorResult table for other purpose.
Can I do that?
Please your help for this.
Thaks a lot

Rgds
Santiadji

 

[tina]

yes, you could - but you shouldn't. i'll guess that the primary key of
tblEmployee is the BadgeNo field. if that's the case, you need to use that
field - and ONLY that field - as a foreign key in tblInspectorResult.
example:

tblEmployees
BadgeNo (primary key)
FirstName
LastName
Division
Title

tblInspectorResults
ResultID (primary key)
BadgeNo (foreign key from tblEmployees)
TestType (paper test, or practical test)
TestResult

relationship is
tblEmployees.BadgeNo 1:n tblInspectorResults.BadgeNo

you can create a form bound to tblInspectorResults. add a combobox control,
with ControlSource set to BadgeNo and RowSource set to a SQL statement, as

SELECT BadgeNo, LastName & ", " & FirstName FROM tblEmployees;

if you want to enter an employee by their badge number, set the ColumnWidth
of the first column to 1; if you want to enter an employee by their name,
set the width of the first column to 0. you'll need to read up on combobox
control properties, so you'll understand how they work.

you can link the two tables in a query when you need to make the employee
names, division, etc available in the same record with inspector results.

recommend you read up on relational design principles, so you'll understand
why your tables/relationships design was flawed and why the one i posted is
more correct. also, you could use some tips on naming conventions, etc. for
more information, see http://home.att.net/~california.db/tips.html.

hth

 

[Santiadji]

Thank you Ms Tina,
But, I am sorry, I never use foreign key before, How to get (set) the foreign
key?

Once again thank you, It helpful for me, cause I need to finished it at 24Nov
due to urgent request from my Bos.
Please your help

yes, you could - but you shouldn't. i'll guess that the primary key of
tblEmployee is the BadgeNo field. if that's the case, you need to use that
field - and ONLY that field - as a foreign key in tblInspectorResult.
example:

tblEmployees
BadgeNo (primary key)
FirstName
LastName
Division
Title

tblInspectorResults
ResultID (primary key)
BadgeNo (foreign key from tblEmployees)
TestType (paper test, or practical test)
TestResult

relationship is
tblEmployees.BadgeNo 1:n tblInspectorResults.BadgeNo

you can create a form bound to tblInspectorResults. add a combobox control,
with ControlSource set to BadgeNo and RowSource set to a SQL statement, as

SELECT BadgeNo, LastName & ", " & FirstName FROM tblEmployees;

if you want to enter an employee by their badge number, set the ColumnWidth
of the first column to 1; if you want to enter an employee by their name,
set the width of the first column to 0. you'll need to read up on combobox
control properties, so you'll understand how they work.

you can link the two tables in a query when you need to make the employee
names, division, etc available in the same record with inspector results.

recommend you read up on relational design principles, so you'll understand
why your tables/relationships design was flawed and why the one i posted is
more correct. also, you could use some tips on naming conventions, etc. for
more information, see http://home.att.net/~california.db/tips.html.

hth

Dear Sir,

[quoted text clipped - 15 lines]

Rgds
Santiadji

 

[Santiadji]

Thank you for the help
I tried already the below instruction, but I am sorry, It does not work as my
expectation.
I already created a form bound to tblInspectorResults and add a combobox
control.
I put textbox on the form are: ResultID, BadgeNo, FirstName, Division, &
TestResult. But when I click the combobox, it fill automatically to textbox
BadgeNo only, not to FirstName & Division textbox.
And when I checked to tblInspectorResults, only show field NadgeNo which have
data.
Would you please help me for this

Thanks again

yes, you could - but you shouldn't. i'll guess that the primary key of
tblEmployee is the BadgeNo field. if that's the case, you need to use that
field - and ONLY that field - as a foreign key in tblInspectorResult.
example:

tblEmployees
BadgeNo (primary key)
FirstName
LastName
Division
Title

tblInspectorResults
ResultID (primary key)
BadgeNo (foreign key from tblEmployees)
TestType (paper test, or practical test)
TestResult

relationship is
tblEmployees.BadgeNo 1:n tblInspectorResults.BadgeNo

you can create a form bound to tblInspectorResults. add a combobox control,
with ControlSource set to BadgeNo and RowSource set to a SQL statement, as

SELECT BadgeNo, LastName & ", " & FirstName FROM tblEmployees;

if you want to enter an employee by their badge number, set the ColumnWidth
of the first column to 1; if you want to enter an employee by their name,
set the width of the first column to 0. you'll need to read up on combobox
control properties, so you'll understand how they work.

you can link the two tables in a query when you need to make the employee
names, division, etc available in the same record with inspector results.

recommend you read up on relational design principles, so you'll understand
why your tables/relationships design was flawed and why the one i posted is
more correct. also, you could use some tips on naming conventions, etc. for
more information, see http://home.att.net/~california.db/tips.html.

hth

Dear Sir,

[quoted text clipped - 15 lines]

Rgds
Santiadji

 

[tina]

And when I checked to tblInspectorResults, only show field NadgeNo which
have

data.

correct. and again, assuming BadgeNo is the primary key of tblEmployees,
that is the only field you should have in tblInspectorResults - not name,
not division, just badge number.

Santiadji, nothing i tell you is going to make sense until you understand
the basics of relational design, which include the use of primary and
foreign keys. and there's no point in me writing a long explanation here,
when it's already been done many times, and better than i would do it, in
the links i directed you to. and since you're on a deadline, suggest you go
to those links and start reading right away.

hth

Thank you for the help
I tried already the below instruction, but I am sorry, It does not work as my
expectation.
I already created a form bound to tblInspectorResults and add a combobox
control.
I put textbox on the form are: ResultID, BadgeNo, FirstName, Division, &
TestResult. But when I click the combobox, it fill automatically to textbox
BadgeNo only, not to FirstName & Division textbox.
And when I checked to tblInspectorResults, only show field NadgeNo which have
data.
Would you please help me for this

Thanks again

yes, you could - but you shouldn't. i'll guess that the primary key of
tblEmployee is the BadgeNo field. if that's the case, you need to use that
field - and ONLY that field - as a foreign key in tblInspectorResult.
example:

tblEmployees
BadgeNo (primary key)
FirstName
LastName
Division
Title

tblInspectorResults
ResultID (primary key)
BadgeNo (foreign key from tblEmployees)
TestType (paper test, or practical test)
TestResult

relationship is
tblEmployees.BadgeNo 1:n tblInspectorResults.BadgeNo

you can create a form bound to tblInspectorResults. add a combobox control,
with ControlSource set to BadgeNo and RowSource set to a SQL statement, as

SELECT BadgeNo, LastName & ", " & FirstName FROM tblEmployees;

if you want to enter an employee by their badge number, set the ColumnWidth
of the first column to 1; if you want to enter an employee by their name,
set the width of the first column to 0. you'll need to read up on combobox
control properties, so you'll understand how they work.

you can link the two tables in a query when you need to make the employee
names, division, etc available in the same record with inspector results.

recommend you read up on relational design principles, so you'll understand
why your tables/relationships design was flawed and why the one i posted is
more correct. also, you could use some tips on naming conventions, etc. for
more information, see http://home.att.net/~california.db/tips.html.

hth

Dear Sir,

[quoted text clipped - 15 lines]

Rgds
Santiadji