Link to home
Start Free TrialLog in
Avatar of TomDedecker
TomDedecker

asked on

URGENT !! : Update record in datagrid with help of a datacombo

VB6

Problem:
update a record in a datagrid using a datacombo on the datagrid

Situation:
Master/detail situation: two tables are linked by a third table in which a
couple of id's from the 2 tables is stored (many to many relationship).
The detail information is shown in a datagrid that is bound to the recordset,
for a selected master record.
On this datagrid we have a bound datacombo on the current record in the recordset. When we
move through the recordset, the datacombo is placed on top of the right field of the datagrid.
This datacombo holds all posible elements of the detail recordset, and is bound to the linking
table by setting the "bound text" property of the datacombo to the id of the detail element.
A textbox holds the id of the master record. These both id's must be saved in the linking table.
We can go to "edit mode" now, to the value in the datacombo can be changed. We can leave this
"edit mode" by a commit or a rollback button. This is OK.
But we also want to save the changed value of the record when we click on the datagrid to select another
detail record, so we can change and update different detail records without having to click
the commit button every time.
So every time we jump to another record in the datagrid, and we are in "edit mode", we must save
the value of the datacombo to the recordset, and refresh the datagrid.

The major problem here is that the value of the datacombo has already changed to the value of the
record we jump to, instead of the value of the record we changed before, before we can save the record
to the recordset and update the recordset.

We have tried the following solutions but there were too many problems with each of them.
Does anyone know how we can solve this problem?

Solution1:
When we call a function to save the record on the "WillChangeRecord" event of the recordset in
the DataEnvironment: When the value of the current record is set to the value of the datacombo,
"willchangerecord" is triggered again. Because we don't want to start the savefunction again, it
is bypassed by setting a boolean.
When the update method of the recordset is called, the following error occurs: "Provider called
a method from IRowsetNotify in the consumer and the method has not yet returned"

Solution2:
When we call the savefunction from the "RowColChange" event of the datagrid, the value of the
datacombo is already adjusted to the record we jumped to. As the savefunction uses this datacombo
to update the recordset, the wrong value is used:
When we change a record into a value that already exists in the database, the record is not saved but
no error is returned from the database (unique primary key on both id's in the linking table).
But when we change a record into a value that doesn't already exist in the database, the record is saved
with the right value (the value we wanted, the value we changed to in the datacombo).
So the update is good, but no errormessage is displayed from the database when we want to enter a value
that already exists in the database.
So we tried to check the database ourselves, to find out if the value we entered already exists in the
database.
But therefore we need the value we changed to in the datacombo, but the value the datacombo displays is
the value we jumped to after the update. So we tried to get the changed value in the "change" event of
the datacombo. But this value also changes when we jump to another record, so we had to distinguish
these cases (by setting a boolean in the "movecomplete" event of the recordset in the DataEnvironment).
We also need the original value of the datacombo to check if the changed value already exists in the
database, because a record can be changed and then be changed back into itself and then saved:
When we save the record with its original value no error box may be shown which says that this value
already exists.
This value is set every time we move to another record after the save is completed, or when we move
without changing a value.
But all these variables that have to be set and reset in the events (which are triggered in very much
different cases) can't be kept consistent.


I know this is a very complicated situation, therefore I shall add email adresses tomorow for more info...


ASKER CERTIFIED SOLUTION
Avatar of ivanc
ivanc

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial