Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

MS Access: Updating Data In One Field Should Update Data In All Relevant Fields


(!)

fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
21-Feb-2009, 08:24 AM #1
MS Access: Updating Data In One Field Should Update Data In All Relevant Fields
I am an absolute begineer in MS Access. Will try to put my question as simply n clearly as possible.

I have designed a database to keep history of our company's projects. The 'Master Project History Table' contains fields including 'Project Ref', 'Project Title', 'Project Budget' and 'Project Year'. The users are expected to populate the table through 'Master Project HistoryForm'.

The key field is set as 'Project Ref'.

Some of the projects are combined with each other and to display this info, 'Master Project History Form' also has the fields 'Project Ref-1', 'Project Title-1', 'Project Budget-1' and 'Project Year-1'.

I am loking for a code for the ('On Update' event) that will fetch and display data in 'Project Title-1', 'Project Budget-1' and 'Project Year-1' fields when 'Project Ref-1' is updated. Please note that all this info is coming from the same 'Master Project History Table'.

My IT guys told me to use a code with variable for each field that goes something like this (to update Project Title-1 field):

Dim varX As Variant

varX = DLookup("[Project Title]" , "Master Project History Table" , "[Project Ref]=" & Forms![Master Project History Form]![Project Ref-1])

Me![Project Title-1] = varX

And then repeat for each other field by defining new variables.

The above is not working giving syntex errors etc.

Will appreciate help from fellow members.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
21-Feb-2009, 08:42 AM #2
fkk, welcome to the Forum.
Your IT guys are obviously High Level Access Programmers and not very helpful for someone at your level of Access knowledge.
First of all having fields with names like 'Project Ref-1' suggests that your Main Table is trying to do too much and that you need a sub-table.
Is the data that is going to be stored in 'Project Ref-1' actually also stored in Project Ref as a Project in it's own right?
What I don't understand is why if the 'Project Ref-1' etc are in the same table as the Project Ref and presumably the same record, they are not included in the Field list of the Form so that you can just have them displayed on the form anyway.
If there is more than one associated Project they should be shown on a Subform on the Project's Mainform.
Can you take a screenshot of the Table Relationships with all fields showing in each table and post it on here for us to look at?
__________________
OBP
I do not give up easily
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
21-Feb-2009, 09:00 AM #3
OBP - Thanx for reply.

Am attaching the screenshot of the actual form after removing all irrelevant fields.

My problem, stated in another way, is:

When I update the Project Ref-1 field using the query therein, the data in Project Title-1, Project Location-1 and Project Year-1 should be updated automatically - all coming from the same parent table.

In other words, the Company is handling say 5 projects at the moment that include PFE 42, PFE 44, PFE 45, PFE 47 and PIE 01. At the same time PFE 44 is combined (commercially and scope wise) with PFE 42. While on the record screen of PFE 42, I wish to enter PFE 44 in the Project Ref-1 field that should fetch the relevant data for its title, location and year.


I would love to have a sub-table since sometimes upto 9 projects may be combined togather. Problem: being a beginner I did not know how to link the sub-table to main table so that updating data in the main table automatically updates the sub-table data since both the tables will be containing exactly the same info. Also, since the info is for display purpose only, with no further processing, thought it convenient to let it remain part of the main table.

Am alos attaching the database in .zip format so that you can have a look at the actual structure, if possible.

Looking forward to your reply!
Attached Thumbnails
MS Access: Updating Data In One Field Should Update Data In All Relevant Fields-form.jpg  
Attached Files
File Type: zip Projects History Cards.zip (77.3 KB, 70 views)
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
21-Feb-2009, 09:11 AM #4
Attaching another image to clarify the problem - in pictorial way!!!!
Attached Thumbnails
MS Access: Updating Data In One Field Should Update Data In All Relevant Fields-form.jpg  
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
21-Feb-2009, 10:17 AM #5
I see what you are trying to do now and why it doesn't work.
You do not need a dlookup to populate those fields, you just need to include them in the Combo's SQL and then use some very simple VBA to put the values in the fields.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
21-Feb-2009, 10:31 AM #6
OK, I have completed the Project Title field for you.
I have added the required columns to the 2 Queries and the Combo.
I have added the VBA to the Combo's "After Update" event procedure so that you can see how it works.
Attached Files
File Type: zip Projects History Cards.zip (103.9 KB, 142 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
21-Feb-2009, 10:50 AM #7
In this version I have added a Combined Projects Table,Query and Form.
The new form is now a Sub Form with the Main Form's ID linked to the MainProjectID and a Combo to select the SubProjects that go with this main project.
You can now have as many Projects connected together as you like, however with this version ther is always a "Master" Poject that links them together, but only in one direction, i.e, the master lists all the sub projects. a Sub project won't list the current master or any of the other sub projects unless you make each one the master by selecting it on your main form and then selecting the others as sub projects .
Attached Files
File Type: zip Projects History Cards.zip (61.4 KB, 79 views)
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
21-Feb-2009, 07:26 PM #8
OBP - thanxxxxx a lot!

I'll be very honest. I went ahead with the first simple vba method and applied it to get the data successfully. I did check the second one with sub-form, but that exceeds my requirement for this particular database.

Am attaching the database again. The following code is now used for update event to populate all fields:

Me.[Project Title-1] = Me.Text433.Column(2)
Me.[Project Year-1] = Me.Text433.Column(3)
Me.[Project Budget-1 (Approved)] = Me.Text433.Column(4)

It works ok. The only problem - I have to click on the year ad budget fields to get the updated value unlike the Title field that appears instantly.

What should I do to get all the values updated/appear at the same time?

Regards
Attached Files
File Type: zip Projects History Cards.zip (67.4 KB, 90 views)
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
22-Feb-2009, 06:23 AM #9
Second problem (or probably the same stated differently):

Using the code above updates the then values after clicking on each box one by one. However, if I navigate away from record X, update something in a record (say title, budget etc.) that is supposed to be combined with X, coming back to the X I still find the old values. These are updated only if I select the same ref. no. from Project Ref combo box and then click on each box as before.

Any ideas???
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
22-Feb-2009, 07:43 AM #10
Yes, you have been naughty , your fields aren't actually called
Me.[Project Year-1]
Me.[Project Budget-1 (Approved)]
they are called
Combo306 (is the Year)
Text190 (is the Budget)

In my example I changed the name to the same as the Control Source.
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
22-Feb-2009, 08:06 AM #11
OPB. Thanks. That did the trick. I was putting the wrong field names.

The second problem however remains.

Using the code updates the then values. However, if I navigate away from record X, update something in record Y (say title, budget etc.) that is supposed to be combined with X, coming back to X I still find the old values. These are updated only if I re-select the same ref. no. from Project Ref combo box.

Hope this will be the last issue for now!!!!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,618 posts.
 
Join Date: Mar 2005
Location: UK
22-Feb-2009, 08:56 AM #12
Yes they will be because you are storing the Actual Text in those fields, not references to them. That is why you need a separate the table & Subform that I posted.
That only stores the ID numbers, so whatever changes you make to the data is always reflected in your Subform.
fkk's Avatar
fkk fkk is offline
Junior Member with 7 posts.
THREAD STARTER
 
Join Date: Feb 2009
Experience: Beginner In MS Access 03
24-Feb-2009, 11:26 PM #13
OBP. Thanx a lot for your very very cordial help in this issue . My laptop crashed two days back hence the delay in posting.

Though I've modified the form to work with update thru code for the time being, am learning to work with sub-tables/sub-forms now - that obviously is the more appropriate way out.

Will be posting back again when successful - or when encounter a problem - whichever comes first
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑