[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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?
0
candg
Asked:
candg
  • 3
  • 2
1 Solution
 
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.
-Jim
0
 
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°)
0
 
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
candgAuthor Commented:
i took off the dbopentable and it worked
0
 
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°)
0
 
harfangCommented:
Ah, didn't catch your last posting. Glad it worked.
(°v°)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now