Allowing combo box to accept blank entries

[Mezta1988]

Hi,

I have a form with many combo boxes, and the problem is, if I leave one of any of those combo boxes as blank then I get an error message says.

You cannot add or change a record because a related record is required in the table 'Table Name'.

Is there any solution to save an entry even if I leave some of the combo boxes as blank.

Sorry guys I'm really new to MS ACCESS.

 

[isladogs]

It sounds like all the fields have been set as REQUIRED in the table design.
If you want to allow blanks in some/all fields change to REQUIRED =No.
Obviously your primary key field(s) have to stay as required fields

 

[Mezta1988]

Hi,

The Required parameter is already set to "NO" I just checked now. Is there any other setting that I might need to change to solve this issue?

 

[GinaWhipp]

Hmm, sounds like you are using a query as the Recordsource of your Form and you are trying to enter one side of one of Tables where they is no matching record in the other Table.

OR

You only have one Table as your Recordsource but because of a Relationship it won't allow an entry on the MANY side with a record on the ONE side.

No matter which one that has to be fixed first. If you tell us which one we can better help with a solution.

 

[Mezta1988]

Here a glimpse on how I connect my tables.

Tables
1. DeviceList
2. DeviceType
3. Employee Details

DeviceLists Table
*ID
*DeviceName
*Brand
*DeviceType -------- Connected to my device type table
*ModelNo
*SerialNo
*EmpID ------- referenced to my employee details table.

that is the summary on how I connect my tables.

 

[theDBguy]

Can you post a screenshot of your form?

 

[isladogs]

... and another with the relationships between the tables

 

[Mezta1988]

Hi,

I attached three files which are captured from my MS ACCESS database. I just want to make the data type as optional and able to save the entry even if the devicetype field is empty.

 

[isladogs]

Did you mean the 'device type' should be optional?
If so, I think you need to remove the lookup table from your query
Alternatively it might work changing the inner join to outer but I'm not at my PC to test that suggestion.

If neither if those help then please post a screenshot of some data.
Hide anything that's confidential.

 

[Mezta1988]

Yes, the "Device Type" is one of those lookup field which is preventing entries to be saved on the table if I leaved it blank. Actually I have the same issue on my other forms which I did not disclose it here since their having the same problem, what I mean by that is, if we solve the problem on my "DeviceLists" form then I can apply the same process to the other form.

 

[isladogs]

OK so have you tested my suggestions?

Also I wonder whether you really need all those lookup tables?
Gender seems superfluous … and possibly others

 

[Mezta1988]

I think you need to remove the lookup table from your query
Alternatively it might work changing the inner join to outer

I am not sure if I get your suggestion clearly. Because as I have stated above, all the record source are coming from the table and not from the query. I can send you the whole database if you want but not here since it only allows 2mb max data size.

 

[isladogs]

I am not sure if I get your suggestion clearly. Because as I have stated above, all the record source are coming from the table and not from the query. I can send you the whole database if you want but not here since it only allows 2mb max data size.

You've now confused me as well. You posted the design view of a query which I thought was used as the record source for your form????

If you want to post a stripped down copy of your database here, someone will look at it -possibly several people. Don't send it to me direct.
To do so, make a copy of your dB, remove all irrelevant items, remove all but a handful of records that are enough to show the issue.
Check it compiles than compact, zip and upload
When you do so, tell us what exactly we need to do to see the issue and what you want to happen instead

 

[Mezta1988]

I don't mean to confuse anyone I'm just trying my very best to illustrate my point, I just thought it might be helpful if I post some the important part of the database so you all can get my point quickly. And I think the best way to illustrate this is to have guys check the database file, which fortunately I manage to attach here after I delete some of the table, queries and other forms.

 

[isladogs]

OK - I've made a few changes (attached) and the form now works fine

First of all I compacted it - reduced to 20% of size!

1. Gender & EmploymentStatus tables - DELETED (not needed)

2. Renamed objects removing spaces:
Office Location table => OfficeLocation - to remove space
Employee Details table => EmployeeDetails
Employee Details for form query renamed as qryEmployeeDetails

3. EmployeeDetails table
Changed Gender to Text with field size 1
Removed EmploymentStatus
Added Active Yes/No (in place of EmploymentStatus

Removed FullName - calculated field not needed - - concatenate as needed

EMailAddress - changed to text - hyperlink fields often cause problems

Photo - strongly recommend changing from Attachment to Text field and just store the file path in the database. Attachment fields cause significant file 'bloat'

4. Updated Relationships for new object names
Are you sure the arrow should start at DeviceList - unable to tell without your data

5. Updated form in line with above changes. Now works perfectly

 

[Mezta1988]

Thank you very much for your help, but unfortunately, I cannot check it right now because I'm out of the office. I will message you back as soon as I check the file and of course if I have another thing to clarify

 

[Mezta1988]

Thank you @isladog for helping me out to get rid of those errors. I checked out the new file and it worked perfectly fine. But there is one thing that I noticed, I tried to enter a record but leaving one of the combo box blanks and when I click save it didn't appear on the list box below. But when I open the employee details table the entry was there but it didn't also appear to the query table.

 

[isladogs]

Can you be more specific. Which combo(s) are causing an issue?

 

[Mezta1988]

Inside the Employee Details form, if leave one of these combo box blank such as the Department, Position or the Office Location and hit save the record is not showing on the ListBox below even on the query. But it appears on the database table.

 

[isladogs]

Thanks. Easily fixed.
I've changed the query to outer joins so any or all of the drop downs can now be left blank