Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Create Id numbers in text field from continuation of another table

access 2010
vba code

I have a database called  xref.accdb
Linked Table name : tblXrefInfo_Extra

3 fields:
xblxrefId -Text
xreftype- text
comments-text

Table Name:
Xref
4 fields:
XrefId- text
Item-text
Mfrnum-text
mfgname-text

What I need:
In the Linked Table "tblXrefInfo_Extra"  I need to go to the very last record and get the number that is there.

i.e. 6808403

Then once I know what that number is.
Create a number sequence in the "Xref" Table: In the field:  "XrefId"
for all those records.


Thanks
fordraiders
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

<In the Linked Table "tblXrefInfo_Extra"  I need to go to the very last record and get the number that is there.>

you can use

dim lastID

lastID=dmax("ID","tblXrefInfo_Extra")
And then lastID = lastID + 1
--> Create a number sequence in the "Xref" Table: In the field:  "XrefId"
for all those records.

Are you looking to update existing records or add new ones?

To update existing records:

Sub UpdateRecords()
Dim NextID as long 
NextID = DMax("YourIDField", "tblXrefInfo_Extra") + 1

Dim strSQL as string 
dim rs as dao.recordset

strSQL = "SELECT * FROM Xref ORDER BY YourIDField"  '<-- if you have an autonumber field, order by it
Set rs = currentdb.openrecordset (strSQL)
if rs.recordcount = 0 then exit sub
do until rs.eof
     rs.edit
     rs!XrefId = NextID
     rs.update
     rs.movenext
     NextID = NextID + 1
loop
rs.close
set rs = nothing
End Sub

Open in new window


if you are looking to define numbers in sequence as the user is entering records, use your form's Current Event

Private Sub Form_Current()
      if me.newrecord = true 
            if DCount("*", "Xref") = 0 then
                  me.XrefId = DMax("YourIDField", "tblXrefInfo_Extra") + 1
            else
                  me.XrefId = DMax("XrefId", "Xref") + 1
            end if
      end if
end sub

Open in new window

Avatar of Fordraiders

ASKER

no, as stated need vba code to automate this. Thanks.
No manual data entry can be attempted.
Thanks will try it out !
fordraiders
Ok... then if you need help specifically with code to insert/update data in your xref table, you might need to show us some sample data indicating what exactly is getting updated and how that sequence fits in...

are you simply sequencing all of the parts in  your xref table, or are you sequencing groups of parts... for example serial numbers for work orders related to a single part?  

(We might need more detail)
In the Linked Table "tblXrefInfo_Extra"  I need to go to the very last record and get the number that is there.

i.e. 6808403

Before Update:
XrefId     Mfrnum   mfgname
               232aw         proto
               4453sw       proto
               45rt            sears
               342e3         sears

After Vba update code:
XrefId     Mfrnum   mfgname
6808404    232aw     proto
6808405    4453sw   proto
6808406    45rt        sears
6808407    342e3     sears


Thanks
fordraiders
What insures that your records are in the correct order before attempting to update the running number id?
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
testing it today...Thanks
'
User generated imagembizup.

error after reaching so many numbers...
i will have approx 1.5 mil records to increment.


Thanks
fordraiders
Just verifying ... you kept the NextID variable declared as LONG, correct?  Long allows values up to  2,147,483,647, versus the Integer data type which only allows numbers up to 32,767.

Also, what datatype is your tblXRefinfoID field defined as?  If it is numeric, unsure that it's "Length" property is something that allows larger numbers than 'Integer' does.

All that said about data types, you might also have some corruption in your table, and it might be stopping/failing on a corrupt row of data.  If you do indeed have the data types defined properly, try any/all of the following

- Compact/repair your database
- Copy the data into a new table and run the function against that new table
- upload a sample copy of your database here so that we can take a look at it
Thanks ...works fine