Exclude similar records in table

[BrianLe]

In AC97, I have a form frmAddNewTrainNo with tbxTrainYear, tbxTrainNoStart, tblTrainNoFinish that is used to add new train numbers to tblTrain. There is also a lbxTrainNoYear with a record source query that displays the last train number's TrainYear and TrainNo. The user is supposed to enters values into tbxTrainYear, tbxTrainNoStart, and tbxTrainNoFinish for the next train numbers to add. However, if a pre-existing TrainYear and TrainNo are entered, the duplicate train number will be added to the tblTrain.

In tblTrain, there are fields TrainID, TrainYear, and TrainNo. Each train number is made up of TrainYear and TrainNo. In tblTrain I only want one record with each TrainYear-TrainNo pair.

I have the following code, but my "If" statement doesn't prevent the inclusion of duplicate TrainYear-TrainNo pairs.

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
   DoCmd.SetWarnings True

   Dim AddTrainNo As Long, SQL1 As String
   Dim lngTrainNoStart As Long
   Dim lngTrainNoEnd As Long
   Dim lngTrainYear As Long
  
 
   lngTrainNoStart = Me!tbxTrainNoStart
   lngTrainNoEnd = Me!tbxTrainNoFinish
   lngTrainYear = Me!tbxTrainYear
   
   For AddTrainNo = lngTrainNoStart To lngTrainNoEnd
    
   If lngTrainYear = [TrainYear] And lngTrainNoStart = [TrainNo] Then
   MsgBox "Train number already exists!", vbInformation, "Selection Error"
   Exit Sub
   Me.Undo
   End If
        
        SQL1 = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
                 "VALUES(" & lngTrainYear & "," _
                           & AddTrainNo & ");"
 
         Debug.Print SQL1
         DoCmd.RunSQL SQL1
   Next
   DoCmd.SetWarnings True
   
Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
   MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
    
 End Sub

Any suggestions?

Thanks,

Brian

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

If I use this code, the first train number will be added, but then it will stop since now there is a train number with TrainYear = tbxTrainYear and TrainNo = tbxTrainNoStart in tblTrainNo.

If DLookup("TrainYear", "tblTrain", "TrainYear = [Forms]![frmAddNewTrainNo].tbxTrainYear" & " And TrainNo = [Forms]![frmAddNewTrainNo].[tbxTrainNoStart]") Then

I then tried various versions of the following code so that it would look at the next train number to be added by AddTrainNo, but I get a syntax error.

If DLookup("TrainYear", "tblTrain", "TrainYear = [Forms]![frmAddNewTrainNo].tbxTrainYear" & " And TrainNo = "'AddTrainNo' "") Then

Any suggestions?

Thanks,

Brian

 

[TheAceMan1]

How are ya BrianLe . . .

I keep wondering why your fighting with detecting duplicate [blue]TrainNo's[/blue], when it appears your process of assignment can be fully automated without the need, save an user input of the number of trains to generate the new [blue]TrainNo's[/blue] for.

It's easy enough to take the current year and last [blue]TrainNo[/blue] for that year, and increment [blue]TrainNo[/blue] for as many trains as required! . . . [purple]No duplicates here! . . .[/purple] Its kinda like your own auto number only its incremented by you! As an example, I come up with the following routine:

[blue]Public Sub GenerateTrains([purple][b]TrainCnt[/b][/purple] As Long)
   Dim db As DAO.Database, SQL As String, Cri As String
   Dim idx As Long, x As Long, TYr As Long
   
   Set db = CurrentDb
   TYr = Year(Date) [green]'Current Year[/green]
   
   Cri = "[TrainYear]=" & TYr
   [purple][b]idx[/b][/purple] = Nz(DMax("[TrainNo]", "TblTrain", Cri), 0) [green]'Max TrainNo for TYr[/green]
   
   For x = 1 To TrainCnt
      [purple][b]idx[/b][/purple] = [purple][b]idx[/b][/purple] + 1
      SQL = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
            "VALUES(" & TYr & "," & [purple][b]idx[/b][/purple] & ");"
      db.Execute SQL, dbFailOnError
   Next
   
   Set db = Nothing
   
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[BrianLe]

AceMan,

I can't get to my computer that has the project right now, but one problem I see with your suggestion is that the train numbers are assigned a few weeks in advance. Therefore, we wouldn't be able to assign train numbers for next year until next year.

Thanks,

Brian

 

[TheAceMan1]

BrianLe . . .

In that case [blue]TYr[/blue] would come from the form (Input by the user) . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[BrianLe]

I've never used a Public Sub before. How to you execute one?

Thanks,

Brian

 

[TheAceMan1]

[blue]I've never used a Public Sub before. [purple]How to you execute one?[/purple][/blue]

The [blue]public sub[/blue] came about as a matter of presenting the example. Since you asked, I'll stay on that course.

A [blue]Public Sub or Function[/blue] is a routine that can be called anywhere in code. If you have need to append trains from more than one location in code, [blue]this is the reason why![/blue] . . . Instead of having the same code in many places, we allot one routine/function and call it from all these places!

Before we continue, backup, then delete, the code in your [blue]click event[/blue] so you can come back to square one . . .

In the following example we'll use [blue]tbxNoStart[/blue] as a count of trains to be appended. That is . . . you'll enter the number of trains to be appended! . . . If you want to append 10 trains . . . thats what you'll enter. tbxNoFinish is of no consequence!.

So your going to enter the year in [blue]tbxTrainYear[/blue] and the count (number of trains to be appended) in [blue]tbxNoStart[/blue]!

Now . . . copy/paste the following routine to the code module of the form (the same module where the click event resides!):

[blue]Public Sub GenerateTrains(TrainYear As Long, TrainCnt As Long)
   Dim db As DAO.Database, SQL As String, Cri As String
   Dim idx As Long, x As Long, TYr As Long
   
   Set db = CurrentDb
   
   Cri = "[TrainYear]=" & TrainYear
   'idx = Max TrainNo for TrainYear
   idx = Nz(DMax("[TrainNo]", "TblTrain", Cri), 0)
   
   For x = 1 To TrainCnt
      idx = idx + 1
      SQL = "INSERT INTO tblTrain (TrainYear,TrainNo) " & _
            "VALUES(" & TYr & "," & idx & ");"
      db.Execute SQL, dbFailOnError
   Next
   
   Set db = Nothing

End Sub[/blue]

Then in the [blue]Click[/blue] event of the button, copy/paste the following:

[blue]Call GenerateTrains(Me!tbxTrainYear, Me!tbxNoStart)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997