Combo Box value updates a field in table with date? afterupdate?

[mkopsick]

Im a novice vb programmer so id like to say in advance thanks for the help. Heres my problem...I have a form with a combo box that has a drop down menu with the following 1 Day, 4 Days, and 10 Days. What i want to do is once the user selects a day the following happens. If the user selects 1 Day it will put the current date in my next text box on the form called susp act date. there is a field in my table with the same name. If they select 4 Days it will add 4 days to the current date and again place it in the next text field (susp act date) and if its 10 Days it will add 10 days to the current date. Ive tried using the afterupdate to make this happen and im getting no where. here is the code:

Private Sub Combo36_AfterUpdate()
If [Table1].[Susp Days] = "1 Day" Then
[Table1].[Susp Act Date] = Date
End If
If [Table1].[Susp Days] = "4 Days" Then
[ Table1].[Susp Act Date] = Date + 4
End If
If [Table1].[Susp Days] = "10 Days" Then
[ Table1].[Susp Act Date] = Date + 10
End If
End Sub

The susp date is the field in the table where the choice from combo box is put. Am i using the wrong process because im using afterupdate? Im getting the following error:

Microsoft access cannot find the field 'l' referred to in your expression

i thought this would be a simple deal but its not looking like it...at least for me...again any help would be greatly appreciated.

 

[mkopsick]

Typo above Susp Days is the field where the combo box selection is stored not susp date.

 

[SOS]

You can't reference a table like this:

If [Table1].[Susp Days]

Is that table in your form's recordsource?

 

[mkopsick]

yes table1 is the table and the field is susp days

 

[mkopsick]

remember im a novice...table1 is where the selection for the combo result is stored in a field called susp days. i want to take that input and add the days to it and display in the the next text box witch has a field called susp act days

 

[mkopsick]

i have controlsource set to susp days field

 

[SOS]

Okay a little education here.

1. 99.9% of the time you don't store calculated fields in a table. You just calculate them as you need them (following Normalization rules).

2. So for your thing here, I would say that you don't need to store it in the table but instead use a query to have your calculated field.

In a query it would be:

SuspActDate:Switch([Susp Days] = "1 Day",Date(), [Susp Days]="4 Days", Date()+4, [Susp Days]="10 Days",Date()+10)

 

[Scooterbug]

When referencing data from from combo box, you have to use the following syntax:

me.controlname.column(x) where x is from 0 to however many fields are in your combo box record source.

So, if your combo box has 2 fields (ID, SusDays) and you wanted to grab the value of SusDays, it would be me.controlname.column(1)

 

[JCShort]

The table is where your data is ultimately stored, but your form is where the data is manipulated. When you say add 4 days, do you need to add weekdays only? i.e. Today is Wednesday so +4 = Monday, not Saturday.

'date + number' is a great excel formula, but it doesn't work in access. You need to use the DateAdd() function.

 

[SOS]

'date + number' is a great excel formula, but it doesn't work in access. You need to use the DateAdd() function.

Umm, yes it does work because Access stores dates as Doubles and days are the default. So you can use Date() + 4 to add 4 days. DateAdd is more explicit and is probably the better way to go, but doing Date() + 4 WILL work and even in VBA:

Date + 4

will yield 4 days past today's date.

 

[mkopsick]

SOS would i run that query under the afterupdate?

 

[SOS]

SOS would i run that query under the afterupdate?

No, you wouldn't do that. You would add that to any query that you want to use to have that field (in other forms, reports, other queries, etc.)

But to do the same in the form you can just set the control source of the text box to

=Switch([Susp Days] = "1 Day",Date(), [Susp Days]="4 Days", Date()+4, [Susp Days]="10 Days",Date()+10)

 

[mkopsick]

the afterupdate on my combobox?

 

[mkopsick]

ok that worked but i need to store that date in a field in table1 called susp act date

 

[SOS]

ok that worked but i need to store that date in a field in table1 called susp act date

Again, I will say NO YOU DON'T. You do NOT need to store that and neither should you. Wherever you need it at runtime (for a report, a form, another query, an export, etc.) you use that other post I gave you of how to create the field in your query at runtime. You do NOT STORE IT IN THE TABLE. Does that make sense?

 

[JCShort]

Thanks for the correction, SOS. I had all sorts of issues adding dates when I was trying to simply add a number. Obviously doing something wrong.

I deal w/ weekdays all the time anyway, so a straight add days doesn't work for my application. Good to know before giving others advice though.

 

[mkopsick]

The reason i was going to store it is because once i have the susp act date i will need to have that date turn red in a report when 1 day is red instantly, 4 days turns red on the 3rd day and 10 days will turn red on the 8th day. dont i need those dates in a field to do that?
i really appreciate your help!

 

[SOS]

The reason i was going to store it is because once i have the susp act date i will need to have that date turn red in a report when 1 day is red instantly, 4 days turns red on the 3rd day and 10 days will turn red on the 8th day. dont i need those dates in a field to do that?
i really appreciate your help!

Instead of using a table for your report you use a query (should use queries for reports anyway) and you add that field like I showed to the query.

 

[mkopsick]

excuse my ignorance please but wouldnt i have to store those dates and run the query? we will be entering dates everyday and need to keep track of them?

 

[mkopsick]

i use querys for the report i have but im pulling info in the query from fields in my tables....when i used the control source code in my form for that text box it works great but how will i save the susp act date again please excuse my ignorance here