Solved

Create Id numbers in text field from continuation of another table

Posted on 2013-01-12
12
218 Views
Last Modified: 2013-01-24
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
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38770517
<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")
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 38770521
And then lastID = lastID + 1
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38770564
--> 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

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 3

Author Comment

by:fordraiders
ID: 38770638
no, as stated need vba code to automate this. Thanks.
No manual data entry can be attempted.
Thanks will try it out !
fordraiders
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38770664
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)
0
 
LVL 3

Author Comment

by:fordraiders
ID: 38773510
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
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 38773512
What insures that your records are in the correct order before attempting to update the running number id?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38773626
Did you try the first block of code I posted in my last comment?  It should do what you are asking.

As MacroShodow noted, it may be important to get the records in the table you are exporting to in a specific order (that's why I placed the comment about ORDER BY in my earlier code :-)  ).  If order is important you should have some field such as an automunber or a Date field so that you can arrange the records in the table before updating the xrefID field with your new sequence.  

If you don't have such a field, it would be a good idea to add an autonumber field at this time.

This is the code I posted earlier.  Again, if you have an autonumber field, use it in the ORDER BY clause in the code.  If you don't have or want an autonumber field, you can remove the ORDER BY from the SQL in the code below and try it without (but if you have been sorting your table, so might run into trouble with the ordering):


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, 
' strSQL = "SELECT * FROM Xref"  <--- If you don't have an autonumber, use this instead
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

0
 
LVL 3

Author Comment

by:fordraiders
ID: 38775378
testing it today...Thanks
'
0
 
LVL 3

Author Comment

by:fordraiders
ID: 38775459
error after so many records being updated.mbizup.

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


Thanks
fordraiders
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38775681
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
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 38814603
Thanks ...works fine
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question