How to use VBA to return a value from a query

You mean you have two tables, one with the EmployeeID and POSID, and another one with a kind of position history?
In that case I just would query the position history and sort it by change date (newest first). If you open (the sorted) recordset, the newest record is always the first record... YOu can read the Position from this query and compare it with the POSID from the other table.

Yes you nailed the two tables. tblJobTitle has the position history.
I was dreading the recordset answer since I don't really understand the syntax. So tblJobTitle has the fields, IDJobTitle (the autonumber record id) POSID, Activedate and EmpID. How would I write the recordset code and get the value from POSID? All fields are numeric except date.
Thanks!

Hello esbyrt,

give me some hours to provide you with some code...

Are you trying to correct a data error?  This doesn't sound like a normal process.  History tables are created using one of two methods.  Either the table contains ALL values including the current value or it contains all values EXCEPT the current.  How does yours work?  What is the problem you are trying to correct?

If you are using the record(s) of the query as the recordsource for the form, you can create a textbox and use the desired field (or column) as the controlsource for the textbox - start with it visible to start with as you can always make it not-visible for production version.  On the OnCurrent event, do your checks and do a MsgBox if needed.

Hello esbyrt,
here some code you can use (just an example) to read the tables and to manipulate your form....
Not trested as I do not know, hoe your tables look like...So you have to change the names for the tables and fields...
I prefer always to make such manipulations with pure VBA (in comparison with DoCMD) as it is more flexible...

Option Compare Database
Option Explicit

Private DB As Database
Private SDyn1 As Recordset
Private SDyn2 As Recordset

Private SQL1 As String
Private SQL2 As String

CurPosID As Long
NewPosID As Long

Private Sub DoSomething()

SQL1 = "SELECT * From CustomerTable where CustomerID = " & MyForm.CustomerIDField
SQL2 = "SELECT * From HistoryTable where CustomerID = " & MyForm.CustomerIDField & " order by Changedate desc"

Set SDyn1 = DB.OpenRecordset(SQL1, dbOpenDynaset)
CurPosID = SDyn1("POSID")
SDyn1.Close
'Here we get the current POSID stored in the customer table

Set SDyn2 = DB.OpenRecordset(SQL2, dbOpenDynaset)
NewPosID = SDyn2("POSID")
'This is the first record from the Histroy table, the most current because the recorset is sorted (order by)...
SDyn2.Close

If NewPosID > CurPosID Then
    MyForm.CustomerIDField = NewPosID
    'Here we write back the new PosID into the form, therefor into the customer table...
End If

End Sub

Select allOpen in new window

Pat - the error I'm trying to correct is in my original design. The position history is a table set up as a subform. It has position and from and to dates so the company can run a report and see how many years experience in each position a crew has. The most recent position has only the start date and when the report is run today's date is used to make that final calculation. When I designed the database I set up an afterupdate event on position which then populates a text box at the top of the main form with the current position. Now that the database is in use we see that there are times when updating position via a popup window on another form (frmEmployeeList) is easier for data entry but the popup form doesn't update the text box on the main form (frmEmployee). So I was thinking a better design would be to have that text box update when the form opens instead of only after the position is changed in the subform. I have attached a picture of it if that helps visualize what's going on. I circled the text box I need to update.
pic.JPG

Bembi and Tomgueth - thanks for the help. I will be off my computer until Monday now so I will try your solutions out then. Thanks!

!.  You shouldn't be storing the same information twice.  That is what is causing your problem. Either ALL the position history including the current record should be stored in the history table or the current position should be kept in the employee record and all earlier positions are kept in the history table.  Notice that in neither case is the same data stored twice.
2. The procedure should be to have the main form archive the position info to the history table if it is changed.  You seem to be doing this backwards.

You really should not be updating the position data every time a form opens.  You should only ever update it when it is changed.  If what you are saying is that you want to populate an unbound control with data from the position history table, that's different and you don't need code to do it.  You can probably use a domain function as the ControlSource for the control.

Okay Pat mentioned Domain Function which sent me searching and I found DMax. I guess I was asking the wrong question. A combination of DMax and Dlookup will poplulate my text boxes with what I need. However I am having trouble making it work.

I created a text box called txtPosition and set the source as =DMax("ActiveDate","tblJobTitle",'Empid=[IDEmp]').  This correctly returns the most recent date for the employees position.

Then I tried another text box called text179 for now and set it's source as =DLookUp([PosID],[tblJobTitle],"[ActiveDate]=# " & [Forms]![frmEmployee]![txtPosition].[Value] & "#"). I have tried many variations of this and can't get an answer. I always get #Name? in the text box.  

Then more googling and I found the dlookup and dmax can be used together so I created another textbox called text181 and tried this code =DLookUp("PosId","tblJobTitle",'Empid=[IDEmp]' AND DMax(""ActiveDate"",""tblJobTitle"","'Empid=[IDEmp]'")).  That isn't working either.

I'm pretty sure my text179 isn't working because of the date format. Am I missing some quotes or something? I am fine with using two textboxes if that's what it takes to make it work but one would be tidier. Any tips?

There are only two circumstances where you would ever use Format() to format a date.

1. You are creating embedded SQL in VBA.  SQL assumes that STRING dates are formatted as mm/dd/yyyy and so you must comply.  Generally this isn't a problem for people who use mdy as their standard date part order.  It is usually people in countries other than the US where the issue arises.
2. You are exporting a file and you specifically want to control the export format.

In all other cases, leave dates as date time data types and make sure that any unbound controls that will hold dates have their format property set to "Short Date". Do not use specific date formats UNLESS you need to compress a date because you don't have room, so you might use something like "mm/yy" as a date format on a form or report with tight space.

Using Format() converts a date to a string which makes it act like a string instead of a date and so:
1/17/17 is less than 1/2/16 because strings are compared and sorted character by character, left to right.

Given all that, the following should work assuming txtPosition is either bound to a date field or the control has a "short date" format.  Of course "txtPosition" doesn't sound much like the name of a date field to me:

=DLookUp("[PosID]","[tblJobTitle]","[ActiveDate]= [Forms]![frmEmployee]![txtPosition]")

PS - ALWAYS assign meaningful control names as soon as you create the control.  The lackadaisical "for now" simply leads to extra work and lurking errors.  Discipline is very important when you are developing.

Pat I tried your code and it works most of the time but not always. txtPosition has the most recent date for that employee but the =DLookUp("[PosID]","[tblJobTitle]","[ActiveDate]= [Forms]![frmEmployee]![txtPosition]") looks at the entire table and searches for that date. There can be more than one employee that has the same position date and in that case it returns the first position that matches that date, not necessarily the one for the current employee. I really need to find a way to return the max date for the employee position [PosID]  filtered by both [empid] and [activedate].
Thanks!

Hi Pat. I have renamed txtPosition to txtActiveDate to reduce confusion. Note that txt at the beginning is what I use to tell me it's a text box, not the type of data it holds. With a little tweaking I finally got it to work. Here's what did the trick. I'm not sure why having the forms path in there was messing things up but as soon as I took it out away we went. Thank you! Thank you! And a big thanks to Bembi who took the time to write out some code for me even if it ended up not being what I needed.

=DLookUp("[PosID]","[tblJobTitle]","[ActiveDate]= [txtActiveDate] AND EmpID = IDemp")

I understand that txt was the prefix for the textbox.I do that also.  I use cbo for combos, lst for listboxes, cmd for buttons, etc   But consistency is important.

What I don't understand is why the string you posted actually worked.  It should not work.  It should need the complete form path reference as long as the names are enclosed in the string.

=DLookUp("[PosID]","[tblJobTitle]","[ActiveDate]= #" & [txtActiveDate] & "# AND EmpID = " & IDemp)

would also work but as you can see in this case, you are having VBA evaluate the arguments and convert them to string values.  With the whole where clause embedded in souble quotes and the arguments referencing form fields, the query engine would be evaluating the arguments.

I don't know if it makes a difference but I am using a Navigation form (never again - what a pain). I did update the code you supplied with the necessary path for that ie: =DLookUp("[PosID]","[tblJobTitle]","[ActiveDate]= [Forms]![frmNavigation].[NavigationSubform]![txtPosition] AND EmpID = [Forms]![frmNavigation].[NavigationSubform]![!txtEmpID]"). I'm just happy it's working now - as long as it doesn't break later and cause me grief.

The Navigation Form bites someone new.  It was something I never used because I knew how to make one myself.  Early on I discovered a few issues.  One is that there is actually only a single subform control and each subform is loaded into it as that "tab" gets focus.  Now, you could make a tab control work the same way but people generally put a subform on each tab.  That makes the form "heavier" but not terrible if you're only using a few tabs.  The issue this can cause is if you are using a form field on subformA as criteria on subformB, you can't because subformA and subformB are never loaded at the same time.  This is by design and not a bug but it is something you need to be aware of and I've never seen it explicitly explained in the documentation.