Semi Automatic numbering

[dtutton]

I have a table with a pimary key consisting of two parts i.e. xxxYYY like where xxx is the three letter equipment type code i.e. DOZ and YYY is a numerical sequential number i.e. DOZ121

When I add a new record I wish to automatically number using to the same sequence i.e DOZ122

I need some help on the vba code, the equivalent in clipper would be something likeuntested but shows the idea)

max=0
vtype="DOZ"
sele 1 'go to top of table
do while .not. eof()'scroll thru table
if substr(DATNo,1,3) = vtype
if val(substr(DATNo,4,3)) > max
max = val(substr(DATNo,4,3))
endif
endif
skip
loop
enndo
vDATNo=vtype&alltrim(str(max+1))
append blank
replace DATNo with vDATNo

i.e. loop thru the table looking for the higher DATNo for a specific type of equipment.

or can I do it with the the DMax command with some sort of string splitting as per the clipper example, or say a query with two new fields calculated for XXX and YYY

Thanks to anyone who can help me with the switch from clipper.

David

 

[ghubbell]

Hi David!
I'll send you over to Thread181-59887 to see an idea to handle the number part in one easy table. I have also done similar where a prefix is required after the number is selected but by selecting it from a combobox that "feeds" from a table called "tblprefix". In your case I think you have the idea of how to blend the two: something like MyValueIs = "ABC" & NextNumber(1). It's just a matter of getting that next number! Gord
ghubbell@total.net

 

[JDJohnson]

Probably some very elegant ways to do this. In a previous database I used the DMAX function to check what was in use for each of the "parts" of the hyphenated number, if the number was already in use I displayed a dialog telling the user to try again (and the after 2d try reset to default).

Once I was assured hyphenated parts were "ok" I just used

'set record...
Me![CCHyp] = A & "-" & C & "" & B & "-" & S

to put them together. Simple approach for sure but worked fine for me.

Jim, AL

 

[dtutton]

Gord,

This is the big step into SQL from clipper I need some clarifiaction. Im first trying to duplicate a record but increment the counter to the next available sequence No.

OK, I can pick up the current record i.e. DOZ12

I understand the main form code as follows:

DoCmd.GoToRecord , , acNewRec
Me![DATNo] = NextNumber(vDATNo)

and the function to be:

Public Function NextNumber(RefType As Variant) As Variant
On Error GoTo ErrNN
Dim SQL1 As String, Rs As Recordset, Db as Database
Set Db = CurrentDb()
SQL1 = "SELECT tbequip.* FROM tbequip WHERE (((tbequip.DATNo)= " & RefType & &quot"



>I assume this finds the record DOZ12
Set Rs = db.OpenRecordset(SQL1, dbOpenDynaset)

If Rs.RecordCount = 0 Then
Rs.Close
GoTo ErrNN
End If
>I assume that checks to see if record found

>this is where Im lost - is this a do loop which reads from the current record , what are the commands to move to the next record etc. Sorry this is where I need the equivalent of my clipper do loop.


NextNumber = Rs!Counter + 1
Rs.Edit
Rs!Counter = NextNumber
Rs.Update
Rs.Close





ExitNN:
Exit Function

ErrNN:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Next document number generation error."
Resume ExitNN
End Function


If you can clue me a bit further it would be appreciated.

Best Regards,

David

 

[ghubbell]

Geeze does daylight savings time mess me up. Good morning David! You will need to make the table "tbldoctype" as described, and all it is going to do is store one number, the current top number. This is the number portion only. So Me![DATNo] = NextNumber(vDATNo) should be something like Me![DATNo] = "ABC" & NextNumber(1), 1 being the "doctype". Your assumptions on how it works are good:

Set Rs = db.OpenRecordset(SQL1, dbOpenDynaset)
makes a recordset <table in space!> using SQL1 (the defined query or table) and dbOpenDynaset one of many types you can use, this one being editable.

If Rs.RecordCount = 0 Then
Rs.Close
GoTo ErrNN
End If
Exactly. If there's no record, abandon ship! Some people use &quot;If Rs.EOF&quot; (end of file) to get similar results.

The section where you're lost is where there is no looping to do. In the original code we ask &quot;go to the tbldoctype and get me the &quot;Counter&quot; number for doctype 1&quot;. There is only 1 so it gets it or not.

Then apply it to your real table adding ABC or what ever you'd like, and now that the Counter number has just been &quot;sold&quot;, increase the Counter number in tbldoctype by +1.

You're doing fantastic as this is a little tricky but I can see you're right on your way. Keep plugging at it and let me know how you fair. Meanwhile, I'll go reset some clocks!
Gord
ghubbell@total.net

 

[MichaelRed]

Don't get me wrong, I LIKE the wheels, the more the merrier. On the otherhand, faq700-184 give a reasonable &quot;canned&quot; soloution. It doesnt EXACTLY match, but if you can read it, yoy could certainly modify it enought to do the job.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[dtutton]

Gord,

I was traveling for last two days so thus lack of response to your monday soln.

My equipment table actually has duplicate sequence numbers thus I dont think your suggestion would work i.e. it has for example DOZ(1-25), SHO(1-60) and TRK(1-40). Also I occassionaly add records via a table thus storing the maximum seqno in another table may create errors.

What I tried to do was as follows:

a) set up a query for the table(under queries) with two expressions:

SeqNo: Val(Mid([DATNo],4,3)) (sorted descending)
ItemType: Left([DatNo],3) (sorted descending)

b) Put a dummy (I hid it) text label on the form with the following expression:

=Left([DATNo],3) & Trim(Str(DLookUp(&quot;SeqNo&quot;,&quot;Query3&quot;,&quot;ItemType='&quot; & Left([DATNo],3) & &quot;'&quot+1))

c) In the vba code for dumplicating records incorporated:

varDATNo = Me!Text156
Msg = &quot;Appending a new record for &quot; & Me!Text156
If MsgBox(Msg, vbOKCancel) = vbOK Then
DoCmd.GoToRecord , , acNewRec
[DATNo] = varDATNo etc

This worked for duplicating one record but then bombs on subsequent records. I guess my lack of knowledge of vba sql is clear and I used almost a spreadsheet approach. Please could you advise:

1. Why couldnt I use the dlookup statement as is in vba and only in an expressionon the text label

2. How can I dynamically update the query from vba.

3. How can I try to do the whole thing in vba without an external query.

Any assistance would be a great help.

Thanks, David

 

[ghubbell]

Good morning again David, You'll have to explain a little further on the duplicate sequence number part: Are you changing i.e. from DOZ 25 to the next: DPA 1? Kind of like some license plate numbers? Oooh this could be really fun! Gord
ghubbell@total.net

 

[dtutton]

Good afternoon Gord (Im in Germany). No basically I have an equipment file with Dozers(DOZ), Trucks (TRK), Shovels (SHO) etc data in it. If I add another machine, I want to add the next number to the sequence code for that item in sequence. i.e. lets say last dozer was DOZ41, then the added record should be DOZ42 or for a shovel say the last was SHO9 (no leading spaces/zeroes), then the next would be SHO10.

The method using a Query, text box and expressions worked but the query only updated when one actually left the form.

I guess what I need to try to do is, in vba set up some sort of a dynamic query mirror of the table accessed by the form and then do a seek on the query. (probably the wrong speak here)

Thanks alot,

David

 

[ghubbell]

Ah Ha! possible, but brings me back to the prefix issue...Who does the choosing at the moment you want the new number? Is it the user? Silly question...it should be the user assuming all the listings reside in one table?! Get a number and &quot;prefix it&quot; with the letters appropriate to the type of equipment! What do you think? Am I fading? Gord
ghubbell@total.net

 

[dtutton]

Gord,

On choosing number:

1. For duplicating record, takes Item Type of current record, and appends a new record with same content except DATNo which is the equivalent of

Left([DATNo],3) & Trim(Str(DLookUp(&quot;SeqNo&quot;,&quot;Query3&quot;,&quot;ItemType='&quot; & Left([DATNo],3) & &quot;'&quot+1))

i.e. if existing = DOZ41, duplicate is DOZ42.

2. For adding record, takes item type from a combo box and appends a blank record with next DATNo as above.

Generally additions would be from form only but in past I have also added new records in excel and then imported into access thus my wish to re-index table and find highest sequence number for each item.

Thanks,

David

 

[ghubbell]

Ok then.... :-(

In your forms &quot;On Current&quot; event in VB add this:

Me.Text156.Requery

Maybe make it visible for your tests and see if it's doing what its supposed to... ;-) Gord
ghubbell@total.net

 

[dtutton]

Gord,

90% there. Tried it on the On Current but didnt work even when followed by a Me!Refresh. Subsequently put in the code for On Click of Duplicate button command and works perfectly (well almost).

For some reason, some fields seem to get dropped in the duplicate - i.e. in current record, width has a numeric value but when duplicated it is a nul which then crashes on next duplicate. If I comment out line, it works perfectly.

Private Sub DuplicateEquipment_Click()
Dim Msg As String
Dim varDATNo, varItem, varModel, etc....As Variant
Dim varLength, varWidth, varHeight, varTyres, etc as Variant

varDATNo = Me!Text156
varItem = Me![Item]
varModel = Me![Model]....
varLength = Me![Length]
varWidth = Me![Width]
varHeight = Me![Height]
varTyres = Me![Tyres]
Msg = &quot;Appending a new record for &quot; & Me!Text156
If MsgBox(Msg, vbOKCancel) = vbOK Then
DoCmd.GoToRecord , , acNewRec
[DATNo] = varDATNo
[Item] = varItem
[Model] = varModel
[Length] = varLength
[Width] = varWidth
[Height] = varHeight
[Tyres] = varTyres
etc
Else
Me.Undo
Me.Refresh
End If
Me.Text156.Requery
Me.Refresh
End Sub

I assume it may be with my Dim statement ? any ideas ?

Otherwise Ill close this thread and wait till I get a reference book on sql in vba - thanks again for help.

David

 

[ghubbell]

We'll see if we can't get this fixed before your dinner time! Please try this:

Private Sub DuplicateEquipment_Click()
Me![Item].DefaultValue = Me![Item]
Me![Model].DefaultValue = Me![Model]
Me![Length].DefaultValue = Me![Length]
Me![Width].DefaultValue = Me![Width]
Me![Height].DefaultValue = Me![Height]
Me![Tyres].DefaultValue = Me![Tyres]
If MsgBox(&quot;Are you sure you would like to append a new record for &quot; & Me.Text156 & &quot;?&quot;, vbYesNo + vbQuestion, &quot;Append confirmation...&quot = vbYes Then
DoCmd.GoToRecord , , acNewRec
me.DATNo = Me.Text156
Else
Me.Undo
Me.Refresh
End If
Me.Text156.Requery
Me.Refresh
End Sub

;-)

Gord
ghubbell@total.net

 

[dtutton]

Looks loke a late dinner !

No luck on that. With numeric fields seems ok but with text fields didnt work i.e. model is a text field. Also I had some numeric fields with Nul values that also werent liked.

Also the DATNo via the Text156 label didnt update

David

 

[ghubbell]

Hi Dave,
Sorry for the delay...I stepped out for lunch

Puzzling but please try this for me. I think we must be pretty close. You can put stops (red dots in the left margin) and watch the code as it runs. Hover your cursor over each &quot;value&quot; and it should say what it is but always the line before the active one.

Private Sub DuplicateEquipment_Click()
On Error GoTo Err1
DoCmd.RunCommand acCmdSaveRecord
If Not IsNull(Me!Text156) Then Me.Text156.DefaultValue = Me.Text156
If Not IsNull(Me![Item]) Then Me![Item].DefaultValue = Me![Item]
If Me![Model] <> &quot;&quot; Then Me![Model].DefaultValue = Me![Model]
If Not IsNull(Me![Length]) Then Me![Length].DefaultValue = Me![Length]
If Not IsNull(Me![Width]) Then Me![Width].DefaultValue = Me![Width]
If Not IsNull(Me![Height]) Then Me![Height].DefaultValue = Me![Height]
If Not IsNull(Me![Tyres]) Then Me![Tyres].DefaultValue = Me![Tyres]
If MsgBox(&quot;Are you sure you would like to append a new record for &quot; & Me.Text156 & &quot;?&quot;, vbYesNo + vbQuestion, &quot;Append confirmation...&quot = vbYes Then
DoCmd.GoToRecord , , acNewRec
Me.DATNo = Me.Text156
Else
Me.Undo
Me.Refresh
End If
Me.Text156.Requery
Me.Refresh

Exit1:
Exit Sub

Err1:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;Ooops!&quot;
Resume Exit1
End Sub

Ok... we'll try and get it before bedtime.... Gord
ghubbell@total.net

 

[dtutton]

Gord,

I tried as suggested and found the DATNo was not getting updated atall - remained blank. To ensure a record was appended with a name I inserted:

varNewDAT = Me.Text156 (with a dim above)

and

Me.DATNo = varNewDAT (after the add command)

Put in some watches and the the values look fine except I cant 'watch' the Me!['field'].DefaultValue .

Record appends with the correct newDATNo and numeric variables but text variables are not correct (blank).

Seems that the defaultvalue is not being set for text variables ?

David

 

[ghubbell]

Dave! Are you still awake! (4:50 in the afternoon here and I'm not)

I've got the text issue...Fussy fussy Access:

Me.Text0.DefaultValue = &quot;'&quot; & Me.Text0 & &quot;'&quot;

Works!
Maybe it's the same with your DatNo ?

Gord
ghubbell@total.net

 

[dtutton]

Gord,

Still going strong (well maybe not). hitting midnite here.

I tried on a few text fields and works fine. Thanks alot.

Will try the whole show tomorrow.

Thanks again.