Records stored and displayed in a form

[ArielSha]

Dear Access experts!
We are trying to upgrade and update an old database of our archaeological excavation. We are using access 2013, although the database originally was designed more than 10 years ago.
There are three main tables that we use:
Main_Locus_Str_Table
Locus (number, PK)
Type of locus (list box with two values: LOCUS;WALL)
And a few other number or text fields

Locus_Card_Table
Locus (number, PK)
Definition (text field)
Date (date/time field)
And a few other number or text fields

Memos_Table
Locus (number, PK)
Description (Text field)
Matrix (Text Field)
And a few more text fields.

We use a simple form to type in data into the Main_Locus_Str_Table, assigning each record with a unique locus number (in the “locus” field) and defining it as either a WALL or a LOCUS in the Type_of_Locus field.

There is a join query (Locus_card_update_query) that filters only records that have the value “LOCUS” in the “Type of locus” field in table Main_Locus_Str_Table. It then also displays the data from records in Locus_Card_Table and Memos_Table that has the same number in the “locus” number field.

A form that we use to input data into the Locus_Card_Table and Memos_Table uses this query as a raw data source. The issue we encountered is that when we type the new data into the fields in the form, it is saved into the correct tables (as it should), but when we close the form and reopen it, it doesn’t show the data already put in. In other words, the form saves the data into the tables, but it doesn’t show the records already in.
The form actually shows data that was typed in last year, in 2015, but for some reason it doesn’t show the new data of 2016, although we can see the data is saved into the tables.

I hope I supplied you with all the data you might need to try and help us!
Thanx in advance to anyone who can help!!

 

[sneuberg]

Something is not right with the record source of the form. It would save a lot of time if you could zip the database and upload it. If you can' do that then post the SQL of the record source query if the record source is a query. If it's not let us know what it is.

 

[ArielSha]

Hi Sneuberg and thanks for the fast response!

Here is the SQL of the query that is used as source for the relevant form:

SELECT Main_Locus_Wall_Strata_Table.AREA, LOCUS_CARD_TABLE.LOCUS, LOCUS_CARD_TABLE.DEFINITION, Main_Locus_Wall_Strata_Table.Stratum, Main_Locus_Wall_Strata_Table.UNIT, LOCUS_CARD_TABLE.[FINAL LOCUS], LOCUS_CARD_TABLE.GRADE, Main_Locus_Wall_Strata_Table.Squares, Main_Locus_Wall_Strata_Table.[DATE OPENED], LOCUS_CARD_TABLE.[DATE CLOSED], LOCUS_CARD_TABLE.CONTINUED, LOCUS_CARD_TABLE.[UPPER LEVEL], LOCUS_CARD_TABLE.[LOWER LEVEL], LOCUS_CARD_TABLE.[FLOOR TYPE], LOCUS_CARD_TABLE.[FLOOR DIRECTION A], LOCUS_CARD_TABLE.[FLOOR LEVEL A], LOCUS_CARD_TABLE.[FLOOR LEVEL B], LOCUS_CARD_TABLE.[LOCI ABOVE], LOCUS_CARD_TABLE.[SEALED ABOVE], LOCUS_CARD_TABLE.[LOCI BELOW], LOCUS_CARD_TABLE.[SEALED BELOW], LOCUS_CARD_TABLE.[MEREG WITH], LOCUS_CARD_TABLE.CLOSED, LOCUS_CARD_TABLE.[SECOND SORT], LOCUS_CARD_TABLE.[POTTERY DRAWN], LOCUS_CARD_TABLE.[OBJECTS DRAWN], LOCUS_CARD_TABLE.[POTTERY REG], LOCUS_CARD_TABLE.RESTORATION, LOCUS_CARD_TABLE.[RESTOR DONE], LOCUS_CARD_TABLE.[SECTION NO], LOCUS_CARD_TABLE.[ON PLAN], LOCUS_CARD_TABLE.[WRITTEN BY], LOCUS_CARD_TABLE.[Updated by], LOCUS_CARD_TABLE.[Update Date], LOCUS_CARD_TABLE.CANCELLED, LOCUS_CARD_TABLE.DONE, LOCUS_CARD_TABLE.[Written on], Memos_Table.Borders, Memos_Table.[Reason for opening], Memos_Table.[Reason for closing], Memos_Table.[General description], Memos_Table.[Description of matrix and finds], Memos_Table.Stratigraphy, Memos_Table.[Equivalent Loci], Memos_Table.[Future work], Memos_Table.[Special finds], Memos_Table.[Periods represented], Memos_Table.Update
FROM (Main_Locus_Wall_Strata_Table INNER JOIN LOCUS_CARD_TABLE ON Main_Locus_Wall_Strata_Table.Locus = LOCUS_CARD_TABLE.LOCUS) INNER JOIN Memos_Table ON LOCUS_CARD_TABLE.LOCUS = Memos_Table.Locus
WHERE (((Main_Locus_Wall_Strata_Table.[TYPE OF LOCUS])="LOCUS"))
ORDER BY LOCUS_CARD_TABLE.LOCUS;

 

[sneuberg]

Nothing jumping out at me from looking at the query. I suggest creating a query from the the record source SQL run that query and see what it displays. If it is also missing the records that you were seeing in the tables you can pick it to pieces until you figure out why. One thing to look for is whether the fields that join the two table have values for these new records.

If this query displays the missing records then check the filter property of the form.

It would really help me figure this out if you could upload the database.

 

[ArielSha]

OK, I'm trying to send it zipped. Hope I did it right.

I just noticed that there is a similar or a bit different issue with the form that we used to enter data into these records that are defined as walls.
I'm not exactly a programmer (far from it) so there are a lot of tables, queries and macros there that I don't understand what they do exactly.
Unfortunately the guy who built this database was older than his database and he is no longer working with us. I tried to improve its graphics and user-friendliness and probably caused a bit of damage in the process...

View attachment ABM Area A 2016 new.zip

 

[sneuberg]

The reason why the records are not showing up is because there's no related record in the Memos_Table. I'm not seeing how the it is suppose to be populate with the Lotus Number. I was looking for the memos form to be in a subform where the lotus number could be propagated through the link fields but that's not the case.

This database needs a lot of work. A user shouldn't have to remember the locus number he entered in the Main locus stata - data entry form so that he can add the related records. Unless there's some good reason not to I suggest you consider consolidating the data in these one-to-one related tables in to one table.

 

[ArielSha]

I did as you suggested and consolidated the Memo_Table with the Locus_Card_Table and with the Wall_Card_Table, so there is no one-to-one relationship. It seems to have fixed the problems with the Locus_Card form and Wall_Card form, but we will try working with it in real-time tomorrow at the field and see if everything runs smoothly.

Thanks a lot for your quick and helpful advice!!

ABM team