More Combo hell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have managed to sort out a problem I had with data not automatically
updating. The problem I now have is. The information which gets inserted
into a text box after selection from a Combo box is a Currency. It is shown
as a currency in the Combo Box drop down menu, but does not show as a
currency when it is inserted into the text box. I have selected Currency as
Format in every box I can think of, but to no avail. Do I need to insert it
as code or something. I used the following code to insert data into the
required text box [ComboboxName].[Column](2)

Can anyone help
Steve
 
Remember, you have two choices:


place a unbound text box on the screen, and then use expression of

=([ComboboxName].[Column](2) )

The above does NOT save the data into a field, but is only an expression. If
you want to save the data into a field, then you must REMOVE the above
expression, and choose a field name from the underlying table that is bound
to the form.

If you wish to save the value, then you have to go back to the using the
after update event of the combo box...

example:

Me.RDescription = Me.cboOption.Column(3)

So, don't confuse using a expression in a text box on a form with that of
setting the value in code. An expression is NEVER saved into the underlying
field (by the way, if you don't need that data to actually be saved into the
table, then you can continue to use the expression, but it does not save the
data, nor set the value. If you want a control on the screen to save data,
then the SOURCE MUST NOT BE A EXPRESSION, BUT A field name!!!

Also, if you do use a expression, MAKE SURE THE TEXT BOX is NOT the name of
a existing field in the table, else, ms-access will get confused as to what
value you are tying to use (the name of the text box, or the name of the
field). In *most* cases, the name of a text box on the form is the SAME as
the underlying field that the text box is attached to. However, if you use a
expression, or have a un-bound text box with the SAME name as an EXISTING
field, then ms-access behaves very weird.....
 
The data type set in every box relating to UnitPrice is currency, but for
some reason it wont display as currency, only as a number. Is this something
to with what Albert has said.

Steve

Tom Wickerath said:
Hi Steve,

I assume you are using a bound text box, correct? (In other words, the text
box has a field specified as the control source). What is the data type, set
in table design view, for this field? Do you have any special formats applied
in table design view for this field?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Steve Moss said:
I have managed to sort out a problem I had with data not automatically
updating. The problem I now have is. The information which gets inserted
into a text box after selection from a Combo box is a Currency. It is shown
as a currency in the Combo Box drop down menu, but does not show as a
currency when it is inserted into the text box. I have selected Currency as
Format in every box I can think of, but to no avail. Do I need to insert it
as code or something. I used the following code to insert data into the
required text box [ComboboxName].[Column](2)

Can anyone help
Steve
Click to expand...
Click to expand...
 
Steve Moss said:
The data type set in every box relating to UnitPrice is currency, but for
some reason it wont display as currency, only as a number. Is this
something
to with what Albert has said.

Steve
Click to expand...

Well, it might be. You have to answer if the text box in question is simply
a text box with a expression (un-bound), or the text box is bound to
underlying table attached to the form. Further, if it is only an
expression, then you need to MAKE SURE that the text box name does conflict
with any of the field names in the underlying table attached to the form.
 
Hi Steve,
Is this something to with what Albert has said.
Click to expand...
I don't believe so. Albert's advice deals with whether or not you need to
store the price information for historical purposes. For example, take a look
at the Northwind sample database. Open Tools > Relationships. Notice that
there are two UnitPrice fields, one in the Products table and one in the
Order Details table. The reason the UnitPrice is being stored in the Order
Details table is that this allows us to later increase the price for records
in the Products table, without artificially inflating the sales totals on
reports that would be printed after the price increase.

In the same Northwind database, open the Orders form. How does the UnitPrice
value display for you in the subform?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
The text box is bound to a field in the table. I was trying to store the
data in this field, but it does not. But it was the only way I could get the
data to automatically insert into the field. If I used the code

Private Sub ProductName_AfterUpdate()

Me!UnitPrice = ProductName.Column(2)

end sub

This didnt work at all. the only way it worked is by using
[ComboboxName].[Column](2). I may be over complicating things and confusing
myself. I am new to access and just learning.

Many many thanks for your help in this matter
 
The UnitPrice is displayed as a number, even though I have currency set.
 
I think I need to explain what I have got at the moment.

I have a Product Info (Table and Form) with ProductID, ProductName and
UnitPrice. This is where my Products are populated.
I also have Invoice Product Info (Table) with InvoiceProductID, Invoice ID,
ProductID, ProductName, UnitPrice and QTY. This is the table I want
UnitPrice to be stored when I select the corresponding ProductName from the
ProductName Combo box.
I also have Invoice Product Info Subform with ProductName, UnitPrice, QTY
and Subtotal. This is where I select the ProductName from a combobox. The
UnitPrice is automatically updated with the Unitprice from using
corresponding data to ProductName.
I couldnt seem to get the Code for After_Update to update the UnitPrice
field to work. So I used the [ComboboxName].[Column](2). This did work.
But instead of displaying the UnitPrice as a Currency (which it is displayed
as in the Combobox dropdown menu) it displays it as a number.
Hope that helps
Steve
 
Sometimes--actually, most of the time--it is much easier to diagnose a
problem when one can look at the actual database. If you are willing to send
me a compacted and zipped copy of your database, I will take a look at it for
you. If any data is sensitive, you can sanitize the data.

I won't have time to look at it until late tomorrow evening, as I am giving
a presentation to our local Access group. But, I will look at it either when
I get home, or on Wednesday evening at the latest.

If you are interested, send me a private e-mail message with your database.
My e-mail address is available at the bottom of the contributor's page
indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.

Gotta call it a night now.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
I couldnt seem to get the Code for After_Update to update the UnitPrice
field to work. So I used the [ComboboxName].[Column](2). This did work.
Click to expand...

*where* did you use [ComboboxName].[Column](2) ????

In code, or a expression? And, if you used a expression for the data source
of the text box, then that text box is UN-BOUND!!!!!!!!!!!!!

So, lets check this again:

Lets use the after update event, and get this working that way....



What is the sql for the combo box...please post it.....


Now, what is the after update event code for the combo box?

and, make sure the text box does not have the above expression, but only the
field name of the table (unitprice).
Also, what is the name of the text box? (hint: the name *can* be different
from the field name).
But instead of displaying the UnitPrice as a Currency (which it is
displayed
as in the Combobox dropdown menu) it displays it as a number.
Click to expand...

Right, because it is expression, and can't take on the field type of the
table since the text box is un-bound.....
 
If I use [ComboboxName].[Column](2), I insert it into the Control Source.
The only code I can find for the ProductName comboBox is the After_Update
code, which is
Private Sub ProductName After_Update( )
Me. UnitPrice = ProductName.Column (2)
UnitPrice textbox does not have any expressions
Albert D. Kallal said:
I couldnt seem to get the Code for After_Update to update the UnitPrice
field to work. So I used the [ComboboxName].[Column](2). This did work.
Click to expand...

*where* did you use [ComboboxName].[Column](2) ????

In code, or a expression? And, if you used a expression for the data source
of the text box, then that text box is UN-BOUND!!!!!!!!!!!!!

So, lets check this again:

Lets use the after update event, and get this working that way....



What is the sql for the combo box...please post it.....


Now, what is the after update event code for the combo box?

and, make sure the text box does not have the above expression, but only the
field name of the table (unitprice).
Also, what is the name of the text box? (hint: the name *can* be different
from the field name).
But instead of displaying the UnitPrice as a Currency (which it is
displayed
as in the Combobox dropdown menu) it displays it as a number.
Click to expand...

Right, because it is expression, and can't take on the field type of the
table since the text box is un-bound.....
Click to expand...
 
Steve Moss said:
If I use [ComboboxName].[Column](2), I insert it into the Control Source.
The only code I can find for the ProductName comboBox is the After_Update
code, which is
Click to expand...
Private Sub ProductName After_Update( )
Me. UnitPrice = ProductName.Column (2)
Click to expand...

I notice a extra space in the above...(right after the me. -- this not
correct). You would do well to use cut and paste from your actual code...

also, after you type in the code, while looking at the code, do a
debug->compile from the menu (you should do this each time).

does your code compile?
 
Sorry about that. Its a typo. I am running two machines. This one is
military, which means I cant run personal files from it. My laptop is the
one with the database. The code compiles fine

Albert D. Kallal said:
Steve Moss said:
If I use [ComboboxName].[Column](2), I insert it into the Control Source.
The only code I can find for the ProductName comboBox is the After_Update
code, which is
Click to expand...
Private Sub ProductName After_Update( )
Me. UnitPrice = ProductName.Column (2)
Click to expand...

I notice a extra space in the above...(right after the me. -- this not
correct). You would do well to use cut and paste from your actual code...

also, after you type in the code, while looking at the code, do a
debug->compile from the menu (you should do this each time).

does your code compile?
Click to expand...
 
Ok, so the code compiles fine.....

Private Sub ProductName After_Update( )

Me. UnitPrice = ProductName.Column (2)

end sub


By the way, use me. for those contorls....

Me.UnitPrice = Me.ProductName.Column (2)

Also, you could put in a test msgbox (remove after everyting works)

eg:
msgbox me.ProductName.Column(2)
Me.UnitPrice = Me.ProductName.Column (2)


Remmeber, coluumn( n ) is zero based (0 = collumn 1, 1 = collum2 etc.).
 
Exaclty as shown

Private Sub ProductName After_Update( )

msgbox me.ProductName.Column(2)
Me.UnitPrice = Me.ProductName.Column (2)

end if

We are placing the msgbox in the code just to "testing" purposes.


does the above msgbox work? what does it display....
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

amount field not showing currency format 3
Text Box values based on Combo Box value 1
Combo box and text box dependency 2
Switch text box to combo box 3
Synchronise two combo boxes 4
Excel Question on Formatting Cell and Adding a Row in Excel 2013 1
Switchboard queries with ComboBoxes 1
Access Form Refresh 1

Back
Top