#Type! error driving me crazy!

[N1755L]

Sorry guys/gals,



Been Googling hours on end, can't find any solution. It must be something fairly simple, but I've been trying everything, for over 4 straight hours now, and I can't get rid of this #Type! foolishness.

I'm attaching the database, hope someone can easily see the problem.

The form in question is frmAutoModels. I have a dlookup textbox that I want to reference the tblEngineSizes table.

Basically, there's a possibility that the engine size might be entered into the form as a letter (W, for example), so in the event that the engine size is entered as a letter, then the lookup textbox, just to the right of the W-entered engine size, will display the actual engine size (1.8L, for example).

Simple, right?

Maybe I'm going about it all wrong, maybe I should be using a different method to detect that letter input in the engine size field, and a different method as well to retrieve the engine size that corresponds to the letter that's input?

Bit novice to Access, seems that the answer for everything is to get info from a query, if this is such a case again, hopefully someone can explain how I would go about accomplishing that in this instance.

Thanks in advance.

[Missinglinq]

I run v2007 and so cannot open a v2010 file. If your question is not version-specific (and this one isn't) you're always better off saving a copy of your file in 2003 format before attaching/posting it here...that way more people are able to download and look at it.

Can you post the DLookup() code you're attempting to use?

Linq ;0)>

[ItsMe]

This is the Dlookup

=DLookUp("[fEngineSize]","tblEngineSizes","[fEngineCode] = " & [Forms]![frmAutoModels]![Engine_Size])

There is a problem with the following expression. It refers to a control on the same form. Going to see if I can bind a new control to replace.

=[Forms]![frmAutoModels]![sfrmAutoModels]![Engine_Size]

I clicked around and changed a bunch of stuff. I was thinking corrupt module at first. I am going to start again.

[ItsMe]

What is the relationship between tblAutomodels and tblEngineSizes? You need to create a join between these two tables and base your subform off of that.

The whole
=[Forms]![frmAutoModels]![sfrmAutoModels]![Engine_Size]
makes no sense to me whatsoever. If, somehow, you got this to work in a continuous form, all it would do is display the value for field fEngineSize and you already have a control bound to fEngineSize. It is named EngineSize.

[N1755L]

Sorry, when I try "Save Database As", it doesn't offer me any other options to save it in any other version... ?

I run v2007 and so cannot open a v2010 file. If your question is not version-specific (and this one isn't) you're always better off saving a copy of your file in 2003 format before attaching/posting it here...that way more people are able to download and look at it.

Can you post the DLookup() code you're attempting to use?

Linq ;0)>

[ItsMe]

Here you go linq. One form was not saved during the conversion. I do not believe the conversion affected the issue at hand. Beware Control Names do not match field names for Control Sources.

[N1755L]

=[Forms]![frmAutoModels]![sfrmAutoModels]![Engine_Size]

The subform part here was just desperation... I was trying everything... I've changed the quotation marks a million times, along with the square brackets, and even fooled around with single quotes, as there appears to be some in the following I found elsewhere:

*****

Re: DLookup type mismatch error

---

Remove the items in red:

Me.Fund_1 = DLookup("[Fund#]", "TradeFundInfo", "[FundName]='" & Me.FromFund1 & "'" And "[Product]='" & Me.ProductCombo & "'")


*****

I spent hours a while back with a dlookup 'cause the examples I was trying to implement were just slightly different placement of quotation marks or square brackets (so many different past versions, example code in Googled forum posts are sometimes dodgy propositions).

[N1755L]

Hmmm... When I input "W" for engine size (third column of the frmAutoModels form), the dlookup should find that corresponding "W" in the tblEngineSizes table, and return the corresponding "1.8L" value in that dlookup textbox control (4th column)... btw, why is it that when I resize or move the controls in Design mode, they do not move/change accordingly in the View mode?).

But now I'm belatedly starting to realize that if I do not input a letter in the 3rd column that corresponds to a letter in the tblEngineSizes table, then there is no match in the lookup... no, wait... no, that can't be the problem, as I was going down this line of thought, I decided to change all the inputs to letters M and W, but still the same error, so that's not the problem.

Why "#Type!"?

Guess I have to go a different route than a dlookup? How do I get the form to check whether or not a user has input an engine size in the form of a letter, and then have a textbox to the right show the engine size that corresponds to the letter that was input?

Yeah, dlookup is never going to work for this purpose, is it? How else to do this?


Thanks.

[Missinglinq]

If the Field in question is defined as Text, as the 'W' implies, the correct syntax would need to be

=DLookup("FieldToLookUp", "TableName", "[FieldName] = '" & Me.ControlNameOnForm & "'")

The syntax you originally used is only valid for a Field defined as a Number.

Linq ;0)>

[N1755L]

I also tried to code VBA so as to open the tblEngineSizes table as a recordset, and then try to compare the value than was input into the 3rd column of the frmAutoModels form, so I could say "If you look at the value input into the 3rd column of the frmAutoModels form, and compare it to the recordset's fEngineCode field and find a match, return the value in the recordset's corresponding fEngineSize field". So, entering a "W" in the 3rd column of the form, would return a "1.8L" value, and entering a "R" would return "1.6L", etc.

I couldn't figure out how to make the code work, but was I on a fool's errand anyway, trying to use this method? Or might this method be a good way to go, provided I can figure out how to code it?

[June7]

Domain aggregates can be slow. As ItsMe suggested and I agree, make the RecordSource of subform a query.

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

Then bind textbox to fEngineSize and set it as Locked Yes, TabStop No.

No DLookup is needed.


tblEngineSizes does not have primary key set. Since you are saving the fEngineCode value into tblAutoModels, set it as primary key and the fESID field can go away.

[N1755L]

Interesting! So here we are again with the single and double quotes again... lemme try that.

Darned! Same #Type! crappola again... tried the following as per your suggestion:

=DLookUp("[fEngineSize]","tblEngineSizes","[fEngineCode] = '" & [Forms]![frmAutoModels]![txtEngineSize] & "'")

Notice I finally changed the control name to "txtEngineSize", instead of "Engine Size" (for some strange reason, when I'd tried to change it this way earlier, the textbox would stop displaying data, so I'd left it alone as "Engine Size", but now I've managed to change it).

Still getting that "#Type!" foolishness... I must be on the wrong track... there must be a better way... no?

If the Field in question is defined as Text, as the 'W' implies, the correct syntax would need to be

=DLookup("FieldToLookUp", "TableName", "[FieldName] = '" & Me.ControlNameOnForm & "'")

The syntax you originally used is only valid for a Field defined as a Number.

Linq ;0)>

[N1755L]

Domain aggregates can be slow. As ItsMe suggested and I agree, make the RecordSource of subform a query.

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

Then bind textbox to fEngineSize and set it as Locked Yes, TabStop No.

No DLookup is needed.


tblEngineSizes does not have primary key set. Since you are saving the fEngineCode value into tblAutoModels, set it as primary key and the fESID field can go away.

Hello June7,

As per your suggestion, I created that query and it works a charm! Now I created a textbox (Text15) to experiment with retrieving the tblEngineSizes.fEngineSize column from that query, so as to see this value in the Text15 box on the form, but it's not working, I tried using the Expression Builder to point directly to that column within the query, but nothing is working, I can't get that query tblEngineSizes.fEngineSize column to show on my frmAutoModels form (constant #Name! error). So close! AAAARRRRGGGGHHHH!!!! MS can be sooooo maddening!

Such a simple little thing, been at it almost 7 hrs. now...

[N1755L]

Thanks Linq, but if I can get this to work without use of a dlookup, I think I'll be doing myself favors... I just didn't know how to do it by way of queries, but think that this is likely the preferred method.

If the Field in question is defined as Text, as the 'W' implies, the correct syntax would need to be

=DLookup("FieldToLookUp", "TableName", "[FieldName] = '" & Me.ControlNameOnForm & "'")

The syntax you originally used is only valid for a Field defined as a Number.

Linq ;0)>

[N1755L]

Blimey! How do I upload the updated database?

Should I start a new "How to reference a query field/column on my form?" topic/thread?