Need to add a row in a SubForm

[RichMax]

I am using the Access 2003 forms creation wizard to create a Data Entry (DE) form which has 2 subforms that show related data in a grid manner. In both the main DE form and the grid subforms, I have several elements that are combo boxes.

The composite form displays existing data correctly and I can update existing data that does not have a combo box control. For data that has a combo box control, if I pull down the list, I see the appropriate choices but I cannot select a new one from the list.

Also, I cannot seem to add subform rows to an existing form row.

I have done no custom coding for this application and would rather stay away from that. Having said that, I realize that, in some instances, I may have no other option.

Thanks for any help you good folks can give.

Richard

 

[Remou]

It appears that your subforms are not updateable. There may be several reasons for this, however a common reason is that the query that the form is based on is not updateable. You can check this by opening the query and seeing if you can add or edit a record.

 

[PHV]

Have a look here;

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

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

 

[RichMax]

I removed the subforms from my form so that only the master table and 1 joined table were defined. The query is as follows:
SELECT Reports.[Report Code], Reports.[Report Name], Reports.Description, Reports.SAE, [Source Group].[Source Group], Reports.[Used by Max], Reports.[Used by Max - More Research], Reports.[Used by MSBR / EAS], Reports.[Used by MSBR / EAS - More Research], Reports.[Retired / Expected to Retire], Reports.[Retired / Expected - More Research], Reports.[Not Used], Reports.[Not Used - More Research], Reports.New, Reports.[Validated by], Reports.[Validation Date], Reports.Transform, Reports.Notes
FROM [Source Group] INNER JOIN Reports ON [Source Group].[Source Group] = Reports.[Source Group]
ORDER BY Reports.[Report Code];

I could not enter a new value into the 'Reports Code' field although I could, as before, update other fields including the joined field. As before, I could not add a new record.

One thing worth noting is that, when I display the query in design view, the title in the header is suffixed with 'Select Query'. Don't know whether that is significant.

I did read through msdn_harness.asp but as far as I could see, I was obeying all of the rules.

Puzzled...

 

[Remou]

Can you add a record to the query? Is Report Code an autonumber field? Have you checked the form and subform properties to see if AllowAdditions is set to Yes?

A select query is suitable for a form.

 

[RichMax]

Thanks for your help so far Remou.

I cannot add a record to the either the form or the subform query.

The subform query is as follows:

SELECT [Report Recipients].[Report Code], [Communication Medium].[Communication Medium], [Report Recipients].Encrypted, [Report Recipients].Recipient, [Communication Medium].[Comm Vehicle Code], Institution.Name, [Group Branch].Name, Division.Name, Positions.[Position Code], Positions.Description, Job.[Job Code], Job.Description, Job.Frequency, Institution.[Juridiction Code]
FROM ((Institution INNER JOIN ([Group Branch] INNER JOIN (Division INNER JOIN Positions ON Division.[Division Code] = Positions.[Division Code]) ON [Group Branch].[Group ID] = Division.[Group ID]) ON Institution.[Institution Code] = [Group Branch].[Institution Code]) INNER JOIN Recipients ON Positions.[Position Code] = Recipients.[Position Code]) INNER JOIN (Job INNER JOIN ([Communication Medium] INNER JOIN [Report Recipients] ON [Communication Medium].[Communication Medium] = [Report Recipients].[Communication Medium]) ON Job.[Job Code] = [Report Recipients].[Job Code]) ON Recipients.Recipient = [Report Recipients].Recipient
ORDER BY [Report Recipients].Recipient;

I think what I need to do is to start off with a skinned down subform query that works, then gradually add the other joins until is stops working.

I'll let you know how far I get...

Thanks again,
Richard

 

[Remou]

Unique indexes are very important.

 

[RichMax]

Here's a little more info on my query where I could not add a new row.

I removed all JOINs from the query and then I could add rows to it. The query is as shown below:

SELECT Reports.[Report Code], Reports.[Report Name], Reports.Description, Reports.SAE, Reports.[Used by Max], Reports.[Used by Max - More Research], Reports.[Used by MSBR / EAS], Reports.[Used by MSBR / EAS - More Research], Reports.[Retired / Expected to Retire], Reports.[Retired / Expected - More Research], Reports.[Not Used], Reports.[Not Used - More Research], Reports.New, Reports.[Validated by], Reports.[Validation Date], Reports.Transform, Reports.Notes
FROM Reports
ORDER BY Reports.[Report Code];

When I included a join to my [Source Group] table in the query, I could not enter values in any field (including the primary key field) except for the join field! As expected I could add a value to that field that was present in the Source Group table primary key into this new record. The joined table was indexed with a unique key. The expanded query looked like the following:

SELECT Reports.[Report Code], Reports.[Report Name], Reports.Description, Reports.SAE, [Source Group].[Source Group], Reports.[Used by Max], Reports.[Used by Max - More Research], Reports.[Used by MSBR / EAS], Reports.[Used by MSBR / EAS - More Research], Reports.[Retired / Expected to Retire], Reports.[Retired / Expected - More Research], Reports.[Not Used], Reports.[Not Used - More Research], Reports.New, Reports.[Validated by], Reports.[Validation Date], Reports.Transform, Reports.Notes
FROM [Source Group] INNER JOIN Reports ON [Source Group].[Source Group] = Reports.[Source Group]
ORDER BY Reports.[Report Code];

I shall try to simplify this example more tomorrow to better illustrate the problem.

Thanks for your help so far...

Richard

 

[Remou]

Why are you joining source group? This query is being used in a form, so a combo to diplay descriptive data is probably more suitable.

 

[RichMax]

I am joining Source Group because the query is used to add entries to Reports and one of the components is Source Group which needs to be either Null or a valid entry in the Source Group table.

Richard

 

[Remou]

I think you will find that a 'Limit To List' combo box is most suitable for your purposes, rather than a joined table. Don't forget that you can also set this relationship in the relationship window.

 

[RichMax]

Thanks Remou, I'll give it a try.

 

[RichMax]

I have mostly been sucessful in setting up my forms to add new and update existing rows.

I took your advice Remou, and defined the table name in the RowSource property of the Combo Box.

I still cannot add a row with an empty field where a foreign key should be (I am using a view comprising several joined tables) but I have solved that problem in a different way.

Thanks for your help...

Richard