Solved

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

Posted on 2006-10-30
10
361 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
  • 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 50 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

827 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