Update field contents from another field

[Ladymuck]

I have a database that has two tables: (well, more but they're irrelevant
right now! tblRecord and tblSoftware.

In tblRecord, there is a field fldSoftware that is linked to tblSoftware. I
have set it so that it is a combo box displaying three fields from
tblSoftware (fldManufacturer, fldProduct, fldVersion). When you select a
particular item (eg Adobe, Acrobat, 5.0) only fldManufaturer is displayed in
fldSoftware. This is fine, I'm happy with that.

I have outputted tblRecord to a form, nothing fancy, used the Wizard, no
additional tables or queries used.

What I would like to be able to do, using the link described above is to
populate another field on tblRecord called fldComment with the other two
fields (fldProduct and fldVersion). Lots of things I've seen on various
forums say to use SQL and an UPDATE query. I have reservations about doing
this as this will update the whole table and not just the current record but
I did it anyway and it bleats like a good 'un about another user updating the
record, do I want to save the changes, etc but it does finally do what I want
it to do. Here is the code:

Public Sub fldSoftware_AfterUpdate()
Dim strSQL As String
strSQL = "UPDATE tblSoftware INNER JOIN tblRecord " & _
"ON tblSoftware.fldSoftwareID = tblRecord.fldSoftware SET
tblRecord.fldComment = 'Software Installed: ' & tblSoftware.fldProduct & ' '
& tblSoftware.fldVersion " & _
"WHERE ((([tblRecord].[fldSoftware])=[tblSoftware].[fldSoftwareID])
AND ((tblRecord.fldSoftware) Is Not Null));"
DoCmd.RunSQL strSQL
End Sub

I've set it to the AfterUpdate event on the fldSoftware control. The not
null bit at the end is because it is not compulsory to select something from
fldSoftware and I don't want it to fall over if it is empty.

Is what I am trying to do possible? Or am I barking up the wrong tree?

BTW, I am using Access 2003 in 2000 mode.

Huge thanks for any hints or tips provided!

 

[tina]

the purpose of relational database design is to *not* duplicate data.
suggest the following:

1. if the fldSoftware in tblRecord is a Lookup field, recommend you get rid
of the lookup. see http://www.mvps.org/access/lookupfields.htm for details.

2. you don't say what the primary key field is, in tblSoftware. that value
should be stored in fldSoftware in tblRecord, and nothing else.

3. once tblSoftware and tblRecord have been linked, by using the primary key
from tblSoftware as a foreign key in tblRecord, then you can display
relevant tblSoftware data with the appropriate tblRecord records in queries,
forms, reports, whenever you want - without storing duplicate Software data
in tblRecord records.

hth

 

[Ladymuck]

Thanks for the hints, I understand what you mean and do agree but there's a
niggling doubt in the back of my mind. Hopefully if I explain a bit more then
maybe it'll clarify things (and I might end up answering my own question!)

I used a lookup because there's over 300 combinations of
Manufacturer/Product/Version in tblSoftware (the primary key is an
autonumber) and when the user is inputting details via the form they won't
necessarily know which number relates to which combination, and I'm trying to
keep selection as simple as possible. The field fldSoftware is linked to the
primary key on tblSoftware and does return this number if the lookup is
removed.

The reason why I want to duplicate the data is so that when I produce a
report, I only need to show the comments field (along with date of entry and
some other bits). fldComments is actually a memo box and could get quite
large when filled with other non-software related information. Therefore the
report will be mainly based around what's in this field.

I've had a go in VBA, hopefully the code will describe what I'm trying to
achieve better than I can!!

Private Sub fldSoftware_AfterUpdate()
Dim strComment As String
If Not IsNull(Me!fldSoftware) Then
strComment = "Software Installed: " & tblSoftware!fldManufacturer & " "
& tblSoftware!fldProduct & " " & tblSoftware!fldVersion
Me!fldComment = strComment
End If
End Sub

This worked when strComment was just "Software Installed" but once I tried
to add on the table/field bits, it returned a type mismatch. I have to admit
that I don't know how it knows what record to return from tblSoftware (I'm
assuming some 'magic' goes on in the background due to the relationship
between tblSoftware and tblRecord)

Once again, thanks for any help.

 

[tina]

using a combo box in a *form* (so your user is looking up the
manufacturer/product/verion, not seeing a numeric key) is standard, and
quite proper - the problem is only with creating a lookup at the *table*
level. if the primary key value of a tblSoftware record is being stored in a
tblRecord record, then that is all Access needs to know to pair the correct
tblSoftware record with the correct tblRecord record.

to show the tblSoftware data in a report, link tblRecord and tblSoftware (on
the foreign key/primary key fields) in a query. then you have the software
data available to display in your report without duplicating it in
tblRecord.

hth