Updating Data In One Field Should Update Data In All Relevant Fields (1 Viewer)

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 want 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 giveing syntex errors etc.

Will appreciate help from fellow members.

Woulden't an UpdateQuery or VBA recordset update be more efficient?

As to your syntax error, put [] brackets around "[Master Project History Table]"

Spaces in tablenames and fieldnames isen't a good ide. This is one reason.

JR

Thanks for reply.

Checked after putting brackets around 'Master Project History Table'.

Results in Run-Time Error 3075 / Syntex error (missing operator) in query expression '[Project Ref]='

Tried the following options:

1. Making Project Ref-1 and Project Title-1 both as unbound text fields.
2. Making Project Ref-1 as combo box with query to select value and Project Title-1 as unbound text fields.

Please help!!!

What is the content of Forms![Master Project History Form]![Project Ref-1]

Brian

Forms![Master Project History Form]![Project Ref-1] is a combo box selecting value from a query.

I asked what the content was as if it is not purely numeric you may need extra syntax such as " "

Brian