We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

candg
candg asked
on
Medium Priority
449 Views
Last Modified: 2008-02-26
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?
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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
Commented:
> 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°)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
i took off the dbopentable and it worked

Commented:
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°)

Commented:
Ah, didn't catch your last posting. Glad it worked.
(°v°)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.