Question 2, splitting and numbering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
 
As written, the code will not run.

I used your example and modified the code. If the field names are not the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3" (without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown, then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
 
Thanks Steve. What was weird was that her original code DID run but when I
tried to integrate it into my program, it didn't. When I ran hers, it
created a query and numbered the counties. When I tried to use that
information, it didn't work. I'm going to try your information tomorrow
morning.

SteveS said:
As written, the code will not run.

I used your example and modified the code. If the field names are not the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3" (without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown, then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
Click to expand...
 
Steve, I got a new problem.

I ran her program again to show her what was going on. We got into a tiss.

I realized that I still don't have a way to sort the information before the
numbers are assigned. How do I program the code to first Sort by County,
then by Last Name, then by first name before running your code?

SteveS said:
As written, the code will not run.

I used your example and modified the code. If the field names are not the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3" (without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown, then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
Click to expand...
 
When you open a recordset using these two lines (already in the code I
provided),

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

the recordset is opened in sorted order.

Then the rest of the code loops thru the recordset and assigns the numbers.

Is that what you are looking for??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Steve, I got a new problem.

I ran her program again to show her what was going on. We got into a tiss.

I realized that I still don't have a way to sort the information before the
numbers are assigned. How do I program the code to first Sort by County,
then by Last Name, then by first name before running your code?

SteveS said:
As written, the code will not run.

I used your example and modified the code. If the field names are not the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3" (without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown, then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
Click to expand...
Click to expand...
 
Steve, I stuck your information in and I think it almost worked. I got an
error that says: User defined type not defined and highlighted

Dim db As DAO.Database

What would cause that error?

When I rem'd the statement the same thing happend with

Dim rst As DAO.Recordset

Ok…here is what I have so far:

The program is modular (ie the commands are used for more than just one
button) and this is what I have wrt this part:



Option Compare Database

Private Sub All_In_One_55_Click()
Delete_Current_55
Import_55
AssignInternalControl
End Sub


Private Sub Delete_Current_55
DoCmd.DeleteObject acTable, "Current55"
End Sub


Private Sub Import_55
DoCmd.TransferText acImportFixed, "55 Import Specs", "Current55", "P:\(OCO)
55, 56, 59, SAIF Lists\Current 55, 59\55.txt", False, "", 0
End Sub


Private Sub AssignInternalControl()

‘delete the old table

DoCmd.DeleteObject acTable, "117Prep"

‘Make Table Query to create 117Prep Table with County Name, County Number,
61 Months, 55 Months, Last Name, First Name, Case Number

DoCmd.OpenQuery "AddInternalControlField"

‘Start Steve’s code

Private Sub AssignInternalControl_Click()

'create the Table
'DoCmd.DeleteObject acTable, "117Prep"
'DoCmd.OpenQuery "AddInternalControlField"

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb

strSQL = "select * from 117Prep"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!Int = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![Int] = vcounter
Else
'start over at 1
!Int = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub


SteveS said:
When you open a recordset using these two lines (already in the code I
provided),

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

the recordset is opened in sorted order.

Then the rest of the code loops thru the recordset and assigns the numbers.

Is that what you are looking for??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Steve, I got a new problem.

I ran her program again to show her what was going on. We got into a tiss.

I realized that I still don't have a way to sort the information before the
numbers are assigned. How do I program the code to first Sort by County,
then by Last Name, then by first name before running your code?

SteveS said:
As written, the code will not run.

I used your example and modified the code. If the field names are not the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3" (without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown, then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of their
information.

Every month I build a mail merge between excel and word to print out a form
with certain client's infomation on it. I have an excel sheet looking to the
database's query, and then after I refresh the excel query (refreshing the
query's query...:]) I hand input the individual internal control number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at the
touch of a button. Only one problem: I can't figure out how to renumber the
internal control numbers. We get a couple of hundred cases which need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name, County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could business
create new numbers for their clients.

I had theorized that I could query each county's case and then number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county, and then
ran an update query. This unfortunately updated the original table and not
the new ones. Finally my mentor, before she went on vacation gave me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55", dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
Click to expand...
Click to expand...
 
That implies that you don't have a reference set to DAO. This would be the
case if you're using Access 2000 or Access 2002: neither of those two
versions of Access have the reference set by default.

While in the VB Editor, select Tools | References. Scroll through the list
of available references until you find the one for Microsoft.DAO 3.6 Object
Library and select it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Altair1972m said:
Steve, I stuck your information in and I think it almost worked. I got an
error that says: User defined type not defined and highlighted

Dim db As DAO.Database

What would cause that error?

When I rem'd the statement the same thing happend with

Dim rst As DAO.Recordset

Ok.here is what I have so far:

The program is modular (ie the commands are used for more than just one
button) and this is what I have wrt this part:



Option Compare Database

Private Sub All_In_One_55_Click()
Delete_Current_55
Import_55
AssignInternalControl
End Sub


Private Sub Delete_Current_55
DoCmd.DeleteObject acTable, "Current55"
End Sub


Private Sub Import_55
DoCmd.TransferText acImportFixed, "55 Import Specs", "Current55",
"P:\(OCO)
55, 56, 59, SAIF Lists\Current 55, 59\55.txt", False, "", 0
End Sub


Private Sub AssignInternalControl()

'delete the old table

DoCmd.DeleteObject acTable, "117Prep"

'Make Table Query to create 117Prep Table with County Name, County Number,
61 Months, 55 Months, Last Name, First Name, Case Number

DoCmd.OpenQuery "AddInternalControlField"

'Start Steve's code

Private Sub AssignInternalControl_Click()

'create the Table
'DoCmd.DeleteObject acTable, "117Prep"
'DoCmd.OpenQuery "AddInternalControlField"

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb

strSQL = "select * from 117Prep"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!Int = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![Int] = vcounter
Else
'start over at 1
!Int = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub


SteveS said:
When you open a recordset using these two lines (already in the code I
provided),

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First Name];"

the recordset is opened in sorted order.

Then the rest of the code loops thru the recordset and assigns the
numbers.

Is that what you are looking for??

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Altair1972m said:
Steve, I got a new problem.

I ran her program again to show her what was going on. We got into a
tiss.

I realized that I still don't have a way to sort the information before
the
numbers are assigned. How do I program the code to first Sort by
County,
then by Last Name, then by first name before running your code?

:

As written, the code will not run.

I used your example and modified the code. If the field names are not
the
same you should be able to see where to make the changes

On a form, you need to have a command button named "Command3"
(without the
quotes). On the EVENTS tab, select "Event Procedure" in the dropdown,
then
click on the three dots (...). This is where the code goes.

'---Begin code------------
Private Sub Command3_Click()

'create variables
Dim vcountyHold As String
Dim vcounter As Integer
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

strSQL = "select * from CurrentMonth55"
strSQL = strSQL & " ORDER BY [County Name], [Last Name], [First
Name];"

'open recordset
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'check to make sure there are records
If rst.BOF And rst.EOF Then
MsgBox "No records"
'no records so clean up and exit
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End If

With rst
.MoveFirst

'handle first record

'init variables
vcounter = 1
vcountyHold = ![County Name]

.Edit
!countyRecNum = 1
.Update

'next record in recordset
.MoveNext

'handle subsequent records
Do While Not .EOF

.Edit
If vcountyHold = ![County Name] Then
'inc counter
vcounter = vcounter + 1
![countyRecNum] = vcounter
Else
'start over at 1
!countyRecNum = 1

'reset counter
vcounter = 1
vcountyHold = ![County Name]
End If
.Update
.MoveNext
Loop
End With


MsgBox "done"

'clean up
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
'---End code-------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Access Nubie again asking 2nd question:

I have a database with a bunch of clients by State and a bunch of
their
information.

Every month I build a mail merge between excel and word to print
out a form
with certain client's infomation on it. I have an excel sheet
looking to the
database's query, and then after I refresh the excel query
(refreshing the
query's query...:]) I hand input the individual internal control
number, by
county, for the clients before I run the mail merge.

I decided to build a form in Access to spit out this information at
the
touch of a button. Only one problem: I can't figure out how to
renumber the
internal control numbers. We get a couple of hundred cases which
need to be
numbered. Example:

Fields: [Internal control number to insert] Last Name, First Name,
County
Name, Case Number, County Code

1, Earl, Frank, Essex, C000111, 007
2, Kidd, Tasha, Essex, C222222, 007
1, Arius, Gregg, Monmouth County, C111000, 013
1, Black, Jack, Ocean, C151515, 015
2, Doe, John, Ocean, C202020, 015


I absolutly KNOW Access has this capablility otherwise how could
business
create new numbers for their clients.

I had theorized that I could query each county's case and then
number them
but alas, I can't find a numbering function.

Then I tried to make a table to do it in, but the autonumber
function only
works if you specify it at creation.

So I tried to create a table with the autonumber, for each county,
and then
ran an update query. This unfortunately updated the original table
and not
the new ones. Finally my mentor, before she went on vacation gave
me this
and I can't understand how to integrate it in my code:

Option Compare Database
Option Explicit
Dim countyHold As String
Dim counter As Integer
Dim updCtr As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim recs As Double

Private Sub Command3_Click()

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from CurrentMonth55",
dbOpenDynaset)

If RecordsetClone.RecordCount > 0 Then
MsgBox " rec count is " & RecordsetClone.RecordCount
recs = RecordsetClone.RecordCount
End If

rst.MoveFirst

'handle first record

counter = 1

[countyRecNum] = counter

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

'handle subsequent records

Do Until updCtr = recs
updCtr = updCtr + 1

If countyHold = [County] Then
counter = counter + 1
[countyRecNum] = counter
End If

If Not countyHold = [County] Then
counter = 1
[countyRecNum] = counter
End If

If updCtr = recs Then
MsgBox "done"
DoCmd.Close
Exit Sub
End If

countyHold = [County]

DoCmd.GoToRecord , , acNext, 1

Loop

End Sub

Attack of the clones. Can someone simplify this?
Click to expand...
Click to expand...
Click to expand...
 
Thank you Douglas.

Now it froze at the first.

!Int = 1

_____________________
It mutht be a black eye cauthed by a thtampede of wild elephanths, in the
confineth of your own home, between the hourths of 3:55 and 4:00 PM, on the
fourthh of July, during a hail-thtorm.
-Daffy Duck
 
It would help to keep the code you're talking about in your post, to save me
the trouble of finding your previous posts...

When you say it froze, are you getting any error?

Actually, I suspect Int is a reserved word (since there's a Int function in
VBA). If you can't rename the field, at least put square brackets around it
(as you have in at least one other reference to that field)
 
Douglas J. Steele said:
It would help to keep the code you're talking about in your post, to save me
the trouble of finding your previous posts...

When you say it froze, are you getting any error?

Actually, I suspect Int is a reserved word (since there's a Int function in
VBA). If you can't rename the field, at least put square brackets around it
(as you have in at least one other reference to that field)
Click to expand...
 
To either Doug or Steve:

Do you guys play
X-Wing Alliance
Star Wars Galactic Battle Grounds
Alpha Centari
or
Dungeon Siege
 
I got it a long time ago from my Aikido instructor. I have since seen it a
couple of times browsing the 'net.


There are three types of people in this world..........
The people that can count.... and the people that can't.


:)

--
Steve S
--------------------------------
Artificial intelligence is no
match for my Natural Stupidity
 
Back
Top