IF THEN Value - Need Help

[taylorsmith8]

I am not a DB expert by any means. That said, I need to do the following, in either the Query based on a table or the Form that is based on the Query, and could use some suggestions:



Field: Box Type (BB bankers box, BO box other, NB not box)
Field: L:
Field: W:
Field: H:

What I need to have happen is that if [Box Type]="BB" then [L:] populates with 15.5, [W:] with 12.5 and [H:] with 10.5

If [Box Type] is "BO" or "NB" then I need to be able to enter custom data

I am stumped. Seems like this should be easy, and maybe it is and I am just missing it. I tried the following in the Field section of each [L:], [W:] and {H:] : (for [L:])... =IIF([Box Type]="BB","15.5") and that worked for all of the Box Type BB records, but then I couldn't manually enter any data at all in any of the not BB records.

I would very much appreciate any help at all. My thanks.

[pbaldy]

One option would be to use the after update event of the box type control on the form:

If Me.BoxType = "BB" Then
  Me.Length = 123
  Me.Width = 456
  Me.Height = 789
Else
  Me.Length = 0
  Me.Width = 0
  Me.Height = 0
End If

The else covers you if the user changes the selection.

[taylorsmith8]

Okay, so how would I write the expression in the After Update? Like I said, I am NO expert at this. Just tying to make it work and clueless.

Thanks so much for your help.

[pbaldy]

Here's how to get into the code editor if that's what you mean:

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

[taylorsmith8]

I tried this and couldn't get it to work On Enter (my fields aren't defined as "Length, Width, Height):

If Me.Item_Type = "BB" Then
Me.[L:] = 15.5
Me.[H:] = 12.5
Me.[H:] = 10.5
Else
Me.[L:] = 0
Me.[W:] = 0
Me.[H:] = 0
End If

What have I done wrong?

[pbaldy]

What does "couldn't get it to work" mean? Error? Incorrect result? What event do you have it in? Again, I'd use the after update event of the item type control.

[taylorsmith8]

Hi there. I put it in the After Update of the Item Type control. This is exactly what I put into the Code Builder:

Private Sub Item_Type__AfterUpdate()
If Me.Item_Type = "BB" Then
Me.[L:] = 15.5
Me.[H:] = 12.5
Me.[H:] = 10.5
Else
Me.[L:] = 0
Me.[W:] = 0
Me.[H:] = 0
End If
End Sub

When I try putting "BB" In the Item Type of the form and then move out I get an error message that says : "Compile Error - Method or Data Member Not Found" with ".Item_Type" highlighted in blue.

[taylorsmith8]

Oh, also, I imported the data I am using from an Excel spreadsheet, some of it is there and some still needs to be filled in using the data entry form. BUT, what that means is that on many of the records, BB is already in the Item Type field.

[pbaldy]

The error implies that Item_Type is not the name of the textbox. Is it? If it has a space in it, try

Me.[Item Type]

[taylorsmith8]

THAT DID IT! Thanks so much!!! Now it even works with the other Item Types (besides "BB")!!! You are a life-saver!

[pbaldy]

Excellent! Glad we got it working.