Updating table values through a form from another table.

[cyoung311]

I'm trying to do an automatic update of one table from another through a
form. Basically, when a selection is made for an item, in this case a
particular workshop, I want to get the associated price of that workshop
from the Workshop table and update my ScheduleDetails table. The tables are
as follows:

tblStudent
StudentID
LastName
FirstName
....etc...

tblScheduleDetails
ID
StudentID
WorkshopID
UnitPrice

tblWorshop
WorkshopID
Name
Date
Time
Location
Instructor
Price

So I'm using an After Update VBA event procedure titled
WorkshopID_AfterUpdate() value that reads:

Me![UnitPrice] = Me![WorkshopID].Column(6)

When I select a workshop (by selecting the ID from a subform) the value that
is in the UnitPrice column dissapears so I'm wondering how to reference or
specify tblWorkshop at the VBA level. The after update mechanism is working
just fine but the price is not getting from tblWorkshop to
tblScheduleDetails.

Any help is appreciated, I've been using VBA for about 3 days now so I'm
even a little fuzzy on what "Me" does for me...

Chuck

[B\(radley]

"cyoung311" <cy*******@cox.net> wrote in message
news:JehLc.12958$Wv4.9561@okepread03

I'm trying to do an automatic update of one table from another
through a form. Basically, when a selection is made for an item, in
this case a particular workshop, I want to get the associated price
of that workshop from the Workshop table and update my
ScheduleDetails table. The tables are as follows:

tblStudent
StudentID
LastName
FirstName
...etc...

tblScheduleDetails
ID
StudentID
WorkshopID
UnitPrice

tblWorshop
WorkshopID
Name
Date
Time
Location
Instructor
Price

So I'm using an After Update VBA event procedure titled
WorkshopID_AfterUpdate() value that reads:

Me![UnitPrice] = Me![WorkshopID].Column(6)

When I select a workshop (by selecting the ID from a subform) the
value that is in the UnitPrice column dissapears so I'm wondering how
to reference or specify tblWorkshop at the VBA level. The after
update mechanism is working just fine but the price is not getting
from tblWorkshop to tblScheduleDetails.

Any help is appreciated, I've been using VBA for about 3 days now so
I'm even a little fuzzy on what "Me" does for me...

Chuck

"Me" is just a shorthand reference to the current form (ie.
Me![UnitPrice] is the same as Forms![frmMyFormName]![UnitPrice])

You may be referencing the wrong column from your combo box. In VBA the
column numbering starts at zero. Therefore, the 6th column is referenced
by the statement....

Me![WorkshopID].Column(5)
--
regards,

Bradley

[C Y]

Thanks Bradley. The item that is the source is "Price" from tblWorkshop
and is the 0,1,2,3,4,5,6th element of each record. I want this value to
go to tblScheduleDetails "UnitPrice" field. So price is the 7th field
(Column(6)) but I'm wondering if I might need to link that table to the
form? Right now the master and child references both contain StudentID
which is contained in both tblStudent and tblScheduleDetails. I need to
get to tblWorkshop which would be pointed to by WorkshopID. Can there
be more than one entry in the master and child fields?
Like separated by ";"s?

Best regards,

Chuck
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

Yes, the LinkMasterFields and LinkChildFields can contain multiple fields,
and the combination of fields will be used to synchronize the main form and
the form embedded in the subform control. From what I scanned quickly here,
it does not seem that the StudentID would enter into accessing tblWorkshop
to get the price, so I don't see how that would apply.

When you are referring to the control [WorkshopID], is that a ComboBox? If
so, the RowSource would identify the table from which you are selecting
data, and there would be no need for the ID to be in the LinkMasterFields
and LinkChildFields (because their only use is to synchronize data in the
main and sub forms). And, of course, your RowSource must include all the
Fields you need... but it can be a Query that only references, say,
WorkshopID, WorkshopName, and Price. I strongly recommend that you do not
use Name or Date for Field names -- they are Access reserved words and can
cause you all kinds of unobvious problems.

(If you did only use the three Fields in the RowSource Query, then Price
would be (2), not (6) -- it refers to the Column property of the Combo,
determined by what you use as RowSource, not to the underlying table
itself.)

One thing that might be causing you a problem is if, as it appears to me you
may be, you are using the same name for the Control as for the Field that is
the Control Source. Yes, the Access wizards do this, but it can be
confusing... so many of us use some naming convention (the one I used is
called the Reddick Naming Convention... you can Google on that, or search at
http://support.microsoft.com for details) that appends an identifying
prefix, e.g., "cbo" for Combo, and avoids possible confusion. Try renaming
your combo to cboWorkshopID and your and see if that helps.

Larry Linson
Microsoft Access MVP

"C Y" <cy*******@cox.net> wrote in message
news:40**********************@news.newsgroups.ws.. .

Thanks Bradley. The item that is the source is "Price" from tblWorkshop
and is the 0,1,2,3,4,5,6th element of each record. I want this value to
go to tblScheduleDetails "UnitPrice" field. So price is the 7th field
(Column(6)) but I'm wondering if I might need to link that table to the
form? Right now the master and child references both contain StudentID
which is contained in both tblStudent and tblScheduleDetails. I need to
get to tblWorkshop which would be pointed to by WorkshopID. Can there
be more than one entry in the master and child fields?
Like separated by ";"s?

Best regards,

Chuck
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Chuck]

Larry,

Thanks for your response. I'm learning as we go here but I did try the
link master and child fields by adding WorkshoID and it didn't like it.
It appears that the form doesn't know anything about tblWorkshop, just
tblStudent and tblScheduleDetails.

The combo that I am using is based on tblScheduleDetail and not on a
query or the subform itself. tblScheduleDetail references tblWorkshop
to get the text name of the workshop (2nd field of table) so I'm
guessing that what you have said is that the VBA code will only
recognize one field by WorkshopID (a single column?). Perhaps I should
use a query to get to the Workshop data instead of going directly to the
table from the form???

Best regards,

Chuck
Nullum Gratuitum Prandium
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"Chuck" <an*******@cox.net> wrote

Thanks for your response. I'm learning as we
go here but I did try the link master and child
fields by adding WorkshoID and it didn't like it.
Well, of course not... WorkshopID does not enter into synchronizing the
records between the main form and the subform.
It appears that the form doesn't know anything
about tblWorkshop, just tblStudent and
tblScheduleDetails.
Did you expect it to _infer_ something because of the WorkshopID field in
tblScheduleDetails? See below...
The combo that I am using is based
on tblScheduleDetail and not on a
query or the subform itself. tblScheduleDetail
references tblWorkshop to get the text name
of the workshop (2nd field of table) so I'm
guessing that what you have said is that the
VBA code will only recognize one field by
WorkshopID (a single column?). Perhaps I
should use a query to get to the Workshop
data instead of going directly to the
table from the form???

From your description, you have a Lookup Field for Workshop in the
tblScheduleDetail. I consider Lookup Fields one of the worst "enhancements"
Microsoft has ever made to Access -- they are very nice for the end-user who
only looks at tables in datasheet view. In every other case, as in yours, it
obscures what is there. Your tblScheduleDetails actually contains the ID,
but it shows you the name... and it does not retrieve the other fields from
tblWorkshop.

Proper table design would include the ID field of the appropriate workshop
as a "foreign key" and you'd retrieve the other Workshop data via a Query
that joined the two tables on the corresponding fields.

Unfortunately, I just don't seem to have enough information about what you
are doing to understand how you expect to get the Price from Workshop ID
from a ComboBox with a RowSource of tblScheduleDetails. Isn't it the
tblScheduleDetails "Price" field that you want to set? Is the
tblScheduleDetails the RecordSource of the Form embedded in the Subform
Control? Why would the ComboBox in the Subform not refer to tblWorkshop to
get a Price? Further, why would a Combo Box in a Form whose RecordSource was
tblScheduleDetails have a RowSource of the same tblScheduleDetails?

Larry Linson
Microsoft Access MVP

[Chuck]

Larry,

The Combo box has a rowsource of tblWorkshop. I modded that RowSource
query to retrieve the WorkshopID, WorkshopName and also Price. It is
this Price that I'm trying to load into the tblScheduleDetails UnitPrice
field.

Since adding the "Price" field to the query I've modified the VBA event
procedure code to now:

Me![UnitPrice] = Me![WorkshopID].Column(2)

and now I get a message box with "Runtime error 438; Object doesn't
support this property or method".

Thanks again for your assistance,

Chuck
Nullum Gratuitum Prandium
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!