#Type! error driving me crazy!

[June7]

Ooops, I took another look at tblAutoModels. I was in too much of a hurry to go somewhere.



tblAutoModels does have the fESID_ID field as foreign key. Then the subform query would be:

SELECT tblAutoModels.*, tblEngineSizes.fEngineSize FROM tblEngineSizes RIGHT JOIN tblAutoModels ON tblEngineSizes.fESID = tblAutoModels.fESID_ID;

Still need to set primary key in tblEngineSizes.

Remove fEngineSize from tblAutoModels.

Normally, the autonumber PK and related FK ID fields do not have meaning to users and do not need to be on forms or reports.

Why do you have fEngineCode field?

[N1755L]

Didn't notice your reply yesterday, hadn't noticed there was a second page... Off to work, will get back this afternoon.

Thank you!

Ooops, I took another look at tblAutoModels. I was in too much of a hurry to go somewhere.

tblAutoModels does have the fESID_ID field as foreign key. Then the subform query would be:

SELECT tblAutoModels.*, tblEngineSizes.fEngineSize FROM tblEngineSizes RIGHT JOIN tblAutoModels ON tblEngineSizes.fESID = tblAutoModels.fESID_ID;

Still need to set primary key in tblEngineSizes.

Remove fEngineSize from tblAutoModels.

Normally, the autonumber PK and related FK ID fields do not have meaning to users and do not need to be on forms or reports.

Why do you have fEngineCode field?

[N1755L]

... Still need to set primary key in tblEngineSizes.

Remove fEngineSize from tblAutoModels.

Normally, the autonumber PK and related FK ID fields do not have meaning to users and do not need to be on forms or reports.

Why do you have fEngineCode field?

Ok, the "real" table is tblAutoModels, which contains all the data about the auto models, including each model's engine size, so the field has to be in this table. The only reason I added this again in the tblEngineSizes table is because sometimes, in the AutoModels table, the model's engine size might be entered as a letter, rather than as 1.8L or the like. As a result, the EngineSizes table is there to "convert" the letter to a Litre, or Cubic Inch value. This is an adminstrative table, not for the users.

Since I don't know how many letter sizes codes there might be, or if there might be double-letter engine sizes codes, this second table (tblEngineSizes) is where all these special code engine sizes are entered (this would be an administrative function), along with the corresponding standard size in litres or cubic inches... the idea being that now when a user enters an engine size in the AutoModels table as a letter value, or whatever other non-standard engine size notation they use, the form will cross-reference the EngineSizes table and in a field just to the right of where the engine size has been entered (as a letter, likely), the standard litre size will be displayed. So if you look at frmAutoModels in View mode, you notice this conversion field just to the right of the EngineSize field/column, which is where, if a letter was entered as engine size, this field would show the converted engine size in a Litre value. I haven't yet figured out how to get this part to work.

If I knew for certain that there are only, or can only be say, three letters that could potentially be used to describe the engine size, then I could just code directly those three possible scenarios in the form and be done with it, but since I'm not certain, now any number of engine size codes and their equivalent conversions can now be added to the table and cross-referenced whenever a user uses anything other than a straight-forward litre or cubic inch value for the engine size.

This is beautifully accomplished by the first query SQL you gave me yesterday, wherever there is a letter entered in the fEngineSize field of the tblAutoModels table, the converted value (retrieved from the tblEngineSizes table) is displayed in that query you suggested... just lovely!

What you've given me is perfect. The only problem I have now is that I can't seem to bring that query value to my form, as I mentioned above... but I think that's the subject of another thread, or is it? Should I start another thread to resolve this part of the form functionality?

[N1755L]

I tried an unbound text box, and using the Expression Builder, got the following:

=[qryGetEngSize]![tblEngineSizes]![fEngineSize]

This results in a #Name? error. Not sure how to retrieve that engine size value from your query, into my form.

[June7]

This all hinges on which field is the primary key in tblEngineSizes.

If you want the autonumber to be primary key then that should be the value saved into tblAutoModels as foreign key.

If you prefer to use the alphanumeric fEngineCode as PK/FK then that value should be saved as FK in tblAutoModels.

Regardless, still need to designate a primary key in tblEngineSizes and no need for fEngineSize in tblAutoModels.

The form query should link on the PK/FK fields and all related info would be available for pulling down to the query field grid. Then the fields should show up in the textbox ControlSource dropdown list.

[N1755L]

Hello again June7,

I'd already had added the fEngineCode field as a PK.

I have to read your reply over many times yet and experiment. From what you suggest, it seems to me as though you've misunderstood what I am trying to do, but it's just as well likely that I don't yet understand your solution.

Will look everything over.

Thanks again for your help.

[June7]

The version of your db I have has fESID_ID field in tblAutoModels. It is a number type. This means would save the fESID value from tblEngineSizes as the foreign key.

If you want to save fEngineCode as foreign key in tblAutoModels then field fESID_ID has no purpose in tblAutoModels.

I understood purpose of this form/subform is to create new records in tblAutoModels by applying filter to main form to select manufacturer then creating related records in the subform. Probably want to select an engine size in combobox on subform and display related info somehow. The only info in tblEngineSizes is the code and size. Instead of the form RecordSource query to retrieve the related info, could just show it in combobox. Maybe that will simplify this issue for you. I probably should have suggested that approach to begin with.

Again, need to be sure which field in tblEngineSizes will be the PK/FK.

Suggestions for combobox RowSource depending on the PK/FK:

SELECT fESID, fEngineCode & " : " & fEngineSize As EngSize FROM tblEngineSizes;

SELECT fEngineCode, fEngineCode & " : " & fEngineSize As EngSize FROM tblEngineSizes;

In both cases, other properties of combobox:
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0", 1.5"

[N1755L]

I had to take a break away from Access for a while, it was just getting too frustrating.

All it took in the end was for me to change the subform recordsource to the query you so ably designed for me. Incredible isn't it? I didn't even realize I could supply a different recordsource for the subform, I knew there was a child/parent link or something of the sort, which I've had to set before, but forget at the moment now. Once I changed the recordsource of the subform to your query, all was working perfectly the way I needed/wanted.

But then, since the query field was empty if a letter hadn't been entered as a engine size, I thought that it should be a very simple matter of an IIF() statement to fill the calculated field with the actual value that the user had entered for engine size in the EngineSize field. This then led to hours of unsuccessful, frustrating attempts.

I needed to take a break away from this thing. The lumps are receding on my forehead, I'm ready for another round.

Thanks again for all your help guys/gals?, I know it's not easy, I know it's a tough slog at times looking at someone's mess and trying to figure out exactly what they're attempting to accomplish, what you're doing for everyone here is more than, I'm fairly certain, most realize.

The version of your db I have has fESID_ID field in tblAutoModels. It is a number type. This means would save the fESID value from tblEngineSizes as the foreign key.

If you want to save fEngineCode as foreign key in tblAutoModels then field fESID_ID has no purpose in tblAutoModels.

I understood purpose of this form/subform is to create new records in tblAutoModels by applying filter to main form to select manufacturer then creating related records in the subform. Probably want to select an engine size in combobox on subform and display related info somehow. The only info in tblEngineSizes is the code and size. Instead of the form RecordSource query to retrieve the related info, could just show it in combobox. Maybe that will simplify this issue for you. I probably should have suggested that approach to begin with.

Again, need to be sure which field in tblEngineSizes will be the PK/FK.

Suggestions for combobox RowSource depending on the PK/FK:

SELECT fESID, fEngineCode & " : " & fEngineSize As EngSize FROM tblEngineSizes;

SELECT fEngineCode, fEngineCode & " : " & fEngineSize As EngSize FROM tblEngineSizes;

In both cases, other properties of combobox:
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0", 1.5"