Good afternoon, I form with multiple lists of entries. Each of the entries are organized by company and month of invoice. Once the invoice has been submitted, I do not want individuals adjusting the invoice. If needed, they can make the adjustments on the next month's invoice. I have built in vba to lock the "filed invoice entries. Thus preventing any adjustments. On my form frmLogService, I have unbound fields cboSOWID and dtDateInvoice. This is used to select the client and the month of invoice to display. I have a subfrm on frmLogService called subfrmInvoiceFiled. This subform's recordsource is a qry based on tblInvoiceFiled where DateInvoiceFiled and ClientID is looking at the unbound fields on the frmLogService form. This allow me to see if the invoice has been submitted and stop anyone from making adjustment. Works great until...I enter a client and month that currently does not have a record. Understandably, If no record if found, I received an error "You entered an expression that has no value" and my subfrm is blank. How can I automatically create a new record using the unbound fields on the frmLogService. I have tried writing if statement in vba (cannot recoginize a null value on blank form), failed with Dlookup, and update query (wont recognized unbound date field). Suggestions? Below is the qry for the subform. Any suggestions would be helpful! Thank you
SELECT tblInvoiceFiled.InvoiceFiledID, tblLogService.ProductID, tblInvoiceFiled.DateInvoiceFiled, tblLogService.DateInvoice, tblLogService.DateActivity, tblInvoiceFiled.InvoiceFiled
FROM tblLogService INNER JOIN tblInvoiceFiled ON (tblLogService.SOWID = tblInvoiceFiled.SOWID) AND (tblLogService.DateInvoice = tblInvoiceFiled.DateInvoiceFiled)
WHERE (((tblInvoiceFiled.DateInvoiceFiled)=[Forms]![frmNavMain]![frmNavMain].[Form].[dtDateInvoice]) AND ((tblInvoiceFiled.SOWID)=[Forms]![frmNavMain]![frmNavMain].[Form].[cboSOWID]));