Auto updating Field Forms and Tables

[Guest]

I would like to start a new thread on my problem since the first one sort of
died on the vine.

I’ve got this application in which I’m using a Form to enter data. I’ll use
selling sandwiches as the example. Let’s suppose that I want to track sells
of different kinds of sandwiches.

I have a table set up with just Egg Sandwiches of which there are four or
five different selections that could be made. Each of the five selections
has a different price. So, in this table for Egg Sandwiches there is the
description and the price for each.

Now - I've got another Table set up just to collect ordering information. I
have created a Form through which the User can select the Sandwiches and
store the selection in the Ordering Information Table. This Form uses a
Combo Box that allows the user to select the Sandwich. What I want is for
the price of the Sandwich to automatically display on the Form when the
Sandwich is selected.

Selecting the sandwich and the price from the Egg Sandwich Table with
individual Combo Boxes works great. I’m able to write the sandwich
description and the price to the correct fields in the Ordering Information
Table. What I want to do is to automatically display the price on my form
when a specific sandwich is selected and have it written to the Table.

I worked out how to automatically display the price when the sandwich is
selected by using a Combo Box to retrieve the description buy setting the
Control Source to a Query that’s linked to the Order Information Table, Bound
Column = 2 and the Row Source is set to a Query that’s linked to the Egg
Sandwich Table. I then have a Text Box with its Control Source set to the
Form I’m working in. The Form looks like it’s doing what I want it to do.

The Problem: The Form is writing the value of the Sandwich to the Order
Information Table instead of the description of the Egg Sandwich and its not
writing a 0 (Zero) in the price field, even though the Form is displaying the
correct information.

I’ve tried everything I can think of. Sure could use some help on this one.

 

[Guest]

Larry,

This is not that hard. How exactly you do it will depend on whether you are
using Bound or Unbound controls for your Order Table. I will assume you are.
Your query that is the control source for the combo box should be on the Egg
Sandwich Table (Wished you had used Tuna, I hate Egg Sandwiches). Let's
assume the first column (me.cboSands.colum(0)) is the Description, and the
second is the Price (me.cboSands.column(1)). The easiest way, but not,
perhaps, the prettiest, would be to display both columns. Now, set the
Column Heads property for cboSands to Yes, and in the Sands Table set the
Caption properties for Description and Price that would be meaningful to a
use.

To get the data into the Order Table, you probably have a text box to show
the price. The Control Source Property for that text box should be something
like:

[txtOrderPrice] = [cboSands].[Column](1)

Where you put it depends on what else is going on in the form. For
simplicity's sake, I will assume it is not important, so you could do it in
the AfterUpdate event of the combo box.

 

[Van T. Dinh]

I understand your set-up but I don't follow your problem.

It seems to me that you are saying there are 2 problems:

* Something related to the Sandwich selected is written into the Ordering
Information Table but it is not what you wanted. You used the word "value"
but it didn't sound like you meant the price.

* The price (i.e. the amount that the buyer pays) is written into the Table
as zero while it is displayed correctly on the Form. You wrote "...its not
writing a 0 (Zero) in the price field. ..." but it sounds like the word
"not" shouldn't be there (and the correct grammar is "it's", not "its").

Perhaps, you like to re-phrase the problem from your viewpoint to clarify
for reader so that respondents can suggest possible solution(s).

BTW, have you check the "Orders Form" and "Orders Subform" in the sample
database "NorthWind"? It sounds very similar to what you need, especially
how "NorthWind" handles the delivery address.

 

[Guest]

Kalatu"

Thanks for the help. I'll get back to the problem on Monday and let you
know how I come out. I'm headed out for a fishing trip this weekend.

Because of my lack of knowledge of wrting code I have to use the
"Properties" function to do everything. Even the Expression Builder gives me
fits. This makes it difficult for me to take full of advantge of your good
advice.

Thanks,
Larry

Larry,

This is not that hard. How exactly you do it will depend on whether you are
using Bound or Unbound controls for your Order Table. I will assume you are.
Your query that is the control source for the combo box should be on the Egg
Sandwich Table (Wished you had used Tuna, I hate Egg Sandwiches). Let's
assume the first column (me.cboSands.colum(0)) is the Description, and the
second is the Price (me.cboSands.column(1)). The easiest way, but not,
perhaps, the prettiest, would be to display both columns. Now, set the
Column Heads property for cboSands to Yes, and in the Sands Table set the
Caption properties for Description and Price that would be meaningful to a
use.

To get the data into the Order Table, you probably have a text box to show
the price. The Control Source Property for that text box should be something
like:

[txtOrderPrice] = [cboSands].[Column](1)

Where you put it depends on what else is going on in the form. For
simplicity's sake, I will assume it is not important, so you could do it in
the AfterUpdate event of the combo box.

I would like to start a new thread on my problem since the first one sort of
died on the vine.

I’ve got this application in which I’m using a Form to enter data. I’ll use
selling sandwiches as the example. Let’s suppose that I want to track sells
of different kinds of sandwiches.

I have a table set up with just Egg Sandwiches of which there are four or
five different selections that could be made. Each of the five selections
has a different price. So, in this table for Egg Sandwiches there is the
description and the price for each.

Now - I've got another Table set up just to collect ordering information. I
have created a Form through which the User can select the Sandwiches and
store the selection in the Ordering Information Table. This Form uses a
Combo Box that allows the user to select the Sandwich. What I want is for
the price of the Sandwich to automatically display on the Form when the
Sandwich is selected.

Selecting the sandwich and the price from the Egg Sandwich Table with
individual Combo Boxes works great. I’m able to write the sandwich
description and the price to the correct fields in the Ordering Information
Table. What I want to do is to automatically display the price on my form
when a specific sandwich is selected and have it written to the Table.

I worked out how to automatically display the price when the sandwich is
selected by using a Combo Box to retrieve the description buy setting the
Control Source to a Query that’s linked to the Order Information Table, Bound
Column = 2 and the Row Source is set to a Query that’s linked to the Egg
Sandwich Table. I then have a Text Box with its Control Source set to the
Form I’m working in. The Form looks like it’s doing what I want it to do.

The Problem: The Form is writing the value of the Sandwich to the Order
Information Table instead of the description of the Egg Sandwich and its not
writing a 0 (Zero) in the price field, even though the Form is displaying the
correct information.

I’ve tried everything I can think of. Sure could use some help on this one.

 

[Guest]

IT'S writting a zero in the price field instead of the actual price.

Didn't expect to get a grammar lesson!

 

[Van T. Dinh]

That is what I thought but you wrote "...its not writing a 0 (Zero) in the
price field. ..." (note the word *not*).

My guess is that the Form doesn't assign the value zero. It is likely that
the zero is the default value for this Field and since you are not assigning
a value to this Field when the new Record is created, the Field will take on
the default value of zero.

What you want is demonstrated in the "Orders Form" of the NorthWind
database. Check that Form's RecordSource and the AfterUpdate Event of the
Customer ComboBox.

 

[Van T. Dinh]

I am not sure since I can't see your Form but the first thing I would do is
to rename the Controls (e.g. TextBoxes / ComboBoxes) to be *different* from
the names of the Fields they are bound to. For example, I always use the
prefix "txt" + FieldName for bound TextBox Controls and "cbo" for
ComboBoxes. This way, at leat we can distinguish which one you are
referring to.

Also check and make sure the Control names are correct in your code.

 

[Guest]

Van,

Thanks man, that bit of info helped me to see what I was doing wrong. I've
got it to work now.

Sure appreciate the help.

Larry

I am not sure since I can't see your Form but the first thing I would do is
to rename the Controls (e.g. TextBoxes / ComboBoxes) to be *different* from
the names of the Fields they are bound to. For example, I always use the
prefix "txt" + FieldName for bound TextBox Controls and "cbo" for
ComboBoxes. This way, at leat we can distinguish which one you are
referring to.

Also check and make sure the Control names are correct in your code.


--
HTH
Van T. Dinh
MVP (Access)

You're correct - zero is the default number.

I looked at the Northwind example and modified my Table, Query and Form to
look like what they did. As I indicated earlier my application has nothing
to do with sandwiches - it's actually a little Safety related project that
I'm working on. As I understand the Northwid example they're using an Event
Procedure to duplicate data on the Form and assigning it to a different
field. For example:

Private Sub CustomerID_AfterUpdate()
' Update ShipTo controls based on value selected in CustomerID combo box.
Me!ShipName = Me![CustomerID].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostalCode
Mee!ShipCountry = Me!Country

End Sub

Here's the Event Procedure that I wrote that doesn't work:

Private Sub Floor_Condition_AfterUpdate()
' Update FloorCondtion controls based on value selected in Floor_Condition
combo box.
Me!FloorCondition = Me!Floor_Condition.Column(1)
Me!FlrScore = Me!Flr_Score
End Sub

FloorConditon and Flr Score are the new fields that I created. So what I'm
attempting is to have the data that is selected in the Floor_Condtion Combo
and the Flr_Score Text Box to be duplicated in the FloorCondtion and FlrScore
fields and posted back to the Table through my Query.

I get a Runtime Error 348 - Object doesn't support this property or object.
No amount of looking in Access Help had helped me. What am I missing? It
has to be something simple.

Sorry is this is confusing but then again I'm confused.

Thanks,
Larry