Ok so I wrote this up and it seems to work fine.
TestNum = DLookup("MaxOfTestRequestNumber", "PrimaryKeyCounterQuery") + 1
StrSQL = "Insert INTO TestRequestTable(TypeOfTest, TRNumber, Requestor, TestRequestNumber, TestLocation, DateSubmitted, DateRequired, SampleArrivalDate, " & _...