Help with my DLOOKUP functions.

[SpaceDyeVest28]

I am very new to Access and am learning as I go but one thing I can't figure out is how to hold the dlookup value on my form.



The dlookup function works exactly as I want it to but here's what is happening:

Before: After:



How do I get the Per Hour and Average Hours fields to stick after entering in another value into the Loss Per Hour field?

I am not using anything in VBA currently. Ignore the #name error, I just haven't updated the name of the control yet.

Thanks in advance.

[Minty]

What is the syntax / how have you written the DLookup ?

[SpaceDyeVest28]

=DLookUp("[perhour]","[combine table]","[condensed]= " & [Forms]![Combine SUBForm]![combo417].[Text])
=DLookUp("[avg hours]","[combine table]","[condensed]= " & [Forms]![Combine SUBForm]![Combo417].[Text])

[Minty]

Okay - that looks a little strange, is Combo417 storing a text or number value?
I suspect what's happening is the DLookup isn't getting a value from the combo until you enter a value in the bound field for the Loss Per Hour.
#Type is type-mismatch, so you are either looking up a number but feeding it a string or vice versa.

[SpaceDyeVest28]

Should be a number but using "text" was the only way to get it to work. I must have been doing something wrong?

[Minty]

Have you used a lookup field in your table for the combo by any chance ?

Don't is the sort answer http://access.mvps.org/access/lookupfields.htm change it to a normal field and store the ID of the value you want to look up if that's how it's stored.

[SpaceDyeVest28]

I am not sure how to do that.

I am going to try that right now.

[Minty]

Can you zip up your DB and post a sample of it - unfortunately it will be easier than trying to explain.

[SpaceDyeVest28]

Yes I can, one sec.

[SpaceDyeVest28]

Here you are.

Thanks for all the help!

[Minty]

Okay - I think you have some serious design issues going on, which I think are confusing how you are trying to make this all work.

You have taken a spreadsheet design and dumped it straight into access tables, which as you are now beginning to see, isn't working.
You are storing your data horizontally, and databases require entities with data stored vertically.

All the field names in Combine options should be records in new table tblOptions that should have 4 or 5 fields, ditto your pricing table.
I'll do some work on making the tables into a normalised structure and repost your db - then hopefully explain why it's better this way and how you should proceed with your project.

[Bulzie]

Should follow what Minty is going with but for the lookups, as you had it below would be for a numeric value.

=DLookUp("[perhour]","[combine table]","[condensed]= " & [Forms]![Combine SUBForm]![combo417].[Text])
=DLookUp("[avg hours]","[combine table]","[condensed]= " & [Forms]![Combine SUBForm]![Combo417].[Text])

If the values are really text values, probably be using:
=DLookUp("[perhour]","[combine table]","[condensed]= '" & [Forms]![Combine SUBForm]![combo417].[Text] & "'")
=DLookUp("[avg hours]","[combine table]","[condensed]= '" & [Forms]![Combine SUBForm]![Combo417].[Text] & "'")

[SpaceDyeVest28]

Yeah. I had to take over maintenance of an already built database and that's how they had it formatted and I followed suit because I did not know better, until now - glad I found this forum. I got tasked with building a new database, so here I am, lol. Thank you guys so much. I'm excited to see what it should look like, Minty!

Thanks!!

[Minty]

Okay - I've added some extra tables - I think these will straighten out what you are doing - unless I've misunderstood the process.

Unfortunately you will need to redesign your forms as you can't really design them in the way that you have with a more normalised data layout, however you would have needed to do that every tine you added a new option to your existing table, this way you won't.Heavy Equipment.zip

[SpaceDyeVest28]

So, in order to get a depreciated amount for these new pricing figures, how would I do that?