Simple Question: Why Can't I add a Record?

[Bill Bisco]

Alright, I have a form with 2 subforms. I'd like to be able to add records in these subforms. The form and subforms look like this:

I want to add a record on the left subform. However, Access will not let me.

I have said yes to

1. Allow Edits
2. Allow Deletions
3. Allow Additions
4. Data Entry

However, I am unable to make a new record even though the new record button is available to be pushed, and blank text boxes appear.

I am able to edit entries that already exist, but I would really like my users to be able to create an entry here.

Any help is appreciated.

Sincerely,
Bill

 

[boblarson]

Sounds like you might have something linked wrong between the forms (in the master/child links). Or it could be something else but, for me, it would be easier to tell by seeing the actual database.

 

[Will_B]

If the row source for the left subform is a table, do you have the access privileges to add new records? If not, see the db administrator. If you do have the necessary privileges, determine if any of the fields are required (see field properties) and if they are being filled on your subform.

If the row source is a query or SQL string (rather than a table), try opening the query/SQL string in Query Designer and see if it is updatable. If it is not updatable, then this Microsoft Knowledgebase article might help you troubleshoot the cause and find a solution:

http://support.microsoft.com/kb/328828

Also do the table checks I described earlier, doing them on the tables in your query/SQL string.

If the query/SQL string is, in fact, updatable, look at the master/child links are between parent form and subform. Are the data types of each matched field the same (that is, a Text field is linked to a Text field, Long integer to Long integer)? To see this, check the tables where the fields reside.

If none of this explains/fixes the problem, post what error message or status bar message appears when you try to create a new record.

 

[Bill Bisco]

Bob and Will,

Thank you 2 so much for posting. I really appreciate it. I have found out the problem by someone kind on another newsgroup
http://www.microsoft.com/communitie...28d-b4dc-12661269be7e&lang=en&cr=us&sloc=&p=1

My problem was that I used the Totals function to get a sum, and Queries that have Totals are read only. And since the query is read-only; my users cannot edit the form....yet

So now, I need to think of a way to trick Access to let me add a record even though it really doesn't want me to. If you have any ideas, please let me know.

Thanks again you 2,
Bill

If you have ideas, please let me know.

Sincerely,
Bill

 

[Will_B]

In your query, replace your SUM column with a new column that consists solely of this (which you place where the field name would otherwise go):

MySum:  DSum("[MyFieldToSum]","[MyTable]")

... and change the "Total" entry (where you'd otherwise select "SUM", "GROUP BY", etc.) so it is "Expression". Keep the quotation marks and brackets, but change the field and table names.

You'll notice that you query is now updatable, yet displays the sum you want.

If you need to add criteria to this DSUM function, use this expression:

MySum:  DSum("[MyFieldToSum]","[MyTable]","[MyFieldForCriteria] = '" MyCriteria & "'")

Use ' around MyCriteria if a string, but take out ' if a number.

 

[Bill Bisco]

Will B, thanks for the response. I actually got confused for a moment. Your solution may solve my problem for another form. But for this form, there is not a totals query being used

The subform is inside an unbound form. So it should not be a problem of the child and parent links being improper because there are no links.

The Query must obviously be non-updatable because if it was I could add a record. Is that tautology correct?

I am the admin., so adding records should not be a problem. As far as required fields go, I have enforced data integrity for this database tblProcesses is connected to tblElements (Each Process has many Elements), and adding a new Element requires the the correct Process ID field to be added.

That might be part of the problem.

@boblarson If you want to see the database, I have uploaded it at: http://cid-a9563c3784075b73.skydrive.live.com/self.aspx/.Public/Copy%20of%20Experiment37%203-26-09.mdb

On the Main form, below the Button which says "View LS and RS Stations Together" click the combo box and choose Station 1

Then click a magnifying glass, and you'll be at the form I'm having trouble adding records to in question

Sincerely,
Bill

 

[Bill Bisco]

I've found a way to add a Record. I've done this Via an Add Record Button.

Private Sub cmdAddRecord_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strTable As String
    Dim strFieldName As String
       
    strTable = "tblElements"
    strFieldName = Me![Process ID]
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    With rst
        .AddNew
        .Fields("Process ID") = strFieldName
        .Fields("Element Name") = "New Element"
        .Update
    End With

End Sub

Tres Magnifique!

If you think there's a superior way, let me know.

Sincerely,
Bill