Another case of a form going blank (can't view existing records)

djmartin999

New member
Local time
Today, 21:28
Joined
Aug 9, 2012
Messages
3
Hi all,

I've just introduced myself to the forums in the relevant forum area, but thought I'd introduce myself here too to explain my situation:

I am a newbie to Access, although I pick stuff up relatively fast - I'm trying to migrate my small business' excel sheets into a central location via a database. At the moment its an asset list of equipment we own, though I hope to expand it in the future.

The problem I'm having, is that when I enter records via the form, I cna only view the records which have been entered via the form (not through the table). When I update the form (adding extra fields as i've only just started developing the database), it does not allow me to see any records that were not entered in that "iteration" of the form, but the data still goes into the table. I've read the previous forum entry which states that in the form properties Data Entry should be set to "No" and that Allow Additions should be set to "Yes".
Spent a couple of hours on this and have not managed to get it to resolve itself.
I've attached the database, it currently has no confidential data in it.

Looking forward to your collective help!
 

Attachments

Your form was based on a query that included several other tables. When entering a new record, if you do not select an item from each of the combo boxes bound to those other tables, SQL cannot make the join and thus the main record is not returned by the query.

In this case, I would base the form strictly on the equipment items table, please see the modified database (attached).
 

Attachments

Thank you very much jzwp22! I understand the logic of your reply, but I do not see what you have actually changed to make it work! I really appreciate the help; any chance you could tell me what buttons you pressed / options you selected to make it work?

Kind regards,
Martin
 
Open the equipment form in design view then open the property sheet. Go to the Data tab and then look for the Record Source property.

The Record Source property in your original database was this:

SELECT [Equipment Items].*, Category.Category, Category.ID, Manufacturers.Manufacturer, Manufacturers.ID AS ID_Manufacturers, Condition.Condition, Condition.ID AS ID_Condition, Suppliers.[Supplier Name], Suppliers.ID AS ID_Suppliers, [Storage Location].[Storage Location], [Storage Location].ID AS [ID_Storage Locations], [PAT Testing Class].[PAT Testing Class], [PAT Testing Class].ID AS [ID_PAT Testing Class]
FROM [PAT Testing Class] INNER JOIN ([Storage Location] INNER JOIN (Suppliers INNER JOIN (Condition INNER JOIN (Manufacturers INNER JOIN (Category INNER JOIN [Equipment Items] ON Category.ID = [Equipment Items].[Category ID]) ON Manufacturers.ID = [Equipment Items].[Manufacturer ID]) ON Condition.ID = [Equipment Items].[Condition ID]) ON Suppliers.ID = [Equipment Items].[Supplier ID]) ON [Storage Location].ID = [Equipment Items].[Storage Location ID]) ON [PAT Testing Class].ID = [Equipment Items].[Pat Testing Class ID];


I changed it to this:

SELECT [Equipment Items].*
FROM [Equipment Items];
 

Users who are viewing this thread

Back
Top Bottom