By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

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

P: 23
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!!
Nov 27 '06 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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).
Nov 27 '06 #2

P: 23
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
Nov 27 '06 #3

PEB
Expert 100+
P: 1,418
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 :)

:)
Nov 27 '06 #4

P: 23
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!
Nov 27 '06 #5

PEB
Expert 100+
P: 1,418
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!
:)
Nov 27 '06 #6

P: 23
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Inp_GotFocus()
  2. Me![Field1] = DLookup("Field2", "Table", "")
  3. End Sub
  4.  
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!
Nov 27 '06 #7

PEB
Expert 100+
P: 1,418
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Inp_GotFocus()
  2. Me![Field1] = DLookup("Field2", "Table", "")
  3. End Sub
  4.  
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?
Nov 27 '06 #8

NeoPa
Expert Mod 15k+
P: 31,660
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.
Nov 27 '06 #9

P: 23
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!
Nov 28 '06 #10

NeoPa
Expert Mod 15k+
P: 31,660
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 :
Expand|Select|Wrap|Line Numbers
  1. 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 (#).
Nov 28 '06 #11

P: 23
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

Expand|Select|Wrap|Line Numbers
  1. Me![InvoiceTax] = DLookup("ProductTax", "Products", "")
  2.  
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
Nov 29 '06 #12

NeoPa
Expert Mod 15k+
P: 31,660
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.
Nov 30 '06 #13

P: 23
I am obliged!
It is SOLVED!
THANKS EVERYBODY!
Dec 1 '06 #14

Post your reply

Sign in to post your reply or Sign up for a free account.