three Sql statements in VBA - 1 of them not working??

[mcamp]

Hi, I have three sql statements that run when the user clicks "Done Adding" button when adding a new "Case" to the database.

Sql should be putting the new "Case Name" and ID in three different tables (these tables are also linked to other databases)

the 1st, and 3rd sql statement work fine, but the second does not. No user side errors, no VB compile errors, just simply does not insert the information into the table "tblEligibility"

there is one difference with this eligibility table- the "FamilyID" does not have a space like the other tables do. There are many forms based on this table, and many errors occur if I change the table id to include the space.

any ideas?? what cant I do to get this info inserted into tblEligibility? The FamilyID not having a space should not matter, right?

Private Sub cmdDoneAdding_Click()
Dim MySql As String
Dim MySqlTwo As String
Dim MySqlThree As String
Dim NewFamId As String
Dim Newchildid As String
Dim LastIdFam As Long
Dim LastIdChild As Long
Dim LastNameValue As String
Dim FirstNameValue As String
Dim NewCaseName As String


If Not Me.Case_Name = "" Then


    Me.IntakeDate = Now()

    NewCaseName = Me.Case_Name
    LastNameValue = "(Enter Last Name)"
    FirstNameValue = "(Enter First Name)"

    LastIdFam = CurrentDb.OpenRecordset("SELECT Max([Family ID]) FROM tblFamilyData;")(0)
    LastIdChild = CurrentDb.OpenRecordset("SELECT Max([Child ID]) FROM tblChildData;")(0)
    'MsgBox " Max ids: " & LastIdFam & " : " & LastIdChild
    NewFamId = LastIdFam + 1
    Newchildid = LastIdChild + 1
    'MsgBox " New ids: " & Newfamid & " : " & Newchildid
    txtFamilyId.Value = NewFamId

MySql = "INSERT INTO tblChildData " & _
"( [Family ID], [Child ID], [Case Name], [First Name], [Last Name])" & _
" VALUES ('" & NewFamId & "','" & Newchildid & "','" & NewCaseName & "', '" & FirstNameValue & "', '" & LastNameValue & "');"

MySqlTwo = "INSERT INTO tblEligibility " & _
"( [FamilyID], [Case Name])" & _
" VALUES ('" & NewFamId & "','" & NewCaseName & "');"

MySqlThree = "INSERT INTO tblRiskAssessment " & _
"( [Family ID], [Case Name])" & _
" VALUES ('" & NewFamId & "','" & NewCaseName & "');"


x = MsgBox("Are you sure you want to add " & "'" & NewCaseName & "'" & " as a New Case?", vbInformation + vbYesNo, "Childrens Crisis Center")
    If x = vbNo Then
        MsgBox NewCaseName & " was NOT added!", vbExclamation, "Childrens Crisis Center"
        Me.Undo
        Exit Sub
    Else
        DoCmd.SetWarnings False

        DoCmd.RunSQL MySql
        DoCmd.RunSQL MySqlTwo
        DoCmd.RunSQL MySqlThree

        DoCmd.SetWarnings True
        DoCmd.Close
        DoCmd.OpenForm "frmFamilyData", acNormal, NewCaseName
    End If
Else
    MsgBox "You have not entered a new Case Name yet!", vbExclamation
Exit Sub
End If
End Sub

 

[mcamp]

immediate window returns

INSERT INTO tblEligibility ( [FamilyID], [Case Name]) VALUES ('6003','Ronald Mcdonald');

for mysqltwo

 

[jackd]

I don't see any reason why that code should not be executed especially if 1 and 3 are and without error.

Is your [Family Id] a text data type?

Also, you have no error routine defined.

Instead of DoCmd.Runsql yourSQLName
you could use db.execute yourSQLName, dbFailOnError which will not prompt for each insertion, and will raise an error if an error occurs.

 

[mcamp]

your right! OMG cheers to my idiocy! I had warnings off!

Ran the sql straight on the backend with the execute function and found there were some 'required' fields that I missed in the dataset. added some Date vars to the sql, and edited the tables 'required' fields to not required, and all is good!

thanks man! just need another brain sometimes!