Access Subform Creation with relationships

[bokane]

Sorry I am new to this forum, but I am currently taking a course which incorporates access databases while trying to embed a subform into a main form I am getting the following error. "the link you selected cannot be used. This link was created based on relationships between source tables. To use this link you must add the missing fields to the form or reports record source". I'm not sure I at all understand what I am doing wrong here I have tried recreating both forms from scratch and re-embedding them but I get the same answer each time. Any help would be greatly appreciated.

[June7]

Provide db for analysis. Follow instructions at bottom of my post.

[bokane]

Ok so the forms that I am trying to combined are the game input detail form, the team / game detail form, and the referee / game form. I zipped them and compacted and repaired per instructions.

[June7]

Not seeing attempted main/subforms arrangement.

The common entity for those forms is game. So assuming the main form is bound to Game table, the subforms will need GameID field in their RecordSource. Then the main and sub forms can link on the GameID.

Those forms have query with INNER JOIN as RecordSource. Change to RIGHT or LEFT. Make sure fields are bound to the correct ID fields. Example for Referee/Game:

SELECT [Referee/Game].RefereeID, [Referee/Game].GameID, Referee.RefereeFirstName, Referee.RefereeLastName, [Referee/Game].HeadReferee FROM Referee RIGHT JOIN [Referee/Game] ON Referee.[RefereeID] = [Referee/Game].[RefereeID];

Set the referee info fields as Locked Yes and TabStop No to prevent edits. Bind the RefereeID combobox to the field from Referee/Game table, not Referee table.

[bokane]

Here is an image of what I am attempting to make this form look like

[bokane]

So what would the SQL code for the game form look like then? That worked amazingly well by the way.

[June7]

You mean the main form? The RecordSource could just be the Game table.

Game Input Form is the main form and the other two will be the subforms.

Team/Game Form needs similar changes

[bokane]

So this is the code I used for the team/game subform. Is this sufficient?

SELECT [Team/Game].[TeamID], [Team/Game].GameID, [Team].[TeamName],[Team/Game].[Points], [Team/Game].[Outcome] FROM Team RIGHT JOIN [Team/Game] ON[Team].[TeamID] =[Team/Game].[TeamID];

[June7]

Looks good to me.

[bokane]

Thanks so much. Last question. How do I get it to write to the individual tables... It does the first one fine but does not move on. For example I can get it to record the first instance of game/team combination and ref combinations, but after that I have to scroll through to get it to schedule the next set. Any ideas?

[June7]

What do you mean by 'next set' - another game?

Are the subforms set for Continuous or Datasheet view? Can arrange controls in Continuous view to look like Datasheet.

A form can be set to automatically cycle to next record from the last control (make sure TabOrder property of controls is proper sequence). This will work nice in the subforms. But will have to manually move to the new record row of the main form for a new game record.

Record navigation can be accomplished with the intrinsic access features of forms or can build custom buttons.

A form can be set to only allow new records. This is the DataEntry property. This can also be controlled programmatically so one form can serve for different modes (only edit existing records, only new records, no editing only view).

The more 'user friendly' the more code.

Get familiar with the properties of forms and controls by exploring the Property Sheet. Try different settings.