Duplicating a value from one field to another

[louisa]

Hi All,

I have a field called TotalKitCost, and on another form called "Equipment" i have an unbound text box with a control source of =SUM([TotalCost]) that text box totals all the amounts and displays the information in the form footer. Instead of manually entering that information in another field i would like it to take that data and display in my field TotalKitCost. Usually i would set the source as =Forms!NameOfMainTable!NameOfField however as this is an unbound text box and the data is not saved in a table i am unsure how to set the field to duplicate the data. Any ideas greatly appreciated.

 

[KenHigg]

My first suggestion is that you shouldn't save this value but rather calculate it on the fly whenever you need it.

 

[Trevor G]

Hi All,

I have a field called TotalKitCost, and on another form called "Equipment" i have an unbound text box with a control source of =SUM([TotalCost]) that text box totals all the amounts and displays the information in the form footer. Instead of manually entering that information in another field i would like it to take that data and display in my field TotalKitCost. Usually i would set the source as =Forms!NameOfMainTable!NameOfField however as this is an unbound text box and the data is not saved in a table i am unsure how to set the field to duplicate the data. Any ideas greatly appreciated.

You could use something like this:

Private Sub Command7_Click()
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdCopy
Docmd.OpenForm "The form name goes here"
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdPaste

End Sub

 

[louisa]

Thanks for coming back to me, where do i need to place the below, i dont work with code often.

Private Sub Command7_Click()
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdCopy
Docmd.OpenForm "The form name goes here"
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdPaste

End Sub ___________

 

[Trevor G]

Thanks for coming back to me, where do i need to place the below, i dont work with code often.

Private Sub Command7_Click()
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdCopy
Docmd.OpenForm "The form name goes here"
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdPaste

End Sub ___________

You would need to have a command button on your first form, when you click the button you want it to goto the field which has the total in it then copy the contents, then move onto the other form and goto the field and past the value in, so change the highlighted colours to the field names and form name

Private Sub Command7_Click()
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdCopy
Docmd.OpenForm "The form name goes here"
DoCmd.GoToControl "Project ID"
DoCmd.RunCommand acCmdPaste

End Sub

 

[louisa]

Oh i see, is there no way of it automatically adding the data, having to copy and paste it kind of defiates the object. I was just hoping to set it up that the total value would be stored in another field.

 

[Trevor G]

Ok rather than put the code behind a button, put it behind a field that once you completed it you can use the after update event.

 

[louisa]

ok do i put the code behind the field i want the value to go to? or the field that already has the total formula behind it?

 

[Trevor G]

Into the field which has the total in it. you may have to play with the correct event, either before update or after update.

 

[louisa]

Where it states Project I.D is that the name of the field i want the information to go to?
If so i have tried using the code provided on before and after update but neither have worked, i also left the """" in not sure if i was supposed to. I have no understanding of code so apologies if i seem clueless.

 

[Trevor G]

Can you see what size your database is and then if zipped it is less than the permitted size upload it to the forum, I will then look to sort it and give you some instructions.

It will be easier than a lot of additional replies and questions.

If it is to big, make a copy of the database then strip away some of the records.

Also please tell me the form names and field names.

 

[louisa]

the problem is my database has been split, am i still able to send it?

 

[Trevor G]

Not really as FE to BE wont function properly.

 

[louisa]

oh not really alot i can do about it then :-(

 

[Trevor G]

Don't give up just yet,

Do you have the skills to copy a table and form into another table ! If so then you can still upload a copy. If not then we can try another method tomorrow.

We can sort this out..

 

[louisa]

I have no idea if i can do that, i will give it a go and see how i get on, thanks for all your help so far.

 

[louisa]

Unfortunately i am unable to copy the table and form, any other ideas?

 

[louisa]

I have attached a dummy DB with a basic view of my current set up. The form Equipment has a Total field (in the footer) which calculates the totals. The Total Kit Cost form just has one field and i would like it to take the Total from the Equipment form and place in the the Total Kit Cost field.
Thats all i want hehe ;-)

 

[Trevor G]

Louisa,

I have looked at your database and have placed an event behind the form for when it ON LOAD EVENT and also AFTER UPDATE EVENT. The code you need to copy to your form is as follows:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("KitCost", dbOpenTable)
Do Until rst.EOF
rst.MoveFirst
If rst.Fields("TotalKitCost").Value > 0 Then
esle: rst.Edit
rst.Fields("TotalKitCost").Value = Me.Text8.Value
rst.Update
End If
rst.MoveNext
Loop

This code needs to go into the 2 events, you can copy and paste this code. I have basically looked at your table and told it to update the record in the table that way when you open the second form it will show you your total sum.

What do you need to check for !

rst.Fields("TotalKitCost").Value = Me.Text8.Value

The line above shown in red is the textbox name you will have to change that to your real total text box name.

Other than that it should work for you.

 

[louisa]

Thanks for that Trevor, the only one query i have is where you state

"What do you need to check for !
rst.Fields("TotalKitCost").Value = Me.Text8.Value"

My text box (as i am aware) doesnt have a name as it is an unbound text box.
Apologies if i am clueless but i am self taught so far.