Subform Append Query

powerpointpete · December 11th, 2003, 02:06 PM

I have a main Form - FrmAddProject

The Form has 4 fields

JobID, JobDate, CustID, CustJobName

with a subform FrmAddProjectTapes

The sub form has 5 fields:

TapeID, ClientTapeID, JobID, AssetTypeID, TapeDesc

I want to add a button to each of the sub form tabs that exports the data:

TapeID, JobDate, OutDate (date()), DispID to a table called Tape Dispatch.

The table Tape Dispatch has the following fields:

DispID, TapeID, JobDate, OutDate

I am currently using an append query (QryDispatchByTape)for this my button code is as follows:

Public Sub DispatchQuery_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDispatchByTape"
msgbox "Done!"
DoCmd.SetWarnings True

End Sub

The query is as follows:

Expr1: [Forms]![FrmAddproject]![FrmAddProjectTapesSubform]!DGPTapeID
Expr2: [Forms]![frmaddproject]![JobDate]
OutDate: Date()
DispID

Though I get no errors, I also dont get any records added to the dispatch table

Heeelp

Steven · December 11th, 2003, 07:05 PM

Firstly - I would take the spaces out of any object names in your database - it tends to make things a LOT harder.
Maybe try something like this (where I've used "tblDispatch" instead of "Tape Dispatch":

Code:

Public Sub DispatchQuery_Click()
Dim intTape as Integer
Dim dtJob as Date
intTape = Form_FrmAddProjectTapesSubform.DGPTapeID
dtJob = Form_frmAddProject.JobDate

CurrentDb.Execute("INSERT INTO tblDispatch ( [TapeID], [JobDate, [OutDate] )" _
    & " Values (" & intTape & ", " & dtJob & ", Date());")

MsgBox "Done"
End Sub

Steven

I am a loud man with a very large hat. This means I am in charge
(edited because of a typo)