Form with two tables as sources and adding info.

[shayko]

Hi Guys,

It's been a while since my last access project...
I know that maybe my question will be basic for most of you, but I will be greatful to get some help dealing with it:
I need to prepare an access form that will manage work hours.
I have three tables that each has the following:
Table 1: Employee number, Name, Surname
Table 2: Brunch number, Brunch name
Table 3: All of the above + Date, Hours (in amount) - my form needs all of this.

Where is my problem:
I manage to create two combo boxes in my form. One opens the Employee numbers list and the other opens the Brunch numbers list.
I need that each combo box will automaticaly fill the related boxes e.g. when I choose Employee number 1000 it will show me also the related person.
As well as in the 2nd combo box that chooses the brunch number, I need it to show the brunch name.
Now remember, that all of the above plus the two extra boxes (date and hours) should be written to my third table...

I know its a long story written, but I'm quite sure the solution is simple... I just couldnt remember it.

Thanks to all helpers...

[NeoPa]

Maybe knowing what a Brunch was might enable me to make more sense of this. Currently I see no way of correlating any table with any other. Maybe Table 3 does, but I still have no idea where Date & Hours fit in.

Please could you explain also what you start with, and what you expect the form to do for you.

[shayko]

First, thanks for the quick response.

I will try to clarify myself...

The idea of this DB is to give the option to each employee to register his work hours thru LAN in the office without the need of filling paper reports.
Note: each employee can work in different branches so he needs to report for each one of them.

Tables
Table 1 called "MembersDetails" and includes the following details in three columns:
EmpNumber (4 digits of employee number)
Name (text)
SurName (text)
Example:
1000 | John | Smith
1001 | Mary | James
2541 | Arthur | Redford

Table 2 called "Branches" and includes the following details in two columns:
BrNumber (4 digits)
BrName (text)
Example:
150 | Kitchen
164 | Warehouse
501 | Gardening

Table 3 called "HourReport" and includes the following headlines:
EmpNumber | Name | SurName | BrNumber | BrName | Date | Hours
(This is the table that should include all details from a form that includes all of the above)

Form
In the form I'm trying to do the following:
1. ComboBox where the employee chooses his number (table 1) and automatically this fills the following two text boxes (name, surname) with the apropriate details.
2. ComboBox where the employee chooses his branch number (table 2) and automatically this fills the following text box (branch name).
3. Date box, where the employee chooses a date to report.
4. Text box where the employee report an amount of hours (e.g 7).

All of the above information should be saved in the 3rd table called "HourReport".

The form part is where I fail again and again of curse... :-)

Hope that's help in understanding my problem.

TNX.

[NeoPa]

That is much clearer :)

What you need to start with is a Table 3 (HourReport) set up with foreign keys to link into the MembersDetails & Branches tables. It is only necessary to have these fields in the table (Table 3 == HourReport). There is no need (indeed it would be an error) to include any other fields from either of the other two tables.
Table=HourReport

Field      Type     Index
WorkNumber AutoNumber  PK
EmpNumber  Numeric     FK (MembersDetails)
BrNumber   Numeric     FK (Branches)
WorkDate   Date
Hours      Numeric

Now, your form should have ComboBoxes for both foreign key fields. These should be fed by the data from the tables they are keys of. Once a record is selected the related data should be shown on the form, but not in bound controls. This can be done with unbound textboxes updated by code in an AfterUpdate event procedure for the ComboBox, or simply design the ComboBoxes such that the key is treated as the value but the related data is what is shown in the control when selected.

After that the rest should be simple enough. Let us know how you get on, or if you have any further questions on the matter.

[shayko]

TNX again for your reply!

I am most interested in unbound text boxes updated by code...
I already did those steps you mentiond and this part is very clear, but the thing is that there is a need to show complete details of each employee as he chooses his number...
You are right, there is no logical need of these details in the HourReport table, but visualy I need it. (I believe there is a way to exclude those details from being entered to the table, am I wrong?).

Hope it's not much of a trouble for you :-)

TNX.

[NeoPa]

That is why you show it on the form, but not store it in the table.

Why don't you go as far as you can with unbound TextBoxes (Create the form; add the ComboBoxes and TextBoxes; Get it working as much as you are able), then explain where you still need help - giving details of what you've managed to do and what the names are of your objects (Form; Controls; etc), and we'll look at fleshing out the last bits for you.