Add New Record with Code

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

Dear All,

I know that there have been posts on this before, and i have tried to follow
them, but got myself a bit confused.

Basically, i have a field on a form, and when i add a new record i want that
field to also be entered in a new record in another table.
e.g.
When i enter field "JobNumber" in a new record on the "Information" form, i
want a new record in tbl "Survey" to be created, with "JobNumber" on that
form filled in. These two tables are joined,
"JobNumber" on "Information" is the primary key, and "JobNumber" on
"Survey" is the foreign key.

If anyone can understand what i am trying to do, i would really appreciate a
small sample of code that would be an example for me to use.

Thanks,

Kirstie
 
Hi Kirstie,

The code would go into the forms After Insert Event:

Private Sub Form_AfterInsert()

Dim db As DAO.Database
Dim rs As DAO.Recordset
If Len(Me.txtJobNumber & "") > 0 Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSurvey")
rs.AddNew
rs![JobNumber] = Me.txtJobNumber
rs.Update
rs.Close
End If

End Sub


--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
 
Hi,


To add a record into a table, you can, without opening any recordset,
use:


CurrentDb.Execute "INSERT INTO tableName( ListOfFields ) VALUES(
ListOfValues ) ;" , dbFailOnError


In my SQL statement, the key words are all cap. The values must be
delimited appropriately if they are string or dates.


Dim s as String
s= "Hello"
CurrentDb.Execute "INSERT INTO myTable( f1) VALUES( """ & s &
""" ) ", dbFailOnError

would insert a record in myTable with the field f1 with the value of
the string of what is actually hold in variable s.


Since VBA is "another user", that record is not automatically seen by
the form, until you requery it.



Hoping it may help,
Vanderghast, Access MVP
 
Kirstie,

I am assuming your form is unbound. With that assumption,
you would put the following in a the OnClick event of a
command button on the form.

dim db as Database
dim qdf as querydef
dim rst as recordset

dim sqlSTR1 as string 'to determine if record exists
dim sqlSTR2 as string 'to append new record to database
dim sqlSTR3 as string 'to modify existing record

set db = currentdb

'the folllowing query will determine if the record exists.
sqlSTR1 = "Select yourtablename.* from yourtablename WHERE
(((yourtablename.fieldname)='" & formfieldname & "'));"

'the following query will modify an existing record
sqlSTR2 = "UPDATE yourtablename SET
yourtablename.fieldname = '" & modifieddatafieldName & "'
WHERE (((yourtablename.field)= '" & PrimaryKeyfieldName
& "'));"

'the following query will append a new record to the table
sqlSTR3 = "INSERT INTO yourTablename(field1, field2)
SELECT '" & [Forms]![FormName]![FieldName1] & "' as
field1, [Forms]![FormName]![FieldName2] & "' as field2
From YourTable;"

set rst=db.openrecordset(sqlSTR1,dbopensnapshot)

if(rst.recordcount>0)then
set qdf=db.createquerydef("",sqlSTR2)
elseif(rst.recordcount=0)then
set qdf.createquerydef("",sqlSTR3)
endif

qdf.Execute

if(qdf.recordsaffected=0)then
msgbox="No Records were affected"
endif

db.close
set db=nothing

endsub

Hope that helps!

Kevin
 
Back
Top