I can't add new record on a form

[medube1]

I can't add new record on a form, the add new record in navigation toolbar is disable on the form, but on properties I enable (addition,edit and delete)but still it's not working.

Please assist

[Seth Schrock]

That means that the recordsource for the form can't be modified. From my experience (although limited) this normally happens when the recordsource is a query, but I believe it can also happen with a table. If the recordsource is a query, then read this link to see why it won't let you modify the data: When can i update data from a query If you need help knowing where your query fits in the list, just post the SQL code for the query (making sure to use code tags. Read Before posting VBA/SQL code) and we can help you know what the problem is.

[jimatqsi]

If the record source is a table it's likely there is no key defined.

[medube1]

I can't add new record on a form

Greetings,

I have added an attachment to my problem so it will be easy to pin point what i have missed, i hope it will help

[jimatqsi]

medube1,
First, in your relationships (not in this form or query, in pull down menu Tools/Relationships) you have two tables appearing with no relationships at all defined. That can't be helpful, but maybe it causes no harm.

Your recordset is update-able but I was unsuccessful adding a record in datasheet mode because I don't know the data well enough. I keep getting key conflicts, but the point is the recordset itself is okay.

I think what you want to do is to change this 1 form into a form-subform design. Make a form that edits structure records only, and make it a subform of a form that edits Establishment records only. Or, you could disable the navigation buttons and create your own "add" button to this form. Then you could manage exactly what is supposed to happen when you click "add."

Right now, if you add a record it's not really clear what you want to happen. Add a structure record only? Or an Establishment and a Structure record? Work that out with form/subforms or manual VBA in the code-behind.

Jim

[medube1]

I want user's to add information within the form not manually on each table in datasheet view, this form is based on query with that combine 3 tables Establishment,D esignation and Structure table.

Firstly Post_id,Post_id Level,postgrade devired from Establishment, designation to Designation table and Cluster_Name,Un it_Name, Department_Name , Branch_Name, Division_Name,S ection_Name and Subsection_Name into the Structure table. That's the only information that the user I want to add

I hope this will help.

[zmbd]

medube1:
Please provide the actual SQL that is being used for the form's record source.
As you have it listed (and in VERY difficult way) it appears to be something in the joins.
(no, I have not d/l your attachment. d/l are typically prohibited on my work pc via IT-Security)

To get at the SQL,
Open the underlying query in design mode.
If it doesn't open in SQL view, then in a blank area where the tables are shown, right click, in the pop-up menu, select SQL View.
You will now be shown a window where the SQL query text is shown.
Please cut and paste this to a post, highlight the text, and then click on the [CODE/] format button to place the required [CODE] [/CODE] tags around your SQL.

[jimatqsi]

medube1,
I was not suggesting your users should use datasheet mode. Far from it. I merely used datasheet mode to prove that you are using an update-able recordset.

I'm going to post your query's SQL code in the next post. Would you please tell us:
1: Is the relationship between Designation and Establishment 1 to 1 or 1 to many?
2: Is the relationship between Establishment and Structure 1 to or 1 to many?

Thanks,
Jim

[jimatqsi]

Here's the query for the form in question:

SELECT DISTINCTROW ESTABLISHMENT.Post_id
   , ESTABLISHMENT.Post_idLevel
   , Designation.Designation
   , ESTABLISHMENT.PostGrade
   , STRUCTURE.Cluster_Name
   , STRUCTURE.ServiceUnit_Name
   , STRUCTURE.Department_Name
   , STRUCTURE.Branch_Name
   , STRUCTURE.Division_Name
   , STRUCTURE.Section_Name
   , STRUCTURE.SubSection_Name
   , STRUCTURE.Cluster_ID
   , STRUCTURE.ServiceUnit_ID
   , STRUCTURE.Department_ID
   , STRUCTURE.Branch_ID
   , STRUCTURE.Division_ID
   , STRUCTURE.Section_ID
   , STRUCTURE.SubSection_ID
FROM Designation 
   INNER JOIN (ESTABLISHMENT 
      INNER JOIN STRUCTURE 
         ON (ESTABLISHMENT.Cluster_Code 
            = STRUCTURE.Cluster_ID)
          AND (ESTABLISHMENT.Branch_Code 
            = STRUCTURE.Branch_ID) 
          AND (ESTABLISHMENT.Section_Code 
            = STRUCTURE.Section_ID) 
          AND (ESTABLISHMENT.SubSection_Code 
            = STRUCTURE.SubSection_ID)
          AND (ESTABLISHMENT.Division_Code 
            = STRUCTURE.Division_ID)
          AND (ESTABLISHMENT.Dept_code 
            = STRUCTURE.Department_ID)
          AND (ESTABLISHMENT.Unit_Code
            = STRUCTURE.ServiceUnit_ID))
    ON Designation.DesignationID 
      = ESTABLISHMENT.DesignationID;

[medube1]

1: Is the relationship between Designation and Establishment 1 to 1 or 1 to many?
Ans:1 to many
2: Is the relationship between Establishment and Structure 1 to or 1 to many?
Ans1 to 1

I have been able to add new information but now I encounter a new error "index or primary key cannot contain a null value" while every field in a form have been entered.

[jimatqsi]

Tell us, what have you done to improve the situation? Others can learn from that.

Perhaps your problem comes from the fact that most of the text controls on your form are unbound; and some of the key fields from the structure table are not represented, not even with unbound fields. I don't see any code that moves values from these unbound controls to a recordset.

It would a very good idea for you to work through some examples given with Access; usually they're in an .mdb called Northwind, but I believe some versions have a different sample database.

I applaud your efforts, you're obviously expanding your programming boundaries, and you've come to the right place for help. But it would be a very idea to master some simpler forms and tables before tackling this one.

Jim

[jimatqsi]

medube1,
I'd like to point out a few critically important flaws with just one of your routines. Look at

Private Sub Combo32_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Section_ID] = " & Str(Nz(Me![Combo32], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

First off, it looks like you learned the lesson about bad names; there are no combo boxes in your form with this name. So maybe you renamed the combo box and did not realize that the code for that combo box did not get renamed.

You've opened a recordset rs but you never close it. That will bite you, I know from experience. Be sure to add

rs.close
set rs = nothing

after using a recordset.

And finally, you don't have any error trapping. You need to add that to most routines. Something like

    On Error GoTo Err_msg
    .... your routine's other code here ....
    exit sub (or function)
Err_msg: msgbox "Error " & err.number & " " err.description
         resume next

Search for vba error handling here in the forum or with Google and study that. There's a lot of good advice about that avaialable.

Jim