Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

721 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