MS Access Concatenate – Omit Space and Comma if First Value is Missing

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer. In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)

Assuming that you know how to edit and run a SQL query in Access, I think you need to execute a SQL Query similar to the following:

    UPDATE FoodPurchases
    INNER JOIN Vendors ON FoodPurchases.VendorName = Vendors.VendorName
    SET FoodPurchases.VendorId  = Vendors.VendorId

I have not verified the above, so let me know if it does not work.