Insert query not inserting all fields

[wirobins]

I'm trying to design a forms based data entry system which will create records in several tables. I built a very similar system several years ago, but haven't really done any programming (in MS Access or otherwise) since then. In any case it's driving me crazy that I can't remember how to get it all working, and I'm stuck in a small country without any of my old work to build from.

What I have below is an incomplete version, excluding some of the other Inserts, but it includes the data that is not inserting correctly.

Private Sub cmdNewCourse_Click()

Dim intMsgBoxAnswer As Integer
intMsgBoxAnswer = 0
Dim Employees As DAO.Database
Dim qdf As DAO.QueryDef
Dim strLanguage As String
Set Employees = CurrentDb

DoCmd.SetWarnings False

If Me.cboCourseDir <> "" Then

intMsgBoxAnswer = MsgBox("Are you sure you want to add a new course?", 292)
'React to value returned by message box function
If intMsgBoxAnswer = vbYes Then

If Me.fraLanguage.Value = 0 Then
strLanguage = "=Ro"
ElseIf Me.fraLanguage.Value = 1 Then
strLanguage = "=Ru"
ElseIf Me.fraLanguage.Value = 2 Then
strLanguage = "=En"
ElseIf Me.fraLanguage.Value = 3 Then
strLanguage = "=Mu"
End If

'Checks to ensure the standard query still exists
If Not QueryExists("qryStaffListQuery") Then
Set qdf = Employees.CreateQueryDef("qryStaffListQuery")
Else
Set qdf = Employees.QueryDefs("qryStaffListQuery")
End If

strSQL = "INSERT INTO Courses ( Course, Course_ID, [Language], Start_Date, End_Date ) "
strSQL = strSQL & "VALUES ( '" & Me.cboCourseDir & "', '" & Me.cboCourseDir & "_" & strLanguage & "_" & Me.txtStart_Date & " ', '" & strLanguage & "', #" & Me.txtStart_Date & "#, #" & Me.txtEnd_Date & "# );"

'Resets the form to accept additional queries
qdf.SQL = strSQL
DoCmd.Echo False

If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffListQuery"
End If

DoCmd.OpenQuery "qryStaffListQuery"

Me.cboCourseDir = Null
Me.fraLanguage = Null

ElseIf intMsgBoxAnswer = vbNo Then
MsgBox "Course Add Cancelled"
End If

DoCmd.SetWarnings True

Else
MsgBox "Please Select a Course"
End If

cmdOK_Click_exit:
DoCmd.Echo True
Set qdf = Nothing
Set Employees = Nothing
Exit Sub

'Error Routine
cmdOK_Click_err:
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub



The course language should be captured based on the option button selection and sent to the string "strLanguage".

Neither this string or the course dates will insert correctly, and I've spent the last 2 days banging my head against the wall trying to figure out why.

Finally, I'd like to find a way to combine 3 different fields from my form and Insert this value into a column. Preferably the format would be cboCourseDir_strLanguage_txtStart_Date

I'm sure all these problems come down to the syntax I'm using to include these fields in the insert, but it's beyond me what I'm doing wrong.

Thanks in advance for any advice you can offer,

Will

 

[wazz]

the values read from the form from:
...& "', #" & Me.txtStart_Date & "#, #" & Me.txtEnd_Date & "# );"
might be read as strings or possibly variants. if you are inserting dates you might need to cdate(me.txtend_date).

not sure about the language. strange. perhaps check for null and see what you get?

if the three fields are not concatenating properly i would consider doing the concatenation outside of the sql string, making sure all values are there and the correct type, concat then use the variable in the sql str. hth.