Help! I cannot figure out right expression

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

Guest

I have a field named Item #1. The control source for Item#1 is a seperate
box named S/O Zip Code. The S/O ZIp Code is linked to another source. I am
trying to write an expression for a field named Billing #1. Depending on the
zip code in Item #1, I want it to look at my table Zip Code Cost and
automatically fill in with the appropriate amount from Column 2 of my Zip
Code Cost Table. Column 1 of my Zip Code Cost table is a list of a bunch of
zip codes. Please help, I am really a beginner with this whole crazy Access
thing!

Thanks,
Taylor
 
I have a field named Item #1. The control source for Item#1 is a seperate
box named S/O Zip Code. The S/O ZIp Code is linked to another source. I am
trying to write an expression for a field named Billing #1. Depending on the
zip code in Item #1, I want it to look at my table Zip Code Cost and
automatically fill in with the appropriate amount from Column 2 of my Zip
Code Cost Table. Column 1 of my Zip Code Cost table is a list of a bunch of
zip codes. Please help, I am really a beginner with this whole crazy Access
thing!

Thanks,
Taylor
Click to expand...

First off... bear in mind that Forms do not contain any data. Tables
contain data; forms are tools which display it.

Secondly, if you have the Zip Code Cost stored in its own table, it's
quite possible that that should be the ONLY place that it should be
stored. The exception would be if the Zip Code Cost might change over
time but you want to record the cost as of a particular point in time.
Assuming that this is in fact the case, you need just a little bit of
VBA code, using the Column() property of the combo box. Open the form
in design view, view its Properties, select the combo box, and on the
Events tab click the ... icon by the AfterUpdate property. Choose the
"Code Builder" option. Access will give you the Sub and End Sub lines
below; edit it to something like

Private Sub Zip_Code_AfterUpdate()
Me![Billing #1] = Me![Zip Code].Column(1)
End Sub

Note that (1) means the *second* column in the combo's row source
query, it's zero based.


John W. Vinson[MVP]
 
John:

I appreciate all of your help, especially on a Sunday. But, I put in the
code and it does not change the value of the field. Basically, I have a
field named "Item #1". "Item #1" has a control source based on the zip code
of an address in a different field. I also have a field named "Billing #1".
I have a table named "Zip Code Cost". In the table are only two fields...Zip
Code and Cost. I need the "billing #1" field to automatically insert the
cost that is associated with the specific zip code that is entered into the
"Item #1" field. Maybe this will help clarify what I need. I am very new at
this whole Access thing and am actually surprised I have been able to catch
on to some of the lingo. I hope you or someone can be of some help. I
really need step by step directions. Thank you again for everything!

Thanks,
Taylor


John Vinson said:
I have a field named Item #1. The control source for Item#1 is a seperate
box named S/O Zip Code. The S/O ZIp Code is linked to another source. I am
trying to write an expression for a field named Billing #1. Depending on the
zip code in Item #1, I want it to look at my table Zip Code Cost and
automatically fill in with the appropriate amount from Column 2 of my Zip
Code Cost Table. Column 1 of my Zip Code Cost table is a list of a bunch of
zip codes. Please help, I am really a beginner with this whole crazy Access
thing!

Thanks,
Taylor
Click to expand...

First off... bear in mind that Forms do not contain any data. Tables
contain data; forms are tools which display it.

Secondly, if you have the Zip Code Cost stored in its own table, it's
quite possible that that should be the ONLY place that it should be
stored. The exception would be if the Zip Code Cost might change over
time but you want to record the cost as of a particular point in time.
Assuming that this is in fact the case, you need just a little bit of
VBA code, using the Column() property of the combo box. Open the form
in design view, view its Properties, select the combo box, and on the
Events tab click the ... icon by the AfterUpdate property. Choose the
"Code Builder" option. Access will give you the Sub and End Sub lines
below; edit it to something like

Private Sub Zip_Code_AfterUpdate()
Me![Billing #1] = Me![Zip Code].Column(1)
End Sub

Note that (1) means the *second* column in the combo's row source
query, it's zero based.


John W. Vinson[MVP]
Click to expand...
 
John:

I appreciate all of your help, especially on a Sunday. But, I put in the
code and it does not change the value of the field. Basically, I have a
field named "Item #1".
Click to expand...

I'd recommend never using either blanks or special characters such as
# in fieldnames.
"Item #1" has a control source based on the zip code
of an address in a different field.
Click to expand...

*FIELDS* do not have Control Sources. A Field in a table is just - a
field in a table.

A Control (a textbox, combo box, other types of controls) on Forms can
have a Control Source; this can be the name of a field in the Form's
Recordsource, or it can be an expression. In the latter case the value
shown on the form will not be stored anywhere and will not be
editable.

So is [Item #1] a table Field? or a Form Control? If it's a textbox,
what IS its control source?
I also have a field named "Billing #1".
Click to expand...

Again... is this a Field, or the name of a Control? They're different.
I have a table named "Zip Code Cost". In the table are only two fields...Zip
Code and Cost. I need the "billing #1" field to automatically insert the
cost that is associated with the specific zip code that is entered into the
"Item #1" field. Maybe this will help clarify what I need.
Click to expand...

No, it doesn't, since - apparently - Item #1 is not a field but a
control, and it already has a control source, therefore it cannot be
assigned a value.
I am very new at
this whole Access thing and am actually surprised I have been able to catch
on to some of the lingo. I hope you or someone can be of some help. I
really need step by step directions. Thank you again for everything!
Click to expand...

Please open your Form in design view; view its Properties; tell me
what the Record Source property is. If it's a Table, what are the
(relevant) fields in the table? If it's a Query, please open the Query
in design mode; select View... SQL; and post the SQL text of the query
here.

Please also post the Control Source of [Billing #1] and the RowSource
property of that combo box.

John W. Vinson[MVP]
 
John:

Okay, I think I understand some of what you are saying. I opened the form
in design view. I looked at the properties. THe source is a table called
Client Information. Now, let me try to explain it to you this way. I have a
combo box that I have named as Billing #1. I also have a text box named Item
#1. The text box when you right click on it and click on properties, the
"control source" is based on another field. I need the Billing#1 combo box
to fill with a dollar amount based on the zip code that is entered into the
Item#1 text box. I am not sure if it would be easier for me to email you the
database so you can see it to understand what I am talking about. If you
would like me to, I can. Also, I was wondering if I had a combo box with a
list of items in it. If I wanted to select more then one of the items and
get them to list in subsequently a different box, how would i do that? For
instance, if the list box had A, B, C, and D as the list, and I wanted to
pick A, B and C. And each time i clicked on one I wanted it to pop up in
another box so they were all listed in the same area, how would i do that? I
am not sure if this makes any sense, but please let me know. I truly do
appreciate all of your help.

THanks Again,
Taylor

John Vinson said:
John:

I appreciate all of your help, especially on a Sunday. But, I put in the
code and it does not change the value of the field. Basically, I have a
field named "Item #1".
Click to expand...

I'd recommend never using either blanks or special characters such as
# in fieldnames.
"Item #1" has a control source based on the zip code
of an address in a different field.
Click to expand...

*FIELDS* do not have Control Sources. A Field in a table is just - a
field in a table.

A Control (a textbox, combo box, other types of controls) on Forms can
have a Control Source; this can be the name of a field in the Form's
Recordsource, or it can be an expression. In the latter case the value
shown on the form will not be stored anywhere and will not be
editable.

So is [Item #1] a table Field? or a Form Control? If it's a textbox,
what IS its control source?
I also have a field named "Billing #1".
Click to expand...

Again... is this a Field, or the name of a Control? They're different.
I have a table named "Zip Code Cost". In the table are only two fields...Zip
Code and Cost. I need the "billing #1" field to automatically insert the
cost that is associated with the specific zip code that is entered into the
"Item #1" field. Maybe this will help clarify what I need.
Click to expand...

No, it doesn't, since - apparently - Item #1 is not a field but a
control, and it already has a control source, therefore it cannot be
assigned a value.
I am very new at
this whole Access thing and am actually surprised I have been able to catch
on to some of the lingo. I hope you or someone can be of some help. I
really need step by step directions. Thank you again for everything!
Click to expand...

Please open your Form in design view; view its Properties; tell me
what the Record Source property is. If it's a Table, what are the
(relevant) fields in the table? If it's a Query, please open the Query
in design mode; select View... SQL; and post the SQL text of the query
here.

Please also post the Control Source of [Billing #1] and the RowSource
property of that combo box.

John W. Vinson[MVP]
Click to expand...
 
John:

Okay, I think I understand some of what you are saying. I opened the form
in design view. I looked at the properties. THe source is a table called
Client Information. Now, let me try to explain it to you this way. I have a
combo box that I have named as Billing #1. I also have a text box named Item
#1. The text box when you right click on it and click on properties, the
"control source" is based on another field. I need the Billing#1 combo box
to fill with a dollar amount based on the zip code that is entered into the
Item#1 text box. I am not sure if it would be easier for me to email you the
database so you can see it to understand what I am talking about. If you
would like me to, I can. Also, I was wondering if I had a combo box with a
list of items in it. If I wanted to select more then one of the items and
get them to list in subsequently a different box, how would i do that? For
instance, if the list box had A, B, C, and D as the list, and I wanted to
pick A, B and C. And each time i clicked on one I wanted it to pop up in
another box so they were all listed in the same area, how would i do that? I
am not sure if this makes any sense, but please let me know. I truly do
appreciate all of your help.
Click to expand...

I'm sorry, but I'm a self-employed consultant. I work on other
peoples' databases, but I do so on contract for a fee. I volunteer my
time here in the newsgroup as a public service, but I do need to keep
it within limits!

I would STRONGLY suggest that you stop, step back a bit, and study up
a bit about how database design should work. You're talking about
"storing A, B, and C in a different box" - that makes *zero* sense in
a relational database; if you have such a one to many relationship,
you store the information in *two tables* in a one to many
relationship. YOu're also talking about "the Billing#1 combo box to
fill with a dollar amount" - as if a Combo Box contained data. It
doesn't; a combo box *is a display tool* to allow you to display and
select data which is stored in a Table.

I suspect that you started designing your database with this Form.
That's the wrong place to start! What you need to do instead is turn
off the computer, go into a different room, and get a pad of paper and
a #2 pencil with a good eraser. Identify the "Entities" - real-life
things, events, or people - of importance to your application. Do you
have Customers? A customer is a person, so you have a Person-type
Entity. You have Products? There's another entity. Sales? That's an
event-type entity in which (presumably) a Customer purchases several
Products.

Take a look a the Northwind sample database which came with Access (it
should be on your OfficePro or Access CD if it's not installed in the
Samples folder already). Take a look at www.mvps.org/access and the
links therein; and at http://support.microsoft.com, search for Access
database design. Or get one of the many good books on Access
databases.

Access is quite capable of doing what you want - but it's a lot more
work than setting up a spreadsheet or a Word document! And if you get
your table design off on the wrong foot, you'll have reams of
problems.

If you'ld like to post a real-life description of what you're trying
to accomplish, I or the other volunteers here can surely guide you in
the right direction; but I really hesitate to try to fix up this form,
because I fear it's built on a shaky foundation!

John W. Vinson[MVP]
 
Back
Top