How can I get a value from a table field into a variable and viceversa using VBA code

[Schultzy]

I am studying a book in VBA language for beginners
and I can't see how I can get a value from a table field
and store it in a variable or get a value from a variable
and store it in a table field.

What code should I write?
And where should I put it?

I also would like to know clearer the following terms:

A procedure: Does this code always go into the events of a control
A module: (I'm not sure what it means)
An object: (I'm not sure what it means)

Thanks!!

[NeoPa]

I am studying a book in VBA language for beginners
and I can't see how I can get a value from a table field
and store it in a variable or get a value from a variable
and store it in a table field.

What code should I write?
And where should I put it?

I also would like to know clearer the following terms:

A procedure: Does this code always go into the events of a control
A module: (I'm not sure what it means)
An object: (I'm not sure what it means)

Thanks!!

Referring to a field in a table (or query for that matter) can only be done when you've openned the dataset (table; query) in code and have a current record.
At that point, your variable (rstThis or such like) that contains the RecordSet holds it (rstThis!FieldName or rstThis.Fields("FieldName")).
To assign the value to a variable (varThis) just say
varThis = rstThis!FieldName
A procedure is a self-contained set of code. It will generally be a method (event procedure), a function (which must return a value to the calling code or a subroutine which just runs some code.
A module is something in which you store code. Amongst other things it can contain procedures.
An object is a defined set of code and variables which, together, make up a consistent whole. Some examples of objects are Access Forms and Reports. Typically an object will have associated variables (properties) and procedures (methods).

[Schultzy]

I just understand it slightly!

Exactly, what steps should I take?
What must I do to open a dataset in code?
How can I set a record as the current record?

When you say 'your variable that contains the RecorSet holds it'
( what do you mean?)
How is this variable assigned?
What is exactly a RecordSet?

I am sorry I have so many questions.
Maybe what I want to do could be done in a different way, but
I had this idea and I am quite sure it would work if only I knew
how to do it.

I will give a brief explanation of the problem:
Let's say I have to tables:
Table1 and Table2
Table1 has a field called CurrentTax
Table2 has a field called Tax
By means of a form I set the Table2 fields.
I must get the CurrentTax value and put it in Tax
(I know that fields cannot be doubled)
But CurrentTax can change as time goes by
but the Tax field must remain with the same value

Is there another way of doing this?
I'm not sure wether this is clear enough

Thanks in advance!
Bye

[PEB]

Try this manner: using always VBA event procedure /CurrentTax GotFocus for exemple:
Paste this one there in conformity with your names that u use:

Me![Currenttax]=Dlookup("Tax","Table1","")

A bit more easy :)

:)

[Schultzy]

I am very new at this and it is difficult to see things clear!

I think that I need it the other way around.
Get the value in CurrentTax and put it in Tax.

Would it be similar?
Thanks!

[PEB]

In fact the function

Dlookup("Tax","Table1","") gets the value from table1 from column Tax

and gives it to the field Me![Currenttax] in your form.

If you want the inverse, only inverse the names...

So you have the method to catch a value from a table it's your turn!
:)

[Schultzy]

I think it is working!!
I have written this in the events of a control
I have used two fields in the same table to do some checking

Private Sub Inp_GotFocus()
Me![Field1] = DLookup("Field2", "Table", "")
End Sub
 

What does Me! mean? Is it the current table or form?
If Field1 and Field 2 were in different tables,
how should I refer them?

I think that now I can work a little with this until
it is completely tuned.
Thanks very much!
Bye!

[PEB]

I think it is working!!
I have written this in the events of a control
I have used two fields in the same table to do some checking

Private Sub Inp_GotFocus()
Me![Field1] = DLookup("Field2", "Table", "")
End Sub
 

What does Me! mean? Is it the current table or form?
If Field1 and Field 2 were in different tables,
how should I refer them?

I think that now I can work a little with this until
it is completely tuned.
Thanks very much!
Bye!

Me refers to the current active Form...

This never turns to a table!

Me replaces Forms![MyformName]
or Reports![MyreportName]
Instaed to write
Forms![Lovers on the beach]![FirstName]="Pete"
u write
Me![FirstName]="Pete"

U see the difference?

[NeoPa]

Schultzy,

In answer to your earlier post (#3) I don't think I can provide an Access course via a web page.
I'm happy to answer questions but 'Please tell me everything I need to know about Access?' is a bit involved for here ;).
You also say :

I must get the CurrentTax value and put it in Tax
(I know that fields cannot be doubled)
But CurrentTax can change as time goes by
but the Tax field must remain with the same value

I don't understand how Tax CAN 'remain with the same value' after you've updated it with the CurrentTax.
You can't have one variable, the other static AND them both the same.

Normally, you would use something like an UPDATE query to update a single value in a recordset.
I'm not sure if this helps as I really don't follow where you're going, but if it does help then good.

[Schultzy]

Me refers to the current active Form...

This never turns to a table!

Me replaces Forms![MyformName]
or Reports![MyreportName]
Instaed to write
Forms![Lovers on the beach]![FirstName]="Pete"
u write
Me![FirstName]="Pete"

U see the difference?

I think it works perfectly right!
The DLookup function is what I needed
I am doing some checking and I have a question:

The function has three arguments
The field
The table
and the criteria
Can I use a variable in the criteria argument?
Something like this:
Me![field] = DLookup("field","table","field = variable")
Is this possible?
I'm trying and it gives me the error 2001
Thanks for everything!

[NeoPa]

I think it works perfectly right!
The DLookup function is what I needed
I am doing some checking and I have a question:

The function has three arguments
The field
The table
and the criteria
Can I use a variable in the criteria argument?
Something like this:
Me![field] = DLookup("field","table","field = variable")
Is this possible?
I'm trying and it gives me the error 2001
Thanks for everything!

Firstly, DLookup() (and other Domain Aggregate functions) is fine for the basic logic, but can be a killer for performance with large datasets. If yours is small, don't worry about it, but if you want to support potentially large datasets then you need to be careful with its (their) use.
Secondly, you're nearly right with your DLookup() with a variable. It is possible, sort of.
You need to create the string parameter including the value from the variable. Use string manipulation (concatenation). Here's an example :

Me.[field] = DLookup("Field","Table","Field = " & Variable)

But bear in mind the rules of variable constants :
Numerics = as is
Strings = surrounded by (')
Dates = formatted as m/d/y and surrounded by (#).

[Schultzy]

It works fine!!!!
Everything is solved....for the moment!

But there's something I have to ask:
This is the code I finally have written and it does what I wanted

Me![InvoiceTax] = DLookup("ProductTax", "Products", "")
 

Is it possible to write in the procedure:

Me![InvoiceTax] = [ProductTax]

The tables are related so:
Would this line get the ProductTax value and put it in InvoiceTax?
I've checked it and it seems to work but I need
to be sure there will be no drawback!

Thanks everybody for everything up to now!
Bye

[NeoPa]

If your form contains a TextBox [ProductTax] or (I think) if the dataset that your form is bound to contains a field with that name, then you should be ok.
It would always reflect the current record of course, but that's probably what you want anyway.

[Schultzy]

I am obliged!
It is SOLVED!
THANKS EVERYBODY!