Form Shows blank when using Form wizard (bad relationship?)

[skylinekiller]

I am a novice Access user and am have a tiny database I am working on for a project. I have chosen to use a home inventory. I only have 3 tables, 2 forms, and 2 queries. Very simple right? I understand the (for me) complex stuff like using lookups, formulas to query certain dates, but I can't get by the portion of the forms.




When I try to use the form wizard and create a master form with all the tables, I get a blank form with just a title. I am not quite sure what the issue is. If I use the form wizard and create one form per table, it works. Can someone tell me why I am getting that blank form when I add all my fields from my tables when creating a form?


After several hours of watching Linkedin Learning videos, I am still not grasping what should be on the form and what relationship is required? I feel like the form should have every field I have in my tables on the form, and then it populates the tables with the inputted data. I know this is probably not correct. Can someone assist me? Hopefully, the hammer hits me hard enough the lightbulb comes on?

Are my table designs ok, is the table contents why I am having a hard time?

Any assistance is appreciated.

[Bob Fitz]

I do not understand your use of tables.
IMHO The fields you have in “ItemName” and “PurchaseInfo” should be in one table, perhaps named “tblItemDet”.
I would have a number of other related tables (one to many) for things like: Status, Condition, HomeLocation, BoughtFrom etc. with each of these tables having an Auto-Number Primary Key.
I would use these tables as Lookups (combo boxes) on a form that is bound to “tblItemDet”.
This will enable you to easily store the Primary Key values of the Lookup tables in “tblItemDet”.

I would advise you to review your knowledge on table and database design.
Also:
Adopt a “Naming Convention”.
Do not use special characters (e.g. #) in field names.
Do not use “Lookups” in tables. Use them on forms.

[skylinekiller]

I do not understand your use of tables.
IMHO The fields you have in “ItemName” and “PurchaseInfo” should be in one table, perhaps named “tblItemDet”.
I would have a number of other related tables (one to many) for things like: Status, Condition, HomeLocation, BoughtFrom etc. with each of these tables having an Auto-Number Primary Key.
I would use these tables as Lookups (combo boxes) on a form that is bound to “tblItemDet”.
This will enable you to easily store the Primary Key values of the Lookup tables in “tblItemDet”.

I would advise you to review your knowledge on table and database design.
Also:
Adopt a “Naming Convention”.
Do not use special characters (e.g. #) in field names.
Do not use “Lookups” in tables. Use them on forms.

Bob,
Thank you so much. I also agree with you I didn't quite understand the methodology to populate my tables.. the training I watched said every table should only have items that relate to the specific ID..

Anyhow, I think i will try to overhaul the tables and then see if I have issues with the form.

When you say "(one to many) for things like: Status, Condition, HomeLocation, BoughtFrom etc. with each of these tables having an Auto-Number Primary Key." Would this be on the right track?

If I am following your advice correctly, I was thinking to have the tables below:
Table - ItemLocation (I should place all my locations in here then use the form to lookup)? ItemLocationID as Primary key?
Table - Fixed to House (Yes or No options on this)

[Bob Fitz]

John

Take a look at the attached db as an example of the kind of set up that I was referring to.

[skylinekiller]

Bob,
Thank you, that really helped me understand how I need to use relationships. I see you got a little crafty and used the table/query for the brands. There will be new brands inputted and this doesn't allow a new brand to be inputted through the form. I tried to change "limit to list" to "no" but it gives me a column error. I like that it limits it to that table, but how can I be able to add a new brand? This is similar to a few other form fields. Can you elaborate on how to make that adjustment to use the brands in the table and add a new brand? The location can be limited to a list since my house only has so many locations.

The way you set up the tables and relationships really helped me comprehend the type of structure I should be going for. Thank you

[Bob Fitz]

Thank you, that really helped me understand how I need to use relationships. I see you got a little crafty and used the table/query for the brands. There will be new brands inputted and this doesn't allow a new brand to be inputted through the form. I tried to change "limit to list" to "no" but it gives me a column error. I like that it limits it to that table, but how can I be able to add a new brand? This is similar to a few other form fields. Can you elaborate on how to make that adjustment to use the brands in the table and add a new brand? The location can be limited to a list since my house only has so many locations.

The way you set up the tables and relationships really helped me comprehend the type of structure I should be going for. Thank you

You're Welcome. Always glad to help if I can

If you look carefully, you will see that I have used an SQL statement for all of the combo boxes. On the form you select, say "Samsung" but it is the Primary Key of the Brand table that is stored in the Items table. The number doesn't mean much to us humans if we look at the tables but it does to the database. Keep in mind that tables are for storing data rather than being looked at. Generally, use tables to store data, queries to recover data, forms to enter data and forms/reports to present data to the user.

In the attached db I have used some code in the "Not In List" event of the brands combo box that offers the user the choice of adding an unlisted brand to the list.

I would adopt this method for all the combo boxes.

[skylinekiller]

Thank you Bob, sorry for the delay. I was able to figure out the SQL and adjusted the VBA for another Field (Store). That helps a lot. I am surprised Access didn't make this option easier. I am just about done now, but when I input data into my form "tblDamaged" nothing saves and it doesn't appear on my record. Is this a relationship issue?

[mike60smart]

Hi John

Currently you have tblItems with various Lookup tables to record specific information about the Item

What are you trying to achieve with your tblDamaged ?

Are you trying to record details of items that belong to a specific person that are damaged?

[Bob Fitz]

Your form called "tblDamaged" would be better named as "frmDamaged".
The form would need to be bound (have its' Record Source property set to a table/query). Presumably that would be tblDamage and have a control (e.g. textbox, combobox, checkbox etc) bound to each of the fields in the table. This would tell Access where to store the data, which it would do automatically when you close the form or move to a new record which makes a "Save Record" button redundant.
However, your table "tblDamage" is both designed and related incorrectly.

All the things that I've mentioned in this post are among the the basics of using Access.
I would strongly recommend that you learn something of these basics.
There are many videos on You Tube that would be of benefit to you.

[skylinekiller]

Your form called "tblDamaged" would be better named as "frmDamaged".
The form would need to be bound (have its' Record Source property set to a table/query). Presumably that would be tblDamage and have a control (e.g. textbox, combobox, checkbox etc) bound to each of the fields in the table. This would tell Access where to store the data, which it would do automatically when you close the form or move to a new record which makes a "Save Record" button redundant.
However, your table "tblDamage" is both designed and related incorrectly.

All the things that I've mentioned in this post are among the the basics of using Access.
I would strongly recommend that you learn something of these basics.
There are many videos on You Tube that would be of benefit to you.

Bob,
I created the form "tblDamaged" just to practice creating a form to see if I can have a form that enters only items that were damaged and the individuals that damaged them. Basically, I just wanted to create a form and thought this might be a relevant source to record. You have answered my original question and I see myself going down a rabbit hole. Thank you again for your assistance. Oh, and I just realized my form name is using a table naming scheme ...

[mike60smart]

If you are trying to record details of items damaged then you would need tables as follows:-

tblPeople
-PeopleID - PK - Autonumber
-Firstname
-Surname

tblItemsDamaged
-ItemDamagedID - PK - Autonumber
-PeopleID - Number - FK (linked to PK in tblPeople)
-DamagedDate
-ItemID - Number FK - (linked to tblItems PK)