By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 883 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

How to Insert combo box values into the table

P: 12
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!
Mar 20 '07 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,347
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Mar 21 '07 #2

P: 12
Here is my tables info:


tblGroups
Expand|Select|Wrap|Line Numbers
  1. GroupCode;Text;PK
  2. GroupName;Text

tblEmployees
Expand|Select|Wrap|Line Numbers
  1. LastName;Text
  2. FirstName;Text
  3. EmplloyeeId;Text;PK
  4. GroupName;Text;FK

tblCompensation
Expand|Select|Wrap|Line Numbers
  1. id;Autonumber;PK
  2. GroupName;Text
  3. LastName;Text
  4. FirstName;Text
  5. EmployeeId;Text;FK
  6. CompensationType;Text
  7. Comp.Amount;Currency
  8. 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!
Mar 21 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
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.
Mar 22 '07 #4

NeoPa
Expert Mod 15k+
P: 31,347
Another point I noticed while working on the answer is that your [tblCompensation] table should be :
tblCompensation
Expand|Select|Wrap|Line Numbers
  1. ID;Autonumber;PK
  2. EmployeeId;Text;FK
  3. CompType;Text
  4. CompAmount;Currency
  5. 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.
Mar 22 '07 #5

Denburt
Expert 100+
P: 1,356
tblGroups
Expand|Select|Wrap|Line Numbers
  1. GroupID:Autonumber
  2. GroupCode;Text;PK
  3. GroupName;Text 
  4.  

tblEmployees
Expand|Select|Wrap|Line Numbers
  1. EmployeeID;Autonumber
  2. GroupID;Number (using lookup tie this into groups)
  3. LastName;Text
  4. FirstName;Text
  5. EmplloyeeId;Text;PK
  6.  


tblCompensation
Expand|Select|Wrap|Line Numbers
  1. Compid;Autonumber
  2. EmployeeId;Number (Tied to Employees)
  3. CompensationType;Text
  4. CompAmount;Currency
  5. CompensationDate;Date/Time 
  6.  
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
Mar 22 '07 #6

NeoPa
Expert Mod 15k+
P: 31,347
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.
Mar 22 '07 #7

P: 12
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
Mar 22 '07 #8

NeoPa
Expert Mod 15k+
P: 31,347
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.
Mar 22 '07 #9

Denburt
Expert 100+
P: 1,356
Glad you got it going and I understand your point about tracking the employees groups over the years, makes sense.

Nicely done Neo.
Mar 22 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.