Issues saving form to table

[Danielt949]

Good afternoon all.

Please bare with me, i am new to access and may ask a few too many questions over the coming weeks.

I am having issues with saving a form to table "Tbl_New_Component"

So far in the build i have 3 tables

1. Tbl_Asset_Type
2. Tbl_Category
3. Tbl_Components

They correspond with the appropriate custom userforms

1. Frm_New_Asset_Type
2. Frm_New_Category
3. Frm_New_Component

Some people may ask why i have forms instead of entering the basic info into the table directly.... This is going to be a system in its completed stage where there will be people not knowing a single thing about access that will be using it, Eventually it will be set up as a dashboard and no access knowledge will be required.

The issue being had is that when i save the input into the "Frm_New_Component" it comes up with an error, If i go to the code and comment the line with error out, it then saves everything except for the combobox causing the problem.

I suspect this is an issue with the primary key, and collecting information from another table without reference to the primary key. However, I do not know how to rectify the problem i have. Can you please send me in the right direction.

I have reduced to database to its bare bones and then zipped it just to allow me to upload. Fingers crossed this will be enough for you to see what i am having issues with.
Ive also posted this into Excelforum into their Access section hoping someone can help there too https://www.excelforum.com/access-ta...ml#post5855643

Thanks in advance


CMMS - Copy.zip

[June7]

Instead of the query object as source for listbox, put SQL in RowSource:

SELECT Component
FROM Tbl_Components
WHERE Asset_Type=[ComboBox_Asset_Type] AND Category=[Combobox_Category];

Set ColumnCount to 1 and ColumnWidths to nothing.
Requery the listbox after selection in each combobox. Me.List10.Requery

Why use a big listbox if asset/category pair can return only one component?

Why use a query object as source for Category combobox RowSource - and doesn't have ID field? Why not:
SELECT Category_ID, Category
FROM Tbl_Category
WHERE Asset_Type = [ComboBox_Asset_Type]
ORDER BY [Category];


Set Asset_Type and Category fields in Tbl_Components as compound index to prevent duplicate pairs.

I never set lookup fields in tables.

I prefer to set controls to Null instead of empty string. I never allow empty string in fields.

[Danielt949]

Hi June7
Thanks for your reply.

Sorry, i am a little lost with what you mean with the above. I understand if you do not want to reply if it is going to be too much effort for a novice like myself.

When you say to try this SQL, I am imagining you are refering to the screen i end up when i right click on the query and go to the design view?
From there, i have selected in the field section, the 3 recommended selections as per above. Tbl_Components.Component, Tbl_Category.Category, Tbl_Asset_Type.Asset_Type

From here i am lost sorry, I do not know where to insert or select the FROM, WHERE and ORDER BY
Please correct me if i am wrong. when refering to the query designer.
SELECT would be the Field:
FROM would be the Table:
WHERE would be the Criteria:
ORDERBY would be the or:

i understand the requery section

You say.... Why use a query as source for Category combobox RowSource and doesn't have ID field? Why not:
I dont understand this, are you refering to a better way of doing things? or its ok the way i am doing it?

Thank you kindly for your patients.

Cheers

[June7]

Just copy/paste those SQL statements directly into combobox RowSource. Adjust ColumnCount and ColumnWidths properties as needed.

You need the ID field for saving to record.

[Danielt949]

Thank you, upon further inspection, the Rowsource titles i was asking above are actually built into the code, i know understand what is meant by the items you have in capitals. When doing things via wizards i missed that.

Am in the process of copying and understanding what i am pasting from your supplied information.

Thanks again

[Welshgasman]

O/P had advised of crosspost, and I missed it.
My apologies.

https://www.excelforum.com/access-ta...ml#post5858805

[Micron]

Interesting. The link in post 1 and in post 6 both land on a page where the url ends in 1409821 yet I can only access the link from post 1. Post 6 link tells me I cannot view because I'm not a member. There must be a couple of characters in there somewhere that differentiate between members and non-members, and this is not the first time today that I've come across this sort of oddity.

[Welshgasman]

Interesting. The link in post 1 and in post 6 both land on a page where the url ends in 1409821 yet I can only access the link from post 1. Post 6 link tells me I cannot view because I'm not a member. There must be a couple of characters in there somewhere that differentiate between members and non-members, and this is not the first time today that I've come across this sort of oddity.

I have since amended my post in both forums and amended here with new link. That works for me.