Table Design

[bparkinson]

If you have a form which allows you to select from a dropdown list of both new and existing tech, it makes no difference. You ares still inserting a new row in the Tech-Card link table whether it's their first card, or a replacement.

 

[bparkinson]

Regarding adding the fuelcard record when the tech record is added. In SQL Server we do this all the time using triggers which fire a batch of SQL commands. You can do the same sort of thing in the AfterInsert event of your Tech record.

 

[Bopsgtir]

ive now done that with the below code

Private Sub NewStarterSubmit_Click()

Dim sSQL As String

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex", "pauldavis@comex", "", "Please Can You Order A Tech ID For The Attached New Starter", "Please Order Tech ID And PDA", True, ""
        If (Me.Fuel_Card_Required = True) Then
            DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex2000uk.com", "", "", "Fuel Card Required", "Please Can You Order A Fuel Card For The Attached Engineer ", True, ""
            
    sSQL = "INSERT INTO TblFuelCard (DateOrdered, OrderReason) VALUES (#" & Date & "#, 'Card Ordered For New Starter " & Me.[FirstName] & " " & Me.[Surname] & "')"

    DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
        
        End If
            DoCmd.GoToRecord acForm, "frmNewStarterForm", acNewRec
    End Sub

 

[bparkinson]

ive now done that with the below code

Private Sub NewStarterSubmit_Click()

Dim sSQL As String

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex", "pauldavis@comex", "", "Please Can You Order A Tech ID For The Attached New Starter", "Please Order Tech ID And PDA", True, ""
        If (Me.Fuel_Card_Required = True) Then
            DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex2000uk.com", "", "", "Fuel Card Required", "Please Can You Order A Fuel Card For The Attached Engineer ", True, ""
            
    sSQL = "INSERT INTO TblFuelCard (DateOrdered, OrderReason) VALUES (#" & Date & "#, 'Card Ordered For New Starter " & Me.[FirstName] & " " & Me.[Surname] & "')"

    DoCmd.SetWarnings False
        DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
        
        End If
            DoCmd.GoToRecord acForm, "frmNewStarterForm", acNewRec
    End Sub

Very Nice!