Update query in event procedure

[ppc via AccessMonster.com]

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

 

[Steve Schapel]

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.

 

[Linq Adams via AccessMonster.com]

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!

 

[Steve Schapel]

We,, what do you know? Thanks, Linq! You're never too old to learn. I
have never noticed that peculiarity before.

 

[ppc via AccessMonster.com]

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

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 )

 

[Steve Schapel]

Prasanna,

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

 

[ppc via AccessMonster.com]

Steve

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

regds

prasanna

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.

 

[ppc via AccessMonster.com]

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

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.

 

[Steve Schapel]

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

 

[ppc via AccessMonster.com]

Steve

Got it right.Thanks for your prompt help.

regds

prasanna

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