Query that over writes over data

[kabir_hussein]

hi

i am trying to do a query where i have a table(T1) with data in it, and if the data in that table is ever changed. it has to be changed via a changeform(T2). however once changes are done in the change form, it must update it self on the T1 table.

in total there are only four entities that can be changed, but if a entity is not changed it is left blank. but when i click on the query to update T1. it will not allow me to do it. as the T1 table can not contain null values.

can anyone suggest any possible solution.

i am using an append query to do the update T1 from T2

many thanks in advance

kabir

 

[Len Boorman]

Bit confused

If the data in table T1 is to be changed then I would have the record source of the for as the table. Open the form in Edit mode and directly edit the data. No need for any update queries.

Len B

 

[kabir_hussein]

that would be a easier solution but this tender may be changed several times within a given period.

So a change field is introduced so everytime it is changed the change field keeps a record of what,why, when it was changed

sorry i should of explained earlier but wanted to make it clear and simple for others

regards

kabir

 

[Len Boorman]

Okay

So what you actually want to do is when a record has to be changed

1) Select Record to be changed
2) Create new record using data from 1
3) Edit record

So do it that way
You may have to go around the houses a bit but as an outline how abou something like


0) Delete tempory table (see 2)
1) Select record using combo box
2) Make table query using value in combo box as parameter
3) Append data from 2 back to main table
4) Use last record data as source of Edit form.

A bit of a round the houses and maybe others would have a more elegant solution but it may help the thought process

Len B

 

[kabir_hussein]

Hi

thanks for your post but i am a bit confused, here is what i am trying to do. I have a form called tenderlink. This form is like an order form. Users place orders using this form.

However if an order has to be changed it is done via a change form. This change form is linked back to changelink table which has a foreign key linking it to the tenderlink.

The change form is used as it has a what,why and when fields so users later on can see why changes have been made.

Once changes have been made and then saved i want some kind of a query-which im thinking must be an append query that changes that are done on the change form are updated on the tenderlink table.

As changes can happen several times or even once i need the change form to keep a tab on all changes

Hope this helps

at present i am using a append query but i get a problem when fields on the change form are not changed therefore causing null values on the tenderlink table. Thus not saving changes on the tenderlink table

 

[Len Boorman]

I am still not really with you yet.

It sounds as if you have a record that may be changed. If it is changed you wish to keep a history of the record.

Relational databases are generally about destructive updates so you have to be inventive to get around the problem.

One point. If you want to change a data element to some other value then an update rather than an append query would be more appropriate.

Before I go any further I need to be sure I understand what you are actually trying to achieve so am I correct is saying

After a record is created it may be changed. If it is changed you wish to keep a history of the changes.

Len B

 

[kabir_hussein]

i can understand you being confused,

My changeform keep a record of all changes made as it has it own PK and everytime a change is made it is recorded on the table.

i really need help on when changes are made on the change form that they update on the main table(tenderlink). however the problem is on the change table there are only four fields that users can only add data to. This can then be udated back to the tenderlink table.

But if only one of the four fields are changed, and i try and append it, the database does not allow me to do it as it would result in a null value on three fields.

I have included a picture i am not sure if it is attached as it does fail for some reason.

 

[Len Boorman]

Had a look at your E-R Diagram.

I only ever suggest the sort of thing that I would do and therefore I can be wrong in my ideas.

Perhaps there are a number of alternative ways of handling this.

Looking at your basic problem. If you run an update query and there is no new value you actually update to Null and this gives the problem. There are a limited number of fields that can be changed so why not run an individual update query for each value that may be changed but use a parameter in the update query of Not Null. So if a new value has not been entered (Is Null) then the opdate will not occurr.

Myself I believe that you are trying to keep essentially multiple copies of the same record. In some of the applications I have constructed I have used an "Archive" principle. That is if a record is to be changed then I firstly copy the record to an Archive Table and date and time stamp it. I then change the "Live" record and at this point make whatever note fields I feel appropriate available.

The first time a change takes place the Archived record obviously does not contain any notes but subsequent records will contain any notes that are added.

If I then want to see the history of a record I can call up current live record with Archive records in a list box on same form.

That's my way that suits my applications. Not saying it's right or your method is wrong. They are just different approaches.

Anyway hope that first suggestion helps

Len B