Combo problem

[Markvand]

hi,

I am working on Project Database,

The thing I want to achieve is to have a combo with a employees name for expample and be able to pick one and automaticaly store it in table, clear the combo and do the same with another employee.

I'm getting something close with this code:

Private Sub Employee_Click()

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

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

rs.AddNew
rs![EmployeeDep] = Me.[Employee]
rs.Update
rs.Close

End Sub

I've got a strange feeling that I should use some sort of counter here, but no idea how.

The purpose behind this is to store few names under one project ID. Maybe there is another way to do this.

Any help would be great.

Cheers

 

[Mile-O]

tblProjects
ProjectID
ProjectName
other details...

tblEmployees
EmployeeID
Forename
Surname
other details ....

tblProjectsToEmployees
ProjectID
EmployeeID

With that structure, you can just use a form/subform combination rather than a combobox and VBA.

 

[Markvand]

That's clear, but is it possible with combo and VBA, it's just that I always like to chalange with something and I reckon this looks better with combo. I might be subjective.

 

[KenHigg]

Didn't we just do this?

ken

 

[Mile-O]

it's just that I always like to chalange with something and I reckon this looks better with combo. I might be subjective.

Why challenge yourself with an inefficient method when Microsoft Access can click it all together for you without any interference?

I enjoy challenges but I enjoy them where they're warranted.

 

[Rich]

Didn't we just do this?

ken

I'll bet there are only about eight employees

 

[Markvand]

Fair enough, thnx anyway,
just for curiousity is there a way? or you just don't know

Rich how did u guess?

Regards

 

[KenHigg]

Markvand, There is a way, it just seems irrational.

1. Do your tables like you were going to do the subform method.
2. On your original main form put the combo box.
3. Set combo box row source to the employee tables 'EmployeeName" field and have it sorted ascending.
4. On the after update event on the combo box, do code that appends the current project id and employeeName in the combox to the projectEmployee table.

Of course if you tried to append a duplicate, you would have to have the pk in the projectEmployee table set to the projectID and employeeName as a composite pk.

There are some more work arounds, but they are clunky, like this one...

ken

 

[Markvand]

Thanks Ken

Preciate all your help,

Regards

Mark

 

[Markvand]

Can I also ask you guys to help me with this problem?

back to a subject,

I have found a thread that suits my situation almost 100%, http://www.access-programmers.co.uk/forums/showthread.php?t=19085&highlight=search+based+combo+box, my idea was to have the results back in the combo,

I reckon I should have something like Me.myCombo.Requery in Ater_Update event in combo, but that's it.

Please throw me some bone here

Thnx in advance

Mark