Writing multiple unbound controls to a table

[dccjr]

I have a user form with multiple unbound textboxes and comboboxes. I would like to have the data from all of these write to the a new record once the user clicks the finalize button. I really didn't know if I could do it without "Dim"ing each one and writing each to the table individually. I also use this form to edit records, so I really did not want to bind the fields to the table. (The user can click the Edit button and enter serach criteria for the record to edit. I think I can query the record and populate the fields (I have not gotten very far with this). Any help would be greatly appreciated.

[pbaldy]

It can be done; you either use the AddNew method of a recordset or build an SQL statement and execute it. It would be far simpler to use a bound form. You might consider adapting the search form in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

[ssanfu]

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)

                     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:

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

[dccjr]

Thank you to both of you. I am looking over both suggestions and trying to decided the best avenue. Being new to VBA (on my own), it is often difficult to discern which way to go. Time constraints at work (for this project) don't make things any better. I really appreciate your input. Thanks again. I will post how it works out.

[Rainlover]

The decision should be easy.

Use Bound Controls. After all this is how MS Access was designed. Not to use unbound forms.

By using unbound controls you are suggesting that you can write better Code than the Architects from Microsoft.

Unbound Controls have their place but their usage is rare.