How do I get the value of a Form into an SQL query?

Question

I am trying to get an Access SQL query that does this (semi-pseudocode below)

UPDATE SignIn SET SignIn.Complete=True, CompletedBy=(Select [FirstName] & " " & [LastName] AS EmployeeName From UserList where POid = Forms!HiddenUserCheck!txtPOid), CompletedDateTime=Now()

So after the query would run, the data in the database would look like

Complete EmployeeName CompletedDateTime
True     John Smith   3/23/2017 8:34:10 AM

THe update query doesn't work because of syntax and not sure how to fix it.

The exact error message is

Invalid Memo, OLE, or HyperLink Object in subquery '[FirstName] & " " & [LastName]'.

Answer 1

The query could be throwing a fit because of the Double Exclamation marks. Instead of

Forms!HiddenUserCheck!txtPOid

Try

Forms!HiddenUserCheck.txtPOid

You also have an extra ) at the end of your WHERE Statment

Answer 2

OK, then your issue may be that the subquery may return more than one record:

UPDATE 
    SignIn 
SET 
    SignIn.Complete=True, 
    CompletedBy = 
        (Select First([FirstName] & " " & [LastName]) AS EmployeeName 
        From UserList 
        Where POid = Forms!HiddenUserCheck!txtPOid), 
    CompletedDateTime = Now()

If your name fields are Memo/LongText fields, that may be the source of the error. If so, try:

UPDATE 
    SignIn 
SET 
    SignIn.Complete=True, 
    CompletedBy = 
        (Select First(Left([FirstName], 255) & " " & Left([LastName], 255)) AS EmployeeName 
        From UserList 
        Where POid = Forms!HiddenUserCheck!txtPOid), 
    CompletedDateTime = Now()

Edit.

You may try using DLookup for the subquery:

UPDATE 
    SignIn 
SET 
    SignIn.Complete=True, 
    CompletedBy = 
        DLookup("[FirstName] & " " & [LastName]", "UserList", "POid = " & Forms!HiddenUserCheck!txtPOid & ""), 
    CompletedDateTime = Now()