ComboBox RowSource Query

[20028431]

Hi Guys looking for some help please. Is a bit complex but will try to keep explination as succint as possible - here goes -

I have a Access 2007 database allocating consultants against a programme of work for a client.
I have created a form (frmEnterExpens es) to enter expenses against a programme for each consultant. The form has two combo boxes (cboProgramme and cboConsultant). I need to store the values from each box in the associated tables but have encountered a problem.
I am trying to filter the cboConsultant box so that only consultants allocated to the programme chosen in cboProgramme are shown.

My problem is that in order to show the programme, I have created a query for the row source of cboProgramme which shows the Client Name and the Programme start date.

The info feeding CboConsultants is stored in tblConsultant as ConsultantID, FistName, LastName (etc) but I want the name to be concatenated to appear in this box as FistName LastName which I have done via a query.

I cannot work out what needs to go into the rowsource for cboConsultants to allow filtering on the choice in cboProgramme and storage of ConultantPK in tblExpenses.

Sorry for the longwinded explanation but wanted to make it as clear as possible. I hope it makes sense and any help or advice you can offer is appreciated.

I have listed the tables below and the query which gathers the relevant info.

tblCollege – CollegeID (pk), CollegeName
tblProbramme – ProgrammeID (pk), StartDate, EndDate, Type, CollegeID (fk from tblCollege), PONumber, ProgStatusID (fk from tblCollegeProgr ammeStatus look up table).
tblProgrammeDet ail – ProgDetID (pk), ProgrammeID (fk from tblProgramme), ConsultantID (fk from tblConsultant), AreaID (fk from tblArea), NoDays
tblConsultant – CosnultantID (pk), FirstName, LastName
tblArea – AreaID (pk), area
tblSpecialism – AreaID , ConsultantID (join table for one to many relationship between consultants and areas)
tblCollegeProgr ammeStatus – ProgStatusID (pk), Status (lookup table)
tblExpenses – ExpenseID, ProgrammeID (fk to tblProgramme), ConsultantID (fk to tblConsultant), Miles, Airfare, TrainFare

qryAllProgramme s –

SELECT tblCollege.CollegeName,
       tblProgramme.ProgID,
       tblProgramme.StartDate,
       tblProgramme.EndDate,
       tblProgramme.PONumber,
       tblCollegeProgrammeStatus.Status,
       tblProgrammeDetail.NumberDays,
       tblArea.Area,
       tblConsultant.[FirstName]+' '+ tblConsultant.[LastName] AS FullName

FROM   tblArea INNER JOIN
       (tblConsultant INNER JOIN
       ((tblCollege INNER JOIN
       (tblCollegeProgrammeStatus INNER JOIN
       tblProgramme
  ON   tblCollegeProgrammeStatus.ProgStatusID=tblProgramme.StatusID)
  ON   tblCollege.CollegeID=tblProgramme.CollegeID) INNER JOIN
       tblProgrammeDetail
  ON   tblProgramme.ProgID=tblProgrammeDetail.ProgID)
  ON   tblConsultant.ConsultantID=tblProgrammeDetail.ConsultantID)
  ON   tblArea.AreaID=tblProgrammeDetail.AreaID;

Thank you for taking the time to read this

[NeoPa]

I cannot work out what needs to go into the rowsource for cboConsultants to allow filtering on the choice in cboProgramme and storage of ConultantPK in tblExpenses.

Let me first make a quick point about hard this is to read through while concentrating on trying to understand what is meant. Typos are fine when messaging your friends, but when asking technical questions they get in the way big time. Please read through your question more carefully before submitting in future, especially when it's as involved as this clearly is.

That out of the way, it seems you are trying to filter a ComboBox within your form, and wanting to store the returned value in the underlying record.

Filtering ComboBoxes is not something you can do by applying a filter string (as you can with forms and reports for instance). You will need to determine what the .RowSource needs to be to effect the desired results and update it commensurately.

For saving the result away in the underlying record you need to ensure that the ComboBox control is bound and that the value returned (reflected by .BoundColumn) is the one you require.

As an aside, did you know that + and & are not exactly equivalent when dealing with string concatenation (Using "&" and "+" in WHERE Clause)?

[Delerna]

You want to select a value in one combobox (cboProgram) and have the values in the second combobox (cboConsultants ) to change and display only the consultants that are allowed for the chosen program.

Sounds similar to this

http://bytes.com/topic/access/answers/872875-filter-drop-down-list-based-value-another-field-pls-help-syntax

there is a database attached to post number 7. Maybe that will help you?