D
davidg2356
Hi…
How would I setup a numbering system for records in a table(Not using
Autonumber)?
I have an export from one system that lists information on Invoices. My
primary key/index in my MASTER table is a combination of the Line Number &
the Invoice Number. My SUB table allows me to paste the export exactly as it
comes with duplicates and all. The duplicate lines drop off as soon as my
macro Update_Master is ran to append unique rows to the Master table, and
purge the Sub Table for the Next import.
My problem comes in on Invoices that have been “Split†(STATUS= “Splitâ€),
which means pretty much what it sounds like, that the Invoice has been split
apart into similar yet separate records. A lot of these records are exactly
alike (i.e. the money has been split equally among a certain number of rows).
The Invoice Number & Line and every field for these split records are
EXACTLY the same.
So… what I have done is setup an append query to send rows with the Split
status from the Sub table over to mySplit_table. mySplit_table has an extra
column, “myNum†in order to assign a numbering system that will be used again
and again, so I do not want to use an Autonumber feature.
Future Functionality of Numbering System:
With this numbering system in place, I could then run an Update query on the
table that would then update the Invoice Number by appending a hyphen and
sequential number for the Records WHERE mySplit_table.Invoice = a.Invoice And
mySplit_table.myNum >= a.myNum or something along those lines, and append it
all back to the Sub table for the export into the Master table.
The key to it all, however, is how do I setup the Numbering system?
I am trying to learn VB, so I would prefer a method along those lines. I
have tried
Public Function numberSplitRecords() As Long
'Establish connection to ActiveX Data Objects
Dim rs As ADODB.Connection
Set rs = CurrentProject.Connection
'Declare Recordset
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = rs
'SQL statement to populate Recordset
'Query to Number Split Lines
Dim mySQL As String
mySQL = "SELECT mySplit_Table.MyNum FROM mySplit_Table"
'Run SQL Select statement
myRecordset.Open mySQL
'Retrieve the value from the upper-left most of the Recordset
Dim X As Long
Dim Z As Long
Z = 0
X = (myRecordset.Fields(0).Value)
Do While EOF = False ' To step through all records
myRecordset.Fields(Z).Value = Z + 1
Update
MoveNext
Loop
'Close Recordset and terminate connection
myRecordset.Close
Set myRecordset = Nothing
Set rs = Nothing
End Function
However, this isn’t working for me, and I would appreciate any advice you
may have to offer.
Thanks for your time,
How would I setup a numbering system for records in a table(Not using
Autonumber)?
I have an export from one system that lists information on Invoices. My
primary key/index in my MASTER table is a combination of the Line Number &
the Invoice Number. My SUB table allows me to paste the export exactly as it
comes with duplicates and all. The duplicate lines drop off as soon as my
macro Update_Master is ran to append unique rows to the Master table, and
purge the Sub Table for the Next import.
My problem comes in on Invoices that have been “Split†(STATUS= “Splitâ€),
which means pretty much what it sounds like, that the Invoice has been split
apart into similar yet separate records. A lot of these records are exactly
alike (i.e. the money has been split equally among a certain number of rows).
The Invoice Number & Line and every field for these split records are
EXACTLY the same.
So… what I have done is setup an append query to send rows with the Split
status from the Sub table over to mySplit_table. mySplit_table has an extra
column, “myNum†in order to assign a numbering system that will be used again
and again, so I do not want to use an Autonumber feature.
Future Functionality of Numbering System:
With this numbering system in place, I could then run an Update query on the
table that would then update the Invoice Number by appending a hyphen and
sequential number for the Records WHERE mySplit_table.Invoice = a.Invoice And
mySplit_table.myNum >= a.myNum or something along those lines, and append it
all back to the Sub table for the export into the Master table.
The key to it all, however, is how do I setup the Numbering system?
I am trying to learn VB, so I would prefer a method along those lines. I
have tried
Public Function numberSplitRecords() As Long
'Establish connection to ActiveX Data Objects
Dim rs As ADODB.Connection
Set rs = CurrentProject.Connection
'Declare Recordset
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = rs
'SQL statement to populate Recordset
'Query to Number Split Lines
Dim mySQL As String
mySQL = "SELECT mySplit_Table.MyNum FROM mySplit_Table"
'Run SQL Select statement
myRecordset.Open mySQL
'Retrieve the value from the upper-left most of the Recordset
Dim X As Long
Dim Z As Long
Z = 0
X = (myRecordset.Fields(0).Value)
Do While EOF = False ' To step through all records
myRecordset.Fields(Z).Value = Z + 1
Update
MoveNext
Loop
'Close Recordset and terminate connection
myRecordset.Close
Set myRecordset = Nothing
Set rs = Nothing
End Function
However, this isn’t working for me, and I would appreciate any advice you
may have to offer.
Thanks for your time,