Originally Posted by
Micron
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.