Subtracting a value on the current record from the value of a previous records issues

evanark

Registered User.
Local time
Today, 14:55
Joined
Jan 4, 2013
Messages
69
I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

This is how I generate the sysBaseNumber

sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1

I am using DMax so I can keep the subform records with the parent form record.

My results are inconsistant:

This is what I want the subform to do when subtracking from the previous record

syshop1 sysHopSpacing
5
20 15
30 10

Instead I get a result that does not make sense like this:

sysHop1 sysHopSpacing
5
20 15
30 4

I cant trace it down, any suggestions???

Thanks
 
It's sounds a little bit like your re-inventing the wheel. I'm wondering why you are not using the Main Forms table PK (which should be an autonumber) and using form Linking to assign the FK for the subform. This would replace your sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1.

And then use the PK for the subform table (also an autonumber) so you would redo

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

to now contain the subform table PK:

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[subTblPK]=" & Me!PK - 1))

Just my thoughts without really addressing your current problem but I think you would not have any problems using PK/FK autonumbers etc.
 
I do have a PK linked to the subform.

Here's what I am working with. I have two tables Account and SystemConfiguation.

On my entry form, the Account is the parent and systemConfiguration is the subform.

The subform is presented in datasheet view while the main form is single view.

The accAccount is the PK for the account table, sysAccount is the FK for the SystemConfiguration table. The SystemConfiguration table has its own PK called sysSystemConfigID.

The tables are linked via account ID.

I enter the account information then I enter the system configuration, the system configuration can have multiple records tied to on account. The issue is when subtracting a value from the previous records, how can I contain the calculation in the system configuration for that account with out the first system added from subtracting the previous record that is tied to another account? Also If was to search for a existin account and add another system, how can I still contain the calculation for that system with that account?

When a customer buys a system, we configure it and we need to calculate the spacing of the network number so if its less than 4, we have to select different network number. The database will help us coordinate network numbers between accounts by automataically subtracking the current number from the previous number to let us know the spacing is acceptable. An Account can have up to five systems. I hope this make sense. I have been working on this problem for awhile. This is the last function need to complete the application.
 
Are your PK fields manual entry and not autonumber? That is OK if you need it that way, but I would then add additional fields that are ID fields autonumber set to Index No duplicates. It just makes things easier to keep track of if you have autonumber fields.

It seems to need to determine the previous record based on it having the same sysAccount (accAccount ) and then the Max sysSystemConfigID less than the current.

Dlookup("[sysHop1]","systemConfiguration","[sysAccount]=" & Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","systemConfiguration","[sysSystemConfigID] <" & Me.sysSystemConfigID),0))

But I'm assuming sysSystemConfigID is an autonumber value. I'm also assuming accAccountID is a number.
 
Yes accAccountID and sysSystemConfigID are autonumbers. That works!! Thank you very much!!! One question, how can this be applied to an existing record and continue the calculation if another system is added? Do I requery the subform before the calculation?
 
One question, how can this be applied to an existing record and continue the calculation if another system is added? Do I requery the subform before the calculation?

Are you saying you want to update existing records? I thought this was just to display a value, not store it? Maybe I don't quite understand so the rest of this post may be unnecessary.

If you want to update records, what you could do is place your code in the On Current Event for your subform and manually cycle through the records which will trigger your code. If you have a lot of records in your Main Account form and a lot of records in your subform systemConfiguration, you could set up a button on your Main form and put code to cycle through all the records something like:

Code:
'This is on your Main form
DimDim X As Integer, Y As Integer
Me.Recordset.MoveLast
Me.Recordset.MoveFirst
For X = 1 To Me.Recordset.RecordCount
    Me.systemConfiguration.MoveLast
    Me.systemConfiguration.MoveFirst
    For Y = 1 to Me.systemConfiguration.Recordset.Recordcount
    Me.systemConfiguration.MoveNext
    Next Y
Me.Recordset.MoveNext
Next X
 
Last edited:
whenever someone thinks they need to do something that requires a record to use information from a "previous" record, I am inclined to review this very carefully. The underlying problem is, is that in a database there is no such thing as "a previous record"

databases are good at generating "totals" of domains - ie the total of all the records on your form. They are not good at all at processing those records sequentially (as far as forms or queries go)

Noiw, reports are different - processing records sequentially in a REPORT is easy, because a report is a sequential entity.

for a query or form, however, the concept of sequential processing becomes most awkward, and is so "unnatural" - that the question needs to be asked - can the process be redesigned so that you do not need to solve the issue of processing records sequentially - or maybe even, how can I redesign the process to avoid the need to process items sequentially.
 
I changed the Hop1 and hop spacing to text boxes

txtHop and txtSpacing

I am getting #Name? text box instead of the calculated value

this is in the control source for txtSpacing

=Abs([Me].[txtHop]-DLookUp("[Me].[txtHop]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmTempestCoordination]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & [Me].[sysSystemConfigID]),0)))
 
Last edited:
When you change control names it can mess up existing formulas. Try this:

=Abs(Me.txtHop-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmTempestCoordination]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & Me.sysSystemConfigID),0)))
 
I tried the code but still get a #Name? in txtSpacing. I suppose I need to store the hop number so the current record can subtract from the last record. Is it possible to do a dlookup if the value in a text box is unbound?
 
Somewhere in the formula it is referencing the control incorrectly. If you want to attach your DB I can troubleshoot a lot better.
 
ok, you might have relink the tables, the data base is a split design. I cleaned the junk data, but you can enter data for troubleshooting. The start form is a login. Use the username User, and password User1 . This will help as some form has on load events that need to check security level.
 

Attachments

Could you tell which Query or form you are attempting to add the formula? Thanks.
 
So, I see that you have the ability to access the subform from multiple main forms. When referring to controls on the main form from a formula used to define a control on the subform you need to be specific.

For use with your form frmSearchSchools, the syntax is:
Code:
=Abs([sysHop1]-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & [sysSystemConfigID]),0)))

But for use with your form frmTempistCoordination, the syntax is:
Code:
=Abs([sysHop1]-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmTempestCoordination]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & [sysSystemConfigID]),0)))

What you can do is on the On Open event for your form frmSubSystem you can test to see what Main form is used and assign the proper formula to your txtSpacing control:

Code:
If Me.Parent.Name = "frmTempestCoordination" Then
Me.txtSpacing.ControlSource = "=Abs([sysHop1]-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmTempestCoordination]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & [sysSystemConfigID]),0)))"
ElseIf Me.Parent.Name = "frmSearchSchools" Then
Me.txtSpacing.ControlSource = "=Abs([sysHop1]-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysSystemConfigID] <" & [sysSystemConfigID]),0)))"
End If

If you have additional Main forms that could be used with this subform you would have to do additional testing.
 
Yeah, the TempestCoordination form is the data entry, SearchSchools form allows the user enter a school name and retrieves the data. From that form the user can add anotehr system to the subform.

I pasted the code in the on open event and I get an compile error

Expected: end of statement.
 
Sorry, my bad, syntax trouble. Try this:

Code:
If Me.Parent.Name = "frmTempestCoordination" Then
Me.txtSpacing.ControlSource = "=Abs([sysHop1]-DLookUp(""[sysHop1]"",""tblSystemConfiguration"",""[sysAccountID]="" & [Forms]![frmTempestCoordination]![accAccountID] & "" And [sysSystemConfigID]="" & Nz(DMax(""[sysSystemConfigID]"",""tblSystemConfiguration"",""[sysSystemConfigID] <"" & [sysSystemConfigID]),0)))"
ElseIf Me.Parent.Name = "frmSearchSchools" Then
Me.txtSpacing.ControlSource = "=Abs([sysHop1]-DLookUp(""[sysHop1]"",""tblSystemConfiguration"",""[sysAccountID]="" & [Forms]![frmSearchSchools]![accAccountID] & "" And [sysSystemConfigID]="" & Nz(DMax(""[sysSystemConfigID]"",""tblSystemConfiguration"",""[sysSystemConfigID] <"" & [sysSystemConfigID]),0)))"
End If
 
Thanks a Million!! You are awesome! I really appreciate your help! It works like a champ!
 
Whew! Sometimes just hearing it from the horse's mouth just ain't enough, ya got to see the whole horse! :)
 
How can I go back and add a new system to an account and still calculate the spacing from the previous record? When I add a new system to the account, the spacing text box is blank is blank.

I used this on Current event in the subform

Me.txtSpacing.ControlSource = "=Abs([sysHop1]-DLookUp(""[sysHop1]"",""tblSystemConfiguration"",""[sysAccountID]="" & [Forms]![frmSearchSchools]![accAccountID] & "" And [sysSystemConfigID]="" & Nz(DMax(""[sysSystemConfigID]"",""tblSystemConfiguration"",""[sysSystemConfigID] <"" & [sysSystemConfigID]),0)))"
 

Attachments

  • Test Screen.JPG
    Test Screen.JPG
    64.9 KB · Views: 122
Last edited:

Users who are viewing this thread

Back
Top Bottom