Transfer Data from Form to Independent table

  • Thread starter Thread starter Fake
  • Start date Start date
F

Fake

Hi I've got a form with 3 boxes that I would like to be entered into a
second table with a button event

they are the following formats

Box 1 = Date "Aud_Date"
Box 2 = Number "Audits"
Box 3 = Number "Shift"

they would go to the following table - JAP_audits

Field 1 is an autonumber
Field 2 = DATE
Field 3 = Num_Audits
Field 4 = Shift

would appreciate any help

Thanks
 
Hi I've got a form with 3 boxes that I would like to be entered into a
second table with a button event

they are the following formats

Box 1 = Date "Aud_Date"
Box 2 = Number "Audits"
Box 3 = Number "Shift"

they would go to the following table - JAP_audits

Field 1 is an autonumber
Field 2 = DATE
Field 3 = Num_Audits
Field 4 = Shift

would appreciate any help

Thanks
Click to expand...


Private cmdInsertRecord_Click()

Dim dao.db as database
Dim strSQL as string

On Error goto cmdInsertRecordErr

Set db = CurrentDB() 'or wherever your database is

strSQL = "INSERT INTO JAP_audits" & _
" (DATE, Num_Audits, Shift)" & _
" VALUES (#" & _
Format(Me.Aud_Date,"dd-mmm-yyyy") & "#," & _
Me.Audtis & "," & _
Me.Shift & ");"

db.execute strSQL,dbFailOnError

Msgbox "Record successfully added.",vbinformation

cmdInsertRecordExit:
Set db = Nothing
Exit Sub

cmdInsertRecordErr:
Msgbox err.description,vbcritical
err.clear
Resume cmdinsertRecordExit

End Sub



You may run into trouble with the field DATE being a reserved keyword
in Access but largely, this should work.


Hope it helps.



richard


Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing
 
Thanks
this worked, you were right about the having a problem with using "date", I
will remember that for future ref I think it's why i was having trouble.


Thanks Kev

I ended up using the following

Dim strSQL As String

On Error GoTo cmdInsertRecordErr

Set db = CurrentDb() 'or wherever your database is

strSQL = "INSERT INTO JAP_audits" & _
" (Aud_Date, Num_Audits, Shift)" & _
" VALUES (#" & _
Format(Me.Aud_date, "dd-mmm-yyyy") & "#," & _
Me.Audits & "," & _
Me.Shift & ");"

db.Execute strSQL, dbFailOnError

Me!JAPdata_subform.Visible = True 'if the sub form opens I know it has
saved the data
Me!JAPdata_subform.SetFocus

cmdInsertRecordExit:
Set db = Nothing
Exit Sub

cmdInsertRecordErr:
MsgBox Err.Description, vbCritical
Err.Clear
Resume cmdInsertRecordExit
 
Back
Top