473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

I can't add new record on a form

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
Aug 8 '13 #1
11 31968
Seth Schrock
2,965 Expert 2GB
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.
Aug 8 '13 #2
jimatqsi
1,271 Expert 1GB
If the record source is a table it's likely there is no key defined.
Aug 8 '13 #3
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
Attached Files
File Type: zip Hombre.zip (1.88 MB, 633 views)
Aug 15 '13 #4
jimatqsi
1,271 Expert 1GB
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
Aug 15 '13 #5
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,Designation and Structure table.

Firstly Post_id,Post_idLevel,postgrade devired from Establishment, designation to Designation table and Cluster_Name,Unit_Name, Department_Name, Branch_Name, Division_Name,Section_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.
Aug 15 '13 #6
zmbd
5,501 Expert Mod 4TB
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.
Aug 15 '13 #7
jimatqsi
1,271 Expert 1GB
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
Aug 15 '13 #8
jimatqsi
1,271 Expert 1GB
Here's the query for the form in question:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW ESTABLISHMENT.Post_id
  2.    , ESTABLISHMENT.Post_idLevel
  3.    , Designation.Designation
  4.    , ESTABLISHMENT.PostGrade
  5.    , STRUCTURE.Cluster_Name
  6.    , STRUCTURE.ServiceUnit_Name
  7.    , STRUCTURE.Department_Name
  8.    , STRUCTURE.Branch_Name
  9.    , STRUCTURE.Division_Name
  10.    , STRUCTURE.Section_Name
  11.    , STRUCTURE.SubSection_Name
  12.    , STRUCTURE.Cluster_ID
  13.    , STRUCTURE.ServiceUnit_ID
  14.    , STRUCTURE.Department_ID
  15.    , STRUCTURE.Branch_ID
  16.    , STRUCTURE.Division_ID
  17.    , STRUCTURE.Section_ID
  18.    , STRUCTURE.SubSection_ID
  19. FROM Designation 
  20.    INNER JOIN (ESTABLISHMENT 
  21.       INNER JOIN STRUCTURE 
  22.          ON (ESTABLISHMENT.Cluster_Code 
  23.             = STRUCTURE.Cluster_ID)
  24.           AND (ESTABLISHMENT.Branch_Code 
  25.             = STRUCTURE.Branch_ID) 
  26.           AND (ESTABLISHMENT.Section_Code 
  27.             = STRUCTURE.Section_ID) 
  28.           AND (ESTABLISHMENT.SubSection_Code 
  29.             = STRUCTURE.SubSection_ID)
  30.           AND (ESTABLISHMENT.Division_Code 
  31.             = STRUCTURE.Division_ID)
  32.           AND (ESTABLISHMENT.Dept_code 
  33.             = STRUCTURE.Department_ID)
  34.           AND (ESTABLISHMENT.Unit_Code
  35.             = STRUCTURE.ServiceUnit_ID))
  36.     ON Designation.DesignationID 
  37.       = ESTABLISHMENT.DesignationID;
Aug 15 '13 #9
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.
Aug 16 '13 #10
jimatqsi
1,271 Expert 1GB
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
Aug 16 '13 #11
jimatqsi
1,271 Expert 1GB
medube1,
I'd like to point out a few critically important flaws with just one of your routines. Look at
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo32_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[Section_ID] = " & Str(Nz(Me![Combo32], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
  9.  
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
Expand|Select|Wrap|Line Numbers
  1. rs.close
  2. set rs = nothing
  3.  
after using a recordset.

And finally, you don't have any error trapping. You need to add that to most routines. Something like
Expand|Select|Wrap|Line Numbers
  1.     On Error GoTo Err_msg
  2.     .... your routine's other code here ....
  3.     exit sub (or function)
  4. Err_msg: msgbox "Error " & err.number & " " err.description
  5.          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
Aug 16 '13 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: EricRobineau | last post by:
hello I have a DB with many inter-related tables (MySQL) My main table called "content" has almost only foreign keys (integers) some have a 3 level relation (ex: content->city->region->country) ...
5
by: Rose | last post by:
I want to use the javascript dom to reference the field of a record in a multi-record html form when that field has been changed. How do I reference the field? I have a JavaScript function coded...
1
by: noname | last post by:
I have no clue where to start. Do I have another table of parts?
3
by: Dan Williams | last post by:
Anyone know if i can take advantage of the datagrid features and use it in a vertical format to view and edit one record from a database? For example, Name: Address: Town: County:
6
by: San | last post by:
Hey, I need to create a form with several text boxes in which users type in key words, press a command button on the form and it opens a matching record. Thanking you in advance.
2
by: reidarT | last post by:
I have 3 fields in an aspx page. The 3. field should be the sum of field A and field B I use OnTextChanged to calculate the sum in field3. At the same time I want to insert the content of theese 3...
1
by: alex.gruenther | last post by:
I am new to Access and need help creating an edit record form. I've got a table called Player with attributes like name, address, phone, etc. I've created an edit player form with a combo box...
2
by: guessvic | last post by:
Hello everyone, Does anyone know why after using the insert record function that Dreamweaver provides for ASP JavaScript page, then you CANNOT pass the value in the text field inside the form to...
4
by: Susan Bricker | last post by:
I have a form that displays record information in Continuous Record display (scrollable list). One of the fields in the record is an Integer value called "rcode" (reason code). But, I don't want...
1
by: Robert Ingles | last post by:
I have a situation where my users need to enter details about new laptops into a table via FRMCreateNewLaptopID - the FRMCreateNewLaptopID includes a UserID field to identify the primary user. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.