Lookup value in another record and return values

[bigroo]

Hi All,



My database consists of 40,000 AM/FM Radio Station records.

Within each record there is a FacilityID which is unique to that station, within that record I have fields for Format (Program type) and Slogan (On air Moniker).

Some stations on FM within the States have translators or repeaters on different frequencies so their main signal is extended to these areas where the main signal is poorly covered, these translators also have a facility id. I have a list that shows the main station with its facility id and with that list, it shows the other stations it re-transmitts on, this is called the Associated ID. I would like to use an update query to update the database rather than manual one record at a time.

What I want to do is enter the associated id value in a text box on a form and go and lookup under the facility id field of the parent station within the same table and return the format and the slogan field to the repeater record.

Someone on another list presented this code as follows.

Private Sub AssociatedID_AfterUpdate() 
If DCount("*", "tblStations", "FacilityID=" & Me.AssociatedID) > 0 Then 
   Me.Format = DLookup("Format", "tblStations", "FacilityID=" & Me.AssociatedID) 
   Me.Slogan = DLookup("Slogan", "tblStations", "FacilityID=" & Me.AssociatedID) 
Else 
   MsgBox "You have entered an invalid Associated ID that does not match any Facility ID", vbOKOnly, "No Updates to Format/Slogan!" 
End If 
End Sub

This code works fine as you type in the associated id number in the record, it does return the Format and Slogan data from the parent record as wanted.

The issue I am finding is I close down the database and come back into same of these records, its not holding the data in the format or slogan fields. I have found if I re-enter the associated id number back in the field again, all is good.

The main reason to do this is to save time. If the parent station changes its format from Country to Classic Rock, it will mean I will need to go to each record they re-transmit on and update the format there, the dlookup expression will update these records as the parent record is updated.

Is there another solution to the above that once the associated id is entered it will present the data in format and slogan when viewing the record at a later date?

I hope this all makes sense!

[Robeen]

Is all the data in ONE table - the Facility ID AND the Associated ID?
Could you explain in different words what you need to do?

Perhaps you could give us the field names in your table?
And maybe a screenshot of some of your data??

Maybe I'm just being slow - but it might help me if I see your problem explained briefly - using different words.

[bigroo]

Description.zipHi Robeen,

Thanks for the reply. Sorry for not getting back to you yesterday.
I have tried to explain a bit clearer with an excel attachment outlining the two table grids and what I wish to accomplish, I sure do hope it explains it better.

Let me know if its clear or you have further questions.

Thanks

[June7]

Would be nice to review the project to see what is really going on. Can attach it as easily as that spreadsheet. Follow instructions at bottom of my post.

[bigroo]

Hi June7,

I would love to attach the database but at 100mb, a bit difficult since 2mb is the zip limit. Also, to strip it down would be hard but not impossible!
Thanks

[June7]

100mb is a big file. Even after running Compact & Repair? Making copy and removing records leaving a few for testing is too difficult? You could try uploading to a fileshare site such as box.com. I don't remember if there is a size limit for free account.

So when you re-enter it saves properly? I really can't figure this out from the information given.

[bigroo]

Hi June7,

So when you re-enter it saves properly? I really can't figure this out from the information given.

Yes, re-entering the number in the assoicated id field saves ok?

Any clue what to look for?

[June7]

No idea about cause. Have you step debugged?