Using Bound Objects

[Roy Goldhammer]

Hello there

I have table that bound to one of my forms

I would like that when i update data on the form it will be on the table and
not when i finished adding or update it

I've tried to refresh it but it return the the tab to the first control in
the form or in worst case brings me an error of: OLE has problem updating
the table

What can cause the problem and how can i solve this things?

any help would be useful

 

[Sylvain Lafontaine]

Try with "DoCmd.RunCommand acCmdSaveRecord". If your form is not the active
form, then you can also use the command: DoCmd.Save acForm,
"Name_of_the_form".

It is also possible that using Requery instead of Refresh will save your
modifications before requerying the database but I'm not sure.

S. L.

 

[Roy Goldhammer]

Whell Sylvain

The error caused by bad data that the refresh tried to enter the table.

However the docmd.runcommand accmdSave doesn't work and don't save the data
to the bound table

I tried to use Requery instead but it still do the same (bring the focus
back to the first contol on the form)

I even tried to use CancelEvent but no good.

As diffrence from access and SQL server the record is being saved on the end
of the update and not on the start.

What else can i do?

 

[Sylvain Lafontaine]

First, acCmdSave is not the same thing as acCmdSaveRecord. The acCmdSave
will not save your current record to the tables.

Second, I'm not sure of truly understanding what you want to achieve. If
you are asking that each of your modifications to be available (written) to
the tables exactly at the same time as you are writing them; then it is not
possible. You can add a lot of saving commands to your forms to achieve a
near 100% coverage but as you point out, in some cases, there are the
possibility of having some logical discrepancy to block it. Even more,
updating a table doesn't mean that all queries (open forms, open tables,
etc.) depending on it will automatically display the new result. You have
to manually refresh your forms and open tables. (This is not quite true, as
an open form without any editing may display an update after a lag time of
about 60 seconds. This lag time may be set in the advanced options but it
doesn't apply when you have begun editing the current record or if the form
is based on a complex query.)

Also, I'm not sure of understanding correctly what you are saying about the
difference beetween Access and SQL-Server when it come to save an update.

S. L.

 

[Roy Goldhammer]

Thankes Sylvain four your attantion

The diffrence i'm talking about between access and sql server is: In access
when you add new record with bound form you get dirrectly the auto number
field(if exists) and all the rest of the defaults. Updating the record and
checking is being done when you leave the row,
When you editing record the data that you change is being update immidietly
and not when you leave the row

About what i neet to achive is: My form carries more information icluding
Subforms. When I add new data the related data should relate to the data I
entered. This whay it is important that the data will be updated immidietly.

 

[Sylvain Lafontaine]

You are right about the slight difference beetween Access and SQL-Server
when it come to obtain the Identity value of a new row. This is supposed to
be done transparently by the Access Data Project (ADP) when you are using
linked forms with subforms. However, many things can go havroc with ADP; as
this is still a new technology, not as mature as MDB/JET.

First suggestion: if you are using Access 2000, replace it with Access 2002
or 2003. There are too many bugs with the 2000 version to achieve
successfully a somewhat complex project. There are still a few bugs with
2002/2003, but a lot less (but there are no difference beetween the 2002 and
the 2003 versions: this is exactly the same engine behind).

Second, the book "Microsoft Access Projects with Microsoft SQL Server", from
Ralf Albrecht and Natasha Nicol, gives many usefull informations about forms
and subforms. Probably that you will find what you need in this book.

S. L.