This one of the problems with using unbound controls and forms. You have to write all of the code to check the data and create the SQL statement.
I do something similar - I read a text file, do some processing and after I have processed one line from the text file, I write (append) the data to the table. It takes a lot of code.....
Here is part of my code to save names to the employee table. The variables starting with a "v" are variables; You would use your control names (example Me.combo1)
Code:
sSQL = "Insert into employee_name_def (end_Last_added, end_SSN, end_Emp_Number, end_Last, end_First"
sSQLVal = " Values(True, '" & vSSN & "','" & vEID & "','" & ConvertQuotesSingle(vLast) & "','" & ConvertQuotesSingle(vFirst)
If Len(Trim(vMI)) > 0 Then
sSQL = sSQL & ", end_MI"
sSQLVal = sSQLVal & "','" & vMI
End If
If Len(Trim(vaddr1)) > 0 Then
sSQL = sSQL & ", end_addr1"
sSQLVal = sSQLVal & "','" & ConvertQuotesSingle(vaddr1)
End If
If Len(Trim(vaddr2)) > 0 Then
sSQL = sSQL & ", end_addr2"
sSQLVal = sSQLVal & "','" & ConvertQuotesSingle(vaddr2)
End If
If Len(Trim(vcity)) > 0 Then
sSQL = sSQL & ", end_city"
sSQLVal = sSQLVal & "','" & ConvertQuotesSingle(vcity)
End If
If Len(Trim(vState)) > 0 Then
sSQL = sSQL & ", end_state"
sSQLVal = sSQLVal & "','" & vState
End If
If Len(Trim(vZip)) > 0 Then
sSQL = sSQL & ", end_zip"
sSQLVal = sSQLVal & "','" & vZip
End If
sSQL = sSQL & ", end_dob, end_Emp_Class"
sSQLVal = sSQLVal & "', #" & vDOB & "#, '" & vEmpClass & "'"
'convert gender to boolean
If Len(Trim(vGender)) > 0 Then
Select Case vGender
Case "M", "Male"
vGenderBool = 0
Case "F", "Female"
vGenderBool = -1
End Select
sSQL = sSQL & ", end_gender)"
sSQLVal = sSQLVal & "," & vGenderBool & ");"
Else
sSQL = sSQL & ") "
sSQLVal = sSQLVal & ");"
End If
sSQL = sSQL & sSQLVal
' Debug.Print sSQL
CurrentDb.Execute (sSQL), dbFailOnError
There is a function named "ConvertQuotesSingle" which deals with apostrophes in names like O'Brian.
These types of lines check if there is data or if NULL >> " If Len(Trim(vaddr1)) > 0 Then"
If there is data, it is added to the string.
-----------
To write from a recordset to controls on a form, the code would look like:
Code:
Set r = currentdb("SELECT ... FROM.... WHERE .....")
Me.ubLast = R!LastName
Me.ubFirst = R!FirstName
Me.ubMI = R!MI
Me.ubAddr1 = R!addr1
Me.ubAddr2 = R!addr2
Me.ubCity = R!city
Me.ubstate = R!State
Me.ubZip = R!Zip