Select data by code (1 Viewer)

[rondon]

I had placed this thread in another forum but I don't think I was making myself clear as it is most likely a coding solution. I have attached a drawing to assist in the explanation.

I need a user who has no coding experience to be able to update a maintenance table(tblAgency) in the front end of the database. Then code in an After Update event in frmAgencyAssigned to insert a value into another field in the form. I would like to do this by firstly creating a maintenance table called tblAgency

Then in another table called tblAgencyAssigned the field AgencyName is selected by a lookup from tblAgency

When the user selects the AgencyName in the example below HomeCare, this runs the code in the fields After Update Event. The Sector value is selected from the SectorName associated to HomeCare in the tblAgency (above)

I will need help with the code I hope this is clear if not please ask me to expand
Thanks Ron

 

[arnelgp]

1. add the SectorName to the Rowsource of the cboAgencyName:

select AgencyName, SectorName From tblAgency

2. add code to the AfterUpdate Event of the cboAgencyName:

private sub cboAgencyName_AfterUpdate()
If Me!cboAgencyName = "HomeCare" Then
Me!txtSectorName = Me!cboAgencyName.Column(1)
End If
end sub

 

[rondon]

Sorry arnelgp I cant quite understand step 1 I have done as advised above can you check if I have understood this correctly

I entered the code and it works when I enter Homecare but not any of the other values. If the user selects DoH it would need to populate with Mobilty etc. If I do it this way do I need to hardcode in each agency name

Thanks Ron

 

[arnelgp]

if tblAgency only holds 1 sectorname per 1 agencyname:

private sub cboAgencyName_AfterUpdate()
If Not IsNull(Me!cboAgencyName) Then
Me!txtSectorName = Me!cboAgencyName.Column(1)
End If
end sub

 

[rondon]

Again thank you for sharing you knowledge, it worked very good
Thanks Ron

 

[arnelgp]

you"re welcome!

 

[Pat Hartman]

SectorName does not belong in the tblAgencyAssigned table. To see SectorName on the form, join tblAgencyAssigned to tblAgency and select SectorName from tblAgency as well as whatever columns you want from tblAgencyAssigned. Use this query as the RecordSource for the form. For safety sake, set the locked property of the control bound to SectorName to Locked to prevent accidental updates.

NO CODING REQUIRED. Plus, tables are properly normalized.