Jason wrote:
I was given a database that has unique IDs for each record such as:
AL00001
AL00002
AL00003
etc
The trouble is, I'm going to need to import data from another source that
will also have IDs such as this and more than likely there are going to be
duplicates. So if I import data that already has an AL00003, how can I get
access to automatically read the table and figure out the latest ID and then
bump it up or update from AL00003 to AL00004?
In this same field the IDs can be anything like AL00001, or CA00001 so I
also want to look at those first two characters and then figure out the max
num thats in the field.
Make sense? I'd like to setup an import query, then run this unique ID/dup
check/update function, then append those temp records into the main table.
Thank you in advance!
OK. Create a query to import the new records into a temp table. I'll
call it QImport.
I'll call the temp table Table1. The other table is called Production
Next, create a query (maybe paramter) that has two columns;
AlphaCode : Left(ID,2)
NumericCode : Mid(ID,3)
and save it to whatever, I'll call it Query1
Now create some code...air code follows
Private Sub ImpRecs
Dim rst As DAO.Recordset
Dim rstProd As DAO.Recordset
Dim rstMax As DAO.Recordset
Dim strSQL As STring
'import the new records into Table1
Docmd.openquery "Qimport"
Set rst = Currentdb.openrecordset("Table1",dbopensnapshot)
Set rstProd = Currentdb.openrecordset("Production",dbopendynaset )
If not rst.BOF Then
rst.MoveFirst
'loop thru all new records
DO while not rst.EOF
'see if rec exists with same id
rstProd.FindFirst "ID = " & rst!ID
If rst!NoMatch then
'rec not found. call insert routine
InsertIt rstProd, rst, rst!ID
Else
'determine max number in Production
strSQL = "Select Max(NumericCode) As "MaxNum From Query1 Where
AlphaCode = '" & Left(rst!ID,2) & "'"
'now get the max num
set rstMax = Currentdb.openrecordset(strSQL,dbopensnapshot)
'you'll need to format MaxNum with leading
'zeros.
Insert rstProd, rst, Left(rst!ID,2) & _
rstMax!MaxNum + 1
rstMax.close
set rstMax = Nothing
rst.MoveNext
Loop
endif
rst.close
rstprod.close
set rst = Nothing
set rstprod = nothing
msgbox "Done with update"
end sub
Private Sub InsertIt(rstProd, rst, NewID)
'now add via AddNew or a SQL Insert. Use the NewID for the ID
'in all cases since the NewID was determined in the calling code
'....AddRec code here
ENd Sub
