I currently have a form called "Aircraft" that allows the user to make new aircraft entries. The form consists of an Autonumber textbox(non-updatable), a SN textbox, an AircraftType text box and a WorkAuthDate text box. All these textboxes are linked to a table, tblacftsn. Here is what I am trying to do and how far I have succeeded. After the user enters the SN, AircraftType and WorkAuthDate, and clicks next record or new record, I wanted to write multiple records to another table called "Records." I wanted to do this because there are some standard jobs that are always performed on these aircraft and it would be easier to automatically write those standard records to the Records table when a new aircraft comes in rather then having to insert that information every time. Here is my code so far (This is in the Aircraft form's AfterInsert procedure):
Private Sub Form_AfterInsert()
Dim db As Database
Dim rst0 As Recordset
Dim rst1 As Recordset
Dim strSQL0 As String
Dim strSQL1 As String
Set db = CurrentDb
' This next line of code sets up the SQL statement that will only select
' those jobs that are standard from the table "JobID"
strSQL0 = "SELECT * FROM JobID WHERE [Standard] = -1;"
strSQL1 = "SELECT * FROM Records;"
Set rst0 = db.OpenRecordset(strSQL0)
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
rst0.MoveFirst
' This loop cycles through all the standard jobs in the JobID recordset
' and creates new records based on that information in the Records table
Do While Not rst0.EOF
rst1.MoveLast
rst1.AddNew
rst1![GROUP] = rst0!GROUP
rst1![Document Type] = rst0!DOCTYPE
rst1![Document Number] = ""
rst1![STC MOD Number] = ""
rst1![SN] = Forms![frmAircraft]![SN]
rst1![AIRCRAFT TYPE] = Forms![frmAircraft]![AIRCRAFT TYPE]
rst1![WorkAuthDate] = Forms![frmAircraft]![WorkAuthDate]
rst1![Due Date] = Null
rst1![Date Complete] = Null
rst1![Comment] = ""
rst1.Update
rst0.MoveNext
Loop
rst1.Close
rst0.Close
db.Close
End Sub
This part of the code works fine. However, this is what I want to do next. I want to take ONLY the newly created records and lookup in another table called "Lookup" the corresponding manufacturing day (field name: M_Day) for the field WorkAuthDate. The "Lookup" table is consisted of two fields, Calendar_Day and M_Day. For example, the WorkAuthDay is 6-1-08 then I want to lookup in the Lookup table for the M_Day that corresponds to that Calendar_Day (i.e. an SQL JOIN clause). Then I want to add x amount of manufacturing days, where x is equal to a corresponding M_Day value in the JobID table for that job (i.e. how many manufacturing days it takes to complete that job). Then I want to find the corresponding calendar day for the new calculated M_Day and record that in the Records table in the field “Date Due.” As you can see in the code above, I have just been assigning the “Date Due” field values to NULL but I want them to be set to the calendar day, which once again is calculated from the WorkAuthDay. Here is a little schematic:
WorkAuthDay -> M_Day -> M_Day+x -> Due Date
Once again, only for the newly written records. Any ideas? Thanks for the help. Sorry it’s so long.
Ben Wion
Private Sub Form_AfterInsert()
Dim db As Database
Dim rst0 As Recordset
Dim rst1 As Recordset
Dim strSQL0 As String
Dim strSQL1 As String
Set db = CurrentDb
' This next line of code sets up the SQL statement that will only select
' those jobs that are standard from the table "JobID"
strSQL0 = "SELECT * FROM JobID WHERE [Standard] = -1;"
strSQL1 = "SELECT * FROM Records;"
Set rst0 = db.OpenRecordset(strSQL0)
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
rst0.MoveFirst
' This loop cycles through all the standard jobs in the JobID recordset
' and creates new records based on that information in the Records table
Do While Not rst0.EOF
rst1.MoveLast
rst1.AddNew
rst1![GROUP] = rst0!GROUP
rst1![Document Type] = rst0!DOCTYPE
rst1![Document Number] = ""
rst1![STC MOD Number] = ""
rst1![SN] = Forms![frmAircraft]![SN]
rst1![AIRCRAFT TYPE] = Forms![frmAircraft]![AIRCRAFT TYPE]
rst1![WorkAuthDate] = Forms![frmAircraft]![WorkAuthDate]
rst1![Due Date] = Null
rst1![Date Complete] = Null
rst1![Comment] = ""
rst1.Update
rst0.MoveNext
Loop
rst1.Close
rst0.Close
db.Close
End Sub
This part of the code works fine. However, this is what I want to do next. I want to take ONLY the newly created records and lookup in another table called "Lookup" the corresponding manufacturing day (field name: M_Day) for the field WorkAuthDate. The "Lookup" table is consisted of two fields, Calendar_Day and M_Day. For example, the WorkAuthDay is 6-1-08 then I want to lookup in the Lookup table for the M_Day that corresponds to that Calendar_Day (i.e. an SQL JOIN clause). Then I want to add x amount of manufacturing days, where x is equal to a corresponding M_Day value in the JobID table for that job (i.e. how many manufacturing days it takes to complete that job). Then I want to find the corresponding calendar day for the new calculated M_Day and record that in the Records table in the field “Date Due.” As you can see in the code above, I have just been assigning the “Date Due” field values to NULL but I want them to be set to the calendar day, which once again is calculated from the WorkAuthDay. Here is a little schematic:
WorkAuthDay -> M_Day -> M_Day+x -> Due Date
Once again, only for the newly written records. Any ideas? Thanks for the help. Sorry it’s so long.
Ben Wion
