AutoFill, Relationship, Form, Control Issues

[Collins]

I have a Task Form. In the form I have several different tasks.

I then have a TabControl with Personnel, Equipment and Tools.

In the Personnel, Equipment and Tools SubForms I am trying to get several text fields to AutoFill when I pick a given Person, Piece of Equipment or Tool and have them assigned to only that one task.

The database will be used on computers without Access so I have to use the RunTime App for MS which does not allow VBA...

So can the above process happen without code and... why can I not get the subforms to connect to One task rather than all tasks???

Task - Tbl

TaskID
PersonnelID
EquipmentID
ToolsID
WorkOrderNumber
WODate
Description
Building
Floor


Department
Room
ContactPerson
ContactNumber

Personnel - Tbl

PersonnelID
TaskID
EmpolymentDate
LastName
FirstName
JobDescription
Seniority
PayRate
BillRate

Equipment - Tbl

EquipmentID
TaskID
Type
Size
Weight
Location
Quantity
Hours
VinNumber
Restrictions
Permits

Tools - Tbl

ToolsID
TaskID
Type
Description
AssignedTo
Location

[CJ_London]

The database will be used on computers without Access so I have to use the RunTime App for MS which does not allow VBA...

not true. VBA can be used, but runtime users will not have access to the ribbon, navigation pane or shortcut menus. By the same token they will not be able to go into the design or layout view for tables, queries, forms, reports of modules. If you need them to have this functionality, you need to develop your own equivalents.

[Collins]

not true. VBA can be used, but runtime users will not have access to the ribbon, navigation pane or shortcut menus. By the same token they will not be able to go into the design or layout view for tables, queries, forms, reports of modules. If you need them to have this functionality, you need to develop your own equivalents.

So RunTime will run VBA code?

[CJ_London]

So RunTime will run VBA code?

yes - if you want to know what it looks like, change the name of your access file extension to .accdr and then open it to imitate a runtime environment. Note you will have to specify an navigation form or login form (in file>options>current database - display form) or create an autoexec macro to open a form since users cannot choose from the navigation window

[Collins]

yes - if you want to know what it looks like, change the name of your access file extension to .accdr and then open it to imitate a runtime environment. Note you will have to specify an navigation form or login form (in file>options>current database - display form) or create an autoexec macro to open a form since users cannot choose from the navigation window

I didn't know that. I've been having a tough time finding computers without access to test RunTime on. So if I just publish the database in .accdr it will work as if I'm running on RunTime and can test it that way?

[Collins]

Also... Would DLookUp be the route to go for those AutoFill Fields?

[CJ_London]

So if I just publish the database in .accdr it will work as if I'm running on RunTime and can test it that way?

no need to publish, just change the file extension. You can then change it back again to edit the file

Also... Would DLookUp be the route to go for those AutoFill Fields?

it's one way to go, but without a full understanding of what you are trying to do I can't say it is the best way.

your tables don't look right to me. For example - why have you got taskID in all the tables? have you defined relationships - if so, what are they? one to many? many to many? one to one?

[Collins]

your tables don't look right to me. For example - why have you got taskID in all the tables? have you defined relationships - if so, what are they? one to many? many to many? one to one?

LOL... It's highly possible my tables are wrong. I want ONE Task with MANY Personnel assigned to it with MANY pieces of Equipment and Tools assigned to that ONE task as well.

[CJ_London]

Unless personnel are only used for one task, and one task only, you have a many to many relationship, not a one to many.

Assuming you mean many to many you need

table for tasks
table for personnel
table for equipment
table for tools

plus joining tables

tasks>personnel
tasks>equipment
tasks>tools

using tasks/personnel as an example

tblTasks
TaskPK
WorkOrderNumber
WODate
Description
Building
Floor
Department
Room
ContactPerson
ContactNumber

tblPersonnel
PersonnelPK
EmpolymentDate
LastName
FirstName
JobDescription
Seniority
PayRate
BillRate

tblTaskPersonnel
TaskPersonnePK autonumber
TaskFK long
PersonnelFK long
you might need additional fields in this table such as dates to start/finish tasks

[Collins]

Thanks Ajax...

I'm with you on the PK = Primary Key.

But what is FK Long?

[CJ_London]

FK=Foreign (or Family) Key

[Collins]

FK=Foreign (or Family) Key

I'm not familiar with this one. Is this to be used for the relationship connection?

Sorry Ajax... I can be kinda thick sometimes.

[CJ_London]

Primary Key uniquely identifies a record, Foreign Key is a field in another table which references back to the primary key.

I use the PK and FK suffix so I know which end of a relationship the field belongs. Many will use the same suffix (e.g. ID)

Recommend you do some googling on relationships, normalisation and database design before going any further.