Calculated Field Padded Count of New Record?

[misscrf]

I have a form for tracking media in a project. Each media gets an asset tag. This tag looks like the following:

123456_00001_CDR

The first 6 digits are the project id. The next 5 are a counter, which is padded for sorting and such. The last 3 characters are an abbreviation for the type of media. I have a look up table with the possible abbreviations and a description of what they are.

Users enter new media on the media tracking form. To get to this form, they are filtering to a given project. Instead of having users create that entire string for each media they are tracking, I want them to choose from a combo, the type of media they just got. Then I want that string to be created and stored in the media tag field, as well as displayed in a read only field on the form. The counter is decided as how many assets exist in that filtered record set. So if there are none, and this is the first record, it will be 00001. But if there are already 9 assets, the new one will be 00010.

I am not sure how to go about doing this. I searched around, but didn't find many examples of padded counters lol.

Anyone know of an easy way to get this working?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Have a look at the Format function, eg:
Format(yourCounter, "00000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

[ol 1]
[li]At the time they pick the type of media from the Combobox, is the 6-digits projectID already showing on the Form?[/li]
[li]Is this a multi-user app, as it sounds?[/li]
[li]Does the media tag field have to be immediately available on the Form, or is it OK if it's there once the Record is saved?[/li]
[li]If the answer to # 2 and # 3 is 'yes,' are you doing any data validation in the Form_BeforeUpdate event, before the Record is saved?[/li]
[/ol]

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[misscrf]

Thank you for the responses, PHV and missinglinq. PHV, the issue is that the sql field is actually the entire string. I don't think I can have a format for the field, when then the whole value is 123456_00001_HDD . I would love to do that type of format in an invisible form field, that can carry the counter and be called for the construction of this field.

Missinglinq:

[ol 1]
[li]At the time they pick the type of media from the Combobox, is the 6-digits projectID already showing on the Form?[/li]
I realized I need to have that information somehow. The case is actually pulled up by a pop-up form filter which has the 6 digit project id in the prefix of the project key (id and name), which is how we filter to the assets that exist in this project. If there are none, it is an empty form, ready for entry. We can use that prefix as the one for the asset.

[li]Is this a multi-user app, as it sounds?[/li]
Yes, it is. I am guessing you ask this, because we would need to lock that project, so multiple people aren't entering in at one time. It would not be practice that more than one person would be on a project at once. It just doesn't work that way. Even so, if there is a way to lock the project, that might work. Another way would be to have an acsaverecord on the after update of the asset type combobox. We would also need code in the change event of that combo, in case they (for some reason) come back and change the type. The asset needs to be updated.

[li]Does the media tag field have to be immediately available on the Form, or is it OK if it's there once the Record is saved?[/li]
Per my previous answer, if we can run the save command after they choose the type, we should be able to display the asset at that time, yes? If not, then we can wait until the rest of the record is filled out.

[li]If the answer to # 2 and # 3 is 'yes,' are you doing any data validation in the Form_BeforeUpdate event, before the Record is saved?[/li]
Right now, I do not have any data validation, which is why I am looking to move my forms to this route, so that I can add some validation. I am all ears on any that I should be doing (and how to do them!).
[/ol]




Thank you both!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Just to add some more kindle to the fire, I am not sure how to handle the counting, as the testing I am doing puts every record as the total record count. Here is the code I started with, just to get the middle part of the string worked out:

Select Case CStr(Me.RecordsetClone.RecordCount)
    Case 1 To 9
        Me.txtCalcAsset.Value = "0000" & CStr(Me.RecordsetClone.RecordCount)
    Case 10 To 99
        Me.txtCalcAsset.Value = "000" & CStr(Me.RecordsetClone.RecordCount)
    Case 100 To 999
        Me.txtCalcAsset.Value = "00" & CStr(Me.RecordsetClone.RecordCount)
    Case 1000 To 9999
        Me.txtCalcAsset.Value = "0" & CStr(Me.RecordsetClone.RecordCount)
End Select

I don't know if I am just way off base, with that idea, but this is what I was thinking I would set as the value for 1 control, and then I could attach the project prefix to the beginning, and the choice from the media type into the suffix, and put that string into the field that I want to store. The problem at the moment is that the above code makes every record the total count, so that if I have 5 assets in this project, that field ends up as:
00005
00005
00005
00005
00005

I am not sure if I am missing something, but I would think the first record should have the count of 1, but I guess every record is counting the total record count. Boy I feel clueless! lol



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Found this code:

'Copyright Stephen Lebans 1999
Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     Text box with ControlSource of:  =RowNum([Form])
    
    With frm.RecordsetClone
        .Bookmark = frm.Bookmark
        RowNum = .AbsolutePosition + 1
    End With
    
Exit_RowNum:
    Exit Function
    
Err_RowNum:
    If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If
    RowNum = Null
    Resume Exit_RowNum
End Function

I put that into a textbox control called txtcalcrow. Then in a new control called txtcalcasset, I put:

=IIf([txtCalcRow] Between 1 And 9,"0000" & [txtCalcRow],IIf([txtCalcRow] Between 10 And 99,"000" & [txtCalcRow],IIf([txtCalcRow] Between 100 And 999,"00" & [txtCalcRow],IIf([txtCalcRow] Between 1000 And 9999,"0" & [txtCalcRow],"WTF"))))

Now I am working on filling a default value for the media type combo that the user will choose. This is not bound to anything, but for any existing records, it should pull the right 3 characters of the asset tag for that record.

I set this up:

Me.cboAssetType.DefaultValue = DLookup(Right([AssetTag], 3), "tblAssetTracking", "Right(AssetTag, 3) =  '" & Me.MediaSuff & "'")

but I get a run-time error '2471' "the expression you entered as a query parameter produced this error:'DVD'

I haven't had a chance to look up that run-time yet, but that is where I am at. Progress!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I now have all the pieces I need to create this asset tag. I have a combo box for the user to choose the media type for the new record (asset).

The sql is to the lookup table, and I have it bound to a field in the asset tracking table, just for the media type fk.

SELECT tblAssetMediaTypes.ID, [tblAssetMediaTypes]![MediaTypeAbbrev] & ": " & [tblAssetMediaTypes]![MediaTypeDesc] AS MyMediaType
FROM tblAssetMediaTypes
ORDER BY [tblAssetMediaTypes]![MediaTypeAbbrev] & ": " & [tblAssetMediaTypes]![MediaTypeDesc];

For the project ID, I have an unbound textbox control that is set on the on open of the form, with the following code:

Me.txtAssetArgs = [Forms]![frmAssetMain]![lstBillingKeyResults].Column(1)
If Len(Me.txtAssetArgs.Value & vbNullString) = 0 Then
    MsgBox "Must have an asset filter to open asset tracking", vbOKOnly, "Must Choose a Matter Filter"
    DoCmd.OpenForm "frmAssetMain"
    DoCmd.Close acForm, "frmAssetResults"
Else
With Me.frmAssetTrackingSub.Form
.Filter = "Left([AssetTag],6)=[Forms]![frmAssetResults]![txtAssetArgs]"
.FilterOn = True
End With
End If

For the counter, I have two textbox controls (unbound). The first is txtCalcRow. It is set as:

=RowNum([Form])

I then have another control called txtCalcAsset. It is set as

=IIf([txtCalcRow] Between 1 And 9,"0000" & [txtCalcRow],IIf([txtCalcRow] Between 10 And 99,"000" & [txtCalcRow],IIf([txtCalcRow] Between 100 And 999,"00" & [txtCalcRow],IIf([txtCalcRow] Between 1000 And 9999,"0" & [txtCalcRow],"NewRec"))))

The idea will be that a user, on adding a new record, will have to choose a media type from the drop-down. Once they do that, the asset tag will be generated.

I still need to come up with that code, and I need to handle if the user chooses the wrong media type and they need to change it. My biggest issue is the form layout. A user starts at a main dialog, where they type in a field any part of the project. ID or name. A multi-select list box fills in with the possible matches. Double-click the right match and a form comes up with all the assets in that project. I need to handle how they add new assets, so that this asset generation can be handled.

Any ideas are appreciated! Right now, I am going back and forth between a datasheet in a subform, so the main form can have controls, vs a continuous form or subform for all the assets. They will want to see what exists in that project, and add new ones.

My main concern is that these users are IT people. They are tech savvy and I want the form to help their lives, not be a burden to fill out.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

You may simplify your expression for txtCalcAsset:
=IIf([txtCalcRow] Between 1 And 9999,Format([txtCalcRow],"00000"),"NewRec")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Can I just put a format on txtCalcRow?

misscrf

It is never too late to become what you could have been ~ George Eliot