Update Field1 From Field2

Mar 6, 2006

I’d like some assistance on the following if possible:

I have a database where I want to update a field (Agree_ID) based on the data input in a second field (Pond_ID). Agree_ID uses only a portion of Pond_ID. I’d like some routine (macro or VBA code) to assist.

Example:
Pond_ID – keyin "05-04-1P"
Agree_ID – updates to "05-04"

I used “=Mid([Pond_ID],1,5)” to update Agree_ID which appears to work. However, I have to close the record; run a macro that runs the code; then go back into the form and open the record. I’d like this to happen automatically when I keyin the data in Pond_ID.

Any ideas would be greatly appreciated.
Thanks,
SKK

View Replies


ADVERTISEMENT

If [field1] 'Yes', Set [field2] To 'Open'

Jan 20, 2006

If [field1] 'Yes', set [field2] to 'Open'. That's what I'm trying to do.

Field 1 is a combo where I choose between 'Yes' or 'No' from an outside table.
Field 2 is a textbox that I don't have showing on the form.

What code do I put into the 'AfterUpdate' of Field 1 when I change the value to either Yes or No?

I can see it all in C++ code...

{
if Field1==Yes, then Field2==1
else if Field 1==No, then Field2==0
}

But I don't know how to translate to the VBA.

View 3 Replies View Related

Tables :: Choose Number From Field1 To Automatically Fill Field2 With Appropriate Data

Jun 30, 2015

I have 2 fields in access table. In one field i fill numbers eg (1,2,3,4,5) drop down list. In the second field I need to fill another data. When I chose one of the numbers from Field 1, I want automatically fill the second field with appropriate data.

1 = 24857
2 = 24869
3 = 24899
4 = 24944
5 = 24994
6 = 24903
7 = 15480
8 = 15164
9 = 15482
10 = 15479
11 = 15468
12 = 15476
13 = 15489
14 = 15494
15 = 7524
16 = 7537
17 = 7523
18 = 7544
19 = 7533
20 = 7536
21 = 7539
22 = 7534

View 2 Replies View Related

Totals On GroupFooter From Field2 Base On Criteria Of Field3

Mar 9, 2005

Getting the total in a GroupFooter from a Field2 based on Criteria on Field3. For instance, I could total values from Field2 based on criteria “KEY” of Field3, however my problem is when I try to get the valued from Field2, which has the same value for all records based on criteria “NoKEY” of Field3.



I’m not sure if this is the best way to explain my problem, but I’ll try to give you a better scenario for you reply. I really appreciate if you help me out in anyway that lead me to the right procedure.


Here is my code for the ControlSource of my unbound for Field1



=Sum(IIf([Field3]="KEY",[Field2]))



How can I capture the value on [Field2] based on criteria [Field3]=”NoKEY”?





Thank so much in advance.

View 3 Replies View Related

Modules & VBA :: Copy Field1 To Field 2 In Table

Feb 20, 2015

I want to save the timestamp created in a table when a user opens the database. My vba saves the computer name and user name and sets the currentuser = yes. The table is mysql odbc, and has the requisite timestamp and id fields. I have a form that I can open that shows who is online at that moment. When a user closes the database, the currentuser field is set =no. All of that works well. Now, I have added a "starttime" field in which I would like to store the timestamp that was set upon opening, since when a user logs off, the timestamp gets changed to the log off time.

Code:

Dim myQry As String
Dim sUserName As String
Dim sUserComputer As String
Dim currentVersion As Date

[code]....

The UserTimestamp in red is the field value of the first record in the table, not the usertimestamp in the user's record. How do I store the log on timestamp in the "starttime" field?

View 1 Replies View Related

Auto-number Generation Based On Field1 To Generate SKU?

Nov 13, 2012

I want to input a number value in [field1], then i want [field2] to generate an auto number BUT i want it to increase based on [field1] THEN combine them into [field3] creating a unique SKU for every item I have.

EXAMPLE1: I will type in [field1]: "001234". [field2]will enter "000001" because it is the first occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00001"
EXAMPLE2: I will type in [field1]: "002468". [field2]will enter "000001" because it is the first occurrence of "002468" in [field1]. [Field3] will then have a value of "002468-00001"
EXAMPLE3: I will type in [field1]: "001234". [field2]will enter "000002" because it is the second occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00002"

View 4 Replies View Related

General :: Saving Data - Update Or Cancel Update Without AddNew Or Edit

Apr 13, 2013

My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.

The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.

Browsing the all of the existing records is fine.

View 9 Replies View Related

Can I Get An Update Query To Not Add Records To Tables Only Update?

Jun 28, 2005

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

View 9 Replies View Related

Using Criteria To Update Fields In A Update Query

Nov 29, 2006

Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers

View 3 Replies View Related

General :: Update Or Cancel Update Without Add New Or Edit

Nov 3, 2013

I get an error "update or cancel update without add new or edit" which seems to point to this code.I am using MS Access 2010.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store when record was last modified and by who.
'Initially stores when the record was entered.
If Me.NewRecord = True Then Exit Sub 'Exit if new record
Me.DateLastModified.Value = Now()
Me.LastModifiedBy.Value = getUser()
End Sub

View 2 Replies View Related

Update Queries Update Message

Mar 30, 2006

I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.

Any help would be appreciated.


Regards
Mark

View 2 Replies View Related

Update Query Won't Update In Access 97

Feb 10, 2008

I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?

Thanks in advance

Wayne

View 5 Replies View Related

Using Update Query To Update Values

Oct 3, 2007

I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.

View 1 Replies View Related

General :: Update Record ID To Another Record ID In Same Table And Update Related Records

Aug 22, 2013

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

View 6 Replies View Related

Update Query Will Not Update!

Aug 16, 2005

I have two tables, each has a "status" for a project. In the left table there is only one instance of each set, but in the right hand table, each set may be used more than once by different Projects.

I need the Status field of the left table to be set to "Assigned" if ANY of the sets assigned to projects in the right hand table are "Assinged"

Here is the update query I have setup but when it runs, it updates "0" rows.

8 is the id for the status "Assigned" in the SetStatus table
2 is the id for the status "Assigned" in the Status table.
http://img166.imageshack.us/img166/8181/temtinv00210fp.jpg (http://imageshack.us)

View 6 Replies View Related

Before Update

Sep 29, 2006

Hi,

I read on the Access help about the 'Before Update' property; however, I still don't understand what it does.

Any help will be very much appreciated.
B

View 5 Replies View Related

How To Update The Name

Oct 6, 2005

Hi:

After I made the query "T1",
I made another query "T2", but this query need query "T1" to work with.

So, when I change the query name from "T1" to "table1", I need to open the query "T2" to add the "table1" and remove the "T1" and change the table name in the query.
So, how can I update the query name? Because "T1" is same "table1' query. I just change the name, change name cause another query does't work. How can I update the query info.?

Thanks.

View 2 Replies View Related

Update

Nov 22, 2006

Hi Guys,

Two Tables:

module
workbook2



UPDATE workbook2 INNER JOIN [module] ON workbook2.ID = module.ID SET workbook2.bankimpid = module.BAImpID
WHERE ((([BASortCode])=[Sortcode]));

Any ideas why this changes 0 records when some records contain the same data in BASortCode and Sortcode??

Cheers

View 1 Replies View Related

Before Update, After Update...????

Feb 2, 2005

I can never get this right and it's frustrating.
I have a field on the form called OverallProjectStatus - it's a combo box and it has values: green, yellow, red.
If a user selects green as a value I would like BackColor of the form to turn green color.
I tried both before update and after update events on this combo box as well as on the form's before and after update events and it's not working.
Could someone please help me put this code in the right place.
If Me.OverallProjectStatus = "Green" Then
BackColor = 13434828
End If
Thanks!

View 2 Replies View Related

After Update

Feb 10, 2005

Can someone tell me if this is the correct/best way to do this.

I have a calculated field "savings". It is calculated on the AfterUpdate event of the "month" field.

I have 2 text boxes (std cost & quoted cost) that have manually entered amounts and one combo with "modules" To get the savings calculation my code looks at the month selected then the module selected in my query and sums up the volumes.

It then multiplies the volume X the std cost minus the quoted cost.

Std Cost Quoted cost Dif Volume Savings
3.00 1.5 1.5 200 $300

Anytime any of the four fields change (std cost, quoted cost, module or month) I need the Savings field to recalculate.

My first thought is that I need to put in the same code calculating the savings in the AfterUpdate event of all of these.

Is that the case? What better way is there?

Thanks!

View 1 Replies View Related

Run Update

Nov 21, 2005

I have a form that has a combo box, once a user selects the required entry it the form performs several tasks after update.

If the required entry isnt listed the user clicks on the add button which opens a form to fill in the necessary details for that entry. Once complete the user clicks on the save button and the form closes updating the combo box on the previous form. But the after update tasks do not happen.

I have tried things like....

[Forms]![Frm1].reload
[Forms]![Frm1].refresh

etc

But cant seem to get it to work. Any ideas?? Thanks

View 2 Replies View Related

Update SQL

Jan 12, 2005

Hey Guys,

I had a problem a while ago about changing the value in one field and having it update a different field in another table.

I have a table Order Details where I want the user to be able to select a quantity when ordering a particular shoe.

I then want a field named UnitsOnOrder in a table Products to update its value based on the amount ordered by the user in the quantity box in the other table.

The Order Details table is a subform not that it makes any difference.

I started receiving help by a Mr M Walts, but he hasnt been around for ages and I'm so very deperate for a fix as the code isnt working correctly and I've no idea how to fix it.

Private Sub Quantity_AfterUpdate()
'will hold the SQL which will update the Products table
Dim strSQL As String

'will hold the new total quantity after the change
Dim intChange As Integer

'see if the quantity had a previous value, might have to seperate into two
'nested if's if it gives an error on the second part of the condition
'when OldValue is null. It might
If Not IsNull(Quantity.OldValue) And (Not Quantity.OldValue = "") Then
'ok, there is a previous value, let's see which is bigger

'the change will be the new value - the old value
intChange = Quantity.Value - Quantity.OldValue

'ok, now we have the value that needs to be added to the
'quantity, if the old value was bigger then it will be negative
'which is what we want
Else
'if there was no old value, then all the really need to do is get the
'new value as the change
intChange = Quantity.Value
End If

'ok, now we create our SQL statement.
strSQL = "UPDATE Products " & _
"SET Products.UnitsOnOrder = UnitsOnOrder + " & intChange & _
"WHERE Products.ProductID = " & ProductID.Value '<- or whatever your control on the form is
'called that is bound to your productID field in
'the order details sub-form
'now we have created the SQL we need, time to run it
CurrentProject.Connection.Execute strSQL

'uncomment the next line to see the SQL that was generated
'debug.print strSQL

'that should do it for the first one, and it should give you an idea of how to carry on
End Sub


That is the code. I get an error message upon leaving the field sayin 'one of the required parameters is missing'

Any ideas?

Any help greatly appreciated. I'm way behind the rest of my I.C.T class as they have al finished their projects.

Surely somebody knows the answer

THanks in advance

Chris Tempest

View 14 Replies View Related

Stock Update

Apr 11, 2006

I have a customer table with all the usual customer details (customer ID, name, address etc...), a transaction table (Transaction ID, Order Number, Customer ID, Title ID, Date, Status (order received, payment taken, ready for dispatch etc...)), and i have a Title table (Title ID, Artist ID, Name, Record Label, Quantity, etc...)

what would be the easiest way to update the title table everytime a new transaction was added to tblTransaction so that the quantity field in tblTitle would go down by one for the right title?

View 1 Replies View Related

Combo Box Update

Jun 20, 2005

Hi All,

I have a combo box on my form that is retrieving its list of values from a table. After I choose a value I want to write it to another table's field. I can get this to work where it is writing to the other table's field, but when I open the form to see the field of where the information is supposed to be writen to it is not refreshing. What property on the combo box should I used to ensure that the field of the other table is instaneously updated when the combo box's value is chosen.

Thanks
Greg

View 3 Replies View Related

Best Way To Update Data

Jul 13, 2005

I have 2 tables:

"LookupDates" which has

ContractYear
YearStart
YearEnd

and "Staff" which has

Name
StartDate

The tables are not linked in anyway as the LookupDates table is used for referencing.

What I want to be able to do is add another column to the staff table that shows what contract year they started in.

I have tried DLookUp but I have read that you cant use update queries with DLookup in the way that I want to...

DLookup("[ContractYear]", "LookupDates", "[StartDate] Between [YearStart] And [YearEnd]

If I create a form and cycle through the records I can get the DLookup function to work via VBA but this isn't very practical

Can anyone help with this problem?

P.S. I didn't put this post in the queries forum as I think it falls under the General section.

Thanks for any help

View 2 Replies View Related

UPDATE Rows

Sep 11, 2005

Hi Im trying to update a row this is the SQL im using

UPDATE HouseEntry
SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1)
WHERE
EntryNo=40;

When I run it I get an error saying "Operation Must Be an Updatable Query"

If I physically put it in the date e.g SET [Date]=#09/15/2005# It works
so there must be a problem with my Select Statement.
Any ideas???
Cheers
Bikeboardsurf

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved