Combo Box and calculate other fields

  • Thread starter Thread starter khashid
  • Start date Start date
K

khashid

I need little help with my form . I have a combo box with different options
such as A, and B. What I am trying to do is when the user selects A , it
would automatically put todays date in the [Date] field and would also
automatically update [Expiration Date] that would be 6 months from todays
date.

If the user select B it would change the [Date] to todays date but
[Expiration Date] would be updated to one year from today.

so basically the [Expiration Date] would be updated/calculated after
selecting option with different critaria.

Thanks in advance
 
In the AfterUpdate event of your combo box, put:

Me.[DateFieldName] = Date
Select Case Me.[ComboBoxName]
Case "A"
Me.ExpirationDate = DateAdd("m",6,Date)
Case "B"
Me.ExpirationDate = DateAdd("y",1,Date)
End Select

Hope that helps you!
 
Thank you very much. you told me exactly about what I was looking for.

really appreciated
 
two more thing want to know pleas

(1) is it going to save the date in the table for expiration date

(2) What if I create one more table, related to my master table. I can save
(Column 1)options = A,B,C... and (Column 2) Validty=18,12, 6,... ( number
of months)

Now what I would like to do is, after user chose an option A, B etc. it would
change the transaction date to todays date and then in the expiration date it
would get the number of months from the new table and use it in the formula
for DateAdd.

This way in the future I can add new options with different expiration date
without changing the codes and I would not have to write DateAdd code for
each option seperatly.

I hope you understand what I mean.

Thanks :)
 
I think I know what you're talking about and I'll try to answer your
questions.

1. The expiration date will only be saved if you have a field for the
expiration date in your table bound to the expiration date field on
your form.

2. You can do that too. The data source of your dropdown box would be
your new table. Make sure in your settings that you say your dropdown
has 2 columns. You can also use the built in wizard to set up your
dropdown. You would again us the After Update Event only this time it
would look like this:

Me.[DateFieldName] = Date
Me.ExpirationDate = DateAdd("m",Me.ComboBoxName.Column(1),Date)

Columns in the combo box are numbered staring with 0 and not 1, just so
you know. Hope I helped again!
 
I have following codes but nothing happens it does not do any change just
changes the transaction date:


Private Sub Package_AfterUpdate()
Me.[Transaction Date] = Date
Select Case Me.[Package]
Case "[Package].Column(1)"
Me.[End Date] = DateAdd("m", Me.[Package].Column(4), [Transaction Date])
End Select
End Sub

Column 4 of package has numbers of months such as 12, 18 etc
Column 1 of package has the values user will choose from the combo box
End date is the field where the dates will be entered and saved.
 
Back
Top