Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 18th, 2004, 05:14 PM
Authorized User
Join Date: Jun 2003
Location: Montclair, CA, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default Coding for data input from one form to 2 tables...

Currently an existing form that Users input --- gets stored into an existing table. I would like for that data entry form to be stored into 2 tables at a time. Can someone plz assist with the logic/code needed in Access VBA? :-) Thanks ahead of time. Your help is much appreciated.

Old February 19th, 2004, 12:35 AM
Authorized User
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

Although you call it a 'data entry form', do you mean that the form is bound and the 'Data Entry' property is set True? If so, you may want to use the entry form's Before Update event to run a line of DAO code:

Dim db As Database
Set db = CurrentDb
db.Execute ("Insert into tbl2 (ID, ProjectID, JobName, StartDate) Values(" & Me.txtID & ", " & _
  Me.txtProjectID & ", '" & Me.txtJobName & "', #" & Me.txtStartDate & "#)")
If db.RecordsAffected = 0 Then
    Cancel = True
    Msgbox "Please check the field values as the record is missing required values or values are invalid"
End if

If the form isn't a data entry form, you can check the form's .NewRecord property to see whether the query should be an "Insert Into" append query or an "Update tbl1 Set" update query using the primary key in a where clause.

An alternative is to use the form's after update event to run an append query from the first table to the second table selecting on a where clause that is limited to the primary key value read from the form. The after update shouldn't require you to check the records affected since the record would not have been written unless the fields are valid. The downside is that if an error occurs between the insert and the append fires, such as a loss of database connection, then the tables will more likely go out of synch.

The syntax of the append query selecting on a single key value is simpler than the sample text given above which shows the delimiter usage required for numeric, text and date type values when inserting form control values.

Edmonton AB Canada

Similar Threads
Thread Thread Starter Forum Replies Last Post
insert data in two tables from form mfarooqw ASP.NET 1.0 and 1.1 Professional 1 July 10th, 2007 08:34 AM
Saving data to a table through form input misskaos Classic ASP Basics 16 October 2nd, 2006 11:54 AM
HELP!!!Coding a command button to open a form solva Beginning VB 6 5 May 10th, 2005 09:56 PM
tables relationship through coding itHighway Classic ASP Basics 2 May 3rd, 2005 06:39 PM
Updating Two Tables from Form Input PamS Access 6 October 30th, 2003 02:46 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.