access 2000 combo box filter problem 1

[allenEd]

Hi

I have 3 tables,

tblpersonel

IDPersonel autonumner
FirstName text
Surname text
IDDepartment number ---(many)--------------
|
tblDepartment |
IDDepartment autonumber-(one)--------------
Department text

tblProject
IDProject autonumber
projectname text
Department lookup from tbldepartment
IDPersonel lookup from tblPersonel

Ok, now I have a from called frmProject, consisting of fields of the tblProject, with 2 combo boxs
how can an entry from the Department combobox act as a criteria for the IDPersonel combobox?

e.g say there is a sales department and an IT department. How do you get the IDPersonel combo box to just show sales personel, if the department combobox has sales selected.

at the moment the rowsource for the IDpersonel is

SELECT [tblPersonel].[IDPersonel], [tblPersonel].[SurnName], [tblPersonel].[FirstName] FROM tblPersonel;


thanks for any help.

 

[PHV]

In the AfterUpdate event procedure of the dept combo:
Me![personel combo].RowSource="SELECT IDPersonel,SurName,FirstName FROM tblPersonel WHERE IDDepartment=" & Me![dept combo]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[allenEd]

Thanks PV,

I entered

Me![IDPersonel].RowSource = "SELECT IDPersonel,Surname,FirstName From tblPersonel WHERE IDDepartment=" & Me![Department]

Into the expression builder, dosn't seem to like the me! part. (I get an error)

I see the idea....



I will keep trying!

thanks
Allen

 

[allenEd]

Thanks PV, it worked on the got focus of the IDpersonel field.

 

[allenEd]

Hi PV,

The trouble now is that when I go back to the previous record in frmProject. The personel field is blank, because the secect row says

SELECT IDPersonel,Surname,FirstName From tblPersonel WHERE IDDepartment=1

Any ideas please..

thanks
Allen

 

[PHV]

Set Me![IDPersonel].RowSource in the Current event procedure too.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[allenEd]

Hi PV,

many thanks for that, that works ok, .....until I set the default view to forms to "continuous form", any ideas,
thank you, again.

my code is now:...

Private Sub Department_AfterUpdate()
IDPersonel.SetFocus

Me![IDPersonel].RowSource = "SELECT IDPersonel,Surname,FirstName From tblPersonel WHERE IDDepartment=" & Me![Department]


End Sub

Private Sub Form_Current()

Me![IDPersonel].RowSource = "SELECT IDPersonel,Surname,FirstName From tblPersonel WHERE IDDepartment=" & Me![Department]
Me.Refresh
End Sub