473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Access 2003 Forms : update field via [Event Procedure]

My Form accesses a Query as follows :
Expand|Select|Wrap|Line Numbers
  1. SELECT tblA.A, tblB.B, tblB.C 
  2. FROM   tblA LEFT JOIN tblB 
  3. ON     tblA.A = tblB.A
  4. UNION 
  5. SELECT tblB.A, tblB.B, tblB.C
  6. FROM   tblB LEFT JOIN TblA 
  7. ON     tblB.A = TblA.A
  8. WHERE  tblB.A not in (select TblA.A from  TblA)
This gives me all the rows from tblA, with tblB columns B & C, when present,
and also all the rows from tblB for those rows with no matches in tblA.

In my Form, however, I would now like the ability to update tblB.C,
via a dropdown in a Combi-Box.

I know that accessing a Query for the data does not really lend itself for such an update, but is it possible to build in an [Event Procedure] for tblB.C to perform my update ?

I tried this on “On Change”, but it didn’t work :
Expand|Select|Wrap|Line Numbers
  1. Private Sub C_Change(NewData As String)
  2.     Dim strSQL As String
  3.     If NewData = "" Then Exit Sub
  4.     strSQL = "update tblB set tblB.C =  NewData;"
  5.     CurrentDb.Execute strSQL, dbFailOnError
  6.     Response = acDataErrAdded
  7. End Sub
The amended value from the dropdown was not redisplayed,
nor was tblB.C updated.

Or am I attempting the impossible ?
Many thanks
Feb 24 '10 #1
3 3353
TheSmileyCoder
2,322 Expert Mod 2GB
Expand|Select|Wrap|Line Numbers
  1. Private Sub C_Change(NewData As String)
  2. Dim strSQL As String
  3. If NewData = "" Then Exit Sub
  4. strSQL = "update tblB set tblB.C = NewData;"
  5. CurrentDb.Execute strSQL, dbFailOnError
  6. Response = acDataErrAdded
  7. End Sub
According to this, your trying to set tblB.C=NewData as a variable which the SQL engine has no chance of knowing. As you are passing a string literal, you will need to enclose it in ' such as:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "update tblB set tblB.C = '" & NewData "& ';"
Note that this would set ALL rows in tblB.C equal to NewData.
Feb 24 '10 #2
sorry for the selay in getting back to you : all is well now : thanks a bundle to 'TheSmileyOne'
May 10 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
Better late then never :)
May 10 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: edo | last post by:
Following is a summary of articles spanning a 7 day period, beginning at 12 Dec 2003 05:22:35 GMT and ending at 19 Dec 2003 04:09:06 GMT. Notes ===== - A line in the body of a post is...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
9
by: Dave Brydon | last post by:
Access 2003: I currently apply my Postal/Zip Code Format based off the Country Selected; the way I do it is by checking on the country; using the forms "On Current" event procedure, then I use...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
21
by: Madingo | last post by:
I have been using Access 2003 for about a year and I am trying to find out how to create a web test environment to try and transition some of my Access applications on to the web. My stumbling...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
13
by: mfreeman | last post by:
The minimal code (VB.NET 2003) needed to show this problem is shown below. All I do is loop through the records in the table and update them without making any changes. Out of 600 records, about...
7
by: preeny8 | last post by:
Hi there, I recently updated to Access 2007, and I'm getting a couple of errors that i'm not sure how to deal with. I worked with a lot of databases in Access 2003 (.mdb), and for some reason...
23
by: Gloops | last post by:
Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
2
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.