Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA code to assign a sequential value to a subform i.e. a line number

Posted on 2006-10-30
10
Medium Priority
?
381 Views
Last Modified: 2010-08-05
I am trying to figure out the most efficient way to assign an incremental number to a field called Line# each record that is filled in on a subform.

Main for is called FormB and Subform is called QryFormBSub - field name LineNo.

I am new at this stuff, and appreciate all the help I can get.

Thanks,
0
Comment
Question by:jalley
[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
  • 4
  • 4
  • 2
10 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 17841183
personally, i find the quickest way is to let access do this for you.

create a table called mylines, with a field called line#, which is of type autonumber, and a field called line (etc).

then use a maktable query to append your data to the mylines table, then view mylines, and you have your number.

if this needs to be dynamic, create mylines_master, and use docmd.copyobject to make a new copy of mylines from mylines_master everytime you need it.  that way your autonumbers will always start from one.
0
 

Author Comment

by:jalley
ID: 17842918
Thank you for your response!

I will need the line number to be dynamic as you said in your last paragraph:  

"if this needs to be dynamic, create mylines_master, and use docmd.copyobject to make a new copy of mylines from mylines_master everytime you need it.  that way your autonumbers will always start from one."

I would love the autonumbers to always start with one in this subform - that is ideal, but I am a little slow at understanding how this works.

Is mylines_master a table that we are copying from within a form?

I have never used docmd.copyobject before, where does this get used?  and which object am I using it with?

Thank you for your assistance!

0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 200 total points
ID: 17843278
some event will need to trigger a procedure. either a user button or the form load event.. it depends how you want it fired.

basically the "mylines_master " is a table object which you will make a copy of.  this is a lazy way to create your table on the fly with the identifier / autonumber always starting at 1.

a routine will delete any previous version of the table, make a copy, append your data in, and then you can have your subform look at the new table

it could get tricky.  would you have one form which does the work and then opens another form?

On Error GoTo err_trap
DoCmd.SetWarnings False ' block the delete table and append records warning message(s)

DoCmd.DeleteObject acTable, "mylines"
DoCmd.CopyObject , "mylines", acTable, "mylines_master"
DoCmd.RunSQL "INSERT INTO mylines ( [value] ) SELECT test_data.values FROM test_data;"

DoCmd.SetWarnings True

err_trap:
Select Case Err.Number
Case 0
'finished with no error
Case 7874
'7874 - ignore "object does not exist" during delete
Resume Next
Case Else
MsgBox Err.Number & Err.Description
End Select
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 1

Expert Comment

by:DYOUNG1006
ID: 17844494
Set db = DBEngine(0)(0)
Set rsA = db.OpenRecordset("tbl_Pruf_SECA")

'Get last PRUF ID

IntMaxNum = 1
Do While rsA.NoMatch = False
rsA.FindFirst "[PRUF_ID_NUMBER] like '" & IntMaxNum & "'"
If rsA.NoMatch = False Then
IntMaxNum = IntMaxNum + 1
rsA.MoveNext
End If
Loop

IntMaxNum should keep your next number without skipping any
0
 

Author Comment

by:jalley
ID: 17846737
Thank you, I will play with that now and let you know how it goes!
0
 

Author Comment

by:jalley
ID: 17992820
I solved this myself.  Thanks anyway.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 17993895
what did you do to solve it yourself?
0
 

Author Comment

by:jalley
ID: 17995634
Sorry - still working with this - haven't resolved yet....
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 17995661
do you need any more help?
0
 
LVL 1

Expert Comment

by:DYOUNG1006
ID: 17995671
what is your problem is it something you dont understand...

this was about month ago...
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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