Problems with forms and relationships

[gianina]

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...

 

[jjb373]

Gianina,

If I am understanding your first question correctly, try this:
You have a combo box with a list of all the NCR numbers.

You will need this combo box(Ex: NCR Numbers) on the same form(Ex: StartForm) as your button "Open NCR Form". You will want to create a new query which has all of the fields you desire to update or view on the new form. In the query, for the "NCR Numbers" field, set the criteria = the combo box(NCR Numbers) from the form "StartForm". It should look something like this:
[Forms]![StartForm]![NCR Numbers]

Next, create a form(Ex: NCR Update Form)which has the query you created as the record source. (You can do this by creating a form, going into the properties for that form and adding the query to the Record Source under the Data tab.)

Now, on the form "StartForm" create a button (this would be your "Open NCR Form" button that opens the "NCR Update Form" you just created.

Once you select a number from the combo box on the "StartForm" and click the "Open NCR Form" button, the new form "NCR Update Form" will open with the information associated with the NCR number you chose in the combo box.


As for your second question, could you clear it up some. Maybe give me an example as best you can.

Let me know if this helps or have other questions.




Cheers,

JJB373

 

[gianina]

Hi JJB373 and thanks for your help.

Your advice helped but there is a problem.
When I push the button to open the "NCR Update Form" it does open the form with the information associated with the NCR number I chose in the combo box but when I modify some data on the form and save it -- and I go to the table to see if the change occurred I notice the following:

....I am not sure how to best describe the situation.
The data in the table are like this:

NCR Number Originated By Date
01 john 05/07/06
02 chris 05/07/06
03 max 05/07/06

When the form opens and I modify for example the "Originated By" field for NCR Number 03 from max to elvis -- the change does occurr but in the mean time NCR Number 01 (first record) also become 03...so I have two NCR Numbers called 03.

Do you know why is this happening ???


My second question earlier is regarding the creation of new record.

I will try to use the same example as above:

(Field 1) (Field 2) (Field 3)
NCR Number Originated By Date
01 john 05/07/06 (record 1)
02 chris 05/07/06 (record 2)
03 max 05/07/06 (record 3)

I ilustrated the fields and records (in my database there are more than 3 fields).

Each field will constitute a form.
Each of those forms will have a button which will open the next form.
Basically I want to record new data into the table (database) through those FORMS.
What I am unable to do is to record data on the same line in the table !!! In the example above: NCR Number 02 is originated by chris on 05/07/06 from department "x" and so on... all data associated to the particular NCR Number MUST stay on the same line as the NCR Number.
What I have so far is that I am able to move to the next record (next line)when creating a new NCR but the rest of the data (fields) are going to overwright the first record in the table (first line).

Thanks again for yout help.

G.

 

[jjb373]

Gianina,

I am not sure why you are getting the NCR numbers to change. If you are updating record 3 then only that record should reflect the changes. Maybe you have multiple tables and your relationships are not set correctly?? If you have only one table and each field is in that one table then everything should stay the same.

What is your primary key? Maybe you accidently changed the number in the dropdown box when you updated the name from max to elvis?

It is tough to pin point something when you can't see the database and what all is going on.


As for the second question.
What are your relationships if you have more than one table? I am lost here. If have data in each field for a particular record, whatever you type in those fields should remain in that record that you changed/updated information.
Also, why do you have a form created for each record or NCR number. You should be able to create one form and that form should be able to:
1 - search through each record via the arrows/scroll buttons on the bottom of the form
2 - create the from from a query where the user would be able to select the criteria then bring back that record in the form.
A relational database should not allow you to update one record and have that update occur on another record.

Do you understand all of this?



Cheers,

JJB373