replace data through a from

[SheilaAlighieri]

Hi,

I placed a similar message before in another part of the forum. But I think my previous idea was wrong, so here I try again I am using Access Y2K, but in the office they have Access 97. If anyone can present me a solution which work onboth, that would be super!

I would like to transfer a value from field "A" to field "B" by use of a text box. In other words, the value that is entered in the textbox should be replaced from "A" to "B". Ofcourse the action has to relate to one specific record. Therefore I would like to add some criteria's to the action: "Name" and "Project Number".

So for instance:

Name: "X"
Project Number: "2"
Value to transfer from "A" to "B": 100

Should do the following:
For the records relating to Name X with Project Number 2 a value corresponding to 100 should be replaced from "A" to "B".

I hope my explantion is clear like this If anyone could help me out a bit, that would be great. This is the last problem for me to solve before I can completely finish my database.

Sheila

 

[JeremyNYC]

Sheila,

I just read this twice and I can't understand what you're doing. I don't get what's being transfered or replaced. Are a and b fields in different tables? Are they different tables? If you describe the table(s) and fields involved, and what the data look like at the start and what they should look like at the finish, we'll have a better shot at getting you to go.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[SheilaAlighieri]

Hi Jeremy,

A & B are indeed fields. They come from one and the same table named budget. I made a form linked to this table through which I would like to enable the user to replace data from field a to b.

The other fields named in my example; Name and Project Number are also fields from the table Budget. I attached these so the user can specify for which project they are replacing the data from a to b.

I made a textbox on my form in which the user can enter a value. I would like this value to be replaced from A to B. The textboxname is txtReplace. Furthermore there are textboxes for the name (txtName) and for the Projectnumber (txtProjectNumber) and a actionbutton (butReplace).

I know I must be terribly unclear But I will describe the situation before and after the action.

Before:
Lets say this is the data stored in my table Budget:

Project Number: 1
Name: Sheila
A: 100
B: 0

Action:
Now I give the following command through my form:

Project Number: 1
Name: Sheila
Amount to transfer from A to B: 100

Then my button (butReplace) for the action to take place. However, I do not know what kind of code I have to attach to my button.

After:
Anyway, the result in my table should be as following:

Project Number: 1
Name: Sheila
A: 0
B: 100

Thanks for being so patient with me I hope this time I am more clear.

Greetings from Roma!

Sheila

 

[JeremyNYC]

Sheila,

The confusing part is the "replace from a to b" bit. Do you mean "move" from a to b? Do you mean to swap the values of the two fields?

If you want to trade or swap values, you could do something like this:
dim intA as integer
intA = me!A
me!A = me!B
me!B = intA

Does that do what you need?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[SheilaAlighieri]

Well it comes close. The value of A doesn't necessarily needs to b replaced in whole. I made a textbox on my form in which the user can enter a value. I would like this value to be replaced from A to B. The textboxname is txtReplace.

For example:

A=100
b=0

move from A to B (which is the text next to my textbox "txtReplace": 25

(button click)

gets

A=75
b=25

thanks for helping

Greetings from Rome,
Sheila

 

[JeremyNYC]

Ah. That's easy.

if me!txtReplace <= me!txtA then
Me!A = me!A - me!txtReplace
me!B = me!B + me!txtReplace
else
call msgbox(&quot;Please choose a value less than A.&quot
end if

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[SheilaAlighieri]

Thanks It works!
Only one small question. Is there a way to clear the field txtReplace after the action has been completed? And you have to press enter before the action works.. but then my form jumps to the next record. Is there some easy way to get round this?

 

[SheilaAlighieri]

Oh, now I am really getting emberassed. I want to edit the code a litte. But I am screwing it up

I want to ask the user a confirmation in a message box. I made the following... but naturally it doesn't work.
I am such a newbie Can you point out my error?


Call MsgBox(&quot;Replace (value entered in txtReplace)?&quot;, vbOKCancel)

If Me!txtReplace <= Me!txtBudget Then
Me!txtBudget = Me!txtBudget - Me!txtReplace
Me!txtPlanned = Me!txtPlanned + Me!txtReplace
Call MsgBox(&quot;(value entered in txtReplace) was replaced.&quot

Else
Call MsgBox(&quot;Please choose a value less or equal to A.&quot
End If

 

[JeremyNYC]

Sheila,

Look into the help files to find out about how to refer to controls on your forms.

For your confirmation, do something like:
if vbyes = msgbox(&quot;yadda&quot then

To set a control to nothing, do
me!ControlName = null

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.