Ladies and Gents,

Could someone guild me through the process of creating a form so that data entered in the various text fields creates a record in my table after i click the add button.

Tables and form already created. Curious to know how i link the txt field to the appropriate field in the table so that the record is added when i click the add button.

Your help would be appreciated.
Chris

Hi Chris

There are a couple of ways to do this. Firstly you can attach the form to the table using the Record Source in the properties of the form. This will link directly to the fields in the Table, thereby allowing the using to enter directly into the table by selecting new from the record selector of the form. In my opinion this is not a safe method as it allows access to all other records in the table, that are editable and thus could be change or deleted by mistake.

I prefer to do this via an editable recordset. This is done through VBA and I feel is the safest method. To do this, you will need something like the following code in the cmd button click event. You will also need to pick the Microsoft DAO 3.6 library from the references in the Visual Basic IDE.

Dim rstTemp As DAO.Recordset
Dim strSql As String

' Set your sql string to open the recordset

strSql = "SELECT * FROM tablename" 'Or alternatively list each field with a 'commer in between. i,e "SELECT [field1], [field2], [field3] FROM [Tablename]" 'p.s. I always put square brackets around my field and tablename. 'This helps to eliminate errors when using names with spaces in them.

Set rstTemp = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

With rstTemp

.Addnew
![YourFieldName] = me.yourtextboxname

Contine to list all you fieldnames and its respective textbox

.Update

.close

end with

When the user clicks the button, this will add the new record to the table

Hope this helps

Martin

You can also change the SQL statement to:

sSQL = "INSERT INTO tblYourTable([Fieldname1], [Fieldname2]) VALUES('Textvalue', NumberValue)"

If you are using variables to build this statement, then you can do:

sSQL = "INSERT INTO tblYourTable([Fieldname1], [Fieldname2]) VALUES('" & sText & "', " & iNumber & ")"

This will eliminate the need for the subsequent lines:

With rstTemp
.Addnew
![YourFieldName] = me.yourtextboxname
...
.Update
.close

since the act of opening the recordset inserts the values into the table.

HTH


mmcdonal

Hi Chris,

mmcdonal is right, you could use this, however I steered away from this for agility. The reson I use recordsets in this manner is for ease of change. If you are updating all your fields in the table, then using the * allows for additions/removal without changing the the initial SQL line. You the only have to change the statement in the addnew file. This is a long term solution not a quick fix and the solution suggested by many top access database developers including Microsoft. If you want to go the way mmcdonal has suggested, by the INSERT INTO tblYourTable([Fieldname1], [Fieldname2]) VALUES('" & sText & "', " & iNumber & ")" then do away with all I have said and simply put docmd.runsql(strsql) into your command button click event. This will update your table, however should you wish to change your table or form, then you will have to re check all your sql statement and make the appropriate changes.

Either way, if you change your table or form, you will have to rewrite your code, the difference is whether you rewrite it on one line, or many lines.

I do mostly Access / SQL, so most of my code is optimized for that environment. If I run a SELECT * from a table with 500,000 records just to add one record, then I am really heating up the wire. If I do an INSERT statement, then I don't have to pull the entire recordset over to add a new record to it, just push one new record in. If you are using DAO and local tables, this really wouldn't be a concern.

MS also recommends that you don't use *, but name each field you are pulling into a recordset. I think that was even a MCSD question at one time. This will also cut down on the number of fields pulled. So in that case, you end up with the same situation "SELECT EmployeeID, LastName, FirstName FROM..."

HTH

mmcdonal

Hey thanks for the help guys.

Martin - i stuck with your theory. Worked a treat first go too.

Thanks a bunch.
Chris