An unbound form with unbound fields how do i put into a table

I have an idea in my head.  I have this idea that it's possible to get an unbound form with unbound fields to create entries into a new table that has only string fields so that there is no problems.  My question is how do i get those value into a table.  And has anyone ever done this before?
Who is Participating?
> And has anyone ever done this before?
That's a good one!

You have basically two approaches:

1) create an append query from the values in all the controls, and run that query

The query can be build in VB, as in

    strSQL = "INSERT INTO tblTheTable VALUES( '" & Me.txtOne & "', '" & Me.txtTwo & "');"

Or you can build the query in the query builder, using the full references to the controls, e.g.

    Update To: Forms!frmNewRecord!txtOne

2) open a recordset  and use that to append the new record.

For example:
    With CurrentDb.OpenRecordset("tblTheTable", dbOpenTable)
        !strFieldOne = Me.txtOne
        !strFieldTwo = Me.txtTwo
    End With

Does that get you started?
Good luck!
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You'll have to write VBA code in either a form button's .Click event, or your form.BeforeUpdate event, that sets a connection to your database table, and runs an append query.  Something like this (using DAO)...

Private Sub cmbSaveMyRecord_Click()

Dim db as DAO.Database
Set db = CurrentDb()

Dim sSQL as String

sSQL = "INSERT INTO tblMyTableName (Field1, Field2, Field3) "
sSQL = sSQL & "VALUES '" & Me.Textbox1 & "', '" & Me.Textbox2 & "', '" & Me.Textbox3 & "')

db.Execute sSQL

End Sub

The above is a pretty simple example.  You'll want to deal with things like validating your textbox fields to make sure values are in them, removing any singe quote marks ( ' ) that would cause your append query to fail, business rule validation, etc.

Hope this helps.
candgAuthor Commented:
ok here is the code i'm using but it's telling me it's an invalid argument.
Private Sub Command20_Click()
With CurrentDb.OpenRecordset("tbldailyattendance", dbopentable)
        !Date = Me.txtresult
        '!strFieldTwo = Me.txtTwo
End With

End Sub
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

candgAuthor Commented:
i took off the dbopentable and it worked
That is probably due to the use of CurrentDb. That is a DAO object, and you might not have linked to the library for it.

If you intend to use it, you need to go to "Tools / References" and make sure you have "Microsoft DAO ??.? Object Library" checkes.

Else, you can also use ADO recordsets (that library is checked by default), like this:

    With New ADODB.Recordset
        .Open _
            "tblDailyAttendance", _
            CurrentProject.Connection, _
            adOpenDynamic, _
            adLockOptimistic, _
        ' etc...
    End With

I suggest you use the ADO recordset if you are new to this. No need to get confused by two the "old" syntax, even if it seems easier to use at first. Also, make sure you lookup help on .AddNew, there are several very good examples for you to cut-and-paste there.

Ah, didn't catch your last posting. Glad it worked.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.