Solved

Create Id numbers in text field from continuation of another table

Posted on 2013-01-12
12
210 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 26

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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now