Combo box not showing results when it has control source

[Paranap]

Hi

I have a form for adding a new member of staff. I have the usual stuff on there but then when it comes to the team I have a combo box with the team location, A and G, and then the next one should show all the teams from that location. This works fine using a query but then as soon as the combo box is linked to a control source it doesn't come up with any results.
Any thoughts??




Neil

[June7]

The teams combobox has a dependent query as RowSource? Show teams combobox properties.

RowSource
ControlSource
BoundColumn
ColumnCount
ColumnWidths

[Paranap]

Row Source which is made in the query builder is

SELECT tblTeam.TeamID, tblTeam.Location, tblTeam.Team FROM tblTeam INNER JOIN (tblOperative INNER JOIN tblOperativeTeam ON tblOperative.Payroll = tblOperativeTeam.Payroll) ON tblTeam.TeamID = tblOperativeTeam.TeamID WHERE (((tblTeam.Location)=[forms]![frmAddOperative]![cbTeamLocation])) ORDER BY tblTeam.TeamID;

Control Source is TeamID
Bound Column is 1
Column Count is 3
Column Widths are 2.5,2.5,2.5

[June7]

Why is tblOperative included in the RowSource? None of its fields are used for display or filter. The INNER JOIN could be limiting the teams in the list. However, the value in the bound field should still show. What is the form's RecordSource? Does it have multiple tables? Is there more than one TeamID field? Is combobox bound to correct field?

The WHERE criteria can probably be simplified: WHERE tblTeam.Location=[cbTeamLocation]


If you want to provide db for analysis, follow instructions at bottom of my post.

[Paranap]

I think the problem I'm having is where I'm trying to add to multiple tables I'm getting bogged down with which table is linked where and how to actually add it.



This shows the relationships between the tables. So you have an operative table with basic details, a operative team table which shows which team or teams they have been in (this is why I put them as a seperate table as they might move teams and want to keep a log of it) and then a teams table with the different teams available. So my form is currently linked to the operative table which is fine then the problem comes when I get the team bit. Should I use a subform to have that bit?? Would that solve my problem?

[June7]

Normally a form can do data entry/edit for only one table.

Options for form arrangement are:

1. single form bound to tblOperativeTeam with comboboxes to select team and operative.

2. main form bound to tblOperative with a subform bound to tblOperativeTeam and combobox to select team

3. main form bound to tblTeam with a subform bound to tblOperativeTeam and combobox to select operative

[Paranap]

Thank you very much. Went down the line of using a subform. With the info on looking at the format of the combo boxes I muddled through and with trial and erro I've learnt stuff and got it working!!

Thank you