Combo Box AfterUpdate Help

[Harry Thomas]

I have two tables, TableA and TableB. In a form I use a combo Box to select
data from Field1 in TableA and place it in Field2 in TableB. What is the
exact command I put in the AfterUpdate field of the combo box to also take
data from Field3 of TableA and place it in Field4 of TableB.

Thank you

 

[Duane Hookom]

If you feel you need to store redundant data, you can use the after update
event of the combo box with code like:

Me.txtField4 = Me.cboFieldB.Column(x)

Replace x with the correct column number from your combo box's row source.
These are numbered beginning with 0.

 

[Jeff Boyce]

Harry

In a well-normalized relational database, it is rarely necessary or
desirable to take data from a field in one table and put it in another
table. If you are using Access, but your data isn't particularly
well-normalized, you will find it much more difficult to make effective use
of Access' many features & functions.

Could you provide an example with real (or imaginary) data of what you
asked? Could you provide a description of your tables/fields? I ask
because there may be a way to accomplish what you want to, even though it
doesn't use the (same) method that you've come up with.

 

[Craig Alexander Morrison]

Dodgy design as already pointed out in "Updating....." thread yesterday.

I think you will find most people will consider your design flawed.

Anyway use the Column property of the ComboBox as already stated yesterday.

I am hesitant to help you make an arse out of your database however in the
AfterUpdate event procedure of the ComboBox do something like this:

Me!FieldB4 = Me!cboFieldA1.Column(1)

This assumes that cboFieldA1 is bound to FieldA1 and that FieldA3 is the
next column.

In this case with the correct design you would not need any code if the
value of FieldA1 was the primary key of the table being referenced and
FieldB4 was the foreign key in the related table.

I speak from 25 years of experience with Relational Databases and nearly 15
with Access/Jet.

I would strongly recommend you redesign your database conforming to the
relational model rather than the big buckets of merde approach.

This last sentence is the one that is of most help to you although you may
not realise it.

 

[Harry Thomas]

I asked a question and expected a reasonable answer, if I wanted a lecture on
database design I would have asked for that instead. You have no idea of the
design, the examples I gave yesterday and today were purely to get an answer
to a question. So unless you want to make an arse of yourself, read the
question.

 

[TC]

Hey Harry, "way to go" to get more help!

TC

 

[Craig Alexander Morrison]

Read the answer!

"Me!FieldB4 = Me!cboFieldA1.Column(1)

As well as the advice on how to do it correctly the bish-bash-bodge-up code
has also been posted.

 

[Craig Alexander Morrison]

BTW If you come to the Tables and Database Design newsgroup it is your
tables and database design that are going to be looked at.

You may wish to use Getting Started or Forms in future if you are hoping for
no-one to notice what a possibly bad database design you have.

Relational Databases store information once only and in the original thread
we discussed that with you, you chose to ignore it, that's your perogative I
can't force you nobody can but just know this - a good relational database
design is rewarded in ease of development when using Access as the
application development tool.

Tell me why your design requires the data duplication that you believe it
does. (excluding point-in-time data that was mentioned in the earlier
thread)

You state I have no idea of your design, come on enlighten us about your
design, prove my assumptions wrong, you never know they may be.

 

[Duane Hookom]

Relax Harry.
1) you got a working answer (you didn't reply to my suggestion)
2) you got several consistent opinions on "tablesdbdesign".

If you had a gun and asked how to take the safety off so you could shoot
yourself in the foot, I would expect you would be advised to leave the
safety on. I didn't see anywhere in your posts that suggested a good reason
for shooting yourself in the foot.

 

[Roger Carlson]

It never ceases to amaze me how people who are asking for FREE help can be
so demanding. No one here is required to answer your question, and I
suspect at least half a dozen people here will never do so again.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Craig Alexander Morrison]

BTW a good way out of this is to tell me that the fields form a
Compound/Composite Primary Key and you need to create a Compound/Composite
Foreign Key in the related table, that would be OK.

Although no doubt someone will interject here with the hallowed AutoNumber
(vbg) as Surrogate.

 

[Harry Thomas]

Before the rest of you pick on me, I didn't start this slanging match. I
asked a simple question and got back a lengthy reply about database design
together with accusations that I was making an arse out of my design and that
my approach was big buckets of merde. What is even more incredible is that
the reply from Mr Morrison is based on assumptions made by him about the
design of a database of which he has no knowledge. I do not for one minute
doubt his ability or expertise and I wouldn't be posting such questions if I
knew the answer. I do not however think it fair to receive such a reply.

 

[TC]

If you post in tablesdbdesign, you will get comments on what could be a
problem in your table design.

If you do not want such comments, go post in some other group!

Of course, you might be the world's best expert on table design.

But then, we'd be seeing you /answering/ questions in this group - not
asking them.

It is your job to perceive & respect the ethos of this group. It is not
our job to be directed by you, as to how we reply to your posts. If you
want that kind of control, go pay someone to wok for you!

HTH,
TC

 

[Craig Alexander Morrison]

Let's remind ourselves of your original question in the earlier thread:

"I have two tables in an ordering system database. One contains names, phone
numbers etc of staff placing the orders and the other the main table
containing full details of the order. I use a combo box in a form to select
the name of the staff member from the staff table and place this name in the
main table. How do I also get other data to transfer across at the same
time, ie place the phone number from the staff table into a corresponding
field in the main table."

Now to many here: placing the Staff Member's Name and Phone Number in the
Order table having copied them from the Staff table would appear to be not
in compliance with the rules of normalisation. This Staff and Order data
could be made available in reports and forms using queries to collate the
data.

The slanging match as you put it would appear to be one sided I have only
pointed out the likely failings in your database design, whereas you have
decided to suggest that I may be an arse (not without the bounds of
possibility (vbg)) for being hesitant about your design.

Whilst I did provide some guidance on how to acheive what you wanted to
achieve I felt it was my responsibility to point out that you may have taken
a wrong turn.

Others use these newsgroups and read the messages that they have not
contributed to and glean useful information from them from time to time. It
would be irresponsible of me to just answer your question without being
curious as to the reason for such a requirement, someone lurking may be in a
similar situation and now they are thinking maybe my database design is
wrong, rather than just applying multiple values from a single combo-box and
stuffing one table with duplicate data from another.

Indeed everyone who did answer your question was also curious about your
design...see the pattern emerging?

As stated earlier in this thread, I can think of legitimate reasons,
"point-in-time" and "compound Foreign Key" however 99 times out of a 100 it
is usually a badly normalised relational database design. For me a database
design that is not fully normalised is generally <insert expletives of
choice>. Access really does reward your application development if you are
working on a fully normalised relational database.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Before the rest of you pick on me, I didn't start this slanging match. I
asked a simple question and got back a lengthy reply about database design
together with accusations that I was making an arse out of my design and
that
my approach was big buckets of merde. What is even more incredible is that
the reply from Mr Morrison is based on assumptions made by him about the
design of a database of which he has no knowledge. I do not for one minute
doubt his ability or expertise and I wouldn't be posting such questions if
I
knew the answer. I do not however think it fair to receive such a reply.

 

[Duane Hookom]

Craig,
I think one of the issues was your lack of bed-side manner by using the
phrase "help you make an arse out of your database" and then later "big
buckets of merde". I expect you can understand how if someone has really
worked hard on a project that being presented with a critique using these
phrases might be a little extreme. What might seem harmless to us is
perceived as offensive by others.

I am often very short (and possible seem curt/rude) with my responses. I try
to not be too judgmental but it's often difficult to hide my opinions. You
will find this true with almost every person who attempts to help here on a
regular basis. I recall a recent thread where the very wise and kind John
Vinson was perceived as rude by an OP. He immediately apologized changed the
course of his response. He took responsibility for the perception of another
person. It would be ideal if everyone showed the same demeanor.

 

[Craig Alexander Morrison]

Considering the cruelty he _may_ be exerting on his poor database I thought
I was both restrained and delightful. (vbg)

Anyway I was much gentler on the earlier thread when he asked the same
question and gave more detail than in this one.

 

[Harry Thomas]

Duane
Thank you for yor support, its a shame that Morrison now finds it so
amusing. I am not normally rude and I appreciate your comments. Its just a
shame that I came across that pompous arse and his mate TC, whose head is so
far up Morrison''s arse he must be short of air.

 

[Duane Hookom]

You are rapidly losing any support you might have gotten as you continue
throwing fuel on the fire.

Why can't people just take responsibility for their comments? When it's
appropriate, we can criticize solutions, methods, designs,.. but it is
rarely acceptable to direct criticism toward a person.

 

[Harry Thomas]

Duane

Like I said before, I am not normally rude but what do you expect. He is
allowed to make rude remarks towards me and then make fun of the fact and
nothing is said. I call him a pompous arse and suggest his mate TC might need
breathing apparatus and the whole world is up in arms against me.

Sorry, I know I shouldn't add fuel to the fire but they deserve it.

 

[Duane Hookom]

You don't need to make any of this public. Ask for their private email and
take it outside. No one who reads through the NGs needs to see this trash.