How can I update 2 tables concurrently ?

[Paul7905]

I have a Main form with a subform.

The subform has a Table as it's record source. The user can go in and make entries to the underlying table.

When the user updates certain data in the subform (and the underlying table), I wish to accumulate some totals and set some yes/no flags (based on whether the user performed some functions or not).

I added some text boxes to the Main form and then, in the subform code, referenced them when i needed to accumulate or set the yes/no flags. This works wonderfully with one major issue, the user does not wish to stay on this form all day long, i.e. they need to be able to go in, make some updates then exit the application entirely. This may happen several times a day. Since the process must be re-entrant, the user entering and leaving the process several times throughout the day, I need to keep track of their activity somehow. ~ The first thing I noticed was that the text boxes would contain nothing when the form was loaded, thus I lose the information that I put into them when the user leaves the form and then returns later.

The only way I could think of to do this was to create an additional table which would consist of ONE record in which would be defined the two totals and three yes/no fields I need to keep track of. I then changed the text boxes on the the main form and bound them to the fields in the new table....... this did not work, i.e. the code would run with no errors however the record in the new table would never actually update at all.

I next tried creating a new subform with the new table containing the data I need to track as it's record source and changed the code to reference the fields; this would not work either.

Here is example of the code (this code is in the before update event of the subform the user makes the changes in)

'First entry for Sticky
If Me!Qty > 0 And curOriginalValueQty = 0 Then
Forms![FarmLibrarySelect]![ChandlerActivity].QTY = Forms![FarmLibrarySelect]![ChandlerActivity].QTY + Me!Qty

Me!Qty = is the field the user just updated

CurOriginalValueQty = is the value that was in the field before the user made a change to the data

FarmLibrarySelect = is the Main form

ChandlerActivity = is the new subform i created that has the new table containing the fields I am trying to track.

ChandlerActivity.QTY = is the data field in the table taht i am trying to accumulate to.


this code runs ok, but as indicated, nothing happens to update the record in the table, likewise, the field in the subform does not reflect anything after the code runs.

I am guessing, but i am thinking that somehow, i need to tell Access to do the update to the record ? Thinking further, once i do update a field in the record, how does Access know that i want to update this very same record
later?

Maybe my approach is all wet here.

basically, if someone can tell me how to accomplish what i need to do I would be most appreciative.

1. have a form with table the user updates.
2. user may come in and do this multiple time a day then
exit the form.
3. Have 5 pieces of information I need to track while the
user is doing all this.
4. User must have visibility to these fields as they are
making updates, then when they leave the form and return
later, see them as they were when they exited previously.

a. total of quantities entered in column "A"
b. total of quantities entered in column "B"
c. User clicked on button "C" yes/no
d. User clicked on button "D" yes/no
e. User clicked on button "E" yes/no

 

[Robertd]

Hi Paul,

possibly add a table to store the values aginst a user name, if you don't run security a simple "log on" with a user id to refer to the record with the values you need.

when the form closes (onclose event)use an sql update to store the for settings for this user record, when the form opens read the values and set the buttons/controls according to the "user" record.

as for not updating record fields, what is your data source?
if you use a recordset you need to use the update method to save a change to the source table. if you use a dynaset then you can apply the changes directly to the underlying tables.

see ya
RobertD

 

[Paul7905]

Robert, thanks

Actually all I want to do is to,

1. at form load, open a table and read the record in the table. the table contains one record. read the values in the record and stuff the values from the record into some text boxes i have on the main form, then close the table

2. at form exit, open the table, read the record in the table, take the values in the text boxes on the main form and stuff them into the record in the table, close the table and then exit.

I have already created the table with one record in it with fields that correspond to the text boxes on the main form.

my problem is, I don't know how to write the code in the form load and exit events to open the table and do the necessary read on the record and move the contents to the list boxes nor how to write the code to take the contents of the list boxes and update the record from the text boxes in the exit.

I imagine that the table record has to have a "key" (right now it has no key; i figured i did not need one as the table will never have more than one record as the data in it is specific to the high level activity of the form and not user specific, i am only trying to save and restore certain information as to the "state" of what was done by the user each time they enter and leave this form (eventually the user will have completed all necessary activities in order to produce a result i am looking for and i can only keep track of whether they have done so via this data in the single record (because they enter the form and then leave it several times a day...... each time performing cumulative actions i need to track) ;-)

Hope you can help with the how-to code a little (just point me in the right direction, I don't expect anyone to write all the code for me as i can peruse the help files if i know where to look first ;-) thanks !

Paul

 

[Robertd]

ahh, i see

ok depends, there are lots of ways to do this i normally work with variables
as you can do a lot of data checking easily.

now you could add a key to the table and lock it to one entry (this just
makes things easier and beter in the long run)

add a "key" (call it what ever fits in)"RCD_ID" field make it a long int
type a define =1 for the validation rule.
dim these vars in the forms declaractions section so there global within the

forms scope.


dim ItemOne as variant
dim Item_N as variant

in the form open event add
' get the values from the table
ItemOne = DLookup("[fieldname]","[tablename]", "[formID]= 1)
Item_N = DLookup("[field_N]","[tablename]", "[formID]= 1)

'repeat this for each item you want to read from the table

'you could do some data checking but i'll leave this up to
'you ;-)

yourControlOne = ItemOne
yourControl_N = Item_N

'so forth.

'for the form close event

dim UpdateSQL as string

get the values
ItemOne = yourControlOne
Item_N = yourControl_N
' update the record
UpdateSQL = "UPDATE tablename SET "
UpdateSQL = UpdateSQL & "tablename.fieldname = "
UpdateSQL = UpdateSQL & ItemOne
~
~
UpdateSQL = UpdateSQL & "tablename.field_N = "
UpdateSQL = UpdateSQL & Item_N

UpdateSQL = UpdateSQL & " WHERE (((tablename.RCD_ID) = 1"
UpdateSQL = UpdateSQL & &quot);"

DoCmd.SetWarnings (False)
DoCmd.RunSQL UpdateSQL
DoCmd.SetWarnings (True)

now i hacked the sql from one of my projects but should be ok

as an outline i think thats basically what you mean

see ya
RobertD

 

[Robertd]

Hi Paul,

sorry it's these late nights,...

ItemOne = DLookup("[fieldname]","[tablename]", "[RCD_ID]= 1&quot

but you would have picked that up...



see ya
RobertD

 

[Robertd]

i really hate that...

' update the record
UpdateSQL = "UPDATE tablename SET "
UpdateSQL = UpdateSQL & "tablename.fieldname = "
UpdateSQL = UpdateSQL & ItemOne
UpdateSQL = UpdateSQL & ","
~
~
adds a comma seperator to the field list on all but the last
item. sql's a bit fussy with that sort of thing
i think thats most of it

see ya
RobertD

 

[Paul7905]

THANKS !