ACCESS: Form, 2 tables with constraint, fail to create new record

[Ruchval]

Hello, I have some doubts creating a new Access, Any help?
:) Thanks in advance.

I have 4 tables (* indicates pk)

10Company
  CompID *
  CompName
  CompDesc
 

20Section
  SectCompID *
  SectID *
  SectName
  SectDesc
 

30Jobs
  JobCompID *
  JobSectID *
  JobID *
  JobName
  JobDesc
 

40Operations
  OpeCompID *
  OpeSectID * 
  OpeJobID *
  OpeOperID *
  OpeName 
  OpeDesc 
 

All tables are 1-N dependant via the ID Field, this means I can not create a Section without a provided Company, I can not create a Job without a created Section and I can not Create an operation without a Job.

I know this is not nice E-R model, but Im given ER and requested to create the maintenance Form for the Operations.

Then I have created a form:

OperationsForm
  ControlSource = 40Operations
 

Then In the Form I have added the following fields:

OpeOperationTxt
  ControlSource = OpeOperID
 
JobsComboBox 
  ControlSource = OpeJobID
  Row Source = Select JobID, JobSectID, JobCompID from 30Jobs
  Bound Column =1
  ColumnCount = 3
  BeforeUpdate = CodeAfterColumns
 
OpeJobTxt 
  ControlSource =  OpeJobID
  enabled, locked = No
 
OpeSectionTxt 
  ControlSource =  OpeSectID
  enabled, locked = No
 
OpeCompanyTxt 
  ControlSource =  OpeCompID
  enabled, locked = No
 

JobsComboBox.BeforeUpdate code

    Me.OpeJobTxt = Me.JobsComboBox.Column(0)
    Me.OpeSectionTxt = Me.JobsComboBox.Column(1)
    Me.OpeCompanyTxt = Me.JobsComboBox.Column(2)
 

Then.... I have a few questions ...

1.- I type a value in the OpeOperationTxt, then I select a value from JobsComboBox combo, it updates the OpeJobTxt, OpeSectionTxt, OpeCompanyTxt correctly, then if I press tab, it should create a new record in the 40Operations table, but instead, I have an error:
"You cannot add or change a record because a related record is required in 30Jobs"

I dont understand why it asks this, as the record in 30Jobs table, exists (i selected it from the combobox).

2.- Second question is, how can i stop the form from creating new records, and to do it via clicking a button


Thanks a lot.

[UPDATE]: If i delete the 1-to-N relationship, it creates the row(record) in the 40Operations table

[Seth Schrock]

Thank-you for providing such detail. It isn't often that a new user does that.

I believe that your problem is that you are using composite keys in non join tables. If you don't have natural single field primary keys, then just add an auto-number field. If you do this, then you can remove the OpeCompID, OpeSectID, and OpeOperID fields from the 40Operations table as this information is all related through your OpeJobID field. Otherwise, you are duplicating data which breaks normalization rules. See Database Normalization and Table Structures. If you need to see the information on the form about the CompID, SectID, and OperID, then build a query that has all the tables in it and then base your form on that. As soon as you select the JobID, the other fields will populate automatically without any code.

[Ruchval]

Hello,
Thanks for answering :)

I totally agree with you, about the model, but the database is not "mine" and i need to use it like it is designed :-(

Yes I have composite relation, and I have seen that after deleting it, the form is working.

So... the problem are the Relationships. But I can not delete them.

I don't understand why the validation is not working as the registry in 30Jobs exists.

Thanks

[Seth Schrock]

I'm not sure how to get around this without changing the tables. Sorry I can't help you more.

[zmbd]

+ Composit keys can be difficult to work with as everything has to be "just so;" however, I have played with them a few times.

+ Check your table indexes Access by default will insert indexes in to the table for any field ending (or often just containing) "ID", "key", "code", or "num" , this is set in the Ribbon/options/object designers/table design view/AutoIndex on Import/Create. Personally, I delete ALL of these
- Open the table in design view
- Ribbon>Design>Show/Hide>Indexes

Check for your primary key and delete any duplicated field indexes.... these duplicated indexes will cause no end of issues!

+ One of the other most common issues, is that what one thinks should be returned from a control isn't what is needed for the underlying table-record; thus, in your update code let's make sure what you think you are returning from the controls is what you should be returning:
Insert just before your Me.* = *:

With Me     
     Debug.Print "OpeJobTxt = " & .JobsComboBox.Column(0)
     Debug.Print "SectionTxt = " & .JobsComboBox.Column(1)
     Debug.Print "OpeCompanyTxt = " & .JobsComboBox.Column(2)
End With

Run your form, once done, <ctrl><G> should open the VBA editor and show the immediate window, therein should be our values.

How does the information output to the window match to the corresponding fields in the tables? Are numeric values being returned or string values? Are the Table Fields numeric or text and do these match?

+ If all looks "as it should" then I need a bit more information about your tables.

+ For each of your [*ID] fields (i.e. CompID, SectCompID, SectID) what is the data type, I am guessing Numeric(Long); however, I would rather not guess.
-- it wouldn't hurt to have the data type for all fields, we can add this to your original post if you like :-)

+ A more explicit detail about how your inter-table relationships are setup is needed...
Once again here I'm guessing:
(I know, It's customary to give these as 1:M; however, I'm doing this as I look at your tables...)
20Section
a1) [20Section]![SectCompID] M:1 [10Company]:[CompID]

30Jobs
b1) [30Jobs]![JobCompID] M:1 [10Company]:[CompID]
b2) [30Jobs]![JobSectID] M:1 [20Section]![SectID]

etc... this is what I currently envision your relationships as... (currently I have the fields set to numeric(long) in the primary key)

[Ruchval]

Thanks a lot to you two.

Finally I got desperate, deleted all, cleaned the "Ribbon/options/object designers/table design view/AutoIndex on Import/Create." option, cleaned the indexes, and it worked fine.

Probably I had an error or an index giving me problems, so on recreating more carefully it worked.

thanks a lot again.
:)