How To Stop Acces From Update The Record When The Subform Query Is On Focus?

Hi

I need to know how to stop acces from update the record when the
subform query is on focus?

Thanks

0
Chipcom
4/21/2007 11:33:36 AM
access 16762 articles. 3 followers. Follow

8 Replies
1840 Views

Similar Articles

[PageSpeed] 30

In news:1177155216.699820.268130@d57g2000hsg.googlegroups.com,
Chipcom <bz1977@gmail.com> wrote:
>
> I need to know how to stop acces from update the record when the
> subform query is on focus?

If you mean that you want to keep the main form's record from being 
saved when the subform gets the focus, the only ways you can do that 
are:

1. Have the main form be unbound.  In that case your own code has to do 
the job of reading the record, assigning its field values to the 
controls on the form, detecting whether the values in the controls have 
changed, assigning them back from the controls to the fields in the 
record, and saving the record when you think it is appropriate.

2. Have the main form be bound, but keep all the controls unbound.  Your 
code has to navigate the form's recordset and assign values from the 
recordset to the controls on the form, and then back again when you're 
ready to save the record.  Since the changes to the controls don't 
affect the form's recordset directly, those changes won't be saved when 
the focus goes to the subform, only after your code assigns the values 
from the controls to the underlying fields and then you navigate the 
form to another record, or close the form, or otherwise force a save.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
4/21/2007 12:59:37 PM
On Apr 21, 2:59 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
wrote:
> Innews:1177155216.699820.268130@d57g2000hsg.googlegroups.com,
>
> Chipcom <bz1...@gmail.com> wrote:
>
> > I need to know how to stop acces from update the record when the
> > subform query is on focus?
>
> If you mean that you want to keep the main form's record from being
> saved when the subform gets the focus, the only ways you can do that
> are:
>
> 1. Have the main form be unbound.  In that case your own code has to do
> the job of reading the record, assigning its field values to the
> controls on the form, detecting whether the values in the controls have
> changed, assigning them back from the controls to the fields in the
> record, and saving the record when you think it is appropriate.
>
> 2. Have the main form be bound, but keep all the controls unbound.  Your
> code has to navigate the form's recordset and assign values from the
> recordset to the controls on the form, and then back again when you're
> ready to save the record.  Since the changes to the controls don't
> affect the form's recordset directly, those changes won't be saved when
> the focus goes to the subform, only after your code assigns the values
> from the controls to the underlying fields and then you navigate the
> form to another record, or close the form, or otherwise force a save.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)




Hi

Tell me please, is it possible to set the form to be unbounded and
after the update to set it with control source with VBA?
if this is not possible what is the shortest way to do that?
Can you please show an example with 2 fields of the shortest way?

Thanks



0
Chipcom
4/27/2007 1:52:26 PM
On Apr 27, 3:52 pm, Chipcom <bz1...@gmail.com> wrote:
> On Apr 21, 2:59 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
> wrote:
>
>
>
>
>
> > Innews:1177155216.699820.268130@d57g2000hsg.googlegroups.com,
>
> > Chipcom <bz1...@gmail.com> wrote:
>
> > > I need to know how to stop acces from update the record when the
> > > subform query is on focus?
>
> > If you mean that you want to keep the main form's record from being
> > saved when the subform gets the focus, the only ways you can do that
> > are:
>
> > 1. Have the main form be unbound.  In that case your own code has to do
> > the job of reading the record, assigning its field values to the
> > controls on the form, detecting whether the values in the controls have
> > changed, assigning them back from the controls to the fields in the
> > record, and saving the record when you think it is appropriate.
>
> > 2. Have the main form be bound, but keep all the controls unbound.  Your
> > code has to navigate the form's recordset and assign values from the
> > recordset to the controls on the form, and then back again when you're
> > ready to save the record.  Since the changes to the controls don't
> > affect the form's recordset directly, those changes won't be saved when
> > the focus goes to the subform, only after your code assigns the values
> > from the controls to the underlying fields and then you navigate the
> > form to another record, or close the form, or otherwise force a save.
>
> > --
> > Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> > (please reply to the newsgroup)
>
> Hi
>
> Tell me please, is it possible to set the form to be unbounded and
> after the update to set it with control source with VBA?
> if this is not possible what is the shortest way to do that?
> Can you please show an example with 2 fields of the shortest way?
>
> Thanks- Hide quoted text -
>
> - Show quoted text -


Any idea?

0
Chipcom
4/29/2007 4:13:16 AM
In news:1177681946.117235.264830@r3g2000prh.googlegroups.com,
Chipcom <bz1977@gmail.com> wrote:
>
> Tell me please, is it possible to set the form to be unbounded and
> after the update to set it with control source with VBA?
> if this is not possible what is the shortest way to do that?
> Can you please show an example with 2 fields of the shortest way?

I'm not sure what you're asking.  After the update of what?  Are you 
talking about setting a control's ControlSource property, or the form's 
RecordSource property, or a combination of the two?  Please describe in 
greater detail what it is you want to do, and what circumstances make 
you want to do it.  I ask that last question because it may have some 
bearing on the answer.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
4/29/2007 8:05:55 PM
In news:1177819996.481457.267560@e65g2000hsc.googlegroups.com,
Chipcom <bz1977@gmail.com> wrote:
>
> Any idea?

Sorry -- I've been too busy to look in the newsgroup.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
4/29/2007 8:06:28 PM
On Apr 29, 10:06 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
wrote:
> Innews:1177819996.481457.267560@e65g2000hsc.googlegroups.com,
>
> Chipcom <bz1...@gmail.com> wrote:
>
> > Any idea?
>
> Sorry -- I've been too busy to look in the newsgroup.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)


Hi Dirk


I am talking about that when I focus on the sub form Access saves the
record.
You explained to me that there is two ways to save the record when the
user is want and I am talking about the Form's ControlSourcre :
Is there a way to unbound the Form with VBA and when the sub form is
on focus and access is trying to save the unbound form to assign the
table name to the ControlSource of the form?

If this is not possible what is the shortest way to do that?
Can you please show an example with 2 fields of the shortest way?

Thanks





0
Chipcom
4/30/2007 4:10:04 AM
On 29 Apr 2007 21:10:04 -0700, Chipcom <bz1977@gmail.com> wrote:

>I am talking about that when I focus on the sub form Access saves the
>record.

The reason that it does this is that - typically - a Form is used for the
"One" side of a one to many relationship, and the Subform for the "many".
Access must save the "one" side record before the subform starts creating
"many" side records, in order to ensure that relational integrity is enforced;
otherwise the subform could create records not bound to any mainform record.

Why do you feel that you need to defeat this? 

>You explained to me that there is two ways to save the record when the
>user is want and I am talking about the Form's ControlSourcre :

Recordsource, I guess you mean?

>Is there a way to unbound the Form with VBA and when the sub form is
>on focus and access is trying to save the unbound form to assign the
>table name to the ControlSource of the form?

This is really confusing, Chipcom. Access isn't ever going to try to save the
unbound form - it's UNBOUND, it's got noplace to save. You can explicitly open
a Recordset and write code to copy data from the unbound form controls into
the table - but what you would end up with is *EXACTLY* what you get with the
bound form: save the mainform record first, then save the subform records. 

What *real life problem* are you trying to solve with this code?


             John W. Vinson [MVP]
0
John
4/30/2007 4:09:44 PM
On Apr 21, 2:59 pm, "Dirk Goldgar" <d...@NOdataSPAMgnostics.com>
wrote:
> Innews:1177155216.699820.268130@d57g2000hsg.googlegroups.com,
>
> Chipcom <bz1...@gmail.com> wrote:
>
> > I need to know how to stop acces from update the record when the
> > subform query is on focus?
>
> If you mean that you want to keep the main form's record from being
> saved when the subform gets the focus, the only ways you can do that
> are:
>
> 1. Have the main form be unbound.  In that case your own code has to do
> the job of reading the record, assigning its field values to the
> controls on the form, detecting whether the values in the controls have
> changed, assigning them back from the controls to the fields in the
> record, and saving the record when you think it is appropriate.
>
> 2. Have the main form be bound, but keep all the controls unbound.  Your
> code has to navigate the form's recordset and assign values from the
> recordset to the controls on the form, and then back again when you're
> ready to save the record.  Since the changes to the controls don't
> affect the form's recordset directly, those changes won't be saved when
> the focus goes to the subform, only after your code assigns the values
> from the controls to the underlying fields and then you navigate the
> form to another record, or close the form, or otherwise force a save.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)



Can you please show me an example of the firs way?


Thanks

0
Chipcom
4/30/2007 5:25:08 PM
Reply:

Similar Artilces:

Run a Query that Excludes Values in A Table
This seems like something that would be very simple, but I can't find anywhere how to do it. I have a table that lists companies and their addresses, and I want to run a query that excludes companies with certain names. Ordinarily, I would just include something in the Criteria portion of the query: Not like "*microsoft*" and not like "*yahoo*" and not like "*disney*" and not like "*morgan stanley*" But there are over 2,000 companies I want to exclude. If I have the companies I want to exclude in a table, how do I produce a query that will exclude ...

How to seek and then reference the found record to a form record?
I am having trouble using seek to find a record with a unique Serial Number and then display that record on a form. The Serial Number input is a string (textbox). My seek code works Ok but after seek, if not EOF, I try to find absolute position and then use "goto" that record on the form but the absoluteposition is not always correct. The record that gets shown is always offset of one or two records from what I need.Any help is appreciated.-- Regards, Michael...

Query too Complex...
words I have seen way too much of today! And if it is not "too compex" then I get a message saying "insufficient resources". I have spent the day trying to break up boards into smaller sections and trying to find other ways to get the same answer. Eventually I have to bring the seperated boards back together again, so the end result is still the same: Query is too complex Does anyone have a suggestion that I could try to get around these frustrating comments? Should I be using subqueries? - would that help simplify things in the FROM clause? Tara Without seeing ...

customer record missing
We are on great plains 8. All of a sudden, one of our customers is missing from Great Plains. I looked at the database and all records are there except that in RM00101 table, the record is empty. The DEX_ROW_ID for the customer record still exists in RM00101 table but the rest of the information is mostly empty including customer name. I dont think we have deleted any record in RM00101 table and I know that the customer cannot be deleted through the front end, so what has happened. Jack- My suspects would be: 1) user attempted to delete through the GP UI 2) user has access to MS Acce...

Open Record using Sub Form
I've seen this done... where one can double click a subform and open an edit form with the record double clicked in the previous subform. Can I get a link to a tutorial or sample of this? Pretty simple, really. You use the Double Click event of one of the controls in the subform form. Here is an example: Private Sub txtPropertyName_DblClick(Cancel As Integer) DoCmd.OpenForm "frmProperty", , , "[PropertyID] = " & Me.txtPropertyID End Sub -- Dave Hargis, Microsoft Access MVP "Juan Schwartz" wrote: > I've seen this done... where one can...

NTDSNoMatch Query
I'm starting to prep our 5.5 directory for migration to Exchange 2003. We already have a Windows 2003 domain in place. I've just run the Exdeploy tool with the ntdsnomatch test and it has picked up on three mailboxes that it says need altering - they all relate to Trend Scanmail as they all use the Exchange Service Account as their Primary NT account. For things like Resource Mailboxes, where we have no Primary account but multiple accounts are given access via the Permissions tab I would expect the ADC to create a disabled user in the Active directory. What I'm not clear ...

so-called updates
EVERY time i let microsoft 'updates' be installed on an old Toshiba laptop with Windows XP sp3 it takes several (2 or 3) remove-the-power and restart before I can get it to (a) shutdown and (b) reboot. If after an update i 'restore' there is no problem - of course then the updates want a do-over! Is the only option to never load an 'update?" There are updates & there are updates, and since you don't mention what update, who knows what to recommend "Rick Merrill" <Rick0.merrill@gmail.nospam.com> wrote in message news:OoNhJfit...

Program updates
If MS downloads a Money 2007 update, i.e. bug fixes, will the program notify me of the download? Bruce. Good question, Bruce! I wouldn't count on either.......... -- Michael Gordon MVP "Bruce Chastain" <bachastain@XNOSPAMXsbcglobal.net> wrote in message news:%23nLHQX8yGHA.996@TK2MSFTNGP03.phx.gbl... > If MS downloads a Money 2007 update, i.e. bug fixes, will the program > notify me of the download? > > Bruce. > > "Michael Gordon, MVP" <gordonm@denison.edu> wrote in message news:O3GCOq8yGHA.2036@TK2MSFTNGP05.phx.gbl... > G...

Unable to retrieve the complete description string of the event log record
Hi All, I have an MFC application that reads and displays event log records using the ::ReadEventLog() API. The problem is with reading the "Description" message string of the event log record. The MFC application is unable to read the complete "Description" message string and displays only some part of it. However the Windows System Event Log Viewer reads and displays the complete "Description" message string correctly. I have ensured that my MFC application reads the entire "Description" message string by retrieving all the strings as prov...

How do you check which update rollup is installed?
May be a stupid question. But I need to check which update rollup was last installed? Thanks Darren Darren Check the add/remove programs applet. check the 'show updates' checkbox at the top, and under each CRM component (Server, Email Router, SSRS Data Connector, etc) it will show the rollup(s) applied. Dave Ireland "darrenm" <darrenm@discussions.microsoft.com> wrote in message news:BD410974-D479-4560-9AEE-69D7ED3B5F25@microsoft.com... > May be a stupid question. > > But I need to check which update rollup was last installed? > > Thanks > Dar...

Money stopped starting (2003 Deluxe)
For about 1 year now I've been happily using Money 2003 Deluxe without problems. However, last night when I came to rec. my bank statements etc I couldn't launch money. It starts up, I get a splash screen, a bit of the "music" and then it exits. No warning messages, nothing, it just dies. I've installed bugtoaster to try to trap any error, but that doesn't spot anything. I can't open either the sample.mny file or any other money file - the application just won't start. I've tried booting into safe mode and firing it up that way, but still no joy (nor...

Activity Date update Case Follow-up
I am trying to figure out a way for the Activity Due Date to update the Follow-up date of the case when the activity is created. Any Ideas? What I origianlly wanted to do was to create a workflow rule to change the status of the case 24 hours before the service activity occurs. I could not find a way to relate serrvice activities adn cases in workflow. So I will settle for a solution to my initial question. Thanks You would need to implement some custom logic via an assembly in workflow on the service activity to obtain this functionality. -- Graham This posting is provided "...

Update table 12-26-07
Hi, I've 2 Textboxes on my form: FROM,TO and a combobox called AGENT I want to select an agent in the combobox then press on a button so that I may Write the agent in all the records between FROM and TO in a tabe called CAP How can i do this? On Wed, 26 Dec 2007 12:24:00 -0800, Pietro <Pietro@discussions.microsoft.com> wrote: >Hi, > > I've 2 Textboxes on my form: FROM,TO and a combobox called AGENT > I want to select an agent in the combobox then press on a button so that I >may Write the agent in all the records between FROM and TO in a tabe called...

PS2003 updating tasks sent from wrong project
I work with PS2003. I just updated the tasks on a project saved them to publish. One of the team members received requests for updates on a task from another project. He never received the correct project as requested. What's going on? 1) Does he see your tasks in his My Tasks page? 2) Was the other PM in that project at the same time? 3) What do you mean, received request? In PWA? Via e-mail? - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I work with PS2003. I just updated the tasks on a project saved them > to publish. One of the team members receiv...

How do I eliminate duplicae records in excel file
I have a large excel file with 12K records of companies, addresses., etc I want to eliminate duplications where company and city are duplicated but not necessarily street addresses. How do I do so? Hi, Look here CPearson has a macro to do that, go to the middle of the page http://www.cpearson.com/excel/deleting.htm "Big B" wrote: > I have a large excel file with 12K records of companies, addresses., etc I > want to eliminate duplications where company and city are duplicated but not > necessarily street addresses. How do I do so? ...

Outlook 2003 Junk E-mail Filter Update: KB832333
http://www.microsoft.com/downloads/details.aspx?FamilyID=ad3699f7-5cc3-4604-a768-32c4d044b630&DisplayLang=en Note: While the update is live, as of this moment the corresponding KB article isn't. Hopefully it'll have some technical detail, but I'm not counting on it. "Milhouse Van Houten" <btvs@myrealbox.com> wrote in message news:ehCzlR6wDHA.2452@tk2msftngp13.phx.gbl... > http://www.microsoft.com/downloads/details.aspx?FamilyID=ad3699f7-5cc3-4604-a768-32c4d044b630&DisplayLang=en > > Note: While the update is live, as of this moment the correspon...

Need query to fill in missing data on table
Hi there- I am using Access 2003. Here's what my table looks like: Name ID Company Cost John Smith 1234 Phil's Towing $84 John Smith $94 John Smith $104 John Smith $151 Presumably, I need a make table query that will fill in the ID and Company data in each row to make it look like this: Name ID Company Cost John Smith 1234 Phil's Towing $84 John Smith 1234 Phil's Towing $94 John Smith 1234 Phil's Towing $104 John Smith ...

Chart updates using cell reference
I have a multiple charts that plot data in A1:Z20. Column A is always the x-axis. Columns B-Z represent different charts. Sometimes I want to see all 20 rows of data, but sometimes I want to choose only a few rows (to zoom in) and plot only a few rows...say 8-14. I'd like to be able to put 8 in cell A22 and 14 in cell A23 and have all charts reference those entries and update to show only data in cells 8-14. Is it possible? thx - jwhprinter Dynamic charts: http://peltiertech.com/Excel/Charts/DynamicCharts.html Your offset formula for X ("myXValues") would be =INDI...

Outlook 2003 not being updated when showing as connected
One notebook user on a SBS2000 network (using exchange 2000) has been upgraded to outlook 2003. All seemed well for a month and then mysteriously last week outlook messages stopped being updated. In the bottom right of outlook it shows as "Connected" but instead of showing the "All folders are up to date" message the time the folder was last updated is displayed and no new messages are sent or received. To get mail the user has to go offline and then do a manual send/receive. Has anyone any idea what has happened and how I fix? Thanks Paul ...

Query Problem
Hi, I have this query but is not working. SELECT BKAR_INV_NUM, BKAR_INV_SONUM, BKAR_INV_INVCD, BKAR_INV_INVDTE, BKAR_INV_CUSCOD FROM BKARINV WHERE (((BKAR_INV_INVCD)=' ') AND ((BKAR_INV_CUSCOD)="SCOENE0100")) OR ((BKAR_INV_INVCD)="X")); I need it to show all records for SCOENE0100 that the BKAR_INV_INVCD are either "X" or " " Thank you all On Mon, 22 Feb 2010 11:08:04 -0700, "Bre-x" <cholotron@hotmail.com> wrote: >Hi, >I have this query but is not working. > >SELECT >BKAR_INV_NUM, ...

Money Updates not working in Money 2002
When I do my Internet Updates in Money 2002 (Standard) I'm getting the Quotes and Exchange Rates okay (unlike others here) but the Money Updates are failing. I get the standard "Your last call encountered an error: 2/3/2004 10:22 PM" and then "An unknown error has occurred. Please try the operation again. If problem persists, please reinstall Money from your original product disks." Tried it -- didn't work. I believe this feature last worked on or around 1/21/04. Any ideas? Does anyone from Microsoft read these boards? Seems like lots of folks are ...

Combo Box Update Question
I have a form with two combo boxes. The second one is dependent on the value in the first. It all works fine other than if the user moves to an existing record, the value on the underlying query does not show in the comboBox2. The code used for providing the data in comboBox2 is :- Private Sub comboBox1_AfterUpdate() On Error Resume Next Me.comboBox2.RowSource = "SELECT tblRepairType.RepairTypeID, tblRepairType.RepairType FROM" & _ " tblRepairType WHERE RepairCategoryID = " & Me.cboRepairCategoryID & _ " ORDER BY RepairType&quo...

Paste picture link not updating
Hi, I had posted this a few days back as a problem with the Camera Tool. I tried out the Paste Picture Link in Excel 2007. The updated source data is not displayed in the picture link in a few machines although it works ok in others. Does it have anything to do with the monitor display settings?. Tried a few combinations but still did not work. Any idea why this happens? Thanks in advance for the help. Regards, Raj ...

Query tables to add new records
My query returns selected records from a table. Works fine How do I select only certain records from that query to add new records to another table. That tables fields are: caCommuned type yes/no checkbox need this to become check for true caDate type Date/time Shortdate need this to be filled with a certain date I select casID type Number longinterger foreign key Need this to fill with the sID The Select Query returns fields from table Souls sID type autonumber primary key sStauts type text sorted as sCommunicant type yes/no checkbax criteria true sLSName type text sorted as s...

Can't open Help file when on subform
Using Access 2003 on Windows XP. I converted a .hlp help file to .chm and now I'm calling it with the code shown below. This code works fine when the focus is on a main form or a form with no subforms, but throws runtime error "2455" when the focus is on a control in a subform which is within the main form. Isn't 'curForm' still the main form? Isn't a subform seen as a control of the main form? How do I edit the code so the help file will open when the focus is on a control of the subform and yet still functions when the focus is on the main form? All cont...