[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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

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
jalley
Asked:
jalley
  • 4
  • 4
  • 2
1 Solution
 
Simon BallCommented:
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
 
jalleyAuthor Commented:
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
 
Simon BallCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DYOUNG1006Commented:
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
 
jalleyAuthor Commented:
Thank you, I will play with that now and let you know how it goes!
0
 
jalleyAuthor Commented:
I solved this myself.  Thanks anyway.
0
 
Simon BallCommented:
what did you do to solve it yourself?
0
 
jalleyAuthor Commented:
Sorry - still working with this - haven't resolved yet....
0
 
Simon BallCommented:
do you need any more help?
0
 
DYOUNG1006Commented:
what is your problem is it something you dont understand...

this was about month ago...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now