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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-11-15T16:13:17+00:00

    Hi Hans. I only changed it because the code you gave me would be great if the user was going to type the new number in each time. I don't really want them to do that.  What I really want to do is to merge the batch create code with the assign territory code and have access do all the work. I would even like the code to determine how many such territories are needed instead of the user having to type in that number.

    What I offer above is a compromise in the event that I am asking you for too much. If you can help me to merge the two sets of code (batchcreate and batchassign) that is what I am really trying to do. Scott's information in helpful and I will try to chunk the code once I get it working. I don't even know how and where to put things so I'm learning best examining your examples and then modifying them to do what I need.

    My goal is:

    • User chooses city and territorytype from cbo on a form, then clicks button OK which open frmTerritoryList.This functionality is working.
    • User clicks cmdAssignRecords and Access calculates how many records there are and creates the number of territories needed to assign 30 records to each.The underlying query to determine how many unassigned records exist for the city and territorytype exists but I don't know how to call a query in code without retyping it in VBA with all of the quotes and things.
    • When remainder of unassigned records is between 1 and 15 Access assigns these orphans to the previous territory number. It the remainder >= 15 but < 30 Access assigns to it's own territory.The number of territories needed would have to be determined during the first calculation.
    • Calling form is refreshed showing newly created territories and how many assigned to each. I already have the calling form showing number of records assigned to each territory.  I will probably add txtbox showing number of unassigned records for the city and territorytype.

    Sorry for the confusion. Since you helped me with these two procedures I figure you are in the best position to help me put them together.  At least that is what I am hoping.  And by the way, not that it matters, I am doing this project as a volunteer for a community service organization.

    Was this answer helpful?

    0 comments
  2. ScottGem 68,845 Reputation points Volunteer Moderator
    2010-11-15T13:27:46+00:00

    I'm not going to deal with the actual code here, but with some concepts that may help you build the functionality you want. First is the issue of modularity. There is a concept in programming called spaghetti code. This is where you have lots of lines of code that loop back into each other making it difficult to follow and debug. You are better off creating smaller procs and functions that can be called from a master procedure. It doesn't matter to the user, the user just sees one button to kick off the process. So my point is to not worry about how many queries, functions or procedures you need.

    Another point on creating dynamic names. This is very easy because you are concatenating using a variable. For example:

    strFilename = "Export" & variable & ".txt"

    Another point on how to deal with the remainder. You can do something like this:

    IF INT(remaining records /30 ) >1 Then

       Recordcout = 30

    Else

      recordcount = remaining records

    End If

    So my advice is to break your code up into smaller chunks that perform one piece of functionality, get thant working and build on that.

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.spaces.live.com/blog Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2010-11-15T08:18:22+00:00

    If you are going to use the current TerritoryID, I don't understand your setup any more. I thought the whole point was to change the TerritoryID in batches of 30 - that's what the UPDATE query does.

    Was this answer helpful?

    0 comments
  4. 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