Backing out of a split form

[Reaper]

I have a split form situation that I have question about.
There are two tables with a one-to-many relationship. The split form is the normal situation with the "many" table form as the subform.

The form works beautifully up to this.



When the user picks an item from the combo box in the sub form, the "afterupdate" event sets a yes/no field in the table to "Yes" . Indicating that the item has been used or consumed. It also sets a field that links the record to the main table.

Question is:

If the user gets into adding the data in the subform and then decides to cancel his entries, how can I program it to back out and undo the records already altered?

[Toyman]

What is the code used to set the yes/no field. Also, how does the user cancel the entry?

[Reaper]

I've toyed with a couple of ways to accomplish what I want. I am building a shipping manifest with the shipping information in the main form and the item details in the sub form. Each item has a unique serial code that is only used one time.

The first way was to use a query against the inventory table to pull only the items that meet two criteria, 1 - it's a "finished good" and 2 - it hasn't been used. #2 is accomplished by a y/n field that is set to true once the item has been used.

The problem with this was that I ended up having a second table just for the shipped item details which duplicated line items with the inventory table. I tried to use a "save" button to get SQL to do an 'update' query on the inventory table to change the y/n field for each item with no success.

I'm now trying to deal with the inventory table directly, but once a combo box selection is made it immediately changes the field. If the user starts and then decides to abort I don't know how to reverse the changes.

The code for the update query follows:

' Do...Loop for updating the inventory table
Me![sbfPickDetails].SetFocus
Me![sbfPickDetails].Form![strTicket].SetFocus
DoCmd.GoToRecord , , acFirst
Do While Not IsNull(strTicket)
DoCmd.RunSQL ("UPDATE tbltransactions SET ynTicketConsumed = true WHERE strTicket = Me![sbfPickDetails].Form![strTicket]")
DoCmd.GoToRecord , , acNext
Loop

The main problem with this is getting it to navigate the records in the sub form properly. I get no result when this code runs.

The user cancels the process by clicking a "Cancel" button on the form.

[Reaper]

I've attached a copy of the database with sample data.

[Toyman]

My recommendation is not check the "consumed" check box yet until the user has finished with the list. Change the wording in the Save/Print button to Checkout/Print. It is assuming that once the user clicks on the Checkout/print button, the user is done with the list. At that time, in the on click event of the button, use an update query to update all the items in the list to check the "Consummed" box.

[Reaper]

All right. That's pretty much where I was headed, so why doesn't my Do-Loop work? Or, if I'm updating the inventory table directly would another method work better?

[Toyman]

All right. That's pretty much where I was headed, so why doesn't my Do-Loop work? Or, if I'm updating the inventory table directly would another method work better?

Instread of using the loop, just use this:

dim sID as integer
sID = Me.strPickLoad_ID

UPDATE tblPickLoadDetails INNER JOIN tblTransactions ON tblPickLoadDetails.strNavisionCode = tblTransactions.strNavisionCode SET tblTransactions.ynTicketConsumed = True
WHERE (((tblPickLoadDetails.strPickLoad_ID)= " & sID & "));"

This will set all items with the PickLoad_ID matching the list to "True"

[Reaper]

Ok, I think I get the idea. However, tblTransactions will not have a value in the strPickLaod_ID field, yet. The tblTransactions and tblPickDetails will be linked by the strTicket field. So the modified code will look like?:


dim sTk as integer
sTk = Me.strTicket

UPDATE tblPickLoadDetails INNER JOIN tblTransactions ON tblPickLoadDetails.strTicket = tblTransactions.strTicket SET tblTransactions.ynTicketConsumed = True
WHERE (((tblPickLoadDetails.strTicket) = " & sTk & "));"

The other half of the process would be to update tblTransactions.strPickLoad_ID with tblPickLoadDetails.strPickLoad_ID. Using the same code?:


dim sID as integer
sID = Me.strPickLoad_ID

UPDATE tblPickLoadDetails INNER JOIN tblTransactions ON tblPickLoadDetails.strTicket = tblTransactions.strTicket SET tblTransactions.strPickLoad_ID = sID
WHERE (((tblPickLoadDetails.strTicket)= " & sTk & "));"

This still leaves me with duplicated data in two tables. Any way around this other than deleting the records in tblPickDetails when I'm done?

[Toyman]

Ok, I think I get the idea. However, tblTransactions will not have a value in the strPickLaod_ID field, yet. The tblTransactions and tblPickDetails will be linked by the strTicket field. So the modified code will look like?:


dim sTk as integer
sTk = Me.strTicket

UPDATE tblPickLoadDetails INNER JOIN tblTransactions ON tblPickLoadDetails.strTicket = tblTransactions.strTicket SET tblTransactions.ynTicketConsumed = True
WHERE (((tblPickLoadDetails.strTicket) = " & sTk & "));"

The other half of the process would be to update tblTransactions.strPickLoad_ID with tblPickLoadDetails.strPickLoad_ID. Using the same code?:


dim sID as integer
sID = Me.strPickLoad_ID

UPDATE tblPickLoadDetails INNER JOIN tblTransactions ON tblPickLoadDetails.strTicket = tblTransactions.strTicket SET tblTransactions.strPickLoad_ID = sID
WHERE (((tblPickLoadDetails.strTicket)= " & sTk & "));"

This still leaves me with duplicated data in two tables. Any way around this other than deleting the records in tblPickDetails when I'm done?

The user should always create the main info first before they enter anything in the sub form. This way, the "strPickLoad_ID" is already created.

I think you should use one of the tables to actually keep track of the total inventory of your product. For example, when you get 16 circuit boards in, that information should go into one of the table. So, the table would reflect 16 Circuit boards. The transaction table should be use when the item is shipped away. Let say, you shipped 5 circuit boards away. You can now figur out the actual number of inventory in you hand

[Reaper]

Yes, I already have the form/subform code written to enforce that.

Thanks! Your code worked perfectly!