Update query in event procedure

Hi

I have created one form with labels as  ScripName,LTP1,LTP2,LTP3.

1.Now i want to write an event procedure for On Click event.I want to run the
following update query when i click on LTP1 label 
UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode = bse500.ScripCode SET
bse500.LTP1 = ppc500.ltp;
( ppc500 and bse500 are my two tables )

2.When i click on label LTP2 the same query should execute with LTP1 replaced
by LTP2 
   ( With New data imported in the above two tables )
3.When i click on label LTP3 the same query should execute with LTP2 replaced
by LTP3 
   ( With New data imported in the above two tables )

regds

prasanna

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

0
ppc
12/15/2007 1:06:51 PM
access.forms 6864 articles. 2 followers. Follow

9 Replies
1235 Views

Similar Articles

[PageSpeed] 46

Prasanna,

In your code, you can use the Execute method, like this:

CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode = 
bse500.ScripCode SET bse500.LTP1 = ppc500.ltp", dbFailOnError

However, labels don't have a Click event, so you may need to use a 
command button or some other event instead.

By the way, you are creating a non-normalised table here.  Maybe there 
is a good reason for this.  But otherwise, you may want to pause and 
consider adding these imported data into separate records, rather than 
separate fields.  In this case, you would ad a new field to the table to 
identify the Port.

-- 
Steve Schapel, Microsoft Access MVP

ppc via AccessMonster.com wrote:
> Hi
> 
> I have created one form with labels as  ScripName,LTP1,LTP2,LTP3.
> 
> 1.Now i want to write an event procedure for On Click event.I want to run the
> following update query when i click on LTP1 label 
> UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode = bse500.ScripCode SET
> bse500.LTP1 = ppc500.ltp;
> ( ppc500 and bse500 are my two tables )
> 
> 2.When i click on label LTP2 the same query should execute with LTP1 replaced
> by LTP2 
>    ( With New data imported in the above two tables )
> 3.When i click on label LTP3 the same query should execute with LTP2 replaced
> by LTP3 
>    ( With New data imported in the above two tables )
0
Steve
12/15/2007 6:04:57 PM
>However, labels don't have a Click event, so you may need to use a 
>command button or some other event instead.

Labels certainly DO have a click event, unless they're the labels that Access
automatically attaches to other objects, such as textboxes and comboboxes!

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

0
Linq
12/15/2007 6:36:10 PM
We,, what do you know?  Thanks, Linq!  You're never too old to learn.  I 
have never noticed that peculiarity before.

-- 
Steve Schapel, Microsoft Access MVP

Linq Adams via AccessMonster.com wrote:
> Labels certainly DO have a click event, unless they're the labels that Access
> automatically attaches to other objects, such as textboxes and comboboxes!
0
Steve
12/15/2007 7:11:54 PM
Thanks Steve

The labels i have created do have on click event.The same query i can execute
by DoCmd.OpenQuery but my requirement is whenever i click on that label i
want the label name which is same as field name of the table bse500 to
extract in a string.With this string i want to replace the LTPn.
That is bse500.strSQL where strSQL is LTP1,LTP2,LTP3 etc.

regds

prasanna

Steve Schapel wrote:
>Prasanna,
>
>In your code, you can use the Execute method, like this:
>
>CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode = 
>bse500.ScripCode SET bse500.LTP1 = ppc500.ltp", dbFailOnError
>
>However, labels don't have a Click event, so you may need to use a 
>command button or some other event instead.
>
>By the way, you are creating a non-normalised table here.  Maybe there 
>is a good reason for this.  But otherwise, you may want to pause and 
>consider adding these imported data into separate records, rather than 
>separate fields.  In this case, you would ad a new field to the table to 
>identify the Port.
>
>> Hi
>> 
>[quoted text clipped - 12 lines]
>> by LTP3 
>>    ( With New data imported in the above two tables )

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

0
ppc
12/16/2007 6:04:19 AM
Prasanna,

If I understand you correctly, you will be able to get this value from 
the Caption property of the label:
Me.NameOfLabel.Caption

-- 
Steve Schapel, Microsoft Access MVP

ppc via AccessMonster.com wrote:
> Thanks Steve
> 
> The labels i have created do have on click event.The same query i can execute
> by DoCmd.OpenQuery but my requirement is whenever i click on that label i
> want the label name which is same as field name of the table bse500 to
> extract in a string.With this string i want to replace the LTPn.
> That is bse500.strSQL where strSQL is LTP1,LTP2,LTP3 etc.
> 
0
Steve
12/16/2007 9:06:54 AM
Steve

Tried that getting Compile error : Method or data member not found

regds

prasanna

Steve Schapel wrote:
>Prasanna,
>
>If I understand you correctly, you will be able to get this value from 
>the Caption property of the label:
>Me.NameOfLabel.Caption
>
>> Thanks Steve
>> 
>[quoted text clipped - 3 lines]
>> extract in a string.With this string i want to replace the LTPn.
>> That is bse500.strSQL where strSQL is LTP1,LTP2,LTP3 etc.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

0
ppc
12/16/2007 11:08:37 AM
Hi Steve

It worked.I put wrong label name previously.
Now how to use this string in the following query in place of LTP1

CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode =
bse500.ScripCode SET bse500.LTP1 = ppc500.ltp", dbFailOnError

regds

prasanna

Steve Schapel wrote:
>Prasanna,
>
>If I understand you correctly, you will be able to get this value from 
>the Caption property of the label:
>Me.NameOfLabel.Caption
>
>> Thanks Steve
>> 
>[quoted text clipped - 3 lines]
>> extract in a string.With this string i want to replace the LTPn.
>> That is bse500.strSQL where strSQL is LTP1,LTP2,LTP3 etc.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1

0
ppc
12/16/2007 12:19:28 PM
Prasanna,

Try like this:
Dim strLTP As String
strLTP = Me.NameOfLabel.Caption
CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode =
bse500.ScripCode SET bse500.[" & strLTP & "] = ppc500.ltp", dbFailOnError

-- 
Steve Schapel, Microsoft Access MVP

ppc via AccessMonster.com wrote:
> Hi Steve
> 
> It worked.I put wrong label name previously.
> Now how to use this string in the following query in place of LTP1
> 
> CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode =
> bse500.ScripCode SET bse500.LTP1 = ppc500.ltp", dbFailOnError
0
Steve
12/16/2007 5:29:57 PM
Steve

Got it right.Thanks for your prompt help.

regds

prasanna

Steve Schapel wrote:
>Prasanna,
>
>Try like this:
>Dim strLTP As String
>strLTP = Me.NameOfLabel.Caption
>CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode =
>bse500.ScripCode SET bse500.[" & strLTP & "] = ppc500.ltp", dbFailOnError
>
>> Hi Steve
>> 
>[quoted text clipped - 3 lines]
>> CurrentDb.Execute "UPDATE ppc500 INNER JOIN bse500 ON ppc500.ScripCode =
>> bse500.ScripCode SET bse500.LTP1 = ppc500.ltp", dbFailOnError

-- 
Message posted via http://www.accessmonster.com

0
ppc
12/17/2007 8:13:43 AM
Reply:

Similar Artilces:

CRM Logs and events
Is there any log for CRM? Which actions are registered in that log? How can I establish the events that I want in the log? Thanks a lot. You can enable tracing on the server and the client. It's described on http://support.microsoft.com/kb/907490/en-us. -- Michael H�hne, Microsoft Dynamics CRM MVP CRM Blog on http://www.stunnware.com ---------------------------------------------------------- "arielqs" <arielqs@discussions.microsoft.com> schrieb im Newsbeitrag news:10F108DA-5D5F-4A68-A202-20EBD912CE6C@microsoft.com... > Is there any log for CRM? Which actio...

Using a query as base for report
Hello, I have created a table where I run a query picking the columns I want in the query. From the query, I create a report. My question is this: Whenever I open the report, I want it to show only the open items. Example: My query has 10 columns, when ALL the fields are populated, the record is complete and does not need to be in the report (i.e. open items report). If the 10 fields are NOT all completed, it's still considered opened and will be on the report when it is run. I think I'm thinking too much about it and making it sound harder to myself. Can you please...

Credit card balance update?
Hope you can help! I am a new user of MS Money 2006. I have several bank accounts entered, and all seem to work nicely except credit cards. Should credit card accounts update like bank accounts do? All my bank account information is update whenever I choose to do a “internet update” But credit cards do not. It seems I have to go to the credit card companies web sit and download the statement manually. Is this correct? Shouldn’t the most current transaction that I see online, on my statement appear in Money after an automatic update, without having to manually download? I h...

Update GAL DL members in OWA or other?
Background: We do not host our own exchange server. Therefore all of the distribution lists in the GAL are owned by a single "service" user that multiple people know the user/password for. We currently have Outlook setup and authenticating to the exchange server as that user on a server that allows us to RDP into and add/del users from distribution lists without having to contact our Exchange host. I am trying to get away from all of our IT staff having to have multiple Mail profiles on their machine in order to administrate the global access list. I have read and understand that d...

Run a query a few times
Is there a way to get a query to run 10 time. I would like a buttonthat will run a query 10 times.It would take a long time to expalin y i need to run it 10 times butthere is a reasonThanks Use a For..Next loop or a form timer and the DoCmd.RunSQL method....

access report on a cross tab query
i have a cross tab query which gives an output like below with following columns------- ozip dzip week no1 week no2 week no3 columns week no1 , week no2 and so on could be dynamic-- it could also be week no52, week no 51 etc nowi i am trying to create a report based on the above query using report wizard--- how should i create a report when column names keep on changing based on what the user needs pls advise thanks subs wrote: >i have a cross tab query > >which gives an output like below with following columns------- > >ozip dzip week no1 wee...

How to update a linked spreadsheet without opening it?
Hello experts, I have two Excel 2000 spreadsheets, S1 and S2. S1 is linked to S2. When values in S2 are changed and saved, I need S1 to update its value itself WITHOUT ME MANUALLY OPENING S1 AND UPDATING IT. Can I update S1 without opening it and how? Thanks vey much for your help -- Message posted from http://www.ExcelForum.com Hi AFAIK no chance to do this -- Regards Frank Kabel Frankfurt, Germany > Hello experts, > > I have two Excel 2000 spreadsheets, S1 and S2. S1 is linked to S2. > > When values in S2 are changed and saved, I need S1 to update its > values ...

Event ID 3005 and OMA
I have Exch 2003 SP2 on a single server (Windows2003 R2 SP1). I have a dozen or so mobile users with Treo 700w's. All was working well until a few days ago. We changed our company-wide primary email addresses, but retained the prior email addresses as alternates. However, since the change our mobile user's wireless syncs (using ActiveSync on the Treo) are generating errors. The user's also report that email deletions made on the Treo's are not syncing, neither are emails read on the Treo showing as being read in their mailbox. They continue to receive and send emails wit...

Use query results to copy files?
Hi, I have a query that returns results like: Name: ID: Picture ID File Name: Bob 1234 bobs_id_pic.jpg Tina 5678 tinas_id_pic.jpg .... Is there a way to use these results to (automatically) copy all the id_pics to another folder (assuming all ID pics are in the same folder)? Thanks! Yes, but it's not a trivial task. You would have to open a recordset on the query results and then use the file system object to do the copying. If there aren't too many, it will be quicker to do it manually. -Dorian "Gary" wrote: > Hi, I have a qu...

Monitor Ribbon Events
With Word2003 I could create an event monitor to detect when a user used a command bar button. For example, the following is used to detect use of the "Print..." command: Private WithEvents ctrlEvent As CommandBarButton Sub AutoExec() Set ctrlEvent = Application.CommandBars("Menu Bar").Controls("File").Controls("Print...") End Sub Private Sub ctrlEvent_Click(ByVal Ctrl As CommandBarButton, CancelDefault As Boolean) CancelDefault = True Select Case True Case ActiveDocument.PageSetup.RightMargin > 72 MsgBox "Set green ...

update fail notification for MS OFFICE SYSTEM 2007
Hi, the following update fails everyday.. pls help me to load them..or if the update is not important help me to stop from updating pls.... Update for Microsoft Office Word 2007 (KB974561) Installation date: ‎19/‎06/‎2010 6:08 AM Installation status: Failed Security Update for Microsoft Office Excel 2007 (KB982308) Installation date: ‎19/‎06/‎2010 6:05 AM Installation status: Failed Security Update for Microsoft Office PowerPoint 2007 (KB982158) Installation date: ‎19/‎06/‎2010 6:02 AM Installation status: Failed Error details: Code 646 Update type: Important Er...

Eliminate Taskbar updates
Excel 2007 Win 7 64-bit I don't know if "Taskbar" is the proper term. I mean the bar at the bottom of the screen that displays the icons and names of all open files. I have some code that opens multiple files, one at a time, copies/pastes, and closes the files. I set ScreenUpdating so that nothing is jumping on the main screen. However, the "taskbar" is, of course, jumping around. Is there a way to freeze that bar by code? Thanks for your time. Otto Try the following; Check the code and comment out any lines that start with Application.statusbar...

format on change event
i received some helpful code that upon change of a cell in one tab, the row is moved to another tab. is it possible to preserve the formatting of the row in the move to the next tab? i would think this would be relatively simple, but cant seem to come close to getting it to happen (the row is 5 columns long, with a thin outline around each cell and wrapped text in the last column). if the above is not possible, i thought that this might work (formatting the row in the new tab after the move has taken place), but im not sure where i am going wrong. Private Sub Worksheet_...

Pivot Table Auto Update?
I'm not too familiar with VBA, so I was wondering if there's a way tha I can have a spreadsheet automatically update itself from a database. Basically, I have a database in Access, and I would like to work wit the data in Excel (as a pivot table). I would like to find a way t have Excel automatically update the pivot table when the spreadshee starts up. Is that do-able? Any examples? We'll assume that the database is data.mdb and is in th same directory as the spreadsheet. Thanks! mat -- mkaak ----------------------------------------------------------------------- mkaake'...

.NET assembly registered as COM obj (events)
Has anyone tried to use a .NET class containing events (delegates) and used them within Dexterity? In the Class Browser, I see my event methods of "add_myevent", "remove_myevent" but they take a parameter of type delegate. How do I accomplish this in Dexterity? Thanks for any help. Bryce Dooley ...

Event ID 22 after deleting old mail store
Hi all, I just emptied out one of our old mail stores and then deleted it through ESM. A while later event id 22 popped up in the event log. The exact msg is: Event Type: Warning Event Source: MSExchangeMTA Event Category: X.400 Service Event ID: 22 Date: 8/24/2006 Time: 9:29:10 PM User: N/A Computer: server Description: An error occurred while processing an association. The association will be terminated and restarted if necessary. [2007 Send CLOSE(ASB) req Pending receive close response MTA XFER-OUT 12 26] (14) Can anyone explain what this is? I havent seen any other warnings or ...

Query Tables (excel odbc) memory problem
I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes...

Portfolio
I am suddenly experiencing problems with MSN Money updating certain fund prices properly. I think (but am not sure) that the problem is that it is putting the decimal point in the wrong place (for example, £9,560.95 is being shown as £95.60 because the price is wrong by two decimal places). It does not happen with all funds, but with some. Is anyone else experiencing the same problem? Does Microsoft know about it? > I am suddenly experiencing problems with MSN Money updating certain fund prices properly. I think (but am not sure) that the problem is that it is putting the de...

Anout raise events
Hi! Here I have some text that I dont fully understand. It says. "Which of the following elements are NOT required if you want a custom class to raise an event. 1 A class that holds event data derived from System.EventArgs 2 A delegete for the event 3 A method to add an eventhandler 4 A class that raises the event. The answer is 3: You do not need a method to add an event handler that functionalitty is provided by the .NET Framework. When I first read this I thould that the meant the event hander that is acting when the event is raised but that is probable wrong. ...

money 2006 account update problem
im still having this problem and getting error 5005 codes in msn money iu tried making a new file with a new account and im getting the same thing ING direct and chevron work fine... grr capitalone dell washington mutual and orchardbank wont update worth a damn wtf It >is< frustrating -- especially when it goes on for this long -- just when you think they've got things up 'n running fairly consistently, it breaks again. Seems like it's been acting up for about a week now... Similar to when a business or service you use regularly goes on strike... jcopin@optonline....

An Online Update Problem 2005/2007
This problem has started in Money 2005 and has now moved to 2007. I have two seperate credit card accounts with Citi. One account works fine. The other "bad" account does not. All observations are from the "View Downloaded Statements" page. In 2005 when I attempt to update credit card balances from Citi the "Bank Balance" would update but none of the transactions would download. The "Local Balance" would also remain unchanged. Now ( in 2005 and 2007) the bank balance will not update on the one "bad" account. The "Last Downloade...

2004 Payroll Year End Update
Is anyone on version 6.0 and how did you handle this update? Thanks. Did you actually find a payroll update for version 6.x? I don't see one in the download area and if I recollect correctly, version 6 is not supported for payroll at all. You must be on at least version 7.0 and be running with a SQL database to get payroll updates. Lyle On Wed, 29 Dec 2004 12:51:45 -0800, "Edgar" <anonymous@discussions.microsoft.com> wrote: >Is anyone on version 6.0 and how did you handle this >update? Thanks. ...

Expression Builder query with zero amounts
I am using MS Access 2007 and I am having a problem building an expression in Expression Builder for a Control Source for a report. I have a totals page where there is an object representing a grand total of ALL invoices for gas. It has the Control Source query: =Sum([Invoice Amount]) Now, in the same section (the report footer) I have the invoice amounts broken out by 5 major buildings. Each contains an object containting the following queries (one for each of the 5) =IIf(IsError([PPTotal]),0,[PPTotal]) =IIf(IsError([InnTotal]),0,[InnTotal]) =IIf(IsError([AddTo...

Events as the DB closes
I build an application that controls the visibility of command bars while it is open. The command bars are restored to the user's previous settings when the application is closed with the Close button I provided . The problem is that some users are in the habit of closing the DB with the X in the upper right corner or with alt/F4. This obviously by-passes my code. I've tried opening and hiding a form when the DB opens and putting my command bar code in the Form_Close event to restore them when the DB closes. This seems to be an unreliable method. To improve performa...

Auto_Open update VBA
Hi all, On Auto_Open the appplication requests user to enter a serial number through an Input Box. I need the user's entry to update a Globals Public Const value so that each time the application is opened thereafter a validation of this constant is made. If entries do not mathch application closes automatically. Question is how do I update the Public Cons in the VBA Editor from the entry made by user in the Input Box. Thank you ...