Change digit by other field. (1 Viewer)

Zabom · Jul 26, 2019

Hey!
I make a food plan, where I am currently working on the ingredient function and I'm having an issue with the durability field.

I plan to have a field where you type a number and then select a value in a field where you choose between days, weeks and months in the "Add ingredient"-form.

The problem is that I'm not sure how to solve it:
I have a table of ingredients where the values are now Ingredient name, price & durability for days.

My best idea was that if you write, for example, a durability of 5 and choose weeks that it multiplies 5 by seven and enter it in the "Durability of days" field in the table.

I guess I need to write code for this, how can it look / how do I?

theDBguy · Jul 26, 2019

Hi. Before we go there, you might give it another thought. If you want to display durability by days, what calculation would you use if the user selects "month" as the multiplier?

arnelgp · Jul 26, 2019

you can make a unbound combobox for the values "days", "weeks", "months".
then add code to the BeforeUpdate event of your form to calculate it for you.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If me.comboDurability.ListIndex = -1  Then 'nothing is selected
    Cancel = True
    Msgbox "Please specify the durability period"
    Me.comboDurability.Setfocus
Else
    Select Case Me.comboDurability
    Case Is = "Weeks"
        Me.DurabilityDays = Me.textboxNumber * 7
    Case Is = "Months"
       Me.DurabilityDays = Me.textboxNumber * 30
    Case Else 'Days
       Me.DurabilityDays = Me.textboxNumber
    End Select
End If
End Sub

Pat Hartman · Jul 27, 2019

Days from when? Wouldn't it be better to calculate an actual expiration or best by date?

Change digit by other field. (1 Viewer)

Zabom

New member

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread