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

Posted on 1999-07-07
Medium Priority
Last Modified: 2008-03-06

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

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?

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"

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

Question by:TomDedecker
1 Comment

Accepted Solution

ivanc earned 800 total points
ID: 1522537
I am not sure what type of controls U allow for the user to key in.  I would advice using  4 different textbox for the input of new values.  Check for the numeric summation first, upon fulfilling the condition then save it into the database that is associated with your datagrid.  I am assuming U are using ADODC.

The textboxes are name as text1, text2, text3 and text4

I am assuming a access database with fields of num1, num2, num3, num4 have been created

Function CheckCond()
Text1.DataField = "" 
Text2.DataField = "" 
Text3.DataField = "" 
Text4.DataField = "" 
If Not IsNumeric(Text1.Text) Then
        MsgBox "Please Key in a valid Integer", vbExclamation, "Wrong Type"
        Exit Function
    End If
If Not IsNumeric(Text2.Text) Then
        MsgBox "Please Key in a valid Integer", vbExclamation, "Wrong Type"
        Exit Function
    End If
If Not IsNumeric(Text3.Text) Then
        MsgBox "Please Key in a valid Integer", vbExclamation, "Wrong Type"
        Exit Function
    End If
If Not IsNumeric(Text4.Text) Then
        MsgBox "Please Key in a valid Integer", vbExclamation, "Wrong Type"
        Exit Function
    End If

nvalue = text1.text + text2.text + text3.text + text4.text
if nvalue >10 then
        MsgBox "The summation is more than 10", vbExclamation, "Data overflow"

exit function

Text1.DataField = "Num1"
Text2.DataField = "Num2"
Text3.DataField = "Num3"
Text4.DataField = "Num4"

end function

I am pretty confident this is not an error free code but I hope it can help U make the situation easier on the programming.



Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question