User entered constants need to be used in Queries & VBA

[inhops]

I have some "constants" that I want to allow the user to enter/change on a form. I then want to use these values in queries & VBA. For example, the calculated profit margin needs to be greater than 9%. I created a table for these values, but I don't know how to get them into a query since the table can't be joined to any of the other tables in that query. What is the best way to create & use these kinds of changeable constants (I know - that is an oxymoron!!)



Thank you,

[pdebaets]

It sounds like these are global options. I use a global options form ("frmGlobalOptions") that I open hidden at application startup. I can then refer to any of the fields on this form using

Forms!frmGlobalOptions!MyGlobalOptionControlName

For SQL that you are running from VBA, you can use a simple function to return the value of the global option:

function GetGlobalOption(pstrGlobalOptionName as string) as variant
GetGlobalOptionName = Forms!frmGlobalOptions("pstrGlobalOptionName")
end function

... then use the function in your SQL like so:

dim strSQL as string
dim rst as DAO.recordset
strSQL = "Select * from MyTable where MyField = GetGlobalOption('MyOption');"
set rst = currentdb.openrecordset(strSQL)

[inhops]

pdebaets, thanks for the help. I understand what you are saying in that once I get the "constants" entered into the form (by default or by the user), I can use these forms in VBA, SQL and queries. But what control do I use on the form to create these Global Form based entries? I can add a Text box but I can't see how to make it enterable/changable. And the Combo box, List Box & Subform all have to be tied to a table, query or form.

Thanks in advance!

[pdebaets]

You can have a GlobalOptions table bound to the frmGlobalOptions form. Create a field in the table for each of your global options. Create a control on the frmGlobalOptions form for each global option and bind the controls to the fields in the GlobalOptions table. You can, of course, use standard Access controls for this purpose.

[inhops]

pdebaets, thanks again for the help. I ended up just adding text controls and made them enterable. I also added defaults and validation. These are not tied to a table, but I can access them in queries, SQL and VBA. The only problem I have with this method is that if someone changes the value, it only remains until next they open the form again. If I were to tie it to a global table, I would want only one record for that table - so when someone presses the enter key, it doesn't take them to the next record in that table. How do I limit the table to only allow one record?

[pdebaets]

Set the primary key to the global options table, then use the table field validation rule to not allow any key values other than the one that already exists. And don't allow the users to delete the record.

[Amicron]

Just remove the NAVIGATION BUTTONS and RECORD SELECTORS from the form. Also, set the CYCLE to Current Record. And, of course, you can set validation rules for each field so they can't just BLANK it.

Finally... you can also throw a simple DLOOKUP function in your queries or VBA code to pull these values up. For example, in a query:

IntRate = DLOOKUP("InterestRate","GlobalSettingsTable")

[kattatonic1]

Hello posters from the year 2011 ,

Just saying thanks for the thread! Just what I needed. I find so much on this forum that I use, I think I ought to say thanks occasionally.

Kay
from Toronto