Datestamp for Checkbox-Query

[Ran]

If anyone can help with VB code for Access 2007 please. I was asked to assign a datestamp when a user checks a checkbox called "OLIED." I have researched and found a code, I added my fields/checkbox names:
Private Static Sub DateStamp()


If ChkBoxOLIED = True Then
Me.[OLIED DATE] = Date
Else
Me.[OLIED DATE] = Null
End If
End Sub
that assigns a datestamp to a checkbox, but doesnt work because I think I am asking to assign a datestamp to a query field and not within a form with EVENTS to assign. Can this be done to my query "Keith Billing?" Below is my mySQL to help with coding. Thank you all in advance!

SELECT [HIM RAW DATA].Coder, [HIM RAW DATA].[Charge Status], [HIM RAW DATA].[RCA Edits], [HIM RAW DATA].[CM Edits], [HIM RAW DATA].[Research: Details], [HIM RAW DATA].[Charge ID], [HIM RAW DATA].[File ID], [HIM RAW DATA].FC, [HIM RAW DATA].[FC Groups], [HIM RAW DATA].[S ins], [HIM RAW DATA].[Payor Dtls], [HIM RAW DATA].[Pt Acct], [HIM RAW DATA].[MR No], [HIM RAW DATA].[Pt Last Name], [HIM RAW DATA].DOB, [HIM RAW DATA].Provider, [HIM RAW DATA].[Provider Name], [HIM RAW DATA].[Claim Info], [HIM RAW DATA].[Addl Description], [HIM RAW DATA].[Note Description], [HIM RAW DATA].Auth, [HIM RAW DATA].[PBS Flag], [HIM RAW DATA].[Dept / Spec], [HIM RAW DATA].Department, [HIM RAW DATA].[CM Mcal], [HIM RAW DATA].Inpt, [HIM RAW DATA].Started, [HIM RAW DATA].[Dif Add 1], [HIM RAW DATA].[Dif Add 2], [HIM RAW DATA].[RVU-RCA], [HIM RAW DATA].[CPT-RCA], [HIM RAW DATA].CPT, [HIM RAW DATA].[TM ID], [HIM RAW DATA].Units, [HIM RAW DATA].Mod1, [HIM RAW DATA].GP, [HIM RAW DATA].Mod2, [HIM RAW DATA].Mcal50, [HIM RAW DATA].Dx, [HIM RAW DATA].Dx2, [HIM RAW DATA].Dx3, [HIM RAW DATA].Dx4, [HIM RAW DATA].[CPT Description], [HIM RAW DATA].AssistNo, [HIM RAW DATA].AssistName, [HIM RAW DATA].AttendNo, [HIM RAW DATA].AttendName, [HIM RAW DATA].[CPT Type], [HIM RAW DATA].CODED, [HIM RAW DATA].OLIED, [HIM RAW DATA].[OLIED DATE], [HIM RAW DATA].HOLDING
FROM [HIM RAW DATA]
WHERE ((([HIM RAW DATA].CODED)<>0) AND (([HIM RAW DATA].OLIED)=Off) AND (([HIM RAW DATA].HOLDING)=Off));

[June7]

Why doesn't work - error message, wrong results, nothing?

That query is the form's RecordSource? OLIED DATE is a field of the RecordSource so it is available to any code behind that form.

I don't think your code is in correct place. I have never seen the Static keyword. This is general sub behind form? How is it executed - how is it called?

I suggest you try code in the Change event of the checkbox.

[Ran]

No error message, just no results. There is no form built, so no recordsource. The reason for no form, my co-workers code these accounts and need to see all data in rows. trust I have suggested a form and none of them took to it, so that is why I am in this predicament. My query "Keith Billing" needs to have a datestamp in OLIED DATE when OLIED checkbox is checked.

Can you please explain "I suggest you try code in the Change event of the checkbox?" thank you

[June7]

My suggestion was assuming a checkbox on a form.

No form means no way to execute the code you show, not with Access 2007. Access 2010 introduced coding behind tables. Just reread your post, yes you are right about 'not within a form with EVENTS to assign'.

Forms can be built 'to see all data in rows'. Now maybe your users will 'see the light'.

[Ran]

Thanks for the communication June7, yes I must agree with about forms, makes my life easier HAHA!
Well in between messages I went ahead created a form based of my query to show records that are coded. I went ahead and created a macro/action=run code/argument=If ChkBoxOLIED = True Then
Me![OLIED DATE] = Date
Else
Me![OLIED DATE] = Null
End If
Then I get an error "The object doesn't contain the Automation object IF"

[June7]

I don't know much about macros, I use VBA. Don't think IF THEN ELSE will work in macro, that is VBA structure. Use an IIf function.

This article shows how to use macro to save calculated value http://support.microsoft.com/kb/209172. Easiest approach might be an IIf in a textbox: IIf([ChkBoxOLIED=-1,Date(), Null). Then refer to this calculated control in macro. Or try the IIf right in the macro: IIf(Forms!formname!ChkBoxOLIED=-1,Date(),Null)

[Ran]

Thank you for the reply as the macro you explained makes sense to me, but reason I was heading that route because I I created a module called "DateStamp" under the modules extension, but nothing happens to the checkbox once I check it, it doesnt create a datestamp.
here is the VBA:

Private Sub CheckBox_OnClick()
If OLIED = True Then
Me![OLIED DATE] = Now()
Else
Me![OLIED DATE] = Null
End If
End Sub

Any ideas why?

[June7]

That's because there is no way to connect event code to a table. Tables don't have events, forms and reports do. The code would be behind the form that has the checkbox.

Also, macros can't call Sub procedures, only Function procedures.

[Ran]

As you mentioned earlier about event code for a table cant be done, I went ahead and created a form for that query, they have the same name "Keith Billing." So basically I need to update the VBA code posted above with the form info correct?

Form Name: Keith Billing

[June7]

Form in design view, select the checkbox control, open properties window, Event tab, look for Change event (think will be better than Click). Select [Event Procedure], double click the ellipses (...) to open the VBA editor. Type code.

Now() will return current date and time, Date() returns date only.

[Ran]

Thanks June7 works fine for me now, I had the same code in VB, but because the form wasnt recognized in VB, code didnt work. So thanks.

Now I have one more question regarding my other post I had earlier last week in mySQL Server about having an update query update a Alpha split. Basically I have a query built with 3 columns called "S ins" "FC Groups" and "Payor Dtls", now there are 78 rows and each rows equals each other S=FC=PD, but my HIM RAW DATA table has all the data, also I import into this table daily as I receive the files, this table also has the same fields, S ins, FC Groups and Payor Dtls, the S ins has assigned Insurance code in the field, but FC and PDtls are blank, need to be updated with data from the query called Combined SFP based of S ins field. Can this be done by VBA or mySQL? I got an update query to update the HIM RAW DATA table but just for one S ins code 277. See mySQL statement below? would rather have VBA written if possible. Please let me know what you think?

UPDATE HRD SET HRD.[FC Groups] = "MCL", HRD.[Payor Dtls] = 'VHP OP/MPC COMM CLINICS'
WHERE [HRD].[S ins] = "277";

Second, I also have another table that has 15000+ procedure codes that have a RVU (relative value unit) that is equal to each procedure code. is there a way to have VBA code to update my HIM RAW DATA table with these RV units according to the procedure code? Without having to write thousand of lines of code. Maybe this field equals this field, update this field in this table which equals procedure code? I know that a basic overview, but wondering if possible. Thanks again. I can have a form built for these alpha splits.

[Ran]

These are the last steps in completing my database, then I can backload it with all the necessary data.

[June7]

I can't follow all that well enough to advise. I would have to see data if you want to provide project. Make copy and remove confidential date, run Compact & Repair, zip if large, attach to post. Attachment Manager is below the Advanced post editor.

It does sound like need to do table joins for the UPDATE. But is it really necessary to have the data in HIM RAW DATA? If it is possible to link the tables in order to do UPDATE, could just work with join queries.

BTW, the UPDATE statement you show is an SQL action statement. VBA reads and processes SQL statements but that is not technically VBA.

[Ran]

June7, yes it is necessary to have the data in the HIM RAW DATA file, reason for this is to cut down the time our coders have to do research instead of just coding surgeries. it can take a couple work days to for research for 1 file. So by having the these two fileds updated saves great amounts of time/research. I was trying to make their life easier by automating their research by building tables based off insurance info.
So if I do join queries, it will give me the data that is inner joined but the fields wont be updated with the FC Groups i.e MCL and Payor Dtls i.e 'VHP OP/MPC COMM CLINICS'.
That SQL statement is just for one MCL FC Group i.e "S ins" field has data in the field i.e 277 which = "MCL" which = "VHP OP/MPC COMM CLINICS."
I have 38 "MCL" groups, 28 "COMM" groups, 9 "MCR" groups, 3 "ERROR" groups that all equal data in the "S ins" field, then I want the update query to update the HIM RAW DATA table in the FC Groups and Payor Dtls fields according to the S ins field.
Can a mySQL UPDATE statement have MANY values in the WHERE clause as well the SET clause?? Or does VBA code need to be written?

[June7]

An UPDATE can have multiple SET and multiple criteria.

Again, I am really blind without having data to analyse and I still can't understand why a join query can't be used to present the data to your users for research effort.