Results 1 to 10 of 10
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Automatically create new record if null

    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]));

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    IF I understand you correctly, I would

    Check if the record exists with a DlookUp
    If result of the DlookUp is Null or zero using the NZ() function, then I would use an Insert query to insert the required data.

    Then work from there.?

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A copy of the db that you've removed sensitive data and whatever doesn't apply to this issue would help. Compact and zip it then post. All that is a bit hard to decipher as the message doesn't seem to apply to the usual situation of trying to create child records without a parent. It reads like a control/field (calculated?) isn't happy with a missing value. That could be anything.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you for your responses. Sorry for the delay, my db stopped working due to a bad control. uhhh. Anyway, I was able to use the dlookup theory. Then, if null, append to the table using an append query targeting the two unbound fields SOWID and DateInvoice on the form. I am able to pass through the SOWID to the append query. Problem...the append query does not like my date values. The format is the same in the unbound field on on the form as in the table. Any ideas how to pass through dates to the append query?

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You would need to show your code?

    Dates should be surrounded by # and generally in mm/dd/yyyy format.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    If I surround my date field with #, I get an error stating "invalid dot (.) or ! operator or invalid parenthesis." Below is a very simple append qry.

    INSERT INTO tblInvoiceFiled ( SOWID, DateInvoice )
    SELECT [Forms]![frmNavMain]![frmNavMain].[Form]![cboSOWID] AS SOWID, [Forms]![frmNavMain]![frmNavMain].[Form]![dtDateInvoice] AS DateInvoice;

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You have frmNavMain twice in each, maybe try:

    INSERT INTO tblInvoiceFiled ( SOWID, DateInvoice )
    SELECT [Forms]![frmNavMain]![cboSOWID] AS SOWID, [Forms]![frmNavMain]![dtDateInvoice] AS DateInvoice;

    Also in your first post you mention frmLogService as the form having the unbound controls, where is frmNavMain coming from. Sounds like a navigation form (which I don't use as they are problematic), have a look at this post https://www.access-programmers.co.uk/forums/threads/refer-to-a-control-on-a-subform-on-a-navigation-subform.294678/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The format is the same in the unbound field on on the form as in the table
    I don't know about anyone else, but that tells me nothing.

    The format of dates means nothing, really. Date data type can only be stored as double precision floating point numbers and must be entered as mm/dd/yyyy. The only way around storing dates as numbers is to store as text. You can format (i.e. make them look different) in many ways, but formatting at the table level doesn't alter the actual field values. However, if you use Format function, any value is converted to text. So all of that has to be addressed first I'd say.

    Below is a very simple append qry.
    Which works or not?? Or is it even involved in the problem??
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have frmNavMain twice in each,
    Maybe because the navigation form and the navigation subform control were given the same name? That's how I read it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I agree but the OP never mentioned a navigation form but frmLogService which is not used in the query....
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-16-2018, 12:50 PM
  2. Replies: 1
    Last Post: 03-17-2017, 11:52 AM
  3. Replies: 2
    Last Post: 03-16-2017, 05:50 AM
  4. Replies: 5
    Last Post: 06-02-2015, 02:51 PM
  5. Automatically replace NULL with zero string
    By tylerg11 in forum Access
    Replies: 13
    Last Post: 09-16-2013, 04:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums