User Defined Function in SQL Select (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. It might help if you think of the data being in tables and not in forms. For example, what is the formula, if any, for bCredit? If it's actually a field in a table, then I don't understand why it can't be included in the subform. Are you not able to make a small mockup database showing the basics of your tables and forms, so we can get to the bottom of this sooner? Just hoping...
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
bCredit is a boolean evaluated in frmPayment (which is also going to do receipts)

So from an index form, user selects a document which could be 'Purchase Payment' or 'Sales Receipt'.
Document type is passed in OpenArgs.

frmPayment opens. Reads OpenArgs, then goes to a config table and finds out if the type of document type is a 'reversal' type.
So now frmPayment knows if the values it reads from the table are presented, as saved, or presented reversed.

That's because some values in the table will be credits, Let's say it's a supplier payment for £123.45 which would be saved as -123.45 the user doesn't expect to see the payment value presented as a negative value.
So the table fields gets copied in, and are reversed for editing, then on final save are reversed back. (A payment could have several sources hence a datasheet). So if the user amended the payment to £223.45 it would be saved in the table as -223.45

If frmPayment determines it's a customer receipt, the values are presented as read and saved, as keyed.

So the same form does payments and receipts (and other stuff like allocations/part allocations) and has to maintain book keeping integrity.

The table values are presented in datasheet form and are edited with a dialog pop up.

The function to present the values is typically =fSign(bCredit,tlSalePrice). fSign is public but bCredit is module and frmPayment doesn't evaluate it until it loads, by which time the sub form datasheet is already loaded. If bCredit is true fSign returns the value negated, so, -123.45 appears as 123.45

So, let's keep focus on the issue. I want to be able to set the recordsource of the datasheet, when the main form loads and has evaluated the value of bCredit.
And when main form knows the value of bCredit, devise a SQL select statement which includes function fSign or has some way of negating the value read from the table and landing it in txtSalePrice. Because we know that tlSalePrice could be negative but we don't want to see it like that.

txtSalePrice isn't a single field, it's a text box in the sub form datasheet.

It's late but it's a work in progress. I'll read up on how to upload a db.


edit: tlSalePrice is the table field. txtSalePrice is the 'local' text box to manipulate it before we finally say:
tlSalePrice = fSign(bCredit,txtSalePrice) and save.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks for trying to explain, but since I am not an accountant, I will probably just wait until I see your sample db, which I bet will immediately shine the light in my dim mind. To me, what you're trying to do may be simplified by using the right Record Source for the subform. We might be able to make it so it doesn't matter whether the main form has loaded yet or not. In fact, simply opening the subform as a stand-alone form should also work, in my humble opinion. Good night.
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
I think a screenshot will be more use than some partly developed code.
I thought I had it cracked with the -1 multiply but it's not a complete solution.
The form will do what I want it to, it's all about presentation and shielding the user from negative values when they don't expect to see them, it's a confusing distraction. The form must do payments to suppliers, and receipts from customers, and the reversal treatment is different for each, according to the value of bCredit.

Main form has two subforms, each subform holds a datasheet.
Each datasheet has a different data source.

The form design screenshot is the sub form for the top half of the main form.

I want some way of negating the values coming from the table before they appear on the form, but I want to be able to control when this is done, because a supplier payment will want different treatment to a customer receipt.

I've been playing around with MyTestFunction but it doesn't work, I get ?Name in the Gross Value column. Which when you think about it, won't work, because MyTestFunction is in the main form, which is where I assess the value of bCredit.

So can I put (thinking around this) a user defined function in Control Source, that references the value of bCredit in the main form ?
I could cop out and just copy the form and code into a new form and adjust it for each scenario, frmPayment, and frmReceipt, but I'm hoping I won't need to do that.

Actually, having just typed that, maybe I should just have a different sub form for each scenario?
So if it's a payment, set the sub form to sfrmPaymentMatchDsheet and if it's a receipt, set the sub form to sfrmReceiptMatchDsheet. Which, DBguy, is what you've hinted at. Hmmmm...

https://photos.app.goo.gl/isWZXieEk7ZrBh499
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, having an image to see what's going on helps a little, but you didn't annotate it to let us know what we're looking at. For example, I couldn't determine where to find bCredit to see its value. Is bCredit a field in a table? Also, I wasn't suggesting using different subforms. You should be able to use the same subform, only change its RecordSource to a more correct version. I think you're stuck in thinking you need a function. You may or you may not. I just need to know how to use bCredit for adjusting the displayed SalePrice to be able to tell you how to use it in the subform. Please remember you're talking to someone who has no clue what your database is about. So, when you describe it, pretend we don't know anything and use common terms. Either that or simply post a small copy of your db, so we can look around it at will.


PS. Just in case it helps, please go ahead and post the code for your function. If nothing else, it should tell us how you're using bCredit.
 
Last edited:

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
bCredit is a main form module boolean set after the two sub forms have loaded. There's a representation of it on the form, the box with the word 'credit' in it, that's just so I know what's going on during development

I was originally asking about the payment side, where tlSalePrice (a table field) would want negating. This is the bottom part of the form. But I realise the issue also applies to the top half datasheet as well. There's a lot going on in this form.

I did think a function embedded in the SQL select would do the job, that's how the thread started, but I'm now thinking, just have two sub form datasheets and select one when the main form has evaluated bCredit. (Actually a choice of two for the top and a choice of two for the bottom)

The sub forms would be the same, except in the signing of the table values.
So, if we're dealing with a supplier payment, one sub form would load and show unsigned values for purchase invoices outstanding.
For example the column Gross Value would have Control Source =[thNetValue_summed] + [thVatValue_summed] (these are table fields)
If we're dealing with a customer invoice, the other sub form loads and shows unsigned values for sales invoices outstanding
and the Gross Value control source might be (negated) : = -([thNetValue_summed] + [thVatValue_summed])

Unless there's a simple way to negate the table values I think this may be the solution.
Let's not lose sight, this is nothing more than a presentational thing. The form works and maintains book keeping integrity whichever way the table values are presented. I just want it to be user friendly

Incidentally, the screenshot shows a purchase invoice PINV created for this account, and a sales invoice SINV created for this account. That wouldn't happen in normal use, I would restrict type of invoice according to whether we're dealing with a customer or supplier. But you can see the effect, a SINV is presenting with a credit value and it's not helpful. If the user sees £938.24-, well, it's just confusing. I want to see it presented as £938.24. In use, a single customer or supplier account could have only one type of invoice raised against it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. I think we can combine the two subforms idea into one. For example, when you said you are dealing with a "supplier payment" and a "customer invoice," how do we determine that? If we "know" the how, then we can simply do something like:


ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])


Hope that makes sense...
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
OK, sorry yes I'm not explaining myself, Supplier Invoice or Customer Invoice are effectively set by bCredit. It's the same thing really.
Likewise Supplier Payment or Customer Receipt are effectively set by bCredit.

The iif is perfect sense except I hadn't thought of using it like that.
So
ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])

could just as easily be
ControlSource: =IIf(bCredit,1,-1)*([thNetValue_summed] + [thVatValue_summed])

So we're back to the same issue, bCredit isn't available until frmPayment loads. frmPayment has to do a lookup to see if bCredit will be true. frmPayment decides what the form caption will be, and what to describe the current document as (payment or receipt). frmPayment is designed to open and display correctly whichever document it is opened with. Why wasn't Access designed to load the main form with its code *before* the sub forms ?
It's the sub form display that's the issue. Even if I could set ControlSource in frmPayment, I'd have to do it for every field that wants negating.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
OK, sorry yes I'm not explaining myself, Supplier Invoice or Customer Invoice are effectively set by bCredit. It's the same thing really.
Likewise Supplier Payment or Customer Receipt are effectively set by bCredit.

The iif is perfect sense except I hadn't thought of using it like that.
So
ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])

could just as easily be
ControlSource: =IIf(bCredit,1,-1)*([thNetValue_summed] + [thVatValue_summed])

So we're back to the same issue, bCredit isn't available until frmPayment loads. frmPayment has to do a lookup to see if bCredit will be true. frmPayment decides what the form caption will be, and what to describe the current document as (payment or receipt). frmPayment is designed to open and display correctly whichever document it is opened with. Why wasn't Access designed to load the main form with its code *before* the sub forms ?
It's the sub form display that's the issue. Even if I could set ControlSource in frmPayment, I'd have to do it for every field that wants negating.
Hi. We are back to the same issue you mentioned about the subform loading before the main form because you got yourself stuck at thinking this way. I have been trying to ask you where is this bCredit coming from. Is it a field in the table? If so, you can add it to the Record Source of your query or use DLookup(). Is it a function? If so, show us the code, so we can tell you how to use it in your subform. The issue of the subform loading before the main form wouldn't come into play in the solution I am suggesting because I am trying to put the value of bCredit into the subform, so we don't have to worry about the main form not being loaded yet.
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
See post #1.


Code:
txtSalePrice = fSign(bCredit(tlSalePrice))
Where fSign returns the value negated if bCredit is true, it's a public function

Where does bCredit come from ?

thDocType is the form record, it's a table field for the record that the form is opened with

function fDocSign will return bCredit according to the record that frmPayment is opened with.

When we create a new payment record, we assign thDocType in the table. For a supplier payment, it's PPAY and it's a field in the table, ie the record

so function fDocSign returns bCredit as true, if PPAY is identified as needing reversal.
function fDocSign does a lookup because the bCredit flag isn't hard coded, it's a system setting for a PPAY document.

Code:
txtDocType = thDocType              ' what document is it ?
bCredit = fDocSign(txtDocType)      ' is it a 'reversal' type ?
The sequence is
frmPayment loads
frmPayment reads thDocType from the record it's opened with
frmPayment assigns bCredit according to thDocType but it does this by calling a function which does the system setting lookup
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:52
Joined
Jan 23, 2006
Messages
15,364
So, for clarity, bCredit is a boolean variable declared within a module?
Please post the code for fDocSign .
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
Yes bCredit is boolean within frmPayment only


fDocSign is due for revision. tblDocTypes is being developed but here's the current lookup


Code:
Public Function fDocSign(strDocType As String) As Boolean
    
    Dim db As Database
    Dim rec As DAO.Recordset

  Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset("tblDocTypes", dbOpenDynaset)


    With rec
        .FindFirst "DocTypesID = '" & strDocType & "'"
        If Not .NoMatch Then
            fDocSign = (rec!dtCredit = True)
        Else
            fDocSign = False
        End If
        .Close
    End With


    Set rec = Nothing
    
End Function
And tblDocTypes where we flag bCredit and other stuff relating to thDocType


https://photos.app.goo.gl/53mEzwF7PgK5xA6HA
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Not in front of a computer right now. Will have to wait for a more coherent reply later. Sorry.

Sent from phone...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Good morning. There are so many moving parts to this thread, so I went all the way back to post #1 to try and put them all together. Barring a sample db, I think I have all the pieces I need except for the table name for the main form where the thDocType can be found. For example, here is what I think we have at play.

Main Form Table Source: ???
Primary Key Field of Main Form: TransHeaderID
DocType Field of Main Form: thDocType
Doctype lookup table to determine if Credit: tblDocTypes
tblDocTypes Field to determine if Credit: dtCredit
Subform Table Source: tblTransLines
Foreign Key Field in Subform: tlTransHeaderFK
Field in Subform to negate or not: tlSalePrice

So, if you can give us the name of the table where the Primary Key field TransHeaderID is located, I think I can try to give you a Record Source for your subform where tlSalePrice will be either negated or not.

Either that or post a sample db, so we can be more certain. Cheers!
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
You're right the parts are moving as I continue to develop it. The form is my most complex one with two datasheets, each one a view on to a different table and they must be kept in sync to work.

Appreciate the help, and you've mentioned posting a db. Do you mean the whole thing ?

It has about 50 forms with code, 25 tables, 20 code modules, and I haven't built a navigation/menu form yet. If I uploaded it without data, I'd have to send you an essay on how to add some records and make sense of it. Plus it's my one and only Access project and probably has scope for improvement everywhere ...

Anyway if I can refer to you to the screenshot of frmPayment

Main form (frmPayment) table: tblTransHeaders
Primary Key Field of tblTransHeaders: TransHeaderID
Bottom half sub form is called: sfrmPaymentLineDsheet
Source object is also called: sfrmPaymentLineDsheet
Record source is: tblTransLines
The table name: tblTransLines
Link master field: TransHeaderID
Master field table name: tblTransHeaders
Link child field: tlTransHeaderFK
DocType lookup table to determine if bCredit is true according to thDocType: tblDocTypes

thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
You're right the parts are moving as I continue to develop it. The form is my most complex one with two datasheets, each one a view on to a different table and they must be kept in sync to work.

Appreciate the help, and you've mentioned posting a db. Do you mean the whole thing ?

It has about 50 forms with code, 25 tables, 20 code modules, and I haven't built a navigation/menu form yet. If I uploaded it without data, I'd have to send you an essay on how to add some records and make sense of it. Plus it's my one and only Access project and probably has scope for improvement everywhere ...

Anyway if I can refer to you to the screenshot of frmPayment

Main form (frmPayment) table: tblTransHeaders
Primary Key Field of tblTransHeaders: TransHeaderID
Bottom half sub form is called: sfrmPaymentLineDsheet
Source object is also called: sfrmPaymentLineDsheet
Record source is: tblTransLines
The table name: tblTransLines
Link master field: TransHeaderID
Master field table name: tblTransHeaders
Link child field: tlTransHeaderFK
DocType lookup table to determine if bCredit is true according to thDocType: tblDocTypes

thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
Hi. Thanks for the info. I'll try to work something out.


When we ask for a copy of the db, we really only need to see a demo version of it. It should only contain enough objects to understand and duplicate the problem in question to help us come up with a working solution. Without seeing all the items in play with the problem, it's hard to provide a good solution. If data is sensitive, it can be replaced with test or dummy data, and we only need a small set of it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
Hi. If I understand the above correctly, that makes it even easier, since we don't have to worry about the DocType from the main form.


So, I know you said you deleted the Record Source of the subform because you wanted to change how txtSalePrice is diplayed based on the record on the main form. You also have some code to assign the Record Source for the subform in the Load event of your main form.


At this time, just as a test, I will ask you to comment out the code in the Main Form's Load event for assigning the Record Source of the subform. And then, I will ask you to actually assign a Record Source to the subform using the following SQL statement:


Code:
SELECT T1.*,  T1.tlSalePrice * IIf(T2.dtCredit,-1,1) AS txtSalePrice FROM tblTransLines T1

INNER JOIN tblDocTypes T2

ON T1.tlDocType=T2.DocTypesID


If that doesn't work, seeing a sample db would really help speed up the process of finding a solution for you. Hope it helps though...
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
OK I put your code in the record source and it works (I think)
I get the same records that I expect to show in the bottom datasheet
I need to check some stuff out as I've done quite a bit today

Should the values show reversed ?

Does this mean it's going to tblDocTypes for *every* datasheet line that is displayed ? I have difficulty with that. It strikes me as odd to have to go to a table a dozen times or more when the same condition applies to every datasheet line.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
OK I put your code in the record source and it works (I think)
I get the same records that I expect to show in the bottom datasheet
I need to check some stuff out as I've done quite a bit today

Should the values show reversed ?

Does this mean it's going to tblDocTypes for *every* datasheet line that is displayed ? I have difficulty with that. It strikes me as odd to have to go to a table a dozen times or more when the same condition applies to every datasheet line.
Hi.

Re: "Should the value show reversed?"

If it needs to be, try switching the expression I used to:

Code:
T1.tlSalePrice * IIf(T2.dtCredit,1,-1) AS txtSalePrice
And see if that helps.

As for going through all the records in a query, that's what a database's job is, so it shouldn't be a problem. But if you're seeing an issue with the speed (is it running slow now?), then we could try it another way.
 

GK in the UK

Registered User.
Local time
Today, 11:52
Joined
Dec 20, 2017
Messages
274
OK I posted a payment of £500 for a supplier where bCredit = true
I posted a receipt of £500 for a customer where bCredit = false (the same form)
I've checked the table values and tlSalePrice is £500 in each case, two records in the table

On sfrmPaymentLineDsheet, txtSalePrice was bound to tlSalePrice so it just displayed £500 for each case of bCredit (no reversal)

So I deleted the control source for txtSalePrice thinking that was the problem, so txtSalePrice on sfrmPaymentLineDsheet now has no control source

txtSalePrice on the datasheet now displays nothing, it's an empty field
The other fields display so the binding must be there

just to be clear: I dropped your code into the Record Source of the form: sfrmPaymentLineDsheet

I can't see any difference between the expression in your first example and the second
 

Users who are viewing this thread

Top Bottom