Automatically Assign ID Number to Set of Records and Loop Until End

Anonymous
2010-11-14T17:08:07+00:00

I am a beginner so any assistance I receive will need to be specific with examples. I can't yet go from the abstract idea and turn that into hard code.

I have two queries (separate because I do not know how to make them into one). The first query exports the top 30 records into a table, then I manually run the second query to update the source table with the resultant ID number. I have thousands of records and I'd like to be able to put code behind a button that:

  • Assigns the top 30 records and does the SELECT INTO (Instead of the prompt the user will choose the city name from a combo box which I now know how to design.)
  • Updates the source table with the ID number
  • Loops until the end but on the last record assign ALL remaining records to the last ID number.  I have already determined exactly how many records need to be created manually. I have a report that tells me how many ID numbers I will need based on dividing the recordset into 30. I then bulk create those IDs.

BONUS: Have Access determine how many IDs I need and then assign the records and create the exports as text files with the name 'export1', 'export2' or 'export' & 'resultantidnumber', etc.

qryEXPStreetsandTrips:

SELECT TOP 30 tblSurveyData.SurveyID, tblSurveyData.FullName, IIf(tblSurveyData.Address2="1/2",[StNumber] & " " & [Address2] & " " & tblSurveyData.StName,[tblSurveyData].[StNumber] & " " & [tblSurveyData].[StName]) AS Address, IIf([tblSurveyData].[Apt?]=-1,"Apt. ",IIf([tblSurveyData].[Room?]=-1,"Room ",IIf([tblSurveyData].[Studio?]=-1,"Studio ",IIf([tblSurveyData].[Suite?]=-1,"Suite ",IIf([tblSurveyData].[Unit?]=-1,"Unit ",IIf([tblSurveyData].[Space?]=-1,"Space ",IIf([tblSurveyData].[Lot?]=-1,"Lot ",IIf([tblSurveyData].[Bldg?]=-1,"Bldg ",IIf([tblSurveyData].[Floor?]=-1,"Floor ",Null))))))))) & [Address2] AS Addr2, tblCity.City, tblSurveyData.Zip, CLng([Enter the territoryID to assign:]) AS TerritoryID INTO EXPStreetsandTrips

FROM tblSurveyData LEFT JOIN tblCity ON tblSurveyData.CityID = tblCity.CityID

WHERE (((tblCity.City) Like [Enter the city name:] & "*") AND ((tblSurveyData.TerritoryID)=106) AND ((tblSurveyData.TerritoryTypeID)=3))

ORDER BY tblSurveyData.Zip, tblSurveyData.Latitude;

qrySurveyData SET Survey (I have to wait a few seconds until the EXPStreetsandTrips is created before launching this query or it won't work, of course):

UPDATE tblSurveyData INNER JOIN EXPStreetsandTrips ON tblSurveyData.SurveyID=EXPStreetsandTrips.SurveyID SET tblSurveyData.TerritoryID = [EXPStreetsandTrips].[TerritoryID]

WHERE (((tblSurveyData.TerritoryID)=106) And ((tblSurveyData.SurveyID)=EXPStreetsandTrips.SurveyID) And ((tblSurveyData.TerritoryTypeID)=3));

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2010-11-17T19:18:13+00:00

Here is a new version that exports text files. Before testing it, change the export path in the variable strPath. It MUST end in a backslash.

Private Sub cmdAssignTest_Click()

  Dim strCongregationID As String

  Dim strUserID As String

  Dim lngBatchSize As Long

  Dim lngOldTerritoryID As Long

  Dim lngNewTerritoryID As Long

  Dim dbs As DAO.Database

  Dim rst As DAO.Recordset

  Dim rst2 As Recordset

  Dim strSQL As String

  Dim lngRecordCount As Long

  Dim lngCurrentRecord As Long

  Dim strTerritoryName As String

  Dim lngTerritoryNumber As Long

  Dim f As Integer

  Dim strPath As String

  Dim strLine As String

  Dim strDelimiter As String

  On Error GoTo ErrHandler

  ' Change these as needed

  ' Batch size

  lngBatchSize = 30

  ' Unassigned ID

  lngOldTerritoryID = 106

  ' Path for text files

  strPath = "C:\Surveys"

  ' Delimiter for text files

  strDelimiter = vbTab ' can also be ","

  ' Get some info

  strCongregationID = DLookup("CongregationID", "tblVar")

  strUserID = DLookup("UserID", "tblVar")

  strTerritoryName = Nz(Me.TerritoryName, InputBox("Please provide a name for the new territory"))

  ' Get number of unassigned survey records

  strSQL = "SELECT * FROM tblSurveyData WHERE CityID=" & strCityID & _

    " AND TerritoryTypeID=" & strTerritoryTypeID & _

    " AND TerritoryID=" & lngOldTerritoryID

  Set dbs = CurrentDb

  Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

  If rst.EOF Then

    MsgBox "No records!", vbExclamation

    GoTo ExitHandler

  End If

  rst.MoveLast

  rst.MoveFirst

  lngRecordCount = rst.RecordCount

  f = FreeFile

  ' Get highest TerritoryNumber

  lngTerritoryNumber = Nz(DMax("TerritoryNumber", "tblTerritory", _

    "CityID=" & strCityID & " AND TerritoryTypeID=" & strTerritoryTypeID), 0)

  Set rst2 = dbs.OpenRecordset("tblTerritory", dbOpenDynaset)

  ' Loop

  lngCurrentRecord = 0

  Do While Not rst.EOF

    lngCurrentRecord = lngCurrentRecord + 1

    If lngCurrentRecord Mod lngBatchSize = 1 Then

      ' Do we have enough records left?

      If lngRecordCount - lngCurrentRecord >= lngBatchSize \ 2 _

          Or lngCurrentRecord = 1 Then

        ' Create new territory

        lngTerritoryNumber = lngTerritoryNumber + 1

        rst2.AddNew

        rst2!TerritoryNumber = lngTerritoryNumber

        rst2!TerritoryTypeID = strTerritoryTypeID

        rst2!CityID = strCityID

        rst2!CongregationID = strCongregationID

        rst2!EnteredBy = strUserID

        rst2!TerritoryName = strTerritoryName

        ' Remember new ID

        lngNewTerritoryID = rst2!TerritoryID

        rst2.Update

        ' Close previous text file

        If lngCurrentRecord > 1 Then

          Close #f

        End If

        ' Open new text file

        Open strPath & "Territory" & lngNewTerritoryID & ".txt" For Output As #f

      End If

    End If

    ' Assign territory

    rst.Edit

    rst!TerritoryID = lngNewTerritoryID

    rst.Update

    ' Build line for text file

    strLine = rst!SurveyID & strDelimiter & Chr(34) & rst!FullName & Chr(34) & _

      strDelimiter & Chr(34) & rst!StNumber & " "

    If rst!Address2 = "1/2" Then

      strLine = strLine & rst!Address2 & " "

    End If

    strLine = strLine & rst!StName & Chr(34) & strDelimiter & Chr(34)

    Select Case True

      Case rst![Apt?]

        strLine = strLine & "Apt. "

      Case rst![Room?]

        strLine = strLine & "Room "

      Case rst![Studio?]

        strLine = strLine & "Studio "

      Case rst![Suite?]

        strLine = strLine & "Suite "

      Case rst![Unit?]

        strLine = strLine & "Unit "

      Case rst![Space?]

        strLine = strLine & "Space "

      Case rst![Lot?]

        strLine = strLine & "Lot "

      Case rst![Bldg?]

        strLine = strLine & "Bldg "

      Case rst![Floor?]

        strLine = strLine & "Floor "

    End Select

    strLine = strLine & rst!Address2 & Chr(34) & strDelimiter & _

      Chr(34) & DLookup("City", "tblCity", "CityID=" & strCityID) & Chr(34) & _

      strDelimiter & rst!Zip & strDelimiter & lngNewTerritoryID

    ' Write line to file

    Print #f, strLine

    ' On to the next record

    rst.MoveNext

  Loop

ExitHandler:

  On Error Resume Next

  Close #f

  rst.Close

  rst2.Close

  Set dbs = Nothing

  Me.Requery

  Exit Sub

ErrHandler:

  MsgBox Err.Description, vbExclamation

  Resume ExitHandler

End Sub

Was this answer helpful?

0 comments

41 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2010-11-15T07:49:08+00:00

    I just had a brainstorm. I can just put a new button on the frmTerritoryList.  This new button will be AssignTerritory. I'd like to put it on the detail of the form and if the user clicks it I want it to take the territoryID and territorytypeID of the record that is on the same line where they clicked and assign the top 30 records to it.  I don't know how to tell the button to use the specificID of the current line. There is already a dblclick procedure on this same page.

    My preference of course if a more automated solution but I will take what I can get.  :)

    Here is the code as it stands now.  I started trying to modify it since I don't want user input in the form of text - I was thinking combo box but now all I want them to do is click the record and/or click the button and have access assign that id number to the 30 records it selects. I got lost somewhere in there. I don't think we need to declare all of these variables now since many of them will come from the current record right?

    Private Sub cmdAssignSurvey_Click()

      Dim lngBatchSize As Long

      Dim strCity As String 

      Dim lngOldTerritoryID As Long

      Dim lngNewTerritoryID As Long

      Dim lngTerritoryTypeID As Long (this should come from current row)

      Dim dbs As DAO.Database

      Dim rst As DAO.Recordset

      Dim n As Long

      Dim lngRecordCount As Long

      Dim strSQL1 As String

      Dim strSQL2 As String

      ' Fixed parameters

      lngBatchSize = 30

      lngOldTerritoryID = 106

      lngTerritoryTypeID = 3

      ' Variable parameters

      ' strCity = InputBox("Enter the city") the cbo that calls frmTerritoryList gets the cityid which I can use to get city name prior to export

      Set dbs = CurrentDb

      strSQL1 = "SELECT Count(*) AS RecCount FROM tblSurveyData " & _

        "WHERE tblSurveyData.CityID=" & strCityID & " AND tblSurveyData.TerritoryID=" & _

        lngOldTerritoryID & " AND tblSurveyData.TerritoryTypeID=" & lngTerritoryTypeID

      ' Compute record count

      Set rst = dbs.OpenRecordset(strSQL1, dbOpenDynaset)

      rst.MoveLast

      rst.MoveFirst

      lngRecordCount = rst.RecordCount

      rst.Close

      Do While lngRecordCount > 0

        n = n + 1

        ' Ask for new territory ID

        ' lngNewTerritoryID = CLng(InputBox("Enter the new territory ID"))(this should come from current row)

        ' Build INSERT SQL

        strSQL2 = "SELECT TOP " & lngBatchSize & " tblSurveyData.SurveyID, tblSurveyData.FullName, " & _

          "IIf(tblSurveyData.Address2='1/2',[StNumber] & ' ' & [Address2] & " & _

          "' ' & tblSurveyData.StName,[tblSurveyData].[StNumber] & ' ' & " & _

          "[tblSurveyData].[StName]) AS Address, IIf([tblSurveyData].[Apt?]=-1," & _

          "'Apt. ',IIf([tblSurveyData].[Room?]=-1,'Room ',IIf([tblSurveyData].[Studio?]=-1," & _

          "'Studio ',IIf([tblSurveyData].[Suite?]=-1,'Suite ',IIf([tblSurveyData].[Unit?]=-1," & _

          "'Unit ',IIf([tblSurveyData].[Space?]=-1,'Space ',IIf([tblSurveyData].[Lot?]=-1," & _

          "'Lot ',IIf([tblSurveyData].[Bldg?]=-1,'Bldg ',IIf([tblSurveyData].[Floor?]=-1," & _

          "'Floor ',Null))))))))) & [Address2] AS Addr2, tblCity.City, tblSurveyData.Zip, " & _

          lngNewTerritoryID & " AS TerritoryID INTO EXPStreetsandTrips " & _

          "FROM tblSurveyData LEFT JOIN tblCity ON tblSurveyData.CityID = tblCity.CityID " & _

          "WHERE tblSurveyData.CityID=" & strCityID & " AND tblSurveyData.TerritoryID=" & _

          lngOldTerritoryID & " AND tblSurveyData.TerritoryTypeID=" & lngTerritoryTypeID & _

          " ORDER BY tblSurveyData.Zip, tblSurveyData.Latitude"

        ' Fill table

        dbs.Execute strSQL2

        ' Export to text file

        DoCmd.TransferText acExportDelim, , "EXPStreetsandTrips", "Export" & n, True

        ' Built UPDATE SQL

        strSQL2 = "UPDATE tblSurveyData INNER JOIN EXPStreetsandTrips ON " & _

          "tblSurveyData.SurveyID=EXPStreetsandTrips.SurveyID SET tblSurveyData.TerritoryID=" & _

          lngNewTerritoryID & " WHERE tblSurveyData.TerritoryID=" & lngOldTerritoryID & _

          " AND tblSurveyData.SurveyID=EXPStreetsandTrips.SurveyID AND tblSurveyData.TerritoryTypeID=" & _

          lngTerritoryTypeID

        ' Update table

        dbs.Execute strSQL2

        ' Get record count for next round

        Set rst = dbs.OpenRecordset(strSQL1, dbOpenDynaset)

        rst.MoveLast

        rst.MoveFirst

        lngRecordCount = rst.RecordCount

        rst.Close

      Loop (not sure if looping is necessary at this point if the user is manually selecting which territory to assign)

      Set dbs = Nothing

    End Sub

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-11-15T07:25:58+00:00

    Hi Hans. I don't actually want to have the user have to type in hundreds of territoryID numbers one at a time. I was hoping to take the batch create procedure (where we ask the user how many records they want to create) and merge it with this one so that Access would create a new territory record, assign these 30 records to it, create another one and continue this process until Access reaches the last new territory that was created even if there are still records waiting to be assigned. The user can manually assign these records to the last territory because in some cases there is only 1 record left and it wouldn't make sense to have it on a territory all by itself, etc.  This is the way the code reads currently:

        ' Ask for new territory ID

        lngNewTerritoryID = CLng(InputBox("Enter the new territory ID"))

    If merging the two functions into one is too much to think about I can make it easier on the user if I can:

    • still get the above input but with a combo box type of prompt allowing users to select available territory IDs from a list ... without leaving the calling form, or at least if I have to call another form to automatically return to the calling form without losing the variable values. BTW, frmTerritoryList contains all the button Add, BulkAdd, BatchAssign, etc. so the user has already chosen to see the list of territories.

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2010-11-14T20:03:08+00:00

    Very simple: either use its name, or the keyword Call followed by its name:

    Sub Greeting()

        MsgBox "Hello World"

    End Sub

    Sub Test1()

        Greeting

    End Sub

    Sub Test2()

        Call Greeting

    End Sub

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-11-14T19:58:20+00:00

    Thanks so much.  I will test and get back to you.  Question: What is the syntax for calling a procedure from within a procedure?

    Was this answer helpful?

    0 comments