Fordraiders
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
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
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:
if you are looking to define numbers in sequence as the user is entering records, use your form's Current Event
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
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
ASKER
no, as stated need vba code to automate this. Thanks.
No manual data entry can be attempted.
Thanks will try it out !
fordraiders
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)
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)
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
testing it today...Thanks
'
'
ASKER
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
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
ASKER
Thanks ...works fine
you can use
dim lastID
lastID=dmax("ID","tblXrefI