I have a form that users enter information into. What I want to do is
to update certain fields from another table once they have entered a
number in a field. The form is already based on a query as this allows
the user to see only the records they entered. What would be the best
approach to this?
TIA,
Cyberwolf 4 1606
I'd probably do something like (and this is just an example that you
will need to adapt to your needs) ...
dim db as database
dim rec as recordset
dim var1, var2, var3 as variant
set db = currentdb
set rec = db.openrecordse t ("OtherTable ")
var1 = me.field1
var2 = me.field2
var3 = me.field3
LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "
db.Execute LUpdate, dbFailOnError
Cyberwolf wrote:
I have a form that users enter information into. What I want to do is
to update certain fields from another table once they have entered a
number in a field. The form is already based on a query as this allows
the user to see only the records they entered. What would be the best
approach to this?
TIA,
Cyberwolf
On 4 Jan 2007 12:10:58 -0800, "ManningFan " <ma********@gma il.comwrote:
>I'd probably do something like (and this is just an example that you will need to adapt to your needs) ...
dim db as database dim rec as recordset dim var1, var2, var3 as variant
set db = currentdb set rec = db.openrecordse t ("OtherTable ")
var1 = me.field1 var2 = me.field2 var3 = me.field3
LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "
db.Execute LUpdate, dbFailOnError
There is no need for a recordset. The SQL is executed directly on the table
specified (tblMyTable) and the variables need to be concatenated within the SQL
string ie Set [field1]=var1 will not work, it needs to be SET [field1]=" & var1
etc
strSQL = "UPDATE tblMyTable SET [field1]=" & var1 & ", " _
& "[field2]=" & var2 & ", [field3]=" & var3 & ";"
CurrentDB.Execu te strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia
OK, so how does the SQl know which record to look at? ANd, is my code
below correct?
Dim var1, var2, var3 As Variant
var1 = [Current TB].[Claim Number]
var2 = [Current TB].Amount
var3 = [Current TB].Store
strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & ";"
On Jan 4, 5:05 pm, Wayne Gillespie <best...@NOhotm ailSPAM.com.au>
wrote:
On 4 Jan 2007 12:10:58 -0800, "ManningFan " <manning...@gma il.comwrote:
I'd probably do something like (and this is just an example that you
will need to adapt to your needs) ...
dim db as database
dim rec as recordset
dim var1, var2, var3 as variant
set db = currentdb
set rec = db.openrecordse t ("OtherTable ")
var1 = me.field1
var2 = me.field2
var3 = me.field3
LUpdate = "UPDATE rec" & _
LUpdate = LUpdate & " set [field1] = var1," & _
LUpdate = LUpdate & " [field2] = var2 " & _
LUpdate = LUpdate & " WHERE [field3] = var3 "
db.Execute LUpdate, dbFailOnErrorTh ere is no need for a recordset. The SQL is executed directly on the table
specified (tblMyTable) and the variables need to be concatenated within the SQL
string ie Set [field1]=var1 will not work, it needs to be SET [field1]=" & var1
etc
strSQL = "UPDATE tblMyTable SET [field1]=" & var1 & ", " _
& "[field2]=" & var2 & ", [field3]=" & var3 & ";"
CurrentDB.Execu te strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia- Hide quoted text -- Show quoted text -
On 24 Jan 2007 10:40:35 -0800, "Cyberwolf" <cy**********@g mail.comwrote:
>OK, so how does the SQl know which record to look at? ANd, is my code below correct?
Dim var1, var2, var3 As Variant
var1 = [Current TB].[Claim Number] var2 = [Current TB].Amount var3 = [Current TB].Store
strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & ";"
The sql string looks Ok as long as var1, 2 and 3 are numeric. If any of them are
text they need to be wrapped in quotes. (I use Chr(34) for clarity).
eg [Store]=" & Chr(34) & var3 & Chr(34) & ";"
To have the update performed on specific record(s) you need to add a WHERE
clause to the string to define the record(s).
Assuming you wish to update the record currently displayed in your form, you
would add something like this -
strSQL = "UPDATE Chargebacks SET [Ref#]=" & var1 & ", " _
& "[Amount]=" & var2 & ", [Store]=" & var3 & _
& "WHERE MyIDField = " & Me.MyIDControl & ";"
Wayne Gillespie
Gosford NSW Australia This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: |
last post by:
Hello,
Sorry to ask what is probably a simple answer, but I am having problems
updating a table/database from a PHP/
PHTML file. I can Read From the Table, I can Insert into Table/Database, But
not update. Here is a copy of the
script I am using. I do not know what version of MySQL my host is running
nor do I have Shell Access to it. I
|
by: Jason |
last post by:
Let's say I have an html form with 20 or 30 fields in it. The form
submits the fields via POST to a php page which updates a table in a
database with the $_POST vars. Which makes more sense?
1) simply UPDATING the values for all fields in the table, whether or
not any particular field has actually changed
2) running a second SELECT statement and comparing the $_POST vars to
the returned values, and only UPDATING those that have...
|
by: Derek Davlut |
last post by:
I have a Table that contains data that I use in a query to manipulte the
data through expressions. I have a form that uses the query for manipulating
the data. How do I write the changed values back to the table from the form?
I know it changes the query values so do I have to use an update query? I
also need this to be refreshed in realtime. I have tried relationships but I
find it adds a drop-down box to the table and is not really what I...
|
by: jaYPee |
last post by:
does anyone experienced slowness when updating a dataset using
AcceptChanges?
when calling this code it takes many seconds to update the database
SqlDataAdapter1.Update(DsStudentCourse1)
DsStudentCourse1.AcceptChanges()
i'm also wondering because w/ out AcceptChanges the data is still save
into the database and it is now faster.
|
by: Dave |
last post by:
I want to put the information that the user selects in my combo boxes
into a subform that lies on the same form as the combo boxes.
Thanks for your help already,
Dave
| |
by: Spoogledrummer |
last post by:
Hi it's me again, still working on the sam 5 minute problem so feeling kind of thick now.
I've dumped the idea of using a textarea for now and am using a textbox instead but am struggling when it comes to updating the database, I've tried several different methods and the one I'm currently battling with is as follows:
%
set objconn=server.CreateObject("ADODB.connection")
objconn.Mode=3
objconn.Open ("DSN=localserver; User ID=blah;...
|
by: hrreece |
last post by:
I have an Access 2002 database that has a form that can be used to review individual records. At the bottom of the form are buttons that are linked to functions that allow the user to "Find a record using search criteria", "Delete the current record" and so on. After a user has used the search criteria to find a specific record, I would like to use the "delete" function on the form to not only delete the record, but also update another table. ...
|
by: stepterr |
last post by:
I have a form that is built based on a query. Everything is working except when I submit the form the radio buttons are only updating the first row in my database. dcategory and dthumbnail are two radio buttons that I have for each one of the rows in the table. I've tried using a hidden input object for each of the radio button groups to store the values in but clearly I'm having a mental block because I can't get that to work right either. So...
|
by: AlexNunley |
last post by:
I've adopted a moderately sized (65k records) active use database (Access 2000, Windows XP). One of the most commonly used forms is whats called the RMA generation field, used to add claim information to a table on an item-by-item basis. the form is pretty straight forward, but is in need of updating.
In talking with some of the users its been decided to add a few fields to the form. Digging through the guts, I find commands, the cmdAddParts...
|
by: =?Utf-8?B?VmFuZXNzYQ==?= |
last post by:
Hi All!
I am with a situation where I am not getting the right updating to the
form's fields. The situation is the following one:
I have one combobox and one textbox. I am using the CurrentChanged event of
the BindingSource of the combobox to update the textbox. When selecting an
item in the combobox or when selecting a row in the grid, it is updating the
textbox correctly. The problem is when I apply a filter in the grid, and then...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |