Adding value to a table from combo box

[Markvand]

Hi there,

I need a help with this:

I want to add values to a table from a combo box.

I have a list of employees (combo) and I want to add them to specific project. There is no problem when there is only one employee per one project, the problem occurs when there is more then one person per one project.

Thanks in advance for any advice

 

[KenHigg]

Use a list box?

 

[Mile-O]

Use a form/subform combo.

 

[Markvand]

Thanks for that, but please can you give me some more details, I'm just begining a long journey with Access.

Regards

 

[Mile-O]

Post your table structure here.

If you're just starting out it'll pay to get it right.

 

[Markvand]

Here is a piece of database that I have problem with

 

[Markvand]

Use a form/subform combo.

Back to the subject, am I getting this correctly? Should I put a subform which will be linked with my querry?

 

[KenHigg]

Do you still need help with this?

 

[Markvand]

sure, any help would be great

 

[KenHigg]

Not being able to read German, I may have to yeild on this one, but I'll give it a shot...

I see what apears to be a Projects table. In fact, they both look like project tables to me. Seems you need an employee table?

ken

 

[Markvand]

hi,

I do have an employee table, but I haven't post it.

I'll briefly describe the situation, the db that I am working on, suppose to be a project db, my idea was to have to table one with general info about the project containing: project id, description, company, who is in charge, start date, end date and remarks. Data for this table comes from one form, which would be used by person who is responsible for it. The second form is meant to be used by people from specific departments, thus some data will be copied to second table and person from the department will add the date through second form.

What's the problem now, in both tables there is a project id. There is no problem in first case, when I have one person (who creates project files) under one project id, the problem is in second case when there might be more than one person per project, it doesn't add few names under one project id.

Am I confusing or did you get something from it?

If you want I can send you the whole lot by email.

Regards

 

[KenHigg]

I think I understand.

Seems to me that you should go with one project table. If you need different people to enter different pcs of information, I would do a seperate form for each dept. Base all of them on the project table but just include fields for each depts particular data.

Would this work?

ken

 

[Markvand]

I'll give it a go, let you know what happened

Thnx

 

[Markvand]

The idea with one table is fine, however I still can't put more than one name under one projectID, I'm picking names from a combo. I reckon I should use some code to save value in a table and clean it afterwards to allow me to pick another name. No idea, how to do this, though.

 

[KenHigg]

This is where Mile suggested using a subform. But to start with you need a table that stores project employees. Then on your main project form do a subfrm based on that project employes table and link them on the project number(or id). The project employees table should, in addition to some other flds, have a project fld and employee fld.

???
ken

 

[Markvand]

I'll test it tomorrow, I'm off home,

Thnx for your help mate, I'll keep in touch tomorrow.

Regards

 

[Markvand]

Hi,

It works, but there is one more thing, when I have a sub form with employees working on specific project I have to use "continous form" in order to add more than one person per project.

Is there a way to have "single form" and a piece of code or some sort of "save" button to pick somebody from combo and automaticaly put him into table, pick another one and do the same?

I'm pretty sure this can be done, but how?

Mark

 

[Markvand]

Can I use something like that, or am I in completely wrong direction?

Private Sub Employee_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProjectEmployee")

With rs
For lngCounter = 1 To CLng(Me.Employee)
.AddNew
.Fields("EmployeeDep.") = Employee.Column(2)
.Update
Next lngCounter
.Close
End With

Set rs = Nothing
Set db = Nothing


DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True


End Sub

 

[KenHigg]

How much info about the employee do you need in the projects table. Will a name be enough?

 

[Markvand]

Hi Ken,

Yep, name will be fine