Access Statements & VBA

[askrius]

I have an existing access database w/userform.

one of the controls (textbox) has its control source formatted as such:

=[table1] & Right(Year([table2]),2) & [table3]

how would i implement a similar statement in VBA, say to assign this to a string variable?

 

[dkinley]

Dim sVariable As String
 
sVariable = Me.txtNameofControl

Where txtNameofControl is the name of the control that = [table1] & Right(Year([table2]),2) & [table3].

-dK

 

[askrius]

Dim sVariable As String
 
sVariable = Me.txtNameofControl

Where txtNameofControl is the name of the control that = [table1] & Right(Year([table2]),2) & [table3].

-dK

i had tried that, but when used for example

msgbox txtnameofcontrol

i do not get the correct value (which should be a configuration string)....

 

[dkinley]

txtNameOfControl is the name of the control you was stuffing [table1] & Right(Year([table2]),2) & [table3] into. I don't know the name of the control ... substitute it in for txtNameofControl.


-dK

 

[askrius]

txtNameOfControl is the name of the control you was stuffing [table1] & Right(Year([table2]),2) & [table3] into. I don't know the name of the control ... substitute it in for txtNameofControl.


-dK

Yeah, I am aware of that.

The control, which is a textbox, does not give me an option to access the text contained therein. I get "object requried" error when trying to access the "text" property

txtmycontrol.Text

generates the error.

when referencing only the control name, as you suggested, I do not get anything. no error, but still no value. what i was wondering was if I needed to somehow issue the access expression that is contained in the Control Source for this textbox.

i am no stranger to VBA, though i am an access newbie.

 

[pono1]

Assuming table1 and table2 and table3 are text boxes located on the same form as txtTest....

   Me.txtTest.Value = Me.table1.Value & Right(Year([table2]), 2) & Me.table3.Value

Regards,
Tim

 

[askrius]

Assuming table1 and table2 and table3 are text boxes located on the same form as txtTest....

   Me.txtTest.Value = Me.table1.Value & Right(Year([table2]), 2) & Me.table3.Value

Regards,
Tim

no, they are access db tables. perhaps this is where the failing of my explanations lie...

 

[dkinley]

It's late in the day and I won't be back online until Monday. Since I am having difficulties in understanding what it is you really want, you wanted you could post a scaled back version of the database and if someone hasn't fixed for you by then - I could do it on Monday.

-dK

 

[askrius]

It's late in the day and I won't be back online until Monday. Since I am having difficulties in understanding what it is you really want, you wanted you could post a scaled back version of the database and if someone hasn't fixed for you by then - I could do it on Monday.

-dK

It would seem that the contents of the textbox is populated based on data from 3 tables. This string in the textbox's "Control Source" field:

[COLOR=black][FONT=Verdana]=[table1] & Right(Year([table2]),2) & [table3][/FONT][/COLOR]

seems to reference the required values from table1, table2 & table3.

What I was wondering (and it would seem that I did not adequately explain it): is there a function in Access I can feed that string (from the textbox's "Control Source" field) that will perform the same operation (assemble the required reference data from the tables in question)?

 

[pono1]

Hi there,

If you want to use VBA to pull data from three tables, assembling the data and displaying it in, say, one textbox, then the easiest solution might be to use the Dlookup function...roughly...

' Tie together data from 3 tables and put the result in a textbox on a form.
Me.MyTextBoxName.Value = _
DLookup(Field_Name,Table_Name,Field_Name=SomeValue) & _ 
DLookup(Field_Name,Table_Name,Field_Name=SomeValue) & _ 
DLookup(Field_Name,Table_Name,Field_Name=SomeValue)

Be sure to lookup DLookup in Access Help and here in the forum; a search in Google might also help you find examples. Please keep in mind that DLookup is particular when it comes to using strings versus numbers in the "criteria" section of the function. I would suggest you first try to get just one DLookup to work and then, once successful, move on to gettting a second, then the third, and only then would I attempt to string them together as one.

Regards,
Tim

 

[pono1]

Or you can create a query using the Access Query Grid...assuming your data is related...

Tim

 

[NigelShaw]

i had tried that, but when used for example

msgbox txtnameofcontrol

i do not get the correct value (which should be a configuration string)....

Hi,

sorry if ive missed something in the detailing, but to flash a messagebox with the code that dkindley provided, you would put-

 msgbox sVariable

otherwise you would put

msgbox me.txtnameofcontrol

this is probably the object you require


Nigel

 

[dkinley]

I don't if you have this sorted or not, but I am still confused as to why this isn't working. Here are some links that may help you out moving forward.

http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/
http://www.lebans.com/

-dK

 

[askrius]

I don't if you have this sorted or not, but I am still confused as to why this isn't working. Here are some links that may help you out moving forward.

http://www.techonthenet.com/access/index.php
http://www.sienaheights.edu/personal/csstrain/Access2007.htm
http://www.functionx.com/vbaccess/
http://www.lebans.com/

-dK

DLookup() was just what i needed! Thanks