Set value of field = to record number in form

[oihjk]

I'm wanting to know if it's possible to set the value in a control on a form equal to the record number that it is on.
I have a Master form and then a subform on it setup as a columnar form. You know how the records on the subform are number per each record on the master form, like master form record 1 might have 3 records related on the subform. Is it possible to put the value of one of the fields on the subform equal to its record number on the subform?

If this isn't possible how could I count up in the subform and then start over when a new record is added in the master form?

Thanks for the help

 

[oihjk]

Some ideas but can't get them to work

I've searched the forum and microsoft for somethings that may work. Forum definitely got close but I can't get it to work... http://www.access-programmers.co.uk...ighlight=set value record number&pagenumber=1 ....came pretty close but I can't get the code to work for me. What cosmos75 is wanting is basically what I'm trying for. I tried to adapt the following code to my needs and got an error.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SubTaskID = GetLastSubTaskID(Me.Parent!TaskID)
End Sub

Private Function GetLastSubTaskID(TaskID As Long) As Long
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "Select Top 1 SubTaskID From tblSubTask Where [TaskID]=" & TaskID & " Order by [SubTaskID] Desc", Application.CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rst.RecordCount = 0 Then
GetLastSubTaskID = 1
Else
GetLastSubTaskID = rst!SubTaskID + 1
End If
End Function


This is the how I changed it I tried to do it to an on click procedure just as a test, I would also like to know the best procedure to attach this to:

Private Sub Command125_Click(Cancel As Integer)
Me.VarianceNumber = GetLastVarianceNumber(Me.Parent!AFENumber)
End Sub

Private Function GetLastVarianceNumber(AFENumber As Long) As Long
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "Select Top 1 VarianceNumber From VarianceLogInput Where [AFENumber]=" & AFENumber & " Order by [VarianceNumber] Desc", Application.CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rst.RecordCount = 0 Then
GetLastVarianceNumber = 1
Else
GetLastVarianceNumber = rst!VarianceNumber + 1
End If
End Function


The error I got was this:

The expression OnClick you entered as the event property settingproduced the following error: Procedure declaration does not match description of event or precedure having the same name.

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

I don't have too great a knowledge of VBA but I'd appreciate any help. If you can think of a better way for this let me know.

--Something else I thought might work is setting up a field that's value is always one and then sum all the fields that have the certain AFENumber from my parent form. After I get this value I add one and then that value goes into my VarianceNumber field.
I know I could do a macro to filter the records that only contain the AFENumber that is active but I'm not sure how to send that from the form to the code. After I get that to work then I could use query to sum the values of the filtered forms. Someone help on this please. This seems like it would be easier, let me know if you have any ideas.
Thanks
Eric

 

[Pat Hartman]

What you are trying to do is similar to what the people who ask how to generate their own autonumbers are trying to do. The difference is that you don't want to number over the whole set of data but over a part of it.

You will need to use DMax() to obtain the highest current number NOT loop through the recordset looking for the last record.

Search the archives here for DMax() and autonumber and you should find code samples that you can adapt.

 

[oihjk]

What I've got so far

First, Thanks for that tidbit on dmax.
I put this in the on click event of a button just for tests I also tried the after update event of the variancenumber box.

Private Sub Command125_Click(Cancel As Integer)
Me.VarianceNumber = DMax("VarianceNumber", "QryVarianceLogForm", "AFENumber = " & Me.AFENumber & "'") + 1
End Sub

This is the code I'm working with. It is returning the following error: Procedure declaration does not match description of event or procedure having the same name.

Question: The variancenumber field is from the Varianceloginputfrm form. AFENumber field is on the form AFENumber. Varianceloginputfrm form is a manually inserted sub-form of the AFENumber form (meaning I didn't use the wizard to do it). Would the cause of the error be that I need to specify which form the code is referring to?

Thanks
Eric

 

[Pat Hartman]

To refer to fields on the current form use - Me.ControlName
To refer to fields on the parent form from a subform use - Me.Parent.ControlName
To refer to fields on another open main form use - Forms!YourFormName!YourControlName
To refer to fields on anogher open subform use - Forms!YourFormName!YourSubformName.Form!YourControlName

 

[oihjk]

Private Sub PO_Number_AfterUpdate()
Me.Variance_Number = DMax("Variance_Number", "QryVarianceLogForm", "AFENumber = " & Me.Parent.AFENumber & "'") + 1
End Sub

IT gives me another error:
Run-time error '3075':

Syntax error (missing operator) in query expression 'AFENumber = 3R139A.1".

It also gives an error when you try to go to an AFENumber and add a variance to it. And after you debug it, it gives another.

3R139A.1 is what the current AFENumber is. Does anyone see anything wrong with my code? Do I need to do some error checking for when the current AFENumber isn't listed in the VarianceLogInput Table. For example If the max is 0 then Me.Variance_Number = 1. Also what about the single quote in quotations?


I've attached part of my database if anyone wants to take a look.

 

[Pat Hartman]

You have a trailing quote but not a leading quote.

Me.Variance_Number = DMax("Variance_Number", "QryVarianceLogForm", "AFENumber = '" & Me.Parent.AFENumber & "'") + 1

 

[oihjk]

One last thing - I hope

Hey Pat I thank you for your help! I've learned a lot just from your replies. I understand; however, I've got a long way to go. I was trying it with the leading quote, but I was putting before the quotation instead of after. Needless to say it didn't quite work and I'm too naive to understand the problem.

Now:
It works if I already have a record related to the current AFENumber in the VarianceLogInput table; However, It won't enter a number in automatically for a new AFENumber. Like if there are no records in the varianceloginput table for the current AFENumber then it won't start at one(it won't start at all actually). I tried the following code to get it to check it a little and when the code runs it takes the current value out and sets it to somewhere less than or equal to 0 or maybe null (just blank really). When I do run this code the first character I type works fine (meaning it sets the variance number to 1) then I type the second character and it sets it to null or something, because it's going to the else part of the code and since there are no other records to check against it sets it to null I suppose. This is the code.

Private Sub PO_Number_Change()
If Me.VarianceNumber = <= 0 then
Me.VarianceNumber = 1
Else
Me.VarianceNumber = DMax("VarianceNumber", "VarianceLogInput", "VarianceLogInput.AFENumber = '" & Me.Parent.AFENumber & "'") + 1
End If
End Sub

I also tried this:

Private Sub PO_Number_Change()
Me.VarianceNumber = DMax("VarianceNumber", "VarianceLogInput", "VarianceLogInput.AFENumber = '" & Me.Parent.AFENumber & "'") + 1
End Sub

Private Sub PO_Number_Enter() ' this is so that it automatically enters the AFENumber it didn't work at all without this
Me.AFENumber = Me.Parent.AFENumber
End Sub

Private Sub PO_Number_Exit(Cancel As Integer)
If Me.VarianceNumber = Null Then ' I also tried <= 0 here
Me.VarianceNumber = 1
End If
End Sub

Any Ideas ? Maybe the events procedures I'm using aren't quite correct, I've tried several and they've turned out the same.

Thanks,
Eric

 

[Pat Hartman]

The code you showed as being in the AfterUpdate event of the PO number should have worked once you fixed the syntax error. You cannot use the Change event because it fires for EVERY character you type into the field.

 

[oihjk]

Can't quite get it

I put that code in and it does work except in the case when a new AFENumber is being entered where there are no related record to get a dmax on. In this case the DMAX () + 1 returns a null.
I tried this but it gives me the Invalid use of null (error 94).
When I debug it highlights the myTest = Dmax() line

Private Sub PONumber_AfterUpdate()
Dim myTest As Integer
myTest = DMax("VarianceNumber", "VarianceLogInput", "AFENumber = '" & Me.Parent.AFENumber & "'")
If myTest = Null Then
Me.VarianceNumber = 1
Else
Me.VarianceNumber = DMax("VarianceNumber", "VarianceLogInput", "AFENumber = '" & Me.Parent.AFENumber & "'") + 1
End If
End Sub

Here is my database again I've changed it some...if anyone has time can you take a look and see if you know how you can make it start with one even if there are no records related to the new AFENumber.

 

[oihjk]

Finally

Got it! If you say it should've worked the other way, I believe you, most likely something I did wrong. None the less, I got it to work here's how if anyone's interested.

Private Sub PONumber_AfterUpdate()
Dim myTest As Integer
myTest = Me.RecordsetClone.RecordCount
If myTest <= 0 Then
Me.VarianceNumber = 1
Else
Me.VarianceNumber = DMax("VarianceNumber", "VarianceLogInput", "AFENumber = '" & Me.Parent.AFENumber & "'") + 1
End If
End Sub

If anyone sees anything that could cause problems in the future, let me know. Special Thanks to Pat couldn't have done it without your input. Hopefully I can start lending a hand to folks in need of help on the forum...nothing advanced of course.

Eric