How to Insert combo box values into the table

[VictorS]

Hello Everybody!
I'm newbie here looking for an advice.

I have two combo boxes and 1 txtbox on the main form:
cboGroupName
cboEmployeeName (filtered based on cboGroup selection)
txtEmployeeId. (shows Id of the Employee Name selected in 2nd cbo)
This part works perfect.

I need to populate all those three selected values into the table "tblCompensation" . I've created a subform bound to this table, but can't link it to the frmMain- there are no records yet in the "tblCompensation".

Thanks in advance for your help!

[NeoPa]

You'll need to post how your tables fit together.
MetaData of both tables should do for you.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[VictorS]

Here is my tables info:


tblGroups

GroupCode;Text;PK
GroupName;Text

tblEmployees

LastName;Text
FirstName;Text
EmplloyeeId;Text;PK
GroupName;Text;FK

tblCompensation

id;Autonumber;PK
GroupName;Text
LastName;Text
FirstName;Text
EmployeeId;Text;FK
CompensationType;Text
Comp.Amount;Currency
CompensationDate;Date/Time

tblCompensation has no records now. I need to create a data-entry form for it using two combo boxes I mentioned.

Thanks a lot!

[NeoPa]

Before I get into the nitty-gritty of the answer, there are a couple of points to make :

1. Only use a name like [Comp.Amount] for a field if you actively like trouble.
2. tblEmployees has a field GroupName which should surely be GroupCode instead.
3. Your database will work more efficiently if you use AutoNumber fields for your PKs. So much of Access's work is done using the PKs and every time it needs to process a whole string rather than a 4-byte number, is slowing the performance.

[NeoPa]

Another point I noticed while working on the answer is that your [tblCompensation] table should be :
tblCompensation

ID;Autonumber;PK
EmployeeId;Text;FK
CompType;Text
CompAmount;Currency
CompDate;Date/Time

All the other fields are referenceable via the [EmployeeId] FK.
For consistency, and to avoid confusion and/or errors later, use the abbreviation 'Comp' (or not) throughout.
If you're curious as to why it's a bad idea to store the same data twice in two different locations, check out Normalisation and Table structures.

[Denburt]

tblGroups

GroupID:Autonumber
GroupCode;Text;PK
GroupName;Text 
 

tblEmployees

EmployeeID;Autonumber
GroupID;Number (using lookup tie this into groups)
LastName;Text
FirstName;Text
EmplloyeeId;Text;PK
 

tblCompensation

Compid;Autonumber
EmployeeId;Number (Tied to Employees)
CompensationType;Text
CompAmount;Currency
CompensationDate;Date/Time 
 

I think your table structuring should look a bit more like this.
Then you can assign relationships between the tables and tie them together in a more efficient fashion.

Using any characters or spaces other than letters in a field name or table name, forms etc. will cause trouble eventually (my experience).

As a note I hate using [ID] as a field name it really lacks meaning if you use EmployeeID or even EmpID you can put it in both tables and the queries will be more likely to recognize it and auto join them for you, although you should set a designated relationship (Tools Relationships).
Oh and the PK FK ? maybe I need another cup of Mud BRB

[NeoPa]

The first thing you need to do is to set up relationships between these tables in your database. Tools / relationships... takes you to the relevant window. Full referential integrity is a good idea in most cases (I suspect it is in yours).
When that is done, use the Form Wizard to create a Form (with a sub-form) which will automatically link the fields as per the relationship defined.
Let us know how you get on with this and if you come across any problems.

[VictorS]

Thank you very much for your help!
I corrected everything based on NeoPa's suggestions and it does do what I wanted.
Eventually I might have to use a Denburt's way to design these tables.

My real problem is that tblGroups and tblEmployees(EmployeeId was a Text there) are exported from another database to track Compensation. The reason I tried to include GroupId or GroupName in the Compensation is that combination Employee-Group is changing quite a bit every year and managament would like
to see what Group every Employee belong to every year. The other database tracks changes of Employee positions, not groups.
I think I just answered my own question:)

Thanks again!

Victor

[NeoPa]

Glad to hear you got it working Victor.
The wizards are a really powerful concept in Access. One of their most useful points is that you can learn a great deal if you examine what they have created for you.
BTW Denburt's recommendations were broadly in line with what I'd said in my earlier posts. Expressed a little differently, but worth considering anyway - even if you have got past the first hurdle.
Never waste good advice.

[Denburt]

Glad you got it going and I understand your point about tracking the employees groups over the years, makes sense.

Nicely done Neo.