Copy from "field2" to new record "field1"

[ryno du preez]

Hello Let me start of to says I'm a complete newbie when It comes to VBA, But in the problem facing me I can't get around it in Access 2010.Please Help.

In my "Table1" there is two fields "Open" and "Close",
The data that is in the "Close" field must automaticly be copied to the "Open" field on a new Record(all in the same Table) The tables are linked via the same ID key

Id | Open | Close
1 | 100 | 150
1 | 150 | 275
1 | 275 |

I've tried to look at the code of the build in function in access to duplicate a record but know luck.
PLEASE HELP .....

[Rabbit]

You can use the Max(Close) to populate the new Open.

[ryno du preez]

Thank you for your reply, I've tryed to use it in a query but keeps on giving me errors,the query I tryed to use was a update query. please bear in mind my VBA is very weak and close to none exsisting.

[Jerry Maiapu]

First of all. Do you, want to insert values into open field from close field in a one-go process by say clicking a button or is it going to be an ongoing event ie every time you enter a value in close field you want a copy to be placed in the open field?

I think the simplest way to do this is to use append query to get values from close and add/insert into open column.

INSERT INTO table1 ( [Open] )
SELECT Close
FROM table1;

I'm just wondering why on earth you could want to duplicate field values. You could use query to filter and retreive records for reporting and viewing from one column. Only if you expalin what you're trying to achieve, we can't solve the real mysery.

[ryno du preez]

Jerry thank you for the reply. The reason I want to do this is to try and cancel out Human error .Because there is calculations being done between the open and close fieldies ([close]-[open]).
The last [close] entry is also automatically the next [open] value in a new record. and then waits for the user to enter the next [close] value to do the calculations.I' m adding the db in this post, the table in question is "TBrandstof " and the open [OpenOdometerL] and the close is [ClsOdometer], Your query runs correctly but I only want to update the last entry in close to the new records open

[ryno du preez]

The input form is Feul from the main menu or FBrandstof
Thanks again for your help.

[Jerry Maiapu]

I am using Ms access 2007. For some reason I cannot open your table make sure your mdb file is compitable with ms access 2007

[nbiswas]

I have created a table as under

Field   DataType
------  --------
RowID (AutoIncrement)
ID     Number
Open   Number
Close  Number

Based on this , the below query should work

Insert into Table1(ID,Open)
Select  Table1.[ID], Table1.[Close]
FROM Table1
where  Table1.[RowID] =  ( Select Count(*) from Table1);

[NeoPa]

When you open the form set the .DefaultValue property of the txtOpen control to the maximum value found in the table so far. You can use :

Me.txtOpen.DefaultValue = Nz(DMax("[Close]","[Table1]"), 0)

You should also update this whenever you add a new record into the table. You can do this in the form's AfterUpdate event procedure :

Private Sub Form_AfterUpdate()
    If Me.NewRecord Then Me.txtOpen.DefaultValue = .txtClose
End Sub

[ryno du preez]

Here is a 2007 version ............... ............... ............... ......

[ryno du preez]

It works great thanks "nbiswas" only one little bug still to kill, The [ID] is a linked [ID] from another table "[REGNO]", If I run the query on the table only it updates perfectly but as soon as I try to run it from the joint form it doesn't update the Table content.
Do you have any ideas on how to fix the problem?
Thank you every one for each reply so far.

[Jerry Maiapu]

I haven’t actually looked at your db but a SQL need to know on what date the close field value was closed. In that way we could kindly ask the SQL to sort the values by the dates in the close field in an Ascending order, and simply pick the recent closed value from the list. So obviously, the last value in the list will be the most recent/current record value in the closed field. So I am presuming you have a field called ClosedDate or something in the same table.
Try this:

INSERT INTO TBrandstof ([OpenOdometerL])
SELECT Last([ClsOdometer] AS [lastvalue]
FROM TBrandstof
ORDER BY Last(ClosedDate);

Save the query as insertclosedval ues

You can manually run the above query or if you wish to automate using cmd button, then create a button on the form and use its on_click event to execute the above query.

DoCmd.SetWarnings False ‘Removes the default warning message of the query
DoCmd.OpenQuery " insertclosedvalues ", acViewNormal

[Jerry Maiapu]

For the linkage to work you should have two keys ( PK and FK) The FK field is the one need to be updated. so:

INSERT INTO TBrandstof ([OpenOdometerL,FK)
SELECT Last([ClsOdometer]) AS [lastvalue], Last([FK]) AS LFK
FROM TBrandstof
ORDER BY Last(ClosedDate);

Replace FK with your FK field name.

PK= Primary Key
FK= Foreign key (one coming from the other table)