Solved DLookup value display with a minus sign

[E9-Tech]

I have the following code to auto fill a form when is loaded as it reads the latest correct values from a table

Private Sub Form_Load()
    SalaryAnnual.DefaultValue = """" & DLookup("SalaryAnnual", "tblSalary") & """"
    SalaryBasicPay.DefaultValue = """" & DLookup("SalaryBasic", "tblSalary") & """"
    SmartPension.DefaultValue = """" & DLookup("EmployeePensionContributions", "tblSalary") & """"
    OPEmployerPension.DefaultValue = """" & DLookup("TotPensionContributions", "tblSalary") & """"
End Sub

So far so good, however the SmartPension value is a deduction therefore in the form it should load with the minus - sign prior to the value.
Is this possible?
I have a calculated field on the form which I subtract the SmartPension value and is fine, but I would like to display it (e.g. Smart Pension - £ 100.00) on the form for more clarity.

 

[arnelgp]

SmartPension.DefaultValue = """" & DLookup("EmployeePensionContributions", "tblSalary") * -1 & """"

 

[Gasman]

Why not just have the -100 value in the table?
Plus there is no criteria for an employee? Do they all get the same?
So when you employ a new manager, surely their default basic is going to be different to another role employee?

 

[plog]

...as it reads the latest correct values from a table...

But thats not what your doing. None of those Dlookuops have criteria to get 'the latest' values. Your just pulling random values from your table:

DLookup Function - Microsoft Support

Use the DLookup function in Access desktop databases to get the value of a particular field from a specified set of records.

support.microsoft.com

 

[DHookom]

I'm not sure all the quotes are necessary if the fields are numeric. I did some testing something like this should work:

 SalaryAnnual.DefaultValue =  DLookup("SalaryAnnual", "tblSalary")
 SalaryBasicPay.DefaultValue = DLookup("SalaryBasic", "tblSalary")

 

[Mark_]

Does tblSalary have more than one record? If so, how are you verifying you have the correct record?

 

[June7]

DLookup without criteria is pulling random value. Usually, DLookup is not needed. A combobox could be possibly be setup to include associated values then textbox expression refers to combobox column or include lookup table in form RecordSource.

Negative numbers in table or multiply by -1 or have a label next to textbox with minus sign.

 

[mtagliaferri]

SmartPension.DefaultValue = """" & DLookup("EmployeePensionContributions", "tblSalary") * -1 & """"

I have tried this but is not returning the negative value on the form when is opened

 

[mtagliaferri]

Does tblSalary have more than one record? If so, how are you verifying you have the correct record?

The table has only 1 record which values may change once a year I have a different form to update that record.
This is purely to have a default value every time a new record is added in payslip table

 

[June7]

E9-Tech started this thread. Are you a different person? Really should start your own thread instead of hi-jacking another's.

Code provided is setting DefaultValue property. It will do nothing for existing records. For a new record, the value should show when edit is started in another control.

 

[mtagliaferri]

E9-Tech started this thread. Are you a different person? Really should start your own thread instead of hi-jacking another's.

Code provided is setting DefaultValue property. It will do nothing for existing records. For a new record, the value should show when edit is started in another control.

Hi @June7, yes the purpose is to set the DefaultValue only to new records and not modify current records and this seem to be the behaviour when opening the form for an existing record and editing the record. Can you expand on the meaning of ‘the value should show when edit is started…’

Apologies for the confusion with the username, one was an account opened when living in another country with an email which no longer exists and not even support was able to update the email in order to receive notifications and I work on 2 devices only as a hobby and not a professional, sorry for the unnecessary confusion I created!

 

[June7]

I did suspect could be same person.

Scratch what I said before. I just did a test and the default value does show on the NewRecord row.

The code works for me. I tested with and without outer quote marks and concatenation - both worked.

Why use VBA to set DefaultValue property? Could set this in form design.

 

[arnelgp]

The code works for me. I tested with and without outer quote marks and concatenation - both worked.

therefore your code should be:

SmartPension.DefaultValue = DLookup("EmployeePensionContributions", "tblSalary") * -1

 

[CJ_London]

=DLookup("EmployeePensionContributions * -1", "tblSalary")

Or an alternative

 

[E9-Tech]

therefore your code should be:

SmartPension.DefaultValue = DLookup("EmployeePensionContributions", "tblSalary") * -1

Thanks for the solution!

 

[The_Doc_Man]

Or, even simpler,

SmartPension.DefaultValue = -DLookup("EmployeePensionContributions", "tblSalary")

 

[Mark_]

Just as a suggestion, if you have a configuration record you will most likely want to have something unique in its name. TblConfig or TblDefaults would make more sense. Then you could lump all of your "Default" values in the same place. This will help anyone else who needs to work on your database in the future.

Were it me, seeing "TblSalary" makes me think it has current and historical salary data in it. Very important when individuals get promoted or raises and you need to validate what their historical rate of pay was. The you of 2030 may thank me if you look into your naming conventions!