Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create Id numbers in text field from continuation of another table

Posted on 2013-01-12
12
Medium Priority
?
220 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 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 28

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 28

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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

877 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