Lookup table value must be populated

[ST4RCUTTER]

I have a table called tbl_workorders. One of the fields on this table is optional but because the lookup table is on the one side of a 1-to-many relationship using an auto-number key it is requiring that this field be populated to create a record on the workorder table. This is a problem because the field is optional.

tbl_workorders
WorkOrrderID(PK)
Field1
Field2
Field3
Field4
JobSubtypeID (FK-many) <---- Lookup field


tbl_Subtypes
JobSubtypeID (PK-one)
SubtypeDesc


Should I set the SubtypeDesc field to a default value of "none"? I know this is storing unnecessary data, but is there another option?

 

[gemma-the-husky]

you could do that, or you could change the join type to NOT enforce referential integrity

 

[RuralGuy]

Or maybe even revise the design to not use the evil lookup field in your table.

 

[ST4RCUTTER]

I am not opposed to avoiding lookup tables and instead using forms, but how do you avoid using tables with a constantly updating data source such as a table that is maintained by another department which contains hundreds if not thousands of records? This is not the case with this lookup, but I have other lookup tables in the same database that do represent data that is not a simple list.

 

[RuralGuy]

Is this link of any help to you?

 

[ST4RCUTTER]

you could do that, or you could change the join type to NOT enforce referential integrity

Hmmm...tried this tonight to no avail. I still receive an error message indicating that the Jet engine could not find a record in the lookup table that matches the field in the workorder table. So basically, if I don't select a value for this field through code or manually then the record cannot save, referential integrity or not.

 

[ST4RCUTTER]

Is this link of any help to you?

The problem I see with this methodology is that for each record you are storing the same value from the form's list over and over again. For example, if I create a combo box and type in the values to be available, each time a record is created the user selects a value from the list. This value then gets saved to the record. This is not a big deal when you have 10 records, but if you have 5,000 records then you have just stored this value 5,000 times instead of 1 time on the lookup.

 

[Moniker]

You store the lookup reference to the value, not the value. For example:

ID   LookupValue
1    Dallas Mavericks
2    Phoenix Suns
3    New Jersey Nets
4    San Antonio Spurs
5    Utah Jazz
6    Boston Celtics

You store the number 1-6 multiple times, not the team names.

 

[wazz]

in tbl_workorders, does JobSubtypeID have a default value? pre-access 2007 a zero is added automatically. if you delete that (clear the default whatever it is, if there is one) does it help? also make sure you're following what moniker said.

 

[gemma-the-husky]

to return to my post

if you have checkked referential integrity then you HAVE to have an entry in the lookup table

if you havent checked it, you can import anything, although some values may show the dereferenced lookup as blank

 

[neileg]

You need to remove the table level lookup.

 

[ST4RCUTTER]

You store the number 1-6 multiple times, not the team names.

Right, and I get that...this is exactly how a lookup table works, but you are suggesting that on the form I add the combo box and that in the Combo box Wizard I specify 2 columns and enter in a column with ID's and in the second column the value to be referenced. When a selection is made by the user the number in the first column (ID) is stored vs. the value in the second column. The problem here is that with some of my data there are hundreds or even thousands of possible values. I cannot manually entere thousands of records...this is expecially true if the values are ever-changing, such as a list of network devices.

 

[ST4RCUTTER]

to return to my post

if you have checkked referential integrity then you HAVE to have an entry in the lookup table

if you havent checked it, you can import anything, although some values may show the dereferenced lookup as blank

Thanks for hanging in there with me Gemma-the-husky, I have removed the referential integrity checkmark and verified that there are no default values specified in the field properties. This is apparently not what is causing the problem.

 

[ST4RCUTTER]

I found the problem.

It was actually quite simple as these things often are. All my forms are based off queries. This makes adding new content to an existing form much easier. That said, my query was setup with an improper join. It was only showing records where the fields on both tables were equal. This won't work for an optional field. By adjusted the join to a left-sided one, the form was able to represent relationships where a matching value was not on both tables.