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?
candgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
-Jim
harfangCommented:
> 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)
        .AddNew
        !strFieldOne = Me.txtOne
        !strFieldTwo = Me.txtTwo
        .Update
    End With

Does that get you started?
Good luck!
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
candgAuthor Commented:
harfang,
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)
         
        .AddNew
        !Date = Me.txtresult
        '!strFieldTwo = Me.txtTwo
        .Update
End With

End Sub
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

candgAuthor Commented:
i took off the dbopentable and it worked
harfangCommented:
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, _
            adCmdTableDirect
        .AddNew
        ' etc...
        .Update
    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.

Cheers!
(°v°)
harfangCommented:
Ah, didn't catch your last posting. Glad it worked.
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.