Using Table Value in a Form

  • Thread starter Thread starter punter
  • Start date Start date
P

punter

This has to be easy, but I think I am making hard work of this...

I have Access 2007
I have a table that has 1 field and 1 record (therefore the table
includes only one value - which is a numeral)
I have a form that has:
* 1 x label
* 2 x option buttons.

By selecting one of the option buttons, the result should be:
* Option One will add the letter "B" to the front of the number
located in the table. This concatenated value will be displayed in the
label. (eg: B1000)
* Option Two will add the letter "U" to the front of the number
located in the table. This concatenated value will be displayed in the
label. (eg: U1000)

My question is: How do I successfully extract the value from the table
to be used in my IF statement dependant on the option button I choose?

I know how to open databases and recordsets using VBA & SQL. I know
how to delete and add records using VBA & SQL ... but how do I turn a
record value into a variable in which I can use for whatever I like?

Many Thanks in advance ...

Punter.
 
Use DLookup(). Set the ControlSource of a text box to:
=DLookup("YourFieldNameHere", "YourTableNameHere")

If we assume that:
- the 2 option buttons are in an option group
- the first option button has the Option Value 1
- the Default Value of the group is 1
- the option group name is Frame23
then you could set the Control Source like this:
= IIf([Frame23]=1, "B", "U") & DLookup("SomeField", "SomeTable")
 
Allen ..

it worked perfectly ... thanks very much. I knew I was making it
harder than it looked!!

As a matter of interest, IF the table in which I was retrieving this
data had multiple fields and records (hence, multiple values), how
would this be handled? Would I have to isolate the data I need via a
query, then feed of the query?

Thanks once again ...


Punter


Use DLookup(). Set the ControlSource of a text box to:
    =DLookup("YourFieldNameHere", "YourTableNameHere")

If we assume that:
- the 2 option buttons are in an option group
- the first option button has the Option Value 1
- the Default Value of the group is 1
- the option group name is Frame23
then you could set the Control Source like this:
    = IIf([Frame23]=1, "B", "U") & DLookup("SomeField", "SomeTable")

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




This has to be easy, but I think I am making hard work of this...
Click to expand...
I have Access 2007
I have a table that has 1 field and 1 record (therefore the table
includes only one value - which is a numeral)
I have a form that has:
* 1 x label
* 2 x option buttons.
Click to expand...
By selecting one of the option buttons, the result should be:
* Option One will add the letter "B" to the front of the number
located in the table. This concatenated value will be displayed in the
label. (eg: B1000)
* Option Two will add the letter "U" to the front of the number
located in the table. This concatenated value will be displayed in the
label. (eg: U1000)
Click to expand...
My question is: How do I successfully extract the value from the table
to be used in my IF statement dependant on the option button I choose?
Click to expand...
I know how to open databases and recordsets using VBA & SQL. I know
how to delete and add records using VBA & SQL ... but how do I turn a
record value into a variable in which I can use for whatever I like?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
This might help you set up the criteria correctly to get the value you need:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen ..

it worked perfectly ... thanks very much. I knew I was making it
harder than it looked!!

As a matter of interest, IF the table in which I was retrieving this
data had multiple fields and records (hence, multiple values), how
would this be handled? Would I have to isolate the data I need via a
query, then feed of the query?
 
Thanks again Allen ..

Now you have taught me something I've decided to try a few scenarios -
testing the boundaries, and have come stuck at the following:

I created a table called Vendors. This table has two fields: venName
and venAddress
I have created a combo box (cbx_Vendor) in a form with the venName
field being used in the dropdown area.
I have a text box underneath that I am hoping will display the
venAddress value matching the chosen venName.

In the venAddress textbox I am hoping to create the following DLOOKUP
function:

=DLookup("venAddress", "Vendors", "venName=" & cbx_Vendor)

I've tried many variations based on Googling DLOOKUP. I've seen many
variations as to where the " finishes, I've used Forms![myForm]!
cbx_Vendor, etc.
My most successful result is #Error.

Do you or anybody else any ideas on how this best works? I am
convinced that I am a small syntax error short of the answer, however,
possibly DLOOKUP doesn't work in conjunction with combo boxes??

You have only yourself to blame!!!! :)

Thanks again ...

Punter :)
 
So you are looking in the venAddress field of the Vendors table, where the
venName matches the combo?

Suggestions:

1. What kind of field in venName in the Vendors table?
If it is a Text field (not a Number field), you need extra quotes:
=DLookup("venAddress", "Vendors", "venName=""" & cbx_Vendor & """")

If it is a Number field, the criteria would be malformed if it is null, so
try:
=DLookup("venAddress", "Vendors", "venName=" & Nz(cbx_Vendor,0))

2. Look at the combo's properties: the RowSource and the BoundColumn. The
RowSource can give you several columns. The one that is the BoundColumn --
does it match the data type of the venName field?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thanks again Allen ..

Now you have taught me something I've decided to try a few scenarios -
testing the boundaries, and have come stuck at the following:

I created a table called Vendors. This table has two fields: venName
and venAddress
I have created a combo box (cbx_Vendor) in a form with the venName
field being used in the dropdown area.
I have a text box underneath that I am hoping will display the
venAddress value matching the chosen venName.

In the venAddress textbox I am hoping to create the following DLOOKUP
function:

=DLookup("venAddress", "Vendors", "venName=" & cbx_Vendor)

I've tried many variations based on Googling DLOOKUP. I've seen many
variations as to where the " finishes, I've used Forms![myForm]!
cbx_Vendor, etc.
My most successful result is #Error.

Do you or anybody else any ideas on how this best works? I am
convinced that I am a small syntax error short of the answer, however,
possibly DLOOKUP doesn't work in conjunction with combo boxes??

You have only yourself to blame!!!! :)
 
Back
Top