Requery

[Perfac]

1. Open the form f02SalesInvoices, go to the first line on the sub form on "Inventory".


2. Bottom left there is a field named "txtUnitPriceSaleD", a zero displays. The same with the second line on the sub form.
3. Go to the third line, the field at the bottom display a 1 and in the middle of the form a blue button appears. "Set Sales Unit Price Manually".
4. The blue button is named "btnSalesUnitPrice". The intent is have the button visible when "txtUnitPriceSaleD" is greater than zero. That is all.
5. The VBA under the form"Current event" is;
If Me.txtUnitPriceSaleD > 0 Then
Me.btnSalesUnitPrice.Visible = True
Else
If Me.txtUnitPriceSaleD = 0 Then
Me.btnSalesUnitPrice.Visible = False
End If
End If
6. Create a fourth line on the sub form, select "Other Income" again, and from the list that came up select "Commission" or "Donations" or "Interest".
7. The field "txtUnitPriceSaleD" bottom left will display a 1 when one of the three in 6 above is selected.
8. The blue button should have appeared, but it does not. It may be a "requery" issue. I tried a few ways, but not a way that works. Go to a previous line and return to this line then it works. The moment "txtUnitPriceSaleD" changes from 0 to 1, the blue button must display.
9. The sub form is f02SalesInvoicesSub. There is one "Public sub" in the VBA that does part of the job here.

[June7]

That code you posted is not the code that is setting button visibility when navigating subform. That would be:
Me.Parent.btnSalesUnitPrice.Visible = Nz(Me.txtUnitPriceSaleD, 0) = "1" 'added by Vlad

When I attempt step 6 I get error: The form name 'f02InvLedgerAccItem' is misspelled or refers to a form that doesn't exist.


I advise not to use exact same name for multiple controls/objects.

[Perfac]

Thank you. Since I started maybe 7 years ago, I kept to a policy of no two field names the same, and we created more than 3000 names for the app, keeping a separate table to issue the names without duplication. I make more concentration errors than I should, but the show must go on. The form name f02InvLedgerAccItem was changed, I will look at it now.

[June7]

That form is not in posted db.

txtUnitPriceSaleD name is used on main and subform.

[Perfac]

There was previously a form named f02InvLdgrAccItm, but for years f02InvLedgerAccItem was doing the job correct. In the sample I posted, please tell me how you got that error. I do not find any referral to f02InvLdgrAccItm. If I remove the VBA code you refer to, from the sub form (Me.Parent*****) then the blue button does not appear at all. I just tested the code on the main form under the current event, even though I thought it should work it does not. Which two controls got the same name? I did try to find errors through debugging (Compiling).

[Perfac]

That form is not in posted db.

txtUnitPriceSaleD name is used on main and subform.

The form f02InvLedgerAccItem does exist. I will rename txtUnitPriceSaleD as txtUnitPriceSaleD2 on the main form. Of course the posted sample is a fraction of the app, other errors will show which should not influence the challenge discussed here. Thanks.

[Perfac]

Sorry f02InvLedgerAccItem is not on the posted sample.

[June7]

I did not suggest removing code I referenced in previous post (it should stay). Just pointing out the code you posted is not really relevant.

This code references missing form, disabling causes other issues:

Private Sub cboLedacc_IDi_AfterUpdate()'----------------------------------------------------------
       DoCmd.OpenForm "f02InvLedgerAccItem", _
        View:=acNormal, _
        WindowMode:=acDialog, _
        OpenArgs:=Me.Name & "|cboComent_IDx|" & Me!cboLedacc_IDi
    Me.cboComent_IDx.Requery
    DoCmd.Close acForm, "f02InvLedgerAccItem"
'----------------------------------------------------------
End Sub

Debug > Compile revealed missing library references.

[Perfac]

I added f02InvLedgerAccItem, renamed txtUnitPriceSaleD as txtUnitPriceSaleD2 on the main form. The code on the sub form (Me.Parent*****) is still there.

[Edgar]

Hi Perfac
I got stuck in step 1 because when I opened the database, a form opens taking the entire screen. I commented out the maximize and resize code and then the form loaded in a more manageable way, but then I tried to follow the next step, where you mentioned that the second line displays a zero, however, that is not the case, so I could not follow your instructions to reproduce the behavior. I also saw that when I tried to close the form using the custom button, it tries to execute an action query that if you cancel, it throws another error.

[Perfac]

Table "t09SalesPricesAll" was cleared by the sample, I fixed that here as well. Table "t09SalesPricesAll" causes the 1 in bottom left corner of the main form.

[Edgar]

Perfac, you can do this in your f02InvLedgerAccItem form code

Private Sub LstItems_AfterUpdate()
On Error GoTo ErrorHndlr
'If (sForm = "f02PrchsInvcsSub") Then
    'Form_f02PrchsInvcs.f02PrchsInvcsSub.Controls(sControl) = Me.LstItems
If (sForm = "f02SalesInvoicesSub") Then
    Form_f02SalesInvoices.f02SalesInvoicesSub.Controls(sControl) = Me.LstItems
'ElseIf (sForm = "f02PaymentsSub") Then
    'Form_f02Payments.f02PaymentsSub.Controls(sControl) = Me.LstItems
'ElseIf (sForm = "f02ReceiptsSub") Then
    'Form_f02Receipts.f02ReceiptsSub.Controls(sControl) = Me.LstItems
'ElseIf (sForm = "f02JournalsSub") Then
    'Form_f02Journals.f02JournalsSub.Controls(sControl) = Me.LstItems
End If
    If Form_f02SalesInvoicesSub.txtUnitPriceSaleD > 0 Then
        Form_f02SalesInvoices.btnSalesUnitPrice.Visible = True
    End If
    Me.Visible = False
    
ErrorHndlr:

End Sub

I just added the if/then before the Me.Visible = False. I have to say, though, this one was tough. There are some atypical workflows going on there.

[Perfac]

Great. Thank you. All seems perfect on this challenge.

There is something else here. I probably have the ability to get the selection list not to list items already selected in the Sales invoice. The user should not use the exact item more than once, but maybe the Sales Invoice must allow a second time selecting the exact same item.
1. If "Other Income is selected in a following line, and "Discount Received" is selected a second time.
2. The blue button appears as it should because your advice, thanks.
3. But when I set the Sales Unit Price on the little form that opened, it changes the unit price of the record the first time "Discount Received" was captured, and not the current line as it should.

[Edgar]

Mind writing a step by step tutorial of what happens and what it should do? You already did, but I'm not sure I understand.

[Perfac]

1. Open the form Sales Invoice. The selected items on line three on the sub form was "Other Income" and "Discount Received".
2. It does not matter but make the quantity 1 and click on the blue button. Capture any amount say 200. The small form will close and the 200 will reflect on the main form.
3. Now capture the next line and select the same. "Other Income" and "Discount received". Click the blue button. Capture a different amount, say 400. Quantity 1.
4. The amount in the previous line, the 200 changes to 400. No change to this new line, it stays zero?
5. If I select the same for a third line, it again adjusts the first time the same item was selected, after opening the small form to set the unit price.

I owned businesses for 32 years. In some way businesses sell only three categories of items. Inventory, Assets and Other Revenue items. More than 80% sellable items can have a preset price, I want that field not to be open for the user. The intent is to have that fixed. But items that do not have a preset unit price must have access to an editable field. I do not prefer it, but the same invoice should make it possible to select the same item more than once.