Hi Simon,
You can run an append query, using VBA code in the click event of the
command button. Try experimenting with the Northwind sample database first.
Create a new table named "MyTableName", and add a field named ProductID
(Number / Long Integer. Remove default value of 0 for this field, if using
Access 2003 or earlier).
Add a command button to the Product List subform (this subform is associated
with the Categories form). Name it "cmdInsert":
Option Compare Database
Option Explicit
Private Sub cmdInsert_Click()
On Error GoTo ProcError
Dim strsql As String
strsql = "INSERT INTO [MyTableName] ( [ProductID] ) " _
& "Values (" & Me.ProductID & ")"
'Debug.Print strsql '<---Uncomment to print result to Immed. Window
CurrentDb.Execute strsql
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdInsert_Click..."
Resume ExitProc
End Sub
You may want to further enhance this functionality, by setting a unique
index on the ProductID field in MyNewTable, and then trapping for Error 3022
in the Product List subform Form_Error event, to prevent a user from adding
the same record twice. I show how to do this on pages 16-17 of a Word
document that I call "Access Links.doc". You are welcome to download a zipped
copy from my web site:
http://www.accessmvp.com/TWickerath/
If you have not already done so, configure your VBE (Visual Basic Editor) to
automatically add Option Explicit to all newly created modules:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
