Select Case

[SHAZAM]

Hi Hans

Hope all is well.

I need to do a similar Lookup for loads of Fields, and I was trying to extrapolate from this.

Basically, a different Contractors can be billed different amounts at different periods.

Eg.

[tblContractor]
Contractor1 may pay 1,000 @ the end of Month 1; 2,000 @ the end of Month 2; 3,000 @ the end of month 3, etc.
Contractor 2 may pay 500 @ the end of Month 1, 750 @ month 2, and so on.

Therefore, after someone enter the Contract Start Date, I'd like [Field 1] to equal 1,000 if ContractorID is 1, 500 if ContractroID is 2, etc.

I tried it like this for the First one using your DLOOKUP, but I'm not sure what commas, parenthesis, or quotation marks to remove from your above code.

I ended up with this:

Code: Select all

Dim varRate As Variant
varRate = DLookup("[Amount1]", "tblContractor", _
    "ContractorID = " Me.ContractorID)
  Me.Amount1.DefaultValue = Nz(varRate)

But I got a Syntax Error, so I'm not sure what I missed, ie, quotation marks, etc.

And seeing as I'm looking up multiple values, will I have to do this for each 1
Eg
Dim varRate1 as Variant
Dim varRate2 as Variant
Dim varRate 3 as Varient
etc
And finish with . . .
Me.Amount1.DefaultValue = Nz(varRate1)
Me.Amount2.DefaultValue = Nz(varRate2)
Me.Amount3.DefaultValue = Nz(varRate3)

Thanks, as always, for your guidance and help. :)

[HansV]

Welcome to Eileen's Lounge!

You've missed an &:

Code: Select all

varRate = DLookup("[Amount1]", "tblContractor", _
    "ContractorID = " & Me.ContractorID)

You may have to change

Me.Amount1.DefaultValue = Nz(varRate)

to

Me.Amount1.DefaultValue = Chr(34) & Nz(varRate,0) & Chr(34)

If you have multiple amounts in the same record, you must do a separate lookup for each.

[SHAZAM]

Hi Hans

Thanks for your reply, and sorry for the delaying in posting back (I was off last week).

I've run the code as follows:

Code: Select all

Dim varRate As Variant
varRate = DLookup([StartPayment], [tblLC], _
     "LCID = " & Me.LC)
Me.StartPaymentAmount.DefaultValue = Chr(34) & Nz(varRate, 0) & Chr(34)

But it has a run-time error: 2465
Microsoft Access can't find the field '|1' referred to in your expression.

Did I miss another bit of punctuation, perhaps?

Many thanks again.

[HansV]

The arguments to DLookup are strings: the first argument is the name of the field to look up, the second is the name of the table (or query), and the third is the where-condition as a string. So you have to use

Code: Select all

varRate = DLookup("StartPayment", "tblLC", _
     "LCID = " & Me.LC)

[SHAZAM]

Thank you Hans

Apart from a stupid mistake I made (which I rectified by removing Default Amount so it just populates the Field), everything works perfectly.

But, sure, what else can we expect from Clever Cloggs?!

You're brilliant.

Thank you so much.

[Superman]

Hi Hans
I couldn't log in with my existing Account (said username wasn't recognized), so I've created a new Account, but it's still me! :)

Just trying to do another piece of work on this.

I now have another Field to update, which is working great, but this time I need to do a bit of Math on it. However, when I add the Field it needs to subtract from, Access says "Method or Data Member Not Found" (it highlights the last line at "Me.TravelExpenses".

This is what I've typed:

Code: Select all

varRate = DLookup("Excess", "tblLC", _
     "LCID = " & Forms![frmGAP]![LC])
Me.Excess = Me.TravelExpenses - Nz(varRate)

Access used AutoComplete to do this line, ie, as soon as I typed Me, it allowed me to select "TravelExpenses", so I'm not sure why it's coming up with an error message? I've double-checked the Field name in the Form & the Table. I also tried [me].[TravelExpenses] and ([me].[TravelExpenses]) but that didn't help.

Can you help with what I've done wrong?

Many thanks, and sorry for bothering you again!

[HansV]

1) If you'd like me to reset the password for SHAZAM, send me a PM.

2) Open the form in design view.
Do you have a control bound to the TravelExpenses field?
If so, check the name of the control. Is is named TravelExpenses or does it have another name such as Text2?
Otherwise, it would be a good idea to add a text box bound to TravelExpenses to the form; you can set the Visible property of the text box to No if you like.

[Superman]

Hi Hans

Thanks for your reply, and sorry for the delay in getting back to you.

Maybe it's because I'm trying to use the AfterUpdate event on the very Field that's just been updated, ie, someone enters the amount of Travel Expenses, and after the update, the Event Fires and updates the Excess Field with a Sum that subtracts the Travel Expenses field from the Field that was looked-up?

In other words, is it possible to use the AfterUpdate Event to calculate a Sum that includes the Field that's just been updated?

Sorry if that seems really confusing!

[HansV]

That shouldn't be a problem.