Grabbing a value from a previous record to use a default value in a new record

[snipe]

Hey guys,

I am trying to pull a value from the most recent record and use it in a control in the form, so it can be used for a calculation. I highlighted the fields that I am talking about. The top grid is a subform based of the master table, and the bottom boxes are all unbound. VBA is used to push the values to a new record.


Here is the DLookUp code that I am trying to use

=DLookUp("Draw","TABLE_PayrollData","PPE = " & DateAdd("d",-14,[Forms]![MainMenu]![search ppe]) And "Employee = " & [SelectEmp])

[Forms]![MainMenu]![search ppe] is on the main switchboard, and populates the locked date field right below "Store 08" on the bottom left.



I want to pull the Draw field from TABLE_PayrollData where PPE (pay period ending date) is the most recent one before the selected from [Forms]![MainMenu]![search ppe] and since all employees are in the same table, I need the employee chosen in the drop down to match the one in the record I am grabbing.

Thanks guys!

[June7]

Try:

=DLookUp("Draw","TABLE_PayrollData","PPE = #" & DateAdd("d",-14,[Forms]![MainMenu]![search ppe]) & "# And Employee = " & [SelectEmp])

Assumes Employee is a number type field and [SelectEmp] is name of combobox with a number value.

[snipe]

Example Employee number is RR82390. They are saved as a text field in the table. In the screen grab, the field directly below "Ralph Russo" is linked to the bound column in the combo box. That is the Employee number.

Getting #Error with the changes you suggested.

Before any changes were made, the the dlookup pulled the first Draw value in the master table. That's where that "96.3674" came from. Seemed like Dlookup just ignored all of the criteria. The date nor the salesperson matched up.

[June7]

Text field criteria need apostrophe delimiter (just as the date needed #)

& "# And Employee = '" & [SelectEmp] & "'")

[snipe]

Oh heck yes! June7 you rock.

Dlookup is such a pain. I can never get it right.