Where do I find the code in an existing database?

[pcsunaccess]

I am trying to add a small functionality to the existing database. The existing form has a few text boxes that are filled in with data and the data is inserted into the tables in the db, when add button is clicked. I have added few more text boxes to the same form and I am trying to insert the data into the same table. When I opened the addclick button's code I could only see the following code:

Private Sub AddButton_Click()
If Not cTestMode Then On Error GoTo ErrPlace
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Exitplace:
Exit Sub

ErrPlace:
If Err.Number <> cDoCmdError Then
MsgBox Err.Number & &quot;: &quot; & Err.Description, vbOKOnly, cDBName
End If
Resume Exitplace
End Sub

I don't see the code with the 'INSERT' statements and other SQL queries. Where else do I find them? Thanks.

 

[mpastore]

There is no INSERT code, MS access is taking care of this behind the scenes bcause the form is bound to the data table. The command DoCmd.RunCommand acCmdSaveRecord is the equvalent of what you are looking for.

In MS Access you can in fact code in a traditional SQL fashion, but it is for the most part unnecessary. This is what makes Access attractive to people form non-programming backgrounds.

Mike Pastore

Hats off to (Roy) Harper

 

[pcsunaccess]

Thanks for the reply. But what if I want to add data to a different table but from the same form? How should I manage the 'properties' of some of the text boxes to be connected to a different table?

 

[mpastore]

Thats a little more work, depending on the relationship between the table that the form is based on and the other tables you want to update. If the relationship is 1:1 you could make the recordsource for the form a query which has joined the fields you wish to update. If you can do this, your job is done, the form will take care of getting the data back to the source tables via the updatable query.

However, if the other data elements cannot be updated in this way you can do a few other things:

1) Create update or append (insert) queries that take parameters such as the primary key value needed for the update and the values you wish to update. This is probably the most straight-forward way to do it. On your form save event you can run the query after having set the query parameters. You'll have to look into the help to see how to do this, it's pretty easy.

2) Your other option is the traditional SQL route. You can assemble an SQL statement and run it in code using the database.execute() function. This is harder to do because you have to worry about quotation marks for text fields (a lot of posts come into this forum on account of this).

Good luck.

Mike Pastore

Hats off to (Roy) Harper

 

[pcsunaccess]

Thanks. The first one worked.