I have two tables... one contains all the loan officer information
(name/address/phone/email/etc) the other is a NH license table which
lists has 3 columns - txtLOName, dtDateHireSent, dtDateTermSent and
the tables are joined on the LO's name.
I need to create two queries for this.. one for when I tell NH the LO
is hired, and one for when I tell them they're fired. It's the hiring
one I can't get straight.
Basically, I need listed on a continuous form all the LO's that exist
in the LoanOfficers table and not in the NHLOLicense table. (If
they're listed in the NHLOLicense table, that means I sent their
information to NH already). Then, I need it to automatically put the
date in the dtDateHireSent field.
I have the query set up as:
SELECT tblNHLOLicense.txtLOName, tblNHLOLicense.dtDateHireSent,
LoanOfficers.Name, LoanOfficers.Address, LoanOfficers.City,
LoanOfficers.State, LoanOfficers.Zip, LoanOfficers.Phone,
LoanOfficers.[SS#], LoanOfficers.HireDate, LoanOfficers.Inactive
FROM LoanOfficers LEFT JOIN tblNHLOLicense ON LoanOfficers.Name =
tblNHLOLicense.txtLOName
WHERE (((LoanOfficers.Inactive) Is Null))
WITH OWNERACCESS OPTION;
Which does exactly what I want, with one possible exception. I can't
get the names from LoanOfficers.Name to copy over into
tblNHLOLicense.txtLOName.
Someone a few days ago gave me this loop to use for a different
purpose:
(keep in mind, I modified this loop to what I thought was right, but I
accidentally deleted that. So this is the loop pre-modification)
Dim rst As Object
Set rst = Me.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark
Me.DateTermStatementSent = Date
rst.MoveNext
Loop
Set rst = Nothing
Which, when I tried to modify it for what I needed, it added the
form's name to both LOName fields instead of copying the name from
LoanOfficers.Name to tblNHLOLicense.txtLOName.
I hope that makes sense... this has burned me out