How to remove one row/record in a continuous subform

[T. McConnell]

Greetings all,
I have a question maybe could get some assistance on. I have a subform that will display parts available based on specific vehicle information (Year/Make/Model) this all works fine. What I am trying to do is when lets say I add more than one part to the subform but then realize I only need one. I added a remove button which when clicked will uncheck it from being a used part, remove the row from the list, and then run a query to remove the line from my Order Details. What is happening is if there is more than one and I click the remove button it removes all from the subform and instead of having both show back up in my list after a requery of the form, it only shows the oldest ID part available. I have attached pictures of the form before removing it, and what it looks like afterwards.
List picture is what is showing available to add.
List1 picture is when both added to the form.
List2 picture shows what is showing available after hitting the remove button and both clear from the subform. This also shows where the oldest ID is available. and the newest is no longer shown.


The codes below are what is being used.

Remove Button Code:

Private Sub btnRemove_Click()
Me.UsePart.Value = False   'This unchecks the used part checkbox
Me.Requery                      'This will requery my form to show the parts available based off if used part is checked or not.
DoCmd.OpenQuery "QryRemoveOtherInv"  'This query will remove the 0 quantity of my Order Details table (for reporting purposes mostly)
End Sub

This is the Query Code to remove the Inventory (In SQL view):

DELETE OtherOrderDetails.OtherOrderNumber, OtherOrderDetails.OtherProductID, OtherOrderDetails.Quantity, OtherOrderDetails.ProductCost
FROM OtherOrderDetails;

I am just trying to figure out how to remove only one if needed, each row/record is tied to an OtherProductID, so if I can figure out how to code the remove button to just remove that row by the ID would be helpful.

Thanks for any and all help from the community.

 

[Deleted member 147267]

I'm not 100% clear on which aspects are running OK and which are not, and what is linked to what. You show the delete sql but don't clearly state if that is QryRemoveOtherInv, and is that the table that the form is based on? I'll suggest that your issue is because you don't provide criteria for the delete. Try

FROM OtherOrderDetails WHERE
OtherOrderDetails.ID = Forms!frmName.txtboxName

Of course, you substitute the correct object names since I don't know them. Have to say that it's been a long time since I've removed records from a continuous form in this fashion (if ever) so I'm not entirely sure if the suggestion will remove the correct record because you are using a button to do this. Without seeing the form design, I can surmise that your checkbox is bound, which would make the correct record the current one when you check/uncheck it. A button cannot be bound, thus could a different record be the current one when you click a button on another list row? Personally, I'd uncheck the box to remove the record and do away with the button - assuming your checkbox is bound.

 

[arnelgp]

delete only those records whose UsePart = False

DELETE OtherOrderDetails.OtherOrderNumber, OtherOrderDetails.OtherProductID, OtherOrderDetails.Quantity, OtherOrderDetails.ProductCost
FROM OtherOrderDetails [COLOR="Blue"]WHERE ((OtherOrderDetails.UsePart) = False)[/COLOR];

 

[T. McConnell]

I tried using this for my delete query, it seems to almost work but I am getting the enter parameter msg when trying to click on the remove button.
The code I am using for the delete query is below, and yes the delete query is the one called QryRemoveOtherInv.
The subforms record source is a query derived of 4 different tables to get specific fields to show where needed.

DELETE OtherOrderDetails.OtherOrderNumber, OtherOrderDetails.OtherProductID, OtherOrderDetails.Quantity, OtherOrderDetails.ProductCost
FROM OtherOrderDetails
WHERE (((OtherOrderDetails.OtherProductID)=[Forms]![fsubOtherOrderDetails]![OtherProductID]));

I also tried

DELETE OtherOrderDetails.OtherOrderNumber, OtherOrderDetails.OtherProductID, OtherOrderDetails.Quantity, OtherOrderDetails.ProductCost
FROM OtherOrderDetails
WHERE OtherOrderDetails.OtherProductID=Forms!fsubOtherOrderDetails.OtherProductID;

These both give me the enter parameter error. I appreciate your help, I am slowly in the right path, as when I enter the Other Product ID in the Enter Parameter box it clears just the one from the form and is able to be reselected like I am wanting, just now need to figure out why I get that msgbox.

Thanks again!

 

[Deleted member 147267]

the other suggestion may work if your checkbox is bound - which I wondered about but still don't know. If you're getting a parameter prompt when trying to run an action query from a form, you probably mis-spelled a reference (like the form control name) in the query design grid. If you're running sql from code and not an actual query, then I'd expect that prompt without coding to deal with it first.
EDIT - just noticed something...

The records are on a subform - fsubOtherOrderDetails?
Then the reference is incorrect:

[Forms]![Main form name]![subform control name].[Form]![control name on subform]
where subform control name is the name of the control that contains the subform.

 

[T. McConnell]

Wanted to do a quick reply back on this one. I have come to a solution that will work for what I am attempting.
Basically I have a subform built off a query including my orders, other order details, and other inventory tables. When the person selects a part, it autofills the other textfields and calculates prices for the main form. The issue I was running into mostly was my Other Order Details table which was storing the parts used with a Quantity of 0, this made it impossible to add the part back to an order if it wasn't needed. So I created the QryRemoveOtherInv Delete query to remove those values when the remove button was clicked. I got it working but that was where I was getting the parameter msgbox.

What I did to "fix" my issue was on the remove button have a msgbox pop up asking Yes/No to remove the part from the order. If Yes is clicked it runs the remove query, I still get the Enter Parameter popup, but I made my own Entry in the Criteria to "Enter the ID of the part to Confirm" this then unchecks the UsePart checkbox, (which yes is a bound checkbox, sorry for not specifying that earlier) then removes it from the Other Order Details Table, and makes the part available again if needed. I apologize for my real crude explanation, I know what I was trying to do and sometimes explaining it is harder for me. So thank you all for the help, I really do appreciate it, every bit helps me to hopefully get a better grasp on it.

Below is the code for my remove button in case you wanted to see what my attempt is haha, as well as a picture of the QryRemoveOtherInv query in design/grid view. The section below the End If statement is just to calculate the cost piece on the main form.

Private Sub btnRemove_Click()

Dim msgtext As String

msgtext = "Remove " & Product & " from order?"

If MsgBox(msgtext, vbYesNo + vbQuestion) = vbYes Then
Me.UsePart.Value = False
DoCmd.OpenQuery "QryRemoveOtherInv"
Me.Requery
End If
    Forms![frmAdminOrders]!LaborCost = Forms![frmAdminOrders]!LaborTotal
    Forms![frmAdminOrders]!TotalCost = Forms![frmAdminOrders]!AllTotal
    Forms![frmAdminOrders]!EstJobCost = Forms![frmAdminOrders]!txtEstCost
    Forms![frmAdminOrders]!LaborTotal.Requery
    Forms![frmAdminOrders]!AllTotal.Requery
    Forms![frmAdminOrders]!txtEstCost.Requery
End Sub

Thanks again, I am really glad I have found this community, has been a great help.

 

[Deleted member 147267]

You did a work around, but there is a more "proper" way. If you get the parameter prompt when opening a query in code, you can go to the query property sheet and define the parameter there. That should resolve it.

If you get the prompt in code, you will see lots of examples where the solution is to define a query def and its parameters and assign values - a somewhat complicated approach. I have found that what often works and is simpler is to assign the Forms! reference to a variable and then use the variable in the code sql instead. Code sql cannot resolve references such as Forms! but the part of the code where you create and assign the reference can. Then the sql part can deal with the value that the variable gets from the Forms! reference. Hope that's not too confusing.

 

[Pat Hartman]

Other Order Details table which was storing the parts used with a Quantity of 0, this made it impossible to add the part back to an order if it wasn't needed.

Why would you add a part back to an order if it wasn't needed?

From what I see, this process is more complicated than it needs to be.

If you want to delete a row from a continuous form, use a button on each row of the continuous form. If you are using DS view, you can't add buttons so I make an unbound text field with its control property:

="Delete"

Then in the double click event of this field, I delete the current record. No need to run a query, no need to try to figure out what the PK is for the row the user wants to delete. The control is on the form displaying the the record you are trying to delete.

If you insist on having the delete button on the parent form, then you need to be very clear regarding exactly which row the user intends to delete. Assuming the user has not yet clicked into any record to cause it to become current, the first visible row is always the current record and that is what would be deleted by your delete button. One way to clarify the record to be deleted is if in the current event of the subform, you copy the PK of the subform record to an unbound control on the main form. That way there is some positive inference. Your delete code should examine the unbound control and if it is empty, no record has been selected and therefore your code would raise an error and exit. If there is a PK present, i would prompt the user and display something that identifies the record to be deleted just to be sure. After the delete, your code should clear the unbound control. This method of course takes more code and contains more risk. When a user clicks "delete" on a specific row, he is probably clear in what he wants to delete. When the delete button is not on a specific row, the user may be fuzzy about exactly which row will get deleted and he might think a different record is selected than what is actually selected.

There seems to be some confusion regarding the operation of multi-row forms in this thread. Multi-row forms whether continuous or DS view, still operate exactly the same way a single form does because only a single record can be "current" at any one time regardless of how many are visible. All actions happen to the current record.

 

[mloucel]

Why would you add a part back to an order if it wasn't needed?

From what I see, this process is more complicated than it needs to be.

If you want to delete a row from a continuous form, use a button on each row of the continuous form. If you are using DS view, you can't add buttons so I make an unbound text field with its control property:

="Delete"

Then in the double click event of this field, I delete the current record. No need to run a query, no need to try to figure out what the PK is for the row the user wants to delete. The control is on the form displaying the the record you are trying to delete.

If you insist on having the delete button on the parent form, then you need to be very clear regarding exactly which row the user intends to delete. Assuming the user has not yet clicked into any record to cause it to become current, the first visible row is always the current record and that is what would be deleted by your delete button. One way to clarify the record to be deleted is if in the current event of the subform, you copy the PK of the subform record to an unbound control on the main form. That way there is some positive inference. Your delete code should examine the unbound control and if it is empty, no record has been selected and therefore your code would raise an error and exit. If there is a PK present, i would prompt the user and display something that identifies the record to be deleted just to be sure. After the delete, your code should clear the unbound control. This method of course takes more code and contains more risk. When a user clicks "delete" on a specific row, he is probably clear in what he wants to delete. When the delete button is not on a specific row, the user may be fuzzy about exactly which row will get deleted and he might think a different record is selected than what is actually selected.

There seems to be some confusion regarding the operation of multi-row forms in this thread. Multi-row forms whether continuous or DS view, still operate exactly the same way a single form does because only a single record can be "current" at any one time regardless of how many are visible. All actions happen to the current record.

YES... Thanks that solved my issue, instead of a button saying delete I created a button with a RED X, users now know what this means, of course the regular double warning of are you sure... bla, bla, bla..

Thanls @Pat Hartman

 

[Pat Hartman]

Great. The "button" on the specific record eliminates any confusion regarding what the user wants to delete.

 

[mloucel]

Great. The "button" on the specific record eliminates any confusion regarding what the user wants to delete.

here it is, I am thinking [yes, it is dangerous], what if I hide the X while the record is in the (NEW) line, then enable it only when the record is saved?
I am thinking something like if me.NewRecord then X=visible=false
then on the after event make it enabled
well I am thinking too much
or maybe just a simple if me.newrecord then msgbox "You cannot delete a NEW Record"
we'll see..

Thanks anyway.

 

[LarryE]

View attachment 116361
here it is, I am thinking [yes, it is dangerous], what if I hide the X while the record is in the (NEW) line, then enable it only when the record is saved?
I am thinking something like if me.NewRecord then X=visible=false
then on the after event make it enabled
well I am thinking too much
or maybe just a simple if me.newrecord then msgbox "You cannot delete a NEW Record"
we'll see..

Thanks anyway.

Nothing happens when you delete a new record because it is not a saved record yet, so don't worry about it. You can't delete a new record.

 

[Pat Hartman]

what if I hide the X while the record is in the (NEW) line, then enable it only when the record is saved?

Won't work. Access only maintains a single set of properties for a form so when you are on a new record, the x will be hidden for ALL visible rows and when you are on an existing record, it will also be visible on the new row.

You need code in the click event of the x to ignore it if you are on a new record.

 

[isladogs]

Although that is definitely the case for conditional formatting, that comment isn't completely true.
For example, if you only want users to be able to delete inactive records, you could modify the buttons using the transparency property so buttons aren't visible when the record is active. Here's a similar example based on Northwind2:

For details, see my article:

Paint Me Transparent

This article describes a very simple method for selectively showing / hiding command buttons and other controls in a continuous form.

www.isladogs.co.uk

 

[mloucel]

Although that is definitely the case for conditional formatting, that comment isn't completely true.
For example, if you only want users to be able to delete inactive records, you could modify the buttons using the transparency property so buttons aren't visible when the record is active. Here's a similar example based on Northwind2:

View attachment 116363

For details, see my article:

Paint Me Transparent

This article describes a very simple method for selectively showing / hiding command buttons and other controls in a continuous form.

www.isladogs.co.uk

Thanks Colin, that is another level, honestly, I had no Idea of the transparent property, thanks.

 

[Pat Hartman]

I had no Idea of the transparent property, thanks.

Your form is DS view. Colin's sample is Continuous. I didn't get a chance to check if the transparent property works for DS view. We know you can't show buttons.

 

[isladogs]

The form in @mloucel’s post #11 is continuous. It contains the Red X button control as already discussed.

 

[Pat Hartman]

And you are correct. It was in another thread where he told me he had used a text box on a DS form.

 

[mloucel]

Your form is DS view. Colin's sample is Continuous. I didn't get a chance to check if the transparent property works for DS view. We know you can't show buttons.

Thanks Pat, but most of my forms are always made Continuous, I really don't know what is DS view.. sorry for my impertinence.