DB Connection and updating with user input

[Ikon]

hey guys, i'm new to this forum as far as being a poster, but i have a very pressing question, i have been having a hard time with this for weeks now.

i dont normally use ms access and dont have that much experience with db connections. what i am trying to accomplish is creating a form in access 2007, where the user can add records to one table out of the database.

how do i create the connection code in the code behind for the button? and how to i take the user input and put that into possibly a SQL statement in the code? i'm not use to VBA at all, so this is all new for me, the syntax has been throwing me off majorly, i feel like i'm losing my head. please. SOMEONE HELP!

[Ikon]

heres my current code by the way:

Private Sub btnNew_Click()
Dim dbs As ADODB.Connection
Dim sql As String
Dim rst As Recordset

Set dbs = CurrentProject.Connection
Set rst = dbs.Open("Client_Info")
sql = "insert into client_info (client_id, client_name, account_type) values(" & Me.txtClientID & ", '" & Me.txtClientName & "', #" & Me.txtAcctType & "#)"
DoCmd.RunSQL sql

End Sub

[GolferGuy]

Actually, Access is able to "bind" the data from the table to the form you create within Access. Therefore, when the user inputs, updates, or deletes a record, Access will update the table as soon as the user leaves that record. This would include the user even closing the form. Of course there are ways to block this updating, but this "automatic" updating of the table from the form data is the normal way Access works. All you need to do is put the table name into the DataSource field under the Data tab of the Form properties sheet.

[Ikon]

yea i noticed that access did that, it was kind of an annoyance. I"m doing this small database for this company, and I"m not the biggest fan of Access. I was trying to just have text boxes from which the VBA code could pull the values and insert them into the tables. figured that'd be the easiest way and the best way to keep the user from having even the slightest chance of unwanted interaction. I tried the bind thing too though, and the problem i ran in with that is when the form loads, there apparently is already a record in the text boxes. how to i start the form on the last record, or actually, the last row where a record could be inserted?

[GolferGuy]

Simple binding of a form or report to a data source is one of the basic design concepts of Access. Do you also get annoyed when you can not easily hammer a big nail with a small screwdriver?

It sounds like you do not want the user to be able to do any updating to the table. If that is the case, then set the form to be a data entry form. Look into the properties for the form, the Data tab, and the properties should be fairly self explanatory. You may have to do a little testing, but not too much.

[Ikon]

got it! i ended up just binding it like you suggested, had to change some of the settings in the prop window to have the focus on the last record/new record spot. put in a list box bound to a query to show the records as they get updated on gotfocus on the first text box. works well.

and yes, i do get annoyed when i have to hammer a big nail with a little screw driver. why i'm using a screw driver escapes me....but yea.

honestly though, i'm just not a big fan of access, i can see its uses, i'm just more experienced with oracle, mysql, sql server and a few more of the enterprise level DB software. so this is completely new to me. most of the people i have talked to love access because of its ease of use and lack of user written sql, and yes i know theres a button that allows you sql view to do that, but syntax is slightly different, like with outer joins being left joins, and a few other quirks.

but. i did get it, appreciate the help about the binding though. THANKS!!