Solved

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

Posted on 2006-10-30
10
344 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

947 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

20 Experts available now in Live!

Get 1:1 Help Now