sequential numbers added to null fields

This is a strange one. Would anyone know of a strategy for querying a
table, looking for nulls in a particular field, then filling those
with a sequential number starting at the one after the last number.
e.g.:

Mr. Cohen 03/15/56 13244355
Mr. Rocco 07/15/75 13244356
Mr. Rice 09/01/61 13244357
Mr. Peterson 02/10/78 13244358
Mr. Holister 06/20/64 13244359
Mr. Drake 05/31/65
Mr. Kellspan 01/12/71

Thus how to (in a long series of records), take the two shown with a
null value and update with starting with 13244360, then 13244361, etc?
The number BTW is not the key field.

Thanks! Dave

Re: sequential numbers added to null fields

"slinky" wrote in message
news:7beef17b-bf5e-469f-b9c2-2b1c1387e9f3@1g2000hsl.googlegr oups.com...
> This is a strange one. Would anyone know of a strategy for querying a
> table, looking for nulls in a particular field, then filling those
> with a sequential number starting at the one after the last number.
> e.g.:
>
> Mr. Cohen 03/15/56 13244355
> Mr. Rocco 07/15/75 13244356
> Mr. Rice 09/01/61 13244357
> Mr. Peterson 02/10/78 13244358
> Mr. Holister 06/20/64 13244359
> Mr. Drake 05/31/65
> Mr. Kellspan 01/12/71
>
> Thus how to (in a long series of records), take the two shown with a
> null value and update with starting with 13244360, then 13244361, etc?
> The number BTW is not the key field.
>
> Thanks! Dave

First look up the maximum number already used:

lngMaxNumber = DMax("NumberField", "TableName")

Then it's quite easy using DAO:

strSQL = "Select NameField, DateField, NumberField From" & _
" TableName Where NumberField Is Null" & _
" Order By NumberField"

With DAO.OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF

'Update with the current number
.Edit
!NumberField = lngMaxNumber
.Update

'Increment the number for next time
lngMaxNumber = lngMaxNumber + 1

'Move to the next record
.NoveNext

Loop
.Close
End With

Re: sequential numbers added to null fields

slinky wrote in news:7beef17b-bf5e-469f-
b9c2-2b1c1387e9f3@1g2000hsl.googlegroups.com:

> This is a strange one. Would anyone know of a strategy for querying a
> table, looking for nulls in a particular field, then filling those
> with a sequential number starting at the one after the last number.
> e.g.:
>
> Mr. Cohen 03/15/56 13244355
> Mr. Rocco 07/15/75 13244356
> Mr. Rice 09/01/61 13244357
> Mr. Peterson 02/10/78 13244358
> Mr. Holister 06/20/64 13244359
> Mr. Drake 05/31/65
> Mr. Kellspan 01/12/71
>
> Thus how to (in a long series of records), take the two shown with a
> null value and update with starting with 13244360, then 13244361, etc?
> The number BTW is not the key field.
>
> Thanks! Dave

Sub BeamMeUpScotty()
Dim LeastNumber As Long
If MsgBox("You're running this on a copy, RIGHT?", vbYesNo) = vbYes Then
If SysCmd(acSysCmdGetObjectState, acTable, "Table1") And acObjStateOpen =
acObjStateOpen Then
DoCmd.Close acTable, "Table1", acSavePrompt
End If
With CurrentProject.Connection
'.Execute "ALTER TABLE Table1 DROP Temp"
LeastNumber = .Execute("SELECT Min([Number]) FROM Table1 WHERE [NUMBER]
IS NOT NULL")(0)
..Execute "ALTER TABLE Table1 ADD COLUMN Temp IDENTITY (0, 1)"
..Execute "UPDATE Table1 SET [Number] = [Temp] + " & LeastNumber
..Execute "ALTER TABLE Table1 DROP Temp"
End With
End If
End Sub

name number
Mr. Cohen 13244356
Mr. Rocco 13244357
Mr. Rice 13244358
Mr. Peterson 13244359
Mr. Holister 13244360
Mr. Drake 13244361
Mr. Kellspan 13244362

Re: sequential numbers added to null fields

Thanks... I'm thinking of placing all this into a module. Advisable?
Also can I use a existing query as my datasource for the "strSQL =3D
"Select NameField, DateField, NumberField From TableName Where
NumberField Is Null Order By NumberField" instead of using a table
name?
Thanks! Dave

> First look up the maximum number already used:
>
> lngMaxNumber =3D DMax("NumberField", "TableName")
>
> Then it's quite easy using DAO:
>
> strSQL =3D "Select NameField, DateField, NumberField From" & _
> =A0 " TableName Where NumberField Is Null" & _
> =A0 " Order By NumberField"
>
> With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> =A0 =A0 Do Until .EOF
>
> =A0 =A0 =A0 =A0 'Update with the current number
> =A0 =A0 =A0 =A0 .Edit
> =A0 =A0 =A0 =A0 !NumberField =3D lngMaxNumber
> =A0 =A0 =A0 =A0 .Update
>
> =A0 =A0 =A0 =A0 'Increment the number for next time
> =A0 =A0 =A0 =A0 lngMaxNumber =3D lngMaxNumber + 1
>
> =A0 =A0 =A0 =A0 'Move to the next record
> =A0 =A0 =A0 =A0 .NoveNext
>
> =A0 =A0 Loop
> =A0 =A0 .Close
> End With- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

Comments inline:
> "slinky" wrote in message
> news:53e90a12-ba9b-> 4b2-8926-18a1e98ae260@i29g2000prf.googlegroups.com...
> Thanks... I'm thinking of placing all this into a module. Advisable?

If the code is to be called from more than one form, then yes a module is
the best place. If however it will only be called from one form, the class
module for the form would be better. IOW modules are best used for shared
code.

> Also can I use a existing query as my datasource for the "strSQL =
> "Select NameField, DateField, NumberField From TableName Where
> NumberField Is Null Order By NumberField" instead of using a table
> name?

Sure. Just set up your query then select from that:

strSQL = "Select * From MyQuery"

> First look up the maximum number already used:
>
> lngMaxNumber = DMax("NumberField", "TableName")
>
> Then it's quite easy using DAO:
>
> strSQL = "Select NameField, DateField, NumberField From" & _
> " TableName Where NumberField Is Null" & _
> " Order By NumberField"
>
> With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> Do Until .EOF
>
> 'Update with the current number
> .Edit
> !NumberField = lngMaxNumber
> .Update
>
> 'Increment the number for next time
> lngMaxNumber = lngMaxNumber + 1
>
> 'Move to the next record
> .NoveNext
>
> Loop
> .Close
> End With- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

I see the logic of your solution but I may have some misstakes on my
side; I gave it a try placing the below code into a form with a button
but I got errors with both the With ".OpenRecordset" and ".EOF" parts:

Option Compare Database

Private Sub Command1_Click()

On Error GoTo Err_Command1_Click

lngMaxNumber =3D DMax("CCTR1", "CC Conversion 12807")

strSQL =3D "Select AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit], CCTR1,
[SAP Account], JAN07 From" & _
" [Historical with Cross Reference] Where CCTR1 Is Null" & _
" Order By CCTR1"

With DAO.OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF
.Edit
!CCTR1 =3D lngMaxNumber
.Update
lngMaxNumber =3D lngMaxNumber + 1
.NoveNext
Loop
.Close
End With

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Thanks!!

Dave



On Jan 9, 1:12=A0pm, "Stuart McCall" wrote:
> Comments inline:
>
> > "slinky" wrote in message
> > news:53e90a12-ba9b-> 4b2-8926-18a1e98ae...@i29g2000prf.googlegroups.com.=
...
> > Thanks... I'm thinking of placing all this into a module. Advisable?
>
> If the code is to be called from more than one form, then yes a module is
> the best place. If however it will only be called from one form, the class=

> module for the form would be better. IOW modules are best used for shared
> code.
>
> > Also can I use a existing query as my datasource for the "strSQL =3D
> > "Select NameField, DateField, NumberField From TableName Where
> > NumberField Is Null Order By NumberField" instead of using a table
> > name?
>
> Sure. Just set up your query then select from that:
>
> strSQL =3D "Select * From MyQuery"
>
>
>
> > First look up the maximum number already used:
>
> > lngMaxNumber =3D DMax("NumberField", "TableName")
>
> > Then it's quite easy using DAO:
>
> > strSQL =3D "Select NameField, DateField, NumberField From" & _
> > " TableName Where NumberField Is Null" & _
> > " Order By NumberField"
>
> > With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> > Do Until .EOF
>
> > 'Update with the current number
> > .Edit
> > !NumberField =3D lngMaxNumber
> > .Update
>
> > 'Increment the number for next time
> > lngMaxNumber =3D lngMaxNumber + 1
>
> > 'Move to the next record
> > .NoveNext
>
> > Loop
> > .Close
> > End With- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

"slinky" wrote in message
news:fa8b5cb2-61f1-4970-820a-ad025b949c3a@e25g2000prg.google groups.com...
> I see the logic of your solution but I may have some misstakes on my
> side; I gave it a try placing the below code into a form with a button
> but I got errors with both the With ".OpenRecordset" and ".EOF" parts:

That sounds like you have no reference to the DAO object library set. In the
VBE, drop the menu Tools and select References. Find 'Microsoft DAO 3.6
Object Library' and give it a check mark.

Also, I just spotted a typo of mine in the below code. NoveNext ought to be
MoveNext.

Option Compare Database

Private Sub Command1_Click()

On Error GoTo Err_Command1_Click

lngMaxNumber = DMax("CCTR1", "CC Conversion 12807")

strSQL = "Select AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit], CCTR1,
[SAP Account], JAN07 From" & _
" [Historical with Cross Reference] Where CCTR1 Is Null" & _
" Order By CCTR1"

With DAO.OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF
.Edit
!CCTR1 = lngMaxNumber
.Update
lngMaxNumber = lngMaxNumber + 1
.NoveNext
Loop
.Close
End With

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

Thanks!!

Dave



On Jan 9, 1:12 pm, "Stuart McCall" wrote:
> Comments inline:
>
> > "slinky" wrote in message
> > news:53e90a12-ba9b->
> > 4b2-8926-18a1e98ae...@i29g2000prf.googlegroups.com...
> > Thanks... I'm thinking of placing all this into a module. Advisable?
>
> If the code is to be called from more than one form, then yes a module is
> the best place. If however it will only be called from one form, the class
> module for the form would be better. IOW modules are best used for shared
> code.
>
> > Also can I use a existing query as my datasource for the "strSQL =
> > "Select NameField, DateField, NumberField From TableName Where
> > NumberField Is Null Order By NumberField" instead of using a table
> > name?
>
> Sure. Just set up your query then select from that:
>
> strSQL = "Select * From MyQuery"
>
>
>
> > First look up the maximum number already used:
>
> > lngMaxNumber = DMax("NumberField", "TableName")
>
> > Then it's quite easy using DAO:
>
> > strSQL = "Select NameField, DateField, NumberField From" & _
> > " TableName Where NumberField Is Null" & _
> > " Order By NumberField"
>
> > With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> > Do Until .EOF
>
> > 'Update with the current number
> > .Edit
> > !NumberField = lngMaxNumber
> > .Update
>
> > 'Increment the number for next time
> > lngMaxNumber = lngMaxNumber + 1
>
> > 'Move to the next record
> > .NoveNext
>
> > Loop
> > .Close
> > End With- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

I made the DAO 3.6 reference which helped, but my code (exact code
below) does not seem to do anything (?)... any ideas?

Option Compare Database

Private Sub btnInsertCCs_Click()

On Error GoTo Err_Command1_Click

Dim lngMaxNumber As Number

lngMaxNumber =3D DMax("CCTR1", "Historical with Cross Reference - A")

strSQL =3D "Select ID, AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit],
CCTR1, [SAP Account], JAN07 From" & _
"[Historical with Cross Reference - A] Where CCTR1 Is Null" & _
"Order By CCTR1"

With DAO.OpenRecordset(strSQL, dbOpenDynaset)
Do Until .EOF
.Edit
!CCTR1 =3D lngMaxNumber
.Update
lngMaxNumber =3D lngMaxNumber + 1
.MoveNext
Loop
.Close
End With

Exit_btnInsertCCs_Click:
Exit Sub

Err_btnInsertCCs_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub


On Jan 9, 4:49=A0pm, "Stuart McCall" wrote:
> "slinky" wrote in message
>
> news:fa8b5cb2-61f1-4970-820a-ad025b949c3a@e25g2000prg.google groups.com...
>
> > I see the logic of your solution but I may have some misstakes on my
> > side; I gave it a try placing the below code into a form with a button
> > but I got errors with both the With ".OpenRecordset" and ".EOF" parts:
>
> That sounds like you have no reference to the DAO object library set. In t=
he
> VBE, drop the menu Tools and select References. Find 'Microsoft DAO 3.6
> Object Library' and give it a check mark.
>
> Also, I just spotted a typo of mine in the below code. NoveNext ought to b=
e
> MoveNext.
>
> Option Compare Database
>
> Private Sub Command1_Click()
>
> On Error GoTo Err_Command1_Click
>
> lngMaxNumber =3D DMax("CCTR1", "CC Conversion 12807")
>
> strSQL =3D "Select AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit], CCTR1,
> [SAP Account], JAN07 From" & _
> =A0 " [Historical with Cross Reference] Where CCTR1 Is Null" & _
> =A0 " Order By CCTR1"
>
> With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> =A0 =A0 Do Until .EOF
> =A0 =A0 =A0 =A0 .Edit
> =A0 =A0 =A0 =A0 !CCTR1 =3D lngMaxNumber
> =A0 =A0 =A0 =A0 .Update
> =A0 =A0 =A0 =A0 lngMaxNumber =3D lngMaxNumber + 1
> =A0 =A0 =A0 =A0 .NoveNext
> =A0 =A0 Loop
> =A0 =A0 .Close
> End With
>
> Exit_Command1_Click:
> =A0 =A0 Exit Sub
>
> Err_Command1_Click:
> =A0 =A0 MsgBox Err.Description
> =A0 =A0 Resume Exit_Command1_Click
>
> End Sub
>
> Thanks!!
>
> Dave
>
> On Jan 9, 1:12 pm, "Stuart McCall" wrote:
>
>
>
> > Comments inline:
>
> > > "slinky" wrote in message
> > > news:53e90a12-ba9b->
> > > 4b2-8926-18a1e98ae...@i29g2000prf.googlegroups.com...
> > > Thanks... I'm thinking of placing all this into a module. Advisable?
>
> > If the code is to be called from more than one form, then yes a module i=
s
> > the best place. If however it will only be called from one form, the cla=
ss
> > module for the form would be better. IOW modules are best used for share=
d
> > code.
>
> > > Also can I use a existing query as my datasource for the "strSQL =3D
> > > "Select NameField, DateField, NumberField From TableName Where
> > > NumberField Is Null Order By NumberField" instead of using a table
> > > name?
>
> > Sure. Just set up your query then select from that:
>
> > strSQL =3D "Select * From MyQuery"
>
> > > First look up the maximum number already used:
>
> > > lngMaxNumber =3D DMax("NumberField", "TableName")
>
> > > Then it's quite easy using DAO:
>
> > > strSQL =3D "Select NameField, DateField, NumberField From" & _
> > > " TableName Where NumberField Is Null" & _
> > > " Order By NumberField"
>
> > > With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> > > Do Until .EOF
>
> > > 'Update with the current number
> > > .Edit
> > > !NumberField =3D lngMaxNumber
> > > .Update
>
> > > 'Increment the number for next time
> > > lngMaxNumber =3D lngMaxNumber + 1
>
> > > 'Move to the next record
> > > .NoveNext
>
> > > Loop
> > > .Close
> > > End With- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

Comments inline:
"slinky" wrote in message
news:21ddb949-d3fd-4580-b3ec-3adf1a7e731c@h11g2000prf.google groups.com...
> I made the DAO 3.6 reference which helped, but my code (exact code
> below) does not seem to do anything (?)... any ideas?

> Option Compare Database
>
> Private Sub btnInsertCCs_Click()
>
> On Error GoTo Err_Command1_Click
>
> Dim lngMaxNumber As Number

This should read: Dim lngMaxNumber As Long

> lngMaxNumber = DMax("CCTR1", "Historical with Cross Reference - A")
>
> strSQL = "Select ID, AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit],
> CCTR1, [SAP Account], JAN07 From" & _
> "[Historical with Cross Reference - A] Where CCTR1 Is Null" & _
> "Order By CCTR1"

There are 2 problems with your sql string. You need a space following
'From', and a space before 'Order By'.

> With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> Do Until .EOF
> .Edit
> !CCTR1 = lngMaxNumber
> .Update
> lngMaxNumber = lngMaxNumber + 1
> .MoveNext
> Loop
> .Close
> End With
>
> Exit_btnInsertCCs_Click:
> Exit Sub
>
> Err_btnInsertCCs_Click:
> MsgBox Err.Description
> Resume Exit_Command1_Click
>
> End Sub

Re: sequential numbers added to null fields

Thanks man! It works fine. I do have another twist - I have another
issue that has been added to my puzzle.
How could I handle this below? Note any person with the same last name
gets the same 8-digit number. The reason I needed some automation was
I have 75,000+ records. I need to query the table first to look for
those names that do not begin with Mr. or Mrs. This is why the three
at the bottom have no number. So if the last name has not been used
then I need to get the highest number + 1 for my number posted, this
is assuming that the name doesn't already have a number (Mr. & Mrs.
are taken care of). My concern is how to query and find if a name is
already present either with or without Mr. or Mrs., If the name
already exists I need to assign the same number used. Thanks in
advance for ANY help!!

Mrs. Rice 02/29/67 13244657
Mr. Cohen 03/15/56 13244355
Mr. Rocco 07/15/75 13244356
Mr. Rice 09/01/61 13244657
Mr. Peterson 02/10/78 13244358
Mr. Holister 06/20/64 13244359
Dr. Drake 05/31/65
Dr. Kellspan 01/12/71
Esq. Cohen 12/15/77

NEEDS TO READ AFTER QUERY:

Mrs. Rice 02/29/67 13244657
Mr. Cohen 03/15/56 13244355
Mr. Rocco 07/15/75 13244356
Mr. Rice 09/01/61 13244657
Mr. Peterson 02/10/78 13244358
Mr. Holister 06/20/64 13244359
Dr. Drake 05/31/65 13244360
Dr. Kellspan 01/12/71 13244361
Esq. Cohen 12/15/77 13244355

On Jan 10, 4:43=A0pm, "Stuart McCall" wrote:
> Comments inline:"slinky" wrote in message
>
> news:21ddb949-d3fd-4580-b3ec-3adf1a7e731c@h11g2000prf.google groups.com...
>
> > I made the DAO 3.6 reference which helped, but my code (exact code
> > below) does not seem to do anything (?)... any ideas?
> > Option Compare Database
>
> > Private Sub btnInsertCCs_Click()
>
> > On Error GoTo Err_Command1_Click
>
> > Dim lngMaxNumber As Number
>
> This should read: Dim lngMaxNumber As Long
>
> > lngMaxNumber =3D DMax("CCTR1", "Historical with Cross Reference - A")
>
> > strSQL =3D "Select ID, AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit],
> > CCTR1, [SAP Account], JAN07 From" & _
> > =A0 "[Historical with Cross Reference - A] Where CCTR1 Is Null" & _
> > =A0 "Order By CCTR1"
>
> There are 2 problems with your sql string. You need a space following
> 'From', and a space before 'Order By'.
>
>
>
> > With DAO.OpenRecordset(strSQL, dbOpenDynaset)
> > =A0 =A0 Do Until .EOF
> > =A0 =A0 =A0 =A0 .Edit
> > =A0 =A0 =A0 =A0 !CCTR1 =3D lngMaxNumber
> > =A0 =A0 =A0 =A0 .Update
> > =A0 =A0 =A0 =A0 lngMaxNumber =3D lngMaxNumber + 1
> > =A0 =A0 =A0 =A0 .MoveNext
> > =A0 =A0 Loop
> > =A0 =A0 .Close
> > End With
>
> > Exit_btnInsertCCs_Click:
> > =A0 =A0 Exit Sub
>
> > Err_btnInsertCCs_Click:
> > =A0 =A0 MsgBox Err.Description
> > =A0 =A0 Resume Exit_Command1_Click
>
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Re: sequential numbers added to null fields

slinky wrote:
> Thanks man! It works fine. I do have another twist - I have another
> issue that has been added to my puzzle.
> How could I handle this below? Note any person with the same last name
> gets the same 8-digit number. The reason I needed some automation was
> I have 75,000+ records. I need to query the table first to look for
> those names that do not begin with Mr. or Mrs. This is why the three
> at the bottom have no number. So if the last name has not been used
> then I need to get the highest number + 1 for my number posted, this
> is assuming that the name doesn't already have a number (Mr. & Mrs.
> are taken care of). My concern is how to query and find if a name is
> already present either with or without Mr. or Mrs., If the name
> already exists I need to assign the same number used. Thanks in
> advance for ANY help!!
>
> Mrs. Rice 02/29/67 13244657
> Mr. Cohen 03/15/56 13244355
> Mr. Rocco 07/15/75 13244356
> Mr. Rice 09/01/61 13244657
> Mr. Peterson 02/10/78 13244358
> Mr. Holister 06/20/64 13244359
> Dr. Drake 05/31/65
> Dr. Kellspan 01/12/71
> Esq. Cohen 12/15/77
>
> NEEDS TO READ AFTER QUERY:
>
> Mrs. Rice 02/29/67 13244657
> Mr. Cohen 03/15/56 13244355
> Mr. Rocco 07/15/75 13244356
> Mr. Rice 09/01/61 13244657
> Mr. Peterson 02/10/78 13244358
> Mr. Holister 06/20/64 13244359
> Dr. Drake 05/31/65 13244360
> Dr. Kellspan 01/12/71 13244361
> Esq. Cohen 12/15/77 13244355

It should be easy enough to find those without a code value. I notice
that there are no first or MIs to this field, just title and last name.
Have you considered a query that separates the two? For example, in
the query builder put
Title:Left(NameFld,Instr(NameFld," ") -1)
LastName:Mid(NameFld,Instr(NameFld," ") +1)
From this separation you should be able to link between two tables.

I don't know how you are getting this data...from data entry or from an
external file. You might want to consider creating fields for both
Title and NameFld and parsing them out. For example, I might create a
field to enter the title & name. Then the two table fields for title
and last name. The person enters Dr. Kildare. In the afterupdate event
parse out the title and last name and present them to the user for
modification if required.

If from an external file, append to a temp table and update your table
from the temp table and do your parsing routine and updating the code.

I also guess you have to hope that there are no misspellings in the
name...like Kildare and Killdare. That would be two separate numbers
and if corrected, do you update the code?

It seems like a mess.



>
> On Jan 10, 4:43 pm, "Stuart McCall" wrote:
>
>>Comments inline:"slinky" wrote in message
>>
>>news:21ddb949-d3fd-4580-b3ec-3adf1a7e731c@h11g2000prf.goog legroups.com...
>>
>>
>>>I made the DAO 3.6 reference which helped, but my code (exact code
>>>below) does not seem to do anything (?)... any ideas?
>>>Option Compare Database
>>
>>>Private Sub btnInsertCCs_Click()
>>
>>>On Error GoTo Err_Command1_Click
>>
>>>Dim lngMaxNumber As Number
>>
>>This should read: Dim lngMaxNumber As Long
>>
>>
>>>lngMaxNumber = DMax("CCTR1", "Historical with Cross Reference - A")
>>
>>>strSQL = "Select ID, AUNUMBER, GRP1, [BPCS R/C], [BPCS 7 Digit],
>>>CCTR1, [SAP Account], JAN07 From" & _
>>> "[Historical with Cross Reference - A] Where CCTR1 Is Null" & _
>>> "Order By CCTR1"
>>
>>There are 2 problems with your sql string. You need a space following
>>'From', and a space before 'Order By'.
>>
>>
>>
>>
>>>With DAO.OpenRecordset(strSQL, dbOpenDynaset)
>>> Do Until .EOF
>>> .Edit
>>> !CCTR1 = lngMaxNumber
>>> .Update
>>> lngMaxNumber = lngMaxNumber + 1
>>> .MoveNext
>>> Loop
>>> .Close
>>>End With
>>
>>>Exit_btnInsertCCs_Click:
>>> Exit Sub
>>
>>>Err_btnInsertCCs_Click:
>>> MsgBox Err.Description
>>> Resume Exit_Command1_Click
>>
>>>End Sub- Hide quoted text -
>>
>>- Show quoted text -
>
>