First record in table being overwritten (1 Viewer)

[daveUK]

Hi Guys

I'm hoping you can help me with this as it's driving me NUTS

The database stores info about which offices within each region have been assessed by which assessor.

I have four tables

TblRegion - stores the regions
TblOffice - stores the offices within each region (lookup field in TblRegion)
TblAssessor - stores the assessor names and occupations
TblSite - stores the assessor name, region, office and date of assessment (llokup fields to TblAssessor, TblOffice)

I have a search form, which allows users to search by assessor name, region, office loaction or date. This uses a simple query and displays the results in a report.

The problem is when I close the form, the name of the assessor in the first record in TblAssessor is overwritten!! And it seems to be overwritten by the name of the first result of the query. And this only happens after the search form is closed. If the form remains open, the record isn't overwritten

The form desn't have any code on the Close event except Docmd.Close

e.g. the first record in TblAssessor is Joe Bloggs. I run the query and the first result of the query is John Smith, when I open TblAssessor the name has changed from Joe Bloggs to the record number of John Smith (i.e. if John Smith is record number 12, then '12' will appear instead of Joe Bloggs). If there are no records for the search criteria then Joe Bloogs is overwritten by '0'.

Sorry if I haven't explained this too clearly

Dave

 

[FoFa]

OK, First is it truly being over written (you can not judge this by it being the first record on display as the order it displays records can change). If you verify it is actually over writting the record (an not changing the display order) then what most likely is going on is you are over writting the current record on the form with the search results, and when you close the form Access updates because it has detected changes. When you return a search results, you need to MOVE to that record rather than just populate the current fields with that data as what it sounds like is going on. But I am guessing.

 

[daveUK]

Thanks for the reply.

I understand what you're saying about moving to the records rather than just populating the current fields, but I'm displaying the results in a report not in a form and only some of the fields from TblAssessor are displayed within the report - these aren't overwritten, just the name