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. 2 followers. Follow

8 Replies
1493 Views

Similar Articles

[PageSpeed] 46

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:

Graphs in Other Files do not Update
Hi, I have a main file with graph (AP.xls) and several sub files with graphs that are linked to the main AP.xls file. The columns of the main file are in months and I often have to add new columns for a new month. I find that if I add new columns using copy => Insert copied cells (the column is inserted to the left of the column being copied), the graph within the AP.xls files is updated with the new column/month. HOWEVER, when i open the other sub files, they aren't updated. HOWEVER, if the sub files are opened together with the main AP.xls file, and I insert the new column, t...

disable snap-to while dragging tab stops?
How do I drag a tab stop to arbitrary position without it snapping to the tick marks on the ruler? I tried holding down shift, ctrl, alt. None of them works. I even turned off everything in the arrange->snap menu. Publisher 2003. Set the tab to about where you want it, zoom to 200%, open the tab dialog and input the number where you want the tab positioned, click set. The tab button can be dragged to your toolbar by customizing. This is the best that Publisher can do... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "p...

update a date cell only when worksheet is edited
Hi. is there a function that updates the date cell automatically only when the worksheet is edited or something to that effect. thank you This might help http://www.mcgimpsey.com/excel/timestamp.html Regards Rowan Joe Black wrote: > Hi. is there a function that updates the date cell automatically only when > the worksheet is edited or something to that effect. thank you ...

UPDATE and SET w/ CAST (String Replace)
I have a text field column in SQL 2005 that contains HTML code. I would like to update certain references in this HTML code throughout, but am having problems. I know that I can't do a string replace with the text data type, which is why I would like to cast it as nvarchar(max). Any help would be appreciated. Thanks. UPDATE HTML SET (Cast(HTMLData as nvarchar(max)) = REPLACE(HTMLData,'detail.cfm','detail.do') WHERE HTMLData like '%detail.cfm%' "Daniel Reardon" <danreardon@gmail.com> wrote in message news:1184770539.845716.315320@z24g2000prh.g...

record payment troubles
Hi, this is something I have not been able to solve in money for a long time. In my bills and deposits schedule, I have scheduled my my bills. Now, if I click 'record payment', an entry is made in the appropriate bank account. But, when I download the bank statement, the same information is downloaded again. Is there any way to tell money that those two are the same things. A painful workaround is to delete the entry, but I am trying to find if there's a better way to do it. thanks, -abhinav In microsoft.public.money, ab wrote: > > >this is something I have not been...

stops responding
A few days ago while trying to balance one of my accounts, Money would start hanging and I had to kill the process and created a LRD file. My file is only 12MB and it has been fine until recently. I am using Money 2007. I should have put my system specs. I have a P4 3.8GHZ w/ 2GB RAM running XP Pro. When I have Money open, I typically only have Messenger, a browser, and sometimes Acroabat Reader running also. "Casey" wrote: > A few days ago while trying to balance one of my accounts, Money would start > hanging and I had to kill the process and created a LRD file. ...

adding a new record to a table from a form (works in edit mode)
I have a form which shows select field from a table (showing all rows of data), and have 2 buttons , one to edit, one to add. The edit button works perfectly DoCmd.OpenForm "frm_TRA_EditRoute", , , "[anRouteID]=" & Me.Text62.Value, acFormEdit, , True the add button does not. DoCmd.OpenForm "frm_TRA_EditRoute", , , , acFormAdd in edit mode, the form loads up with all of the details (the autonumber correct, the combo boxes drawing in the correct data), however in add mode the autonumber box is just blank (doesn't say "(Autonumber)" like it ...

blue stop screen 0x0000007E
stop error code after windows installs drivers. this is new hdd and mobo but problem persists.oxoooooo7e (oxf748eobf oxf78da2o8,oxf78d9fo8....was doing it with last mobo and hdd...help please!!! -- thanks one and all for your helping hands xerxies wrote: > stop error code after windows installs drivers. this is new hdd and > mobo but problem persists.oxoooooo7e (oxf748eobf > oxf78da2o8,oxf78d9fo8....was doing it with last mobo and hdd...help > please!!! That *is* a driver issue. You are installing the incorrect drivers or need to contact the manufacturer of said ...

Update attribute using ldp
Hi There, I have written an ldf script which creates few custom attributes and an auxilliary class which contains these custom attributes. Also this auxilliary class is part of Users and OrganizationalUnit classes. Now I want to update the values to the custom attributes. How do I do it using ldp? I can set values for the inbuilt attrbutes without any issues, however when I try to set the values for the custom attributes I get below error, Error 0x20B5 The name reference is invalid. Please advise. Thanks, Suresh -- sureshpalani ----------------------------...

recipient policy stopped working
Hello All, I'm seeing an issue in my Exchange 2003 environment where an admin will create a new account and the recipient policy does not populate the users email address. This just started happening a few days ago here in the US... It happened in our UK office last week but I thought that might have been user error on the admin. We are in mixed mode with Exchange5.5, which we have been for almost a year. I did how ever remove some old 5.5 servers but the last one I removed was almost a month ago, so i don't think it would be that. I know you need to ask this question so yes th...

If statement with subform
Is the a way to perform an conditional if statement on a subfrom like this: If Me.InScope_subform HasData then Me.cmdViewReport.Enabled=True Else If Me.InScope_subform doesn't HaveData then Me.cmdViewReport.Enabled=False End If What I am trying to do is enable a command button if there are records in the subform but I don't know which of the subform properties to use. 1) HasData is either True or False. Since there are no other possibilities: If Me.InScope_subform.HasData then Me.cmdViewReport.Enabled=True Else Me.cmdViewReport.Enabled=False End If Thi...

How do I stop a cc email to myself from going to deleted folder
Sometimes when I send an email, I want to keep a copy ofr myself. When I enter my email address in the cc part, this always goes straight to the deleted folder, even though I have my email listed as a safe sender. Any suggestions? On 13/03/2010 01:53, technochallenged wrote: > Sometimes when I send an email, I want to keep a copy ofr myself. Outlook automatically keeps a copy of every email you send in the Sent Items folder. There is no need to cc yourself. "technochallenged" <technochallenged@discussions.microsoft.com> wrote in message news:D4...

Online updates only working with credit accounts?
I have been struggling to get my account set up with automatic updates in money 2006. I have set it up successfully for 2 credit card accounts. But, when I set up brokerage accounts, they appear to connect to my accounts but then say $0 (this occurred with 3 accounts: Etrade, Fidelity, anad Vanguard). I searched the help/ user guides but found nothing about this problem. My bank (Citizen's Bank) apparently does not allow for automatic updates. I checked to see if Citizen's bank was on the financial institution list before I downloaded the software, and it was, so I feel like...

3197: The microsoft Jet Database Engine stopped the process because you and another user are attempting to change the same data at the same time
Hello, I have a fairly large (For Access - its slated to be transferred to MySQL) backend that allows the user to use the database's front end normally to store and retrieve data. However when I try to open the database I get the Error "The microsoft Jet Database Engine stopped the process because you and another user are attempting to change the same data at the same time". This isn't true because I have it on my local computer. I've tried importing to a new DB file and I get the same error. The original DB will not open to allow import (show table list) and show er...

BEFORE UPDATE EVENT
Hi, I am very fascinated with the control one can have over what goes in by the use of the BEFORE UPDATE EVENT of a control on a form or the form itself. Unfortunately, I do not have the proper knowledge of how to use the procedure so that I do not get error messages when I am entering data. Can anyone recommend a good article I can read to get my feet wet? -- Glint On Oct 30, 11:34 am, Glint <Gl...@discussions.microsoft.com> wrote: > Hi, > I am very fascinated with the control one can have over what goes in by the > use of the BEFORE UPDATE EVENT of a control on a form or...

update query for continous form
Hello, with the following code i am trying to uncheck a box on my continous form as it's closing and this does not work. my recordsource is ProdHistory and in that table the record that i need to disselect everytime the form closes is Select. can anyone help? Private Sub Form_Close() DoCmd.SetWarnings False If Me.Dirty Then Me.Dirty = False 'Save first. strSQL = "UPDATE ProdHistory SET Select = false " & _ "WHERE Select = true;" DoCmd.RunSQL strSQL Me.requery DoCmd.SetWarnings True End Sub Will wrote: >with the follo...

Stop all macros
I have a button on a form, which should do different things depending on 1. The data contained within the current record; 2. Whether the current record is a 'new record'; 3. Whether the current record is the first record in the form. On click, an embedded macro is initiated. Each step in the embedded macro has a condition and a corresponding 'RunMacro' action. All the conditions are mutually exclusive, and each sub-macro has a final step of 'StopAllMacros'. So the embedded macro should trigger one, and only one sub-macro. However, it appears that what is act...

How to disable the "implicit mx record" in Exchange
I am having a problem with exchange sending to hosts in recipient domains where these hosts are not actually mail servers. After a lot of review of the SMTP logs, I realized that sometimes Exchange is sending to the correct MX record host, and sometimes it is sending to the host with the A record for the actual domain. When I say the record for the domain, I mean an A record that refences the bare domain name rather than an individual host in the domain. So when Exchange gets a DNS timeout looking up an MX record, it falls back to sending to the domain A record. This causes an immed...

Multiplying records
I am working on a Labor projection database. I need to be able tocreate a record for every day (only for workingdays) between start andend dates. For example, Job will start on 4/5/2007 and will becompleted on 5/15/2007. I need to create a table with every workingday in between start and end dates. I also need to create otherfields in that table.table should display records as shown below:4/5/2007 10 Actual Acme4/6/2007 15 Projection Merlin4/9/2007 21 Actual Harry......5/15/2007 10 Actual Star...

Form Query
I am trying to run a query for a form, I am using the following criteria [form]![frmSchoolsQry]![txtschool] . The form name and the text box from the form. When I click the command button the launch the query a parameter dialog pop up. The dialog ask for the text that is in the text box. I use forms to query dates, they work great, why don’t this work? Any suggestions? You're missing an s in Form: [Forms]![frmSchoolsQry]![txtschool] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "rap43" <rap43@discussions.microsoft.com>...

I would Like to Receive the Windows Update Services for my Account
Thanks for your service... I would like to receive the Windows Updates for this acco unt at any time; please, take note about it. Thanks. -- FCAMPBELL01 Signatureship: ix/lc=1030 wcfla/lc=xxx-xx60310. fset/lc=xxx-xx25377. FCAMPBELL01 wrote: > Thanks for your service... I would like to receive the Windows > Updates for this acco > unt at any time; please, take note about it. I have taken note. Many others that are your peers likely will as well. However - I would bet the question would be, "Why should we care?" This is a peer-to-peer newsgroup - a foru...

copying a whole record from one table to another
have a form that allows users to search a Read Only database. I have a make table query that pulls data from this databae and puts it into a table in access. When a new record shows up in the linked database, the user will be able to search for it through my form, and what i would like to be able to do is, have a button that would allow the user to automatically import the new record into a different table based on the Manager who entered the new record. to simplify users pulls up the new record added to the linked database and needs to update the access database i would like a button ...

Crosstab query and Lookup field problem
Hi there, I'm trying to create a Crosstab query that shows a Class of Business on the rows, Status along the columns and a total number of Projects as the values. The trouble is, the Project Status field is a lookup from another table, so though the Crosstab query works, along the top it shows 1, 2, 3, 4 etc as the column headings instead of Investigating, Estimating, On Hold...etc. I can't seem to find a way around this, can anyone help? Many thanks That's just one of the many reasons why many of us "seasoned" developers shun lookup fields. If you want to ...

Stopping the scroll bar
Hi all, this is my first time. I don't know if this is the right place for this problem but here I go. I have created a template of an invoice the size of an A4 and would like to stop the scroll bar moving both horizantally and vertically in normal view just as if it was in print view. Is that possible through VBA and if yes what is the code that I would need to achieve such task. Thanks in advance --- Message posted from http://www.ExcelForum.com/ Not too sure what you are after but you can remove the scrollbars with <Tools - Options> and then in the View tab, at the bottom,...

calender updates
Any suggestions as to how I can update the calender in outlook 2002 xp to show bank holidays for years 2007/8/9pls ??? Thx Dale Dale <nothere@blueyon.com> wrote: > Any suggestions as to how I can update the calender in outlook 2002 > xp to show bank holidays for years 2007/8/9pls ??? See if this helps: http://www.outlook-tips.net/howto/missinghol.htm -- Brian Tillman ...