Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems with forms and relationships 1

Status
Not open for further replies.

gianina

Technical User
Jul 4, 2006
56
CA
Hi All,

I created an NCR database (NCR = non-conformance report) in which I want to be able to create new NCR's and also to update existing NCR's.
I created 2 buttons for each function. In each case a new form will pup-up until all necessary data is introduced (data introduced to the table through the form).
Now, in the case of "updating existing NCR's" I created a combo box which shows all existing NCR numbers in the table. At a push of another button, let's called it "OPEN NCR FORM" I want to automaticcaly open the NCR form with all the data from that particular NCR number. I am attaching the database for a better understanding of the situation (First open form called "NEW or OLD").

Another issue that I have is that I am not able to record data in the same line.
Every new NCR begins with a NEW NCR number. To this NCR number will ALL other data be connected, therefore I need them on the same line as the NCR number (same record).

I would appreciate if somebody could help me figure out where's the problem as I am a novice with Access database creation.

Million thanks...
 
I don't exactly understand your explanation - "In each case a new form will pup-up until all necessary data is introduced" - whatever that means.
Create a query to use as the record source for your second form. On the criteria line of the NCRID field place:
[Forms]![YourComboBoxFormName]![ComboboxName]
Now when you click the button to open the second form, it'll run the query which will filter the data for that NCR and show the form.
Your second question, I don't understand.

You say you're a novice. Some questions for you to think about:
Did you normalize your tables?
Did you set your relationships and joins?
Have you programed VBA before?
 
Thanks fneily for your reply.

Let me try to explain...
I have a table with "n" fields.
Each field will constitute a form.
Each form will have a button which will open the next form.
Basically I want to record new data into the table (database) through FORMS.
What I am unable to do is to record data on the same line in the table !!!
What I have so far is that I am able to move to the next record when creating a new NCR but the rest of the data (fields) are going to overwright the first record in the table (first line).
I will attach my database for your review...it will be much clearer.

My second issue is that after recording data into the table I want to be able to modify that data through a form. When a certain NCR number is choosed this will pop-up the NCR form with all fields from the table.

When opening the database I am attaching push button "NEW NON-CONFORMANCE" for creating a new non-conformance and push button "UPDATE EXISTING NON-CONFORMANCE" for updating an exisinting non-conformance.

I hope this helps.

Thanks again for your help...

G.
 
Fneily,

Apparently there's no option of attaching my database here.
Any other ways to show you the database ?

Thanks.
 
I have a table with "n" fields.
Each field will constitute a form.
Each form will have a button which will open the next form."

This is new, interesting, strange to me. Usually, 99.999% of the time, a record's fields are showen on ONE form. The form may be created using the Form Wizard. When the user reaches the end of entering data, then tabs one more time, the record is saved and the textboxes are cleared and ready for the next record.

Unfortunately, you can not attach files to Tek-tip posts and you cannot post your email address.

Maybe someone else understands what you're doing.
 
Yes, ussualy all fields are on ONE form.
What I am trying to do is to have 25 forms (because I have 25 fields in my table)--each form will have 1 field -- and the user can only go to the next form if he or she provided data in the current form (moving to the next form is conditioned by the existance of data in the current form).
 
Fneily,

Regarding this: "[Forms]![YourComboBoxFormName]![ComboboxName]"

I tried this and when I press the button to open the NCR form an error pos-up:
"The changes you requested to the table were not succesful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again"

I didn't defined any primary keys. I don't know were's the problem.
I don't know what to do !!!

Please help.

Thanks.
 
Any chance you could post your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV,

I'm not sure I understand your question !
Please elaborate.

Thanks.
 
Which code raises your error ?
Please, read carefully the second FAQ in my sig.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV,

I'll try to be more specific...

The user uses a form to enter data into the table.
I want to be able to modify the existing data in the table. For that I created a form which contains a combo bx and a button. The record source of the combo box is a querry which basically just shows all all the existing NCR numbers.
The button opens the NCR form of which record source is another querry with all the fields from the table. At the criteria line of the NCRID field I placed:
[Forms]![MyComboBoxFormName]![ComboboxName]

But, when I press the button the form comes up with all the information for the selected NCRID and this error:

"The changes you requested to the table were not succesful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again"

So, here's where I don't know where's the problem.

I'm sorry but I'm not that familiar with codes...maybe if you'll explain where is this code I can show it.

Thanks again.

 
What is the ControlSource property of the combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
have you checked the table properties to make sure that whatever field you are trying to change from the form doesn't have a 'Index - No Duplicates' set for that field?

Is there any particular reason you didn't define any primary keys?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi
 
PHV,

The Control Source of the Combo box is "NCR#" -- is the first field in the table.
The Row Source of the Combo box is "NCR numbers - 05/07/06" -- is a querry which shows all NCR numbers from the table.

Lespaul,

The only field from the table which had the property " Index - No Duplicates " was the "NCR #" field.
When I changed the property to "Index - No" and opened the form to make changes to the particular NCR # this overwrote the next NCR # by the same number. I know this sounds confusing but let me explain:

This is my table:

NCR # Origin Location
01 Internal plant 1
02 Internal plant 2
03 Internal plant 1
04 Internal plant 3
05 Internal plant 2

Now, I select NCR # 03 for example and I press on the button which opens the form and change any field -- go back to the table to see what happened I noticed that NCR # 04 became 03 and NCR # 05 became 04.

Here's where I'm lost...

Regarding the question about the primary key...I didn't defined one because I have only one table. Do I need one in this case ?

Thanks again for your help.
 
For me, a ComboBox used as a navigation tool should be unbound (ie no ControlSource).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV,

In my case that ComboBox show all existing NCR numbers from my table and after selecting one and pressing a button will open the NCR form with all fields related to that NCR number.

If the ComboBox is unbound how will it show all the NCR numbers ?

Thanks.
 
PHV,

Your right, a ComboBox used as a navigation tool should be unbound.

This fixed my problem.

Thank you all again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top