How to accept / commit changes by a form based on a query?

[mdlueck]

I am thinking to put a crack in my standard of "multiple items forms are read only".

I am developing a parts list import wizard. The wizard populates a FE temp table with the .csv file being imported to the database. Next it does a table scan checking for parts either already a part and not currently associated with the product, or perhaps both records exist and it might be only a "qty per" change.

For the remaining parts, these are totally brand new to the database.

I am envisioning building a multiple items form to allow the importer to QA the data before new DB records are created for the remaining items. The way I build multiple items forms, they are based on a QueryDef object.

The Parts Import Wizard automatically checks a Yes/No field in the FE temp table indicating to import the part. For the parts not found at all in the database, on the form which QA's the new record creation, I want the person to be able to uncheck records which would change that Yes/No field to No, and the Parts Import Wizard would know to skip that / those records. (Perhaps the .csv file had a column heading row which is not a part record.)

What would be a way to leave the form based on a query, to be able to on the form edit the Yes/No field of the record, for the FE temp table to be updated immediately, and for the form to then ReQuery to display the current state of the FE temp table? I am thinking to grab the row id, run an UPDATE query toggling the Yes/No value of that record... I just can not think of how to have the form both based on a query AND to have one column's controls yet editable. All other columns will be read-only.

Suggestions?

 

[mdlueck]

It seems that Access has capabilities to support up-datable Queries.

I built a QueryDef for the FE temp table, entered my simple criteria, and when I toggle the YesNo field and MOVE OFF OF the record, then open the FE temp table, I see the updated state of the YesNo field.

So, how to guarantee that the UI matches the table?

Like I said, I see the update when I MOVE OFF OF the record... not prior. So if someone changed a check box state, did not move off, and the form event to continue the import was triggered, then I might not have that last YesNo decision from the user.

Or is moving the mouse to a button / or Enter key to complete the form, enough to commit the last update?

 

[mdlueck]

As I was afraid of... see the attached screen shot.

I unchecked the addpartrecord check box for the top (header) record, then clicked the Rollback button (which has no code defined to it at this point).

Behind is the FE temp table showing the data change never got to the table.

If I move off of the record, then IMMEDIATELY I see the update in the table window.

So, any suggestions how to force pending data change to the table?

 

[vbaInet]

Commit it in the After Update event of the checkbox using one of:

If Me.Dirty Then Me.Dirty = False

DoCmd.RunCommand acCmdSaveRecord

 

[mdlueck]

Oh wow...

Private Sub addpartrecordflg_AfterUpdate()

  If Me.Dirty Then
    Me.Dirty = False
  End If

End Sub

Way cool!!! I can have the same screens up, and each time I click the check box on the form, the table window IMMEDIATELY updates!!! Thanks once again vbaInet!

 

[vbaInet]

Yes it's nice but think about how many clicks a user can do * how many users * how many times you're interrogating the db engine. I guess SQL server can handle it since that's your BE.

 

[mdlueck]

Yes it's nice but think about how many clicks a user can do * how many users * how many times you're interrogating the db engine. I guess SQL server can handle it since that's your BE.

This is in a FE temp table actually. Records not yet created in the BE DB.

This screen allows for proposed new records to be QA'ed prior to actually creating them.

I anticipate usually a stray CSV file column header row will be the only one needing to be deselected.

Thus my concern... "Click the row to disable it, Click the Commit button" without ever moving off of that one record. Without pushing it to the FE table, the Import Wizard would still see that record selected for import.

According to this page, it appears I selected the preferred solution of four possible solutions:

"Microsoft Access Flaws - Losing data when you close a form"
http://allenbrowne.com/bug-01.html

 

[vbaInet]

That should be alright then.

 

[mdlueck]

Suggestion still not 100% reliable...

Frequently I get a popup box saying that data has changed in the table, what would I like to do. I chose "Copy to clipboard" and here is what it copied:

partsimportwizardaddconfirm fldprojecttitle fldproducttitle fldid fldpartnumber fldtitle fldaddpartrecordflg fldqtyper fldSelectedRecordBar Update #1 via SQL - This is a way cool test! This is my first test with Project Title on the Edit form 1 ITEM DESC 0 0 1

Which is that CSV header row I unchecked.

And I end up with a record created for that header row sometimes.

So this form is based on a DAO.QueryDef object. And I use ADO type type code in VBA to cursor through the result set and create records in the BE DB.

It is appearing that SOMETIMES the update is not getting into the FE temp table before the ADO query executes.

I have added a call to DoEvents in the Commit button, same as Cancel which is my work-around to eat up stray Esc keystrokes since the forms are coded for keyboard control. That still was not enough, however.

I put a print.debug in the record processor loop. Both times I unhecked the header row, both times I got the attached error dialog. The header row only appears in the first run. So inconsistent DB results, and a consistent error dialog.

ITEM DESCITEM DESC
0715201126 SLIP-ON PUMP PEDAL PLASTIC,BLK
0721660035 SECHRIST BELLOWS
0753003099 BUTTERFLY 'V' PEDAL
0753010017 LABEL-BRAKE/STEER,H/E, RED
0753010051 LABEL-BRAKE/STEER,F/E,RED
0753010091 INTL BRAKE STEER LABEL RED
0753010151 LAB-BRAKE/STEER,F/E,PURP 12/01
0753010170 LAB-BRAKE/STEER,H/E,PURP 12/09
0753010251 LAB-BRAKE/STEER,F/E,GRN 12/09

0715201126 SLIP-ON PUMP PEDAL PLASTIC,BLK
0721660035 SECHRIST BELLOWS
0753003099 BUTTERFLY 'V' PEDAL
0753010017 LABEL-BRAKE/STEER,H/E, RED
0753010051 LABEL-BRAKE/STEER,F/E,RED
0753010091 INTL BRAKE STEER LABEL RED
0753010151 LAB-BRAKE/STEER,F/E,PURP 12/01
0753010170 LAB-BRAKE/STEER,H/E,PURP 12/09
0753010251 LAB-BRAKE/STEER,F/E,GRN 12/09

Any further suggestions?

 

[vbaInet]

Suggestion still not 100% reliable...

Frequently I get a popup box saying that data has changed in the table,

You get this message because you've changed data from two (or more) objects (i.e. forms and/or tables) at the same time and it (i.e. Access) needs to know which one you would like to commit. So if you changed data on Table 1 and change data on Form 1 that is bound to Table 1, when it comes to saving it will prompt you.

Will your clients be doing this sort of thing?

 

[mdlueck]

Will your clients be doing this sort of thing?

As I am unit testing the UI, and this is how one proceeds through the UI, I would think they will encounter this.

I do not see where I have unintentionally "double edited". In my mind a bit dangerous to be editing the results of a query, so I would have anticipated such problems doing what I am doing. But since Access supports such, I would like to see it do something useful reliably. Lunch break...

 

[vbaInet]

It doesn't have to be manual, it could have been in code.

In any case, if you have two forms open that use the same source, before you commit, undo the changes on the other form then commit on the calling form.

 

[mdlueck]

During my lunch time walk, I realized where the sharp spot is.

Your suggestion at least forced the control value into the table.

Nothing ever committed the table.

So I am off in search of the correct way to send a Commit message from a Form based on a Query. I hope sending a Commit before creating the ADO object and running the query clears that nag message up.

 

[vbaInet]

Nothing ever committed the table.

Care to expand on this? We're not dealing with a transaction. The Jet engine deals with this automatically when you issue the commands already mentioned.

So I am off in search of the correct way to send a Commit message from a Form based on a Query. I hope sending a Commit before creating the ADO object and running the query clears that nag message up.

I've just told you what is causing the message to pop-up and the link you found shows you how to save to the table. Once it's saved it's committed.

 

[mdlueck]

Care to expand on this? We're not dealing with a transaction. The Jet engine deals with this automatically when you issue the commands already mentioned.

rrr....???? The link I posted merely stated that me taking your:

If Me.Dirty Then Me.Dirty = False

work-around was the best one. I see nothing about committing the updates to the table.

I've just told you what is causing the message to pop-up and the link you found shows you how to save to the table. Once it's saved it's committed.

So if your suggestion is "good enough" to commit the update, not merely "have it in the table just not committed", then what else? Must I close the entire form before continuing on with my Part Import Wizard? (So that the DAO query object is not touching the table?)

 

[vbaInet]

So if your suggestion is "good enough" to commit the update, not merely "have it in the table just not committed", then what else? Must I close the entire form before continuing on with my Part Import Wizard? (So that the DAO query object is not touching the table?)

No you don't have to. In my post #12, I explained what you need to do.

Before saving, undo the changes to any other form using the source on that same ID, then commit. Or look into record locking mechanisms - disallow a user from editting a record that is already being editted elsewhere on the same FE.

 

[mdlueck]

Adding this bit to the code behind the Commit button:

  'Make sure all updates are committed / flush the update queue
  Me.Requery

appears to have resolved the troubles. (shrug) No more message boxes.

P.S. Oh, and the CSV header line deselect is consistently honored!

 

[vbaInet]

Lovely!!!

But... I'll give you a scenario:

1. Open your form and edit a record... don't save.
2. Perform an update on the same record perhaps using a DAO recordset and save.
3. Now go back to your form and click that save button.

What happens?

 

[mdlueck]

Lovely!!!

Thank you

But... I'll give you a scenario:

1. Open your form and edit a record... don't save.
2. Perform an update on the same record perhaps using a DAO recordset and save.
3. Now go back to your form and click that save button.

What happens?

But that sort of thing "is not supported" by the Parts Import Wizard. Think "single threaded" work flow. There is nothing else dealing with this FE temp table than the Part Import Wizard itself. So the scenario in Step 2 would never be possible.

 

[vbaInet]

Ok, that was what I asked earlier and you said it will

I think you should re-evaluate your code and do more digging to see where this problem is coming from before moving on. Me.Requery doesn't commit, it fetches. It's definitely a double edit within the same local FE table.