Help a monkey fix a farly simple if statement

[KevinSlater]

i have the following code in a form (continues form):, the "absence reason" field is a drop down field box with 2 columns (absence code, & absence reason) but the code doesnt work, any ideas what a monkey like me is doing wrong?, please help (ive tried in after update & on current events instead but still doesnt work)

when "H" is selected id like a message displayed

Private Sub ABSENCE_REASON_BeforeUpdate(Cancel As Integer)
If Me.ABSENCE_REASON.Value = "H" Then MsgBox "holiday has been selected"
End If
End Sub

 

[KenHigg]

If Me!ABSENCE_REASON = "H" Then
MsgBox "holiday has been selected"
End If

 

[david.brent]

Mmmm

Hi mate,
I think it might be the layout of your If statement.
You haven't started an If block ...

If Me.ABSENCE_REASON.Value = "H" Then MsgBox "holiday has been selected"

...but you've still used an End If. This should give you a nice error.
Here's how I'd do this.

Private Sub ABSENCE_REASON_AfterUpdate()
If Me.ABSENCE_REASON.Value = "H" Then
MsgBox "holiday has been selected"
End If
End Sub

also works if ou drop the 'Me'

Private Sub ABSENCE_REASON_AfterUpdate()
If ABSENCE_REASON.Value = "H" Then
MsgBox "holiday has been selected"
End If
End Sub

As you can see I've used After Update.

Hope this helps a little.

Carl

 

[Jack Skeleton]

You say you have 2 columns in your box.... If there are two columns and the value "H" is not in the dependent one,
1) change the check string for the corresponding value in the second column

OR

2) change the comparison for "Me.ABSENCE_REASON.column(ColumnNumber)", where ColumnNumber is the number of the column where you have the value "H" minus 1 (that means "0" or "1"). Example thinking that ABSENCE_REASON is in the first column:

If Me.ABSENCE_REASON.column(0) = "H" Then MsgBox "holiday has been selected"

PS. If you're a monkey, I'm the monkey king

 

[KevinSlater]

Hi all, many thanks for your replies david, jack & ken, they were all helpful, & all worked fine sorry to be a cheeky monkey but i tryed to add some more code to this to copy any hours (numbers) put in the "hrs absent" field into the "holiday hours" field of a table named "attendance". This added code below does copy the number put into the "hrs absent field" on the from into the "holiday hrs" field in the attendance table but seems to put it into a blank record rather than the actual current record.

Any ideas what i have missing? (tried a where statement but cant get it to work)
--------------------------------------------------------
Private Sub ABSENCE_REASON_AfterUpdate()
If Me.ABSENCE_REASON.Value = "H" Then
MsgBox "holiday has been selected"


'Write to table
Dim SQL_Text As String
SQL_Text = "INSERT INTO ATTENDANCE ([HOLIDAY HRS]) SELECT [HRS ABSENT]"
DoCmd.RunSQL (SQL_Text)
End If
End Sub
---------------------------------------------------------------

 

[Jack Skeleton]

Use the "UPDATE" option instead of the "INSERT INTO" one, so you can use "WHERE" options. Something like this:

"UPDATE ATTENDANCE SET ATTENDANCE.[HOLIDAY HRS] = " & "(the value you want to add)" & " WHERE " & conditions...

 

[KevinSlater]

Thanks, im trying something like this:
(this doesnt work though)

SQL_Text = "UPDATE ATTENDANCE SET ATTENDANCE.[HOLIDAY HRS] = " & "(ABSENCE REASON)" & " WHERE ABSENCE REASON = "H"

 

[Jack Skeleton]

Remember to put field names in brackets (WHERE [ABSENCE REASON]="H")

A useful tip:

Create a Query in "Design view" to do what you want to do, after that change to "SQL view" and copy the statement.

 

[KevinSlater]

Update code...any1 kind enough 2 point out whats missing in this short code?

tried various ones including code below that you said jack, but still not working, not sure how to do it directly in a query first either

the code is in red & tried it without the "sql_text" line but doesnt help, tried removing different brackets and ""'""s but no luck, ill try again soon but if anyones kind enough to point out where im going wrong that would be great.

SQL_Text = "UPDATE ATTENDANCE SET ATTENDANCE.[HOLIDAY HRS] = " & "(ABSENCE REASON)" & " (WHERE [ABSENCE REASON]="H").

 

[Bat17]

I must confess to being cofused as to what you are doing but here are a couple of formats to look at:-

SQL_Text = "UPDATE ATTENDANCE SET ATTENDANCE.[HOLIDAY HRS] = 123 (WHERE [ABSENCE REASON]='H')"
SQL_Text = "UPDATE ATTENDANCE SET ATTENDANCE.[HOLIDAY HRS] = " & Me.MyField & " (WHERE [ABSENCE REASON]='H')"

Peter

Not sure that you want the () around the where clause either!

 

[Jack Skeleton]

Same for me (being confused, I mean). The problem seems to be located in the "(ABSENCE REASON)" value. Is that "ABSENCE REASON" a value form a field?. There are different ways to refer to it depending on this. Just an additional comment to Bat17 answer: if the value to update is a number, you can compare it directly, if it's a string, you should use inverted commas, in this way:

ATTENDANCE.[HOLIDAY HRS] = 123
or
ATTENDANCE.[HOLIDAY HRS] = """ABC"""
or
ATTENDANCE.[HOLIDAY HRS] = """ & ME.yourfield & """ (rest of the SQL sentence)
or
ATTENDANCE.[HOLIDAY HRS] = """ & stringtmp & """ (if "ABSENCE REASON" is stored in a string variant)

There are hundred of combinations, so if you want a 100% correct answer, please tell us where are the values you want to update coming from...

 

[KevinSlater]

Thanks for your replies bart & jack, im still trying to get it to work now.

ill try to explain better...

Absence reason is a 1 or 2 letter code (text/string) stored in a table named "DATA_ABSENCE_REASONS" a query looks at this table and another table named "ATTENDANCE" the form im tyring to use this code on in turn looks at the query. the "absence reason" is from a field on the form (drop down list box) on the form when "H" is selected (H for holiday) i would like any numbers the user has put in the "hrs absent" input field on the form to be copied over to the "Holiday hrs" field on the table named "ATTENDANCE"

the holiday hours field in the "attendance" table is set to a number (double) and the hrs absent field in the form is also set to number (double)



i just thought perhaps i could do something like this below instead:
(this doesnt work either though)

If Me.ABSENCE_REASON.Value = "H" Then Let [ATTENDANCE]![HOLIDAY HRS] = Me.HRS_ABSENT

 

[Jack Skeleton]

Hi again Kevin,
I think the SQL way would be less complicated for you. A question, the "ATTENDANCE" table has a single row?. If not, you'll have the problem again if the concerning record is not active in your form. I mean that to update the table you will have to create first a recordset, what is maybe more complicated than using a SQL string.
You can identify which record in the ATTENDANCE table you want to update?
I'm not sure of talking properly, I hope you'll understand me...

 

[KevinSlater]

Hi, Jack ive created a yes/no field named "update holiday" in the attendance table and in the form made it so if "H" is selected on the "absence reason" field then the yes/no box is ticked (set to true) I added the following code below & seems to have done the job, i will test it more today to make sure though.

Yes the attendance table has several rows (records), id still be interested in doing it the sql way if you have time to help further, and im gueessing the sql method might be more reliable in the long run.
------------------------------------------------------------------
Private Sub ABSENCE_REASON_AfterUpdate()

If [UPDATE_HOLIDAY] = True Then
Let [HOLIDAY HRS] = [HRS_ABSENT]
Else
Let [HOLIDAY HRS] = 0

End If

End Sub
-------------------------------------------------------------------