Solved Lookup on Form Load

[mtagliaferri]

I have the below VBA code to look up a value from another table on loading the form, this works perfectly

Private Sub Form_Load()
    SalaryAnnual.DefaultValue = """" & DLookup("SalaryAnnual", "tblSalary") & """"
End Sub

However I need to lookup a further parameter within the same table, I amended the VBA code to

Private Sub Form_Load()
    SalaryAnnual.DefaultValue = """" & DLookup("SalaryAnnual", "tblSalary") & """"
    SalaryBasic.DefaultValue = """" & DLookup("SalaryBasic", "tblSalary") & """"
End Sub

However this causes an error.
I am not familiar with VBA, any support would be appreciated.
Thanks

 

[theDBguy]

What was the error message?

 

[DHookom]

Does your code compile? Do you have Option Explicit at the top of your module?

 

[LarryE]

Set the default value on the form itself and be done with it. The DLookUp will only give you the first record in your table anyway so it's probably useless. You're not defining any criteria in the DLookUp so it will only return the first record

 

[The_Doc_Man]

LarryE's point is absolutely correct. First, you are engaging in overkill. What you are doing in the Form_Load is to set a formula to be evaluated later... but for the Form_Load event, there IS no "later." The Form_Load event fires once when you load the form. Thereafter, you will have Form_Current events. So you will repeat this action every time the form loads, but default values don't come into play until after the _Current event, so what you are doing here is double-deferring the computation. In fact, your error might be a data type error since that quoted sequence remains a quoted sequence (with fewer quotes) and my guess is that one or both of the salary figures are in one of the numeric formats.

But then, LarryE's second point is also valid. There is no telling which record's salary will be looked up AND it might not be the same one each time if there is any updating in that table between Form_Load events. There is an uncertainty in which record you see when you look at (effectively) the First or Last records of any table that is subject to updates. It's a long story. The short answer is that raw table order can get scrambled from one run to the next for any query or recordset that looks directly at the raw table without imposing an ORDER BY clause on it. So the answer you get might not be the one you wanted anyway.

 

[arnelgp]

since the first code works, maybe check the control name if it is correct or the fieldname if it is correct.
next thing to check is, if SalaryBasic is correct textboxname, then check if on it's control source, it has
an expression on it that starts with an equal (=) sign.

 

[mtagliaferri]

@DHookom @theDBguy I have found the error, the name of the column on the original tbls was misspelled, hence the dLookup not finding it.
Thanks for taking the time to look at my post.