automatically entering specific values in a subform field

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I have a subform field (named InvoiceSuffix) in which I'd like to insert a
repetitive series of values for each matching record in the main form. That
is, for each corresponding record in the main form, I'd like each field in
the subform field InvoiceSuffix to take on the following values:

00 for the first record
01 for the second record
02 for the third record
03 for the fourth record
up to a maximum of 08, because I'll never have more records than that in
the subform.

Since it's predictable and repetitive, I'd like to have these values created
automatically so the user doesn't have to enter these values. Is there a
way to accomplish this?

Thanks in advance,

Paul
 
Hi Paul:

This should work (let me know of any bugs)-

Function GetRecordLocation() As String
Dim rs As Recordset
Dim strResult As String
On Error Resume Next

Set rs = Me.RecordsetClone
With rs
..MoveLast
..Movefirst
Do Until .EOF
strResult = CStr(.AbsolutePosition)
!GetRecordLocationField = "0" & str(strResult)
.MoveNext
Loop
Set rs = Nothing
End With
End Function

Regards,
Al
 
Thanks for the reply, Al.

I tried following your suggestion, but I'm not able to get it to work.
Here's what I did:

1. In the line in your function

!GetRecordLocationField = "0" & str(strResult)

I replaced GetRecordLocationField with the name of the field I'm trying to
update, InvoiceSuffix. Otherwise I didn't make any other changes to your
function. So the function I'm trying to run is:

Function GetRecordLocation() As String
Dim rs As Recordset
Dim strResult As String
On Error Resume Next
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MoveFirst
Do Until .EOF
strResult = CStr(.AbsolutePosition)
!InvoiceSuffix = "0" & Str(strResult)
.MoveNext
Loop
Set rs = Nothing
End With
End Function

2. Next I called the function GetRecordLocation from the After Update event
of the subform:

Private Sub Form_AfterUpdate()
GetRecordLocation
End Sub

But when I try to add (Update) a new record to the subform, Access and VBA
both lock up, and I have to press Ctrl + Break to regain control of Access
again, at which point I get the dialog saying "Code Execution has been
interrupted." When I press End in that dialog box, one of the lines in the
loop is highlighted with the yellow line execution marker.

I must be doing somehting wrong. Can you tell me what that is?

Thanks for your help.

Paul
 
Hi Paul:

I've tested this and after changes, the following works. First of all, I got
rid of the "On Error Resume Next" to see where it gets hung up. I originally
copied the code from my files, and generally it looked good. Here are the
changes that needed to be made, though:

1) The first problem is the CStr(... which is NOT Str(... ; the former is a
Boolian integer (i.e. True/False). What is needed is for the integer to be
changed to a string function.
2) Secondly, the string that you get is a Len of 2 -> i.e. you get a blank
space plus a number. I had to get rid of the blank space using the Right(...
3) I got rid of the second Str(...
4) You got to add 1 to the absoluteposition, otherwise you start with a "00"
instead of a "01".

Once you change these, it works fine:

Private Sub Command30_Click()
Dim rs As Recordset
Dim strResult As String
On Error GoTo command30err
Set rs = Forms![sample1]![samplembed].Form.RecordsetClone
With rs
..MoveLast
..MoveFirst
Do Until .EOF
strResult = "0" & Right(str(.AbsolutePosition + 1), 1)
.Edit
![InvoiceSuffix] = strResult
.UPDATE
.MoveNext
Loop
Set rs = Nothing
End With
Exit Sub
command30err:
MsgBox "error!"
Exit Sub
End Sub

Regards,
Al
 
Al - you're right. It worked!!! Thank you so much.

For my configuration I put it in the form's After Update event, and I had to
dim the rs as an Object, because I ran into a data type mismatch when I
tried dimming it as a Recordset.

I was able to use

Set rs = Me.Recordset.Clone

since I'm running the sub from the subform itself.

Also, I actually wanted the numbers to start from 00, so thanks for
mentioning why you added the + 1 to your expression, which told me what I
needed to do.

Nice Sub you wrote for me.

Have a nice Independence Day weekend.

Paul
 
Back
Top