Results 1 to 10 of 10
  1. #1
    jim.thornton is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    29

    Can't ADD record if no records exist.

    Structure:



    frmHousehold: This form opens and shows the current household information. It has a subform under it showing all members for that household. It also shows another subform showing all "Scenarios". Both Members & Scenarios tables have a HouseholdID_FK field.

    I'm getting an error when trying to add a record in one of the subforms if there aren't any records for that household. Normally, the subform will show any Members (or Scenarios) that have the Household_FK field that matches.

    I don't know why I'm getting this error. If I go into the table and create a record for that HouseholdID, then I can add more. But, if I have NO records in the subform with that HouseholdID I get this message.

    NOTE: I was thinking of maybe creating a Global Variable called ActiveHouseholdID and then assigning the current HouseholdID to ActiveHouseholdID on the Current event. Would that work?? If so, How do I do that? I'm not sure how to access HouseholdID from VBA (I've only been doing it within macros using SetTempVar).
    Attached Thumbnails Attached Thumbnails access-error-message.PNG   access-error-message-2.PNG  

  2. #2
    jim.thornton is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    29
    Okay... I figured out how to access a field from the form and save it to a global variable.

    Is it possible to access a global variable within a macro?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    While that error message is often the result of trying to use a value of the wrong data type (e.g. string vs number) in your case it suggests it is likely prompted by trying to put a Null somewhere. The error number is more generic - basically means the action failed, whatever that action is and for whatever reason. So the number is of much less use in this case.

    However, I see that you're dealing with subforms and the OnLoad event of some form. It might be worth while to point out that subforms load first, so if you try to access a main form value from subform load event code, you will get an error. Can't answer your question about getting at a global variable from a macro as I don't use macros, and needing a global variable for this seems like a bit of a band aid approach and I'd try to prevent the issue instead if possible. It could also be that you are trying to add a child record when there is no parent for it, which could be where the null is coming from. I'd expect a different error if this was code and the forms were bound and linked to each other though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not really clear on your situation, but you can't access the variable directly. You could create a public function that returns the value, or use a TempVar.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jim.thornton is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    29
    Quote Originally Posted by Micron View Post
    While that error message is often the result of trying to use a value of the wrong data type (e.g. string vs number) in your case it suggests it is likely prompted by trying to put a Null somewhere. The error number is more generic - basically means the action failed, whatever that action is and for whatever reason. So the number is of much less use in this case.

    However, I see that you're dealing with subforms and the OnLoad event of some form. It might be worth while to point out that subforms load first, so if you try to access a main form value from subform load event code, you will get an error. Can't answer your question about getting at a global variable from a macro as I don't use macros, and needing a global variable for this seems like a bit of a band aid approach and I'd try to prevent the issue instead if possible. It could also be that you are trying to add a child record when there is no parent for it, which could be where the null is coming from. I'd expect a different error if this was code and the forms were bound and linked to each other though.
    Thanks for your post. However, I decided to abandon the macros. I think it was making things more complicated. I'm more used to coding what I want anyway. Just not familiar with VBA. I've coded a bit of visual basic and php, and some java, but not fluent in any of them.

    I'm now using an SQL delete from command to delete. It works great. However, when I was coding the "Add" button, I'm having a problem with the SQL code.



    Here is my code:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdDeleteMember_Click()
        
        ' If the form is not a new record then confirm delete and execute sql query to delete record
        If Not Form.NewRecord Then
            If MsgBox("Are you sure you would like to delete this member?", vbYesNo + vbQuestion) = vbYes Then
                   
                CurrentDb.Execute _
                    "DELETE FROM tblMembers WHERE tblMembers.MemberID=" & Me.MemberID
                    
                Forms("HouseholdsForm").Refresh
                DoCmd.Close acForm, Me.Name, acSaveNo
            End If
        End If
            
        
    End Sub
    
    
    
    
    Private Sub cmdSaveMember_Click()
    
    
        ' Validate form and save record
        If Not IsNull(Me.FName.Value) Then
            ' view the SQL string (delete when working)
            MsgBox "INSERT INTO tblMembers ([FName], [LName], [Email], [BDate], [HouseholdID]) " _
                & "VALUES (" & Me.MemberID & ", " & Me.FName & ", " & Me.LName & ", " & Me.Email & ", " & Me.BDate & ", " & ActiveHouseholdID & ")"
                
            CurrentDb.Execute _
                "INSERT INTO tblMembers ([MemberID], [FName], [LName], [Email], [BDate], [HouseholdID]) " _
                & "VALUES (" & Me.MemberID & ", " & Me.FName & ", " & Me.LName & ", " & Me.Email & ", " & Me.BDate & ", " & ActiveHouseholdID & ")"
                
            
            Forms("HouseholdsForm").Refresh
            DoCmd.Close acForm, Me.Name, acSaveNo
        End If
        
    End Sub
    When I go to save the member, I get the following errors. See the attached screenshots.

    The one (with the correct email address) gives a different message. I figured that was because I need to escape the special characters some how. So, I just tried inserting a string instead of an email address and then I got the second error.

    Any help would be appreciated.
    Attached Thumbnails Attached Thumbnails access-msgbox-1.PNG   access-error-insert-sql.PNG   access-msg-box-2.PNG   access-error-insert-sql-2.PNG  

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for string you need to use the ' or " identifier
    for dates you use the # identifier
    and for number, no identifier required

    Your error messages are telling you this

    to this line

    "VALUES (" & Me.MemberID & ", " & Me.FName & ", " & Me.LName & ", " & Me.Email & ", " & Me.BDate & ", " & ActiveHouseholdID & ")"

    should look more like this


    "VALUES (" & Me.MemberID & ", '" & Me.FName & "', '" & Me.LName & "', '" & Me.Email & "', #" & Me.BDate & "#, " & ActiveHouseholdID & ")"

    assuming your ID's are numeric.


    Don't understand why you appear to be using an unbound form - the normal way is to bind the form to the table, then no code required. If unbound, you have a lot more coding to do

  7. #7
    jim.thornton is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    29
    Quote Originally Posted by Ajax View Post
    for string you need to use the ' or " identifier
    for dates you use the # identifier
    and for number, no identifier required

    Your error messages are telling you this

    to this line

    "VALUES (" & Me.MemberID & ", " & Me.FName & ", " & Me.LName & ", " & Me.Email & ", " & Me.BDate & ", " & ActiveHouseholdID & ")"

    should look more like this


    "VALUES (" & Me.MemberID & ", '" & Me.FName & "', '" & Me.LName & "', '" & Me.Email & "', #" & Me.BDate & "#, " & ActiveHouseholdID & ")"

    assuming your ID's are numeric.


    Don't understand why you appear to be using an unbound form - the normal way is to bind the form to the table, then no code required. If unbound, you have a lot more coding to do
    Thank you. I will try that later. If by "bound form" you mean the table in the property sheet, it is. If you mean something else?

    Could you give me an example of less coding?

    I only went the SQL route because when I tried to use the DoCmd and DeleteRecord it says it isn't available now. And I didn't know how to save a record without SQL. I read something online that said SQL was the best option.

    If you have a suggestion with less coding, I would greatly appreciate it.

  8. #8
    jim.thornton is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    29
    Thank you! That worked well.

    Can you provide a sample of code that is "easier" than what I'm doing? My forms are bound to the tables.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without seeing the form, but if it is bound to the table, it will automatically save when you got to another record or close the form - no code required

    if you want to save without leaving the record or closing the form you would use something like this in your 'save' button

    me.dirty=false

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you enter data to bound form and then execute an INSERT query to same table, you are likely creating 2 records. Data is committed to table when:

    1. close table/query/bound form

    2. move to another record

    3. run code to save
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-15-2018, 11:45 AM
  2. Replies: 6
    Last Post: 03-17-2017, 10:28 PM
  3. Replies: 8
    Last Post: 12-19-2016, 08:24 PM
  4. Replies: 4
    Last Post: 07-10-2015, 07:51 AM
  5. Replies: 4
    Last Post: 12-14-2012, 06:33 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