Grab value of field when on a record in a grid

[Jberry]

Hi Everyone,

What I need to do is create a copy of a record when a person changes the "Status" of it. I have a field called Status and it can be changed from A to B to C as the record progresses through its life-cycle. Only the latest status should be visible on the grid. So if someone edits the record's Status from A to B then when they click Submit I want to be able to save that record as it was before they made the change.

In the OnChange of the Status field I can save the initial value by using e._currentRowDataNew.Status, however, if the user goes back and changes it again before clicking the Submit button then the original value is lost.

This is being done on a Grid. Access backend. I can use the Sql to save the record once the user clicks Submit, IE:

Dim cn as sql::connection
Dim sqlCommand as c
Dim args as SQL::Arguments
dim rs as sql::ResultSet
...

but I can never seem to get that far because I don't have the old Status.

Maybe I'm going about this the wrong way?

Any help is appreciated, I've been stuck on this for a few days now and not moving forward at all.

Thanks

-John

Sorry, meant to say "e._currentRowDataOld.Status"

[mixter]

Re: Grab value of field when on a record in a grid

The way I would do this is a roundabout way, but far more efficient and you will get performance boost in several places

(a) upsize Access backend tables to a SQL Server Express database. SQL Express is free, but only uses one core of CPU (which normally is enough for many users)

(b) to create the status logging table you want, this can be achieved with a trigger in a SQL table (lots of info on the web for creating these kinds of triggers)

Also, if you have an access front-end already that hooks into your Access back-end, just re-link using ODBC to the SQL Express

This way you get a faster database engine (massively faster for complex queries)

You also offload the archiving of previously modified records to a new table via the trigger, so all happens inside SQL Server, much more efficient

And if you have an Access front end also, it will experience moderate performance gain just by changing the back end and linking tables as ODBC (you may need to rename the linked tables as they will come in as dbo_tablename and you can just rename the links as tablename)

Otherwise, to do exactly what you have specified above, I will leave it to someone else to give a more direct answer :)

[Jberry]

Re: Grab value of field when on a record in a grid

Hi Michael,

That's on the cards but it's not something I can do immediately; it does make me want to push harder to get it moving though.

[GGandhi]

Re: Grab value of field when on a record in a grid

would you give more detail how you are doing this. your first post has few unexplained notions, at least to me.
your backend is access, that I got it.
you are doing in a grid - is it updatable grid or read only grid with updatable detail view.
and you are updating via xbasic using dim sql::connection etc., how are calling that function, from a button that you created?
finally, not the least do you want to save the entire record along with the status field before it was changed. so if there are three status options you will end with three records one for every one to see and two in a log saying when and by whom it was changed, is that correct?
it will be all simpler if you post a video or screen capture.

[Jberry]

Re: Grab value of field when on a record in a grid

Hi Ghandi

Grid is editable - no detail view.

The idea is to save the current un-edited version of the record before the Status field is changed and I'm guessing this is done in the CanUpdateRecord in the Server Side Events but clearly I don't know.

Active Table
Cust Numbr. Status Comments Date
0001 Pending 4th Rec 3/28/14 <- This is the most current record


If the user comes to the above record and changes the Status from Pending to Approved then the Pending record would be copied to the history table and the Approved would remain in the active file.

History Table
Cust Numbr. Status Comments Date
0001 Applied 1st Rec 1/1/14
0001 Pending 2nd Rec 2/1/14
0001 Review 3rd Rec 3/1/14

I'm open to anything. I can't be the only one who has ever needed to do this

Thanks

-John

[GGandhi]

Re: Grab value of field when on a record in a grid

okay
everybody has their own style and implementation.
in my opinion the grids are informational purpose mostly and dialogs are insert data mostly even though they can be used in opposite direction.
I would use a read only grid to show the information and add a button to edit the record and save the information on the primary table and as well as the log table.
if you want me to show that please post back, I will post a screencast.

[Jberry]

Re: Grab value of field when on a record in a grid

Hi Gandhi,

I like the way that sounds, I'd love a screencast of it. I use grids mostly because I'm not too comfortable with the dialog, can never seem to get the fields wide enough etc.. but I'm open to try anything at the moment.

Appreciate you replying.

-John

[GGandhi]

Re: Grab value of field when on a record in a grid

take a look at this screencast:
http://screencast.com/t/7wweTu1AKB8W

dim original_customer_id as n
	dim original_status as c
	dim original_comments as c
	dim original_date as t
	
	original_customer_id = e.oldDataSubmitted.customer_id
	original_status = e.oldDataSubmitted.status
	original_comments = e.oldDataSubmitted.comments
	original_date = e.oldDataSubmitted.date_entered
	
	
'To edit this action, place insertion point anywhere in the command, then click the 'Action Scripting' button'.
ExecuteServerSideAction("Save Data::Save_Submitted_Data_to_Table_s_")

	if rtc.flagRecordWasSaved then
	dim cn as sql::Connection
	dim args as sql::Arguments
	dim result as l
	result = cn.Open("::Name::alpha_access")
	if result
		
		dim sqlInsert as c
		sqlInsert = <<%SQL%
		INSERT INTO alpha_table_history(customer_id,status,comments,original_date)
		VALUES(:newCustomer_id,:newStatus,:newComments,:newDate)
		%SQL%
		
		args.add("newCustomer_ID",original_customer_id)
		args.add("newStatus",original_status)
		args.add("newComments",original_comments)
		args.add("newDate",original_date)
		result = cn.Execute(sqlInsert,args)
		e.javascript = e.javascript + "alert('customer is updated and log entry done');"
		
		cn.close()
		
		end if
end if

this code is in afterValidate event.
see if this helps.

http://screencast.com/t/MNuCdhtawNk
did this video little slower than the other one, same information.

[Jberry]

Re: Grab value of field when on a record in a grid

Gandhi,

My hat off to you sir. Excellent. Tomorrow I am going to re-do my screens and see how it works out. It'll probably take me some time because I have a lot more fields but I'll certainly let you know how it works out.

One last question:

sqlInsert = <<%SQL%
INSERT INTO alpha_table_history(customer_id,status,comments,original_date)
VALUES(:newCustomer_id,:newStatus,:newComments,:newDate)
%SQL%

are you enclosing this between <<%SQL% so that you can put the code on different lines? Because I will have many fields I'll need to go over a few lines.

Thanks much Gandhi, this was a tremendous help.

-John

[GGandhi]

Re: Grab value of field when on a record in a grid

yes, in short.
this will avoid using the quote marks to enclose and the line snaking all the way to Australia.
the code will look better because you are breaking into segments
and most importantly you can see and check all spellings are correct, that is my common mistake.

glad to be of help.