Hi All,
I am currently working for a UK environmental charity. They have an Access 2007 database populated with 150,000 records of property addresses and details of energy efficiency measures. Each record has a unique 8 number code (UPRN) which is used as the primary key but this is not an auto number field, this main table is called “RESIDENTIAL”
I want to create a related table to contain information about water saving measures. Only around 15,000 addresses will be have water saving information recorded so I don’t want to put it in the main table which will then be filled with mostly blank fields.
The main form based on the RESIDENTIAL table is called “ResidentialForm” this has a button which opens up a second form called “WaterForm” which puts data into a related table called “WATER”.
The related link between tables will be the UPRN number. I have put the following code on the button, the intention is when looking at a property record clicking the button puts the UPRN in the UPRNWater field of the newly opened WaterForm and when completed can be saved. Similarly looking at an old record and clicking the button should open up a related WaterForm for editing.
Well I am new to coding and I can’t get the code to work properly it currently just overwrites a single record in the Water Table. I hope this makes sense. Any advice would be much appreciated.
Cheers, David
*************************
Private Sub Command336_Click()
If Me.NewRecord Then
DoCmd.OpenForm "WaterForm", acNormal, , , acFormAdd
[Forms]![WaterForm].UPRNWater = [Forms]![ResidentialForm].UPRN
Else
'open form in Edit mode if old record
DoCmd.OpenForm "WaterForm", acNormal, , , acFormEdit
[Forms]![WaterForm].UPRNWater = [Forms]![ResidentialForm].UPRN
End If
End Sub
I am currently working for a UK environmental charity. They have an Access 2007 database populated with 150,000 records of property addresses and details of energy efficiency measures. Each record has a unique 8 number code (UPRN) which is used as the primary key but this is not an auto number field, this main table is called “RESIDENTIAL”
I want to create a related table to contain information about water saving measures. Only around 15,000 addresses will be have water saving information recorded so I don’t want to put it in the main table which will then be filled with mostly blank fields.
The main form based on the RESIDENTIAL table is called “ResidentialForm” this has a button which opens up a second form called “WaterForm” which puts data into a related table called “WATER”.
The related link between tables will be the UPRN number. I have put the following code on the button, the intention is when looking at a property record clicking the button puts the UPRN in the UPRNWater field of the newly opened WaterForm and when completed can be saved. Similarly looking at an old record and clicking the button should open up a related WaterForm for editing.
Well I am new to coding and I can’t get the code to work properly it currently just overwrites a single record in the Water Table. I hope this makes sense. Any advice would be much appreciated.
Cheers, David
*************************
Private Sub Command336_Click()
If Me.NewRecord Then
DoCmd.OpenForm "WaterForm", acNormal, , , acFormAdd
[Forms]![WaterForm].UPRNWater = [Forms]![ResidentialForm].UPRN
Else
'open form in Edit mode if old record
DoCmd.OpenForm "WaterForm", acNormal, , , acFormEdit
[Forms]![WaterForm].UPRNWater = [Forms]![ResidentialForm].UPRN
End If
End Sub
