Putting a limit on a field based on another fields value

[redekopp]

I am hoping this is easier then I think it is!



I have a dialog form that opens .
Three text fields:
1. Total acres of claim: (name = txtTotalAcres) (control source is " =[TempVars]![TmpActualAcres] ")
2. Total acres counted thus far: (name = txtTotalAcresCounted) (control source is "=DSum("[acresCounted]","[tblClaimItemCounts]","[claimitemid]=" & [ClaimItemID])"
3. Acres of this count: (name = txtCountAcres) (control source = "acresCounted")

If field #1 = 150 and field #2 = 130 then I want it so that there is no way they can type a value higher than 20 in field #3 without giving a MsgBox and not allowing the value to save.

Not sure if its a control source of some sort or what! But I need txtCountAcres to know that it can't make the value of txtTotalAcresCounted higher then txtTotalAcres after update. Which also makes me wonder how to make that record update before closing the form.

Thanks!

[pbaldy]

The typically place to do validation is the before update event of the control or the form. Here's something on that:

http://www.baldyweb.com/BeforeUpdate.htm

Normally I'd suggest the form's event, but since the user can't enter anything in the other two controls, that textbox's event should work too.

[redekopp]

Thanks pbaldy, I edited the code I used and it is responding correctly. Thanks!

Private Sub txtCountAcres_BeforeUpdate(Cancel As Integer)

If (Me.txtCountAcres + Me.txtTotalAcresCounted) > Me.txtTotalAcres Then
  MsgBox "You cannot exceed the Total Acres of this claim."
  Cancel = True
  Else
End If

End Sub

Not sure if im allowed to hijack my own thread but I had one more question!

Im doing an after update of a field that needs to auto populate the following field with the dlookup of a value from the table.

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] = " & [TempVars]![TVCropRow] And "[cropcolumn] = " & [TempVars]![TVCropColumn] And "[calctype] = " & [TempVars]![tmpBranchLossName])
Me.PlantLoss.Requery

I get a type mismatch error. All the tempvars give correct values... I cant seem to crack this one.

[pbaldy]

Your concatenation is off, not restarting the string after the TempVar. See if this helps:

DLookup Usage Samples

[redekopp]

hmm i edited it to how it appears to be in that example, so i thought...

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] =" & [TempVars]![TVCropRow] & " And [cropcolumn] =" & [TempVars]![TVCropColumn] & " And [calctype] = '" & [TempVars]![tmpBranchLossName] & "'")

still getting data type mismatch in criteria expression.
[cropvalue] = short text
[croprow] = short text
[cropcolumn] = short text
[calctype] = short text

[ssanfu]

Try replacing the TempVars with known values to see if the correct value is returned.

So if
[croprow] = 5
[cropcolumn] = 10
[calctype] = "ABC"

you would have

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] = 5 And [cropcolumn] = 10 And [calctype] = 'ABC'")

If that works, add one TempVar

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] =" & [TempVars]![TVCropRow] & " And [cropcolumn] = 10 And [calctype] = 'ABC'")

Continue for the other two variables until the DLookup works....

[pbaldy]

If they're all short text, they all need the single quotes like you have on the last.

[ssanfu]

still getting data type mismatch in criteria expression.
[cropvalue] = short text
[croprow] = short text
[cropcolumn] = short text
[calctype] = short text

Hmmmmm. The OP must have edited Post#5.

I'm sure it was
[cropvalue] = number
[croprow] = number
[cropcolumn] = number
[calctype] = short text

[redekopp]

My apologies, I did indeed edit the field types.. thought i did it fast enough! sorry!
I tried the single quotes but i think my formatting is wrong... i get confused with all the quotes, i may of over quoted.

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] ="'" & [TempVars]![TVCropRow] & "'"" And [cropcolumn] ="'" & [TempVars]![TVCropColumn] & "'"" And [calctype] = "'" & [TempVars]![tmpBranchLossName] & "'")

[aytee111]

Start of string = double quote
Quotes in the string = single quote
e.g.

"[croprow]='" & .....

[redekopp]

Start of string = double quote
Quotes in the string = single quote
e.g.

"[croprow]='" & .....

I had expected it to look like this but its not liking it..

Me.PlantLoss = DLookup("[cropvalue]", "[tblCropSheetData]", "[croprow] = '" & [TempVars]![TVCropRow] & "'" And [cropcolumn] = '" & [TempVars]![TVCropColumn] & "'" And [calctype] = '"  & [TempVars]![tmpBranchLossName] & "'"")

[pbaldy]

I'll get out of the way.

[aytee111]

You keep closing the string when you need to keep it open - every time you close you need an & after the double quote, check your string for that.

" & [TempVars]![TVCropRow] & "'" And [cropcolumn]...

[redekopp]

hallelujah! Praise the vba lord it works!

Thanks all.