Use 2 different controls to increment a number sequence

[sublevelmonkey]

Hi All,



I'm trying to code for a 'duplicate record' button on a form that, as well as copying the values of all the controls of the previous record to the new record, makes a certain control (which contains a number) increment as appropriate.

There are two controls on the form which contain the start and end values of a numerical sequence (of potentially any length) e.g. the range 48-82 shows 48 in text box boxREGSEQSTART and 82 in the text box boxREGSEQEND OR the single value 99 shows 99 in text box boxREGSEQSTART and text box boxREGSEQEND is empty. In a record, the value of boxREGSEQEND would always be higher than that of boxREGSEQSTART or would be blank.

The appropriate incrementing action would be:

After clicking 'Duplicate Record' button,

• If there is no value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSEQSTART should be 1 greater than the value of boxREGSEQSTART in the record it has been copied from.
  
  • i.e. the record being copied is records a single item, a single value, and the next record should show the sequence moved on by one

OR

• If there is a value in boxREGSEQEND in the record being copied, then in the new duplicated record the value of boxREGSTART should be 1 greater than the value of boxREGSEQEND in the record it has been copied from.
  
  • i.e. the record being copied is recording multiple items, a sequence, but the next record should still show the sequence moved on by one

I can get the duplication to work no problem, and I can get the incrementing to work fine for the first set of conditions, but can't for the life of me get it to increment when there is a range!

The logic (in my head, expressed as best I can in the macro language ) for the whole sequence of actions goes like so:

RunMenuCommand

Command SelectRecord

RunMenuCommand

Command Copy

RunMenuCommand

RecordsGoToNew

RunMenuCommand

Command SelectRecord

RunMenuCommand

Command Paste

If [boxREGSEQEND]=[IsNull] Then
GoToControl

Control Name boxREGSEQSTART

SetValue

Item = [boxREGSEQSTART]
Expression = [boxREGSEQSTART]+1

Else
GoToControl

Control Name boxREGSEQSTART

SetValue

Item = [boxREGSEQSTART]
Expression = [boxREGSEQEND]+1

GoToControl

Control Name boxREGSEQEND

SetValue

Item = [boxREGSEQEND]
Expression = [boxREGSEQEND]=Null

Which I would assume would tell the program to copy the current record, make a new one and paste the copy, then if there was a range recorded in the original, start the numbering of the new record one number higher than the end of that range and clear the way in the new form in case it also records a range, otherwise just increment the sequence along by one.




As you can tell, I was working in macros rather than VB (mostly as a learning exercise), but would appreciate help in any macro or VB.

Oh, and apologies for explaining that in a million different ways!

[June7]

You already have the data carrying forward to new record? Google: Access previous record values to new record
http://access.mvps.org/access/forms/frm0012.htm
http://support.microsoft.com/kb/210236
http://allenbrowne.com/ser-24.html

But you want to automate creating multiple records? So switch to a VBA procedure that loops the code for however many times needed to create the specified number of records. Possibly executing an INSERT sql action, something like:

intSequence = whatever the start value should be
For i = 1 To Me.boxREGSEQEND - Me.boxREGSEQSTART
CurrentDb.Execute "INSERT INTO tablename(fieldname, fieldname2, fieldname3) VALUES(" & intSequence & ", '" Me.textboxname1 & "', #" & Date & "#, " & Me.textbox2 & ")"
intSequence = intSequence + 1
Next
Me.Refresh

I don't really understand boxREGSEQEND and boxREGSEQSTART - are these unbound controls?

[sublevelmonkey]

Thanks for the response June7,

I was worried I'd over complicate things!

The information I'm capturing is a list of entries in a catalogue. There is a general numbering sequence through the entries (i.e. 1-100) but any given entry could be for 1 or more items so the sequence of entries could go

1st Entry - #1 (1 item)
2nd Entry - #2 (1 item)
3rd Entry - #3-10 (8 items)
4th Entry - #11 (1 item)
5th Entry - #12-13 (2 items)
&c. &c. &c.

('boxREGSEQSTART' and 'boxREGSEQEND' are what I've named the text boxes on the form that contain the values for 'Start of the register sequence' and 'end of the register sequence', i.e. for 3rd Entry above, boxREGSEQSTART would contain the value 3 and boxREGSEQEND would contain the value 10)

The rationale behind this is that the information in the entries is often identical with the only thing changing being this incrementing of the number sequence so, a button that would duplicate a preceding record and increment the number sequence would really reduce the keying for each record.

What I want the program to do is, when I duplicate a record for the program to look at the text box 'boxREGSEQEND' and if there is a no value there to then change the value of the text box 'boxREGSEQSTART' in the new record to 1 integer higher than that of the record it was duplicated from, but if there is a value, to change the value of the text box 'boxREGSEQSTART in the new record to 1 integer higher than the value it found in 'boxREGSEQEND'.

So I'm not trying to create multiple records, simply to automate the process of filling these fields when duplicating a record - trying to save a couple of keystrokes and learn something about Access at the same time. Something I thought would be simple turning, as usual, into something very complicated!

May be I should think about this during the day rather than last thing at night


Thanks

[June7]

Okay, think I understand now.

Are these textboxes bound to fields? If so, the easiest approach might be a public variable or an unbound textbox (can be hidden) on form. In either case value is retained throughout the session and can be accessed and changed as each record is created. Maybe like:

Option Compare Database
Option Explicit
Public varStart As Variant
--------------------------------------------

Sub Form_Current()
If IsNull(Me.boxREGSEQSTART) Then Me.boxREGSEQSTART = varStart
End Sub

Sub Form_BeforeUpdate(Cancel As Integer)
varStart = 1 + IIf(IsNull(Me.boxREGSEQEND), Me.boxREGSEQSTART, Me.boxREGSEQEND)
End Sub

Drawback with variable is can be a headache during debugging. Code execution interrupted by errors can cause variable to lose value.