Solved

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

Posted on 2006-10-30
10
331 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Expert Comment

by:DYOUNG1006
Comment Utility
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
Comment Utility
Thank you, I will play with that now and let you know how it goes!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jalley
Comment Utility
I solved this myself.  Thanks anyway.
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
what did you do to solve it yourself?
0
 

Author Comment

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

Expert Comment

by:Simon Ball
Comment Utility
do you need any more help?
0
 
LVL 1

Expert Comment

by:DYOUNG1006
Comment Utility
what is your problem is it something you dont understand...

this was about month ago...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

11 Experts available now in Live!

Get 1:1 Help Now