Subtracting a value on the current record from the value of a previous records issues (2 Viewers)

Hi Evan, I made a mistake with the syntax, hopefully this works for Me.txtSpacing.ControlSource:

Code:
=Abs([sysHop1]-DLookUp("[sysHop1]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]","tblSystemConfiguration","[sysAccountID]=" & [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID] <" & [sysSystemConfigID]),0)))
 
You are awesome, it works!! I have a question just for fun, could you subtract the third entry in the system configuration from the first entry. I was just curious if that was possible and display that result in a different text box. If not no big deal just curious. Thank you again for all your help. You are very skillfull!! :)
 
the syntax gets very messy as you need to find the 1st record than the 2nd record than the 3rd record. Let's just say that's something for the future for you to play around with.
 
Sounds fun! Thanks again for all your help, I have learned alot from the code samples you provided! I am still learning and hope to one day be as good as you!
 
I know this is off topic but I am trying to figure out if there a way to check for duplicate school names but allow up to 3 instances of a school name based off my field systemType

Example

School A has system 1
School B also has system 2

But can you prevent a use from enter the same school and the same system?

like if a user had ented the example above but tries to enter them in again, can you prevent the update since that combo exist?

Here is how I prevent a school from being entered twice.

If DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'") > 0 Then
Beep
Me.lblMessage.Caption = "School already exist, please enter an new school name to continue. "
Cancel = True
Me!accSchoolName.Undo
Me.frmSubSystem.Locked = True
Else
Me.lblMessage.Caption = ""
End If

Is there a way to nest the DCount?
 
Your Dcount statement can have multiple criteria:

DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "' And [System]='" & Me.System & "'")
 
I keep getting a type mismatch error

I have a combo box for sytem type but its record source is accSystemType

Heres what I tried, If DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'" And "[accSystemType] ='" & Me.cboSystemType & "'") > 0

The setup is the user selects the system type first then enters the school name. The code is in the before update event in the schoolname text box.
 
DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'" And "[accSystemType] ='" & Me.cboSystemType & "'")

This assumes accSystemType is text. If it is an integer remove the single quotes:

Code:
DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'" And "[accSystemType] =" & Me.cboSystemType)

Text is always surrounded by single quotes ' ', dates by pound signs # # but numbers by nothing.
 
Still getting type miss match. The accSystemType is a text, I just used a combo box on the form because there are only 3 systems type. When I load the form the combo box rowsource type is value list and the rowsourse has the 3 system names.
 
I just noticed another syntax error, an extra quotation mark before [accSystemType]:

Code:
DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'" And [accSystemType] ='" & Me.cboSystemType & "'")
 
It seems to give me an expect expression error when I take the double quotations out

If DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "'" And [accSystemType]='" & Me.cboSystemType & "'") > 0 Then
 
Evan, there still are too may quotes, this time with Me.accSchoolName & "'":

Code:
DCount("*", "tblAccount", "[accSchoolName]='" & Me.accSchoolName & "' And [accSystemType]='" & Me.cboSystemType & "'")
 
That did it, lesson learned I need to pay closer attention to quotation marks with multiple criteria in a Dcount Statment. :D Thank for your help again! I now have a better understanding of Dcount, DlookUp and DMax. Thanks again!!
 
:banghead: Syntax can make you go crosseyed! :banghead:
 
The database is working great, the only thing the users want now is the ability to subtract the third system network number from the first network number in the system configuration subform within the account. I am not sure if this is possible or how to accomplish this goal. I do know I can create a different field to hold the base 3 - base 1 network number result but not sure how to calculate it.
 
Go ahead and attach your latest DB. Are base 3 - base 1 network number on the same record?
 
The code is located in the frmSubSystem. I have not created another field to hold the base number. Each system configuation for an account is a new record in the datasheet. The main code for the current calculation in in the field sysNetworkNumber before update event in the code editor. I still need to subtract from the previous record but on the 3rd entry in base configuration, i need to subtract from first record in the system configuration. I havented created a field to hold that 3rd base spacing result yet. I am in our busy season for sytem repairs so I havent had time to work on the database. I really appreciate your help, you have been an hugh help and inspiration to this project.
 

Attachments

Gemma's earlier comments are extremely important and I dont see in the thread that the problem is being addressed directly.

When entering data, the order of the data is meaningless. There is no previous record. (Actually, there can be more than one "previous" record depending on how you look at it, which is the real problem that can lead to incorrect values.) The "right" way to do this is to fix your data entry forms so that they keep their totals using VBA and that way, the "previous" record can be remembered in code.

In SQL, ALL repeat ALL queries presume that you are doing SQL or DOMAIN AGGREGATE functions for which the set theory says that everything happens at once. Which means, in practical terms, that when SQL is involved, there is no order.

However, code underneath the data entry form sees the data entry events chronologically, for which there is an implied TIME order that allows you to take the totals and keep them in VBA-based variables. Remember if you do that, though, that if you exit the form, the totals vanish in VBA, so anything you wanted to do to records based on that total has to be done already.
 
The Doc_Man has a good point. Previously you wanted to reference the previous record now you want to reference two records previous. But, if you are always in control of your record entry it should be fine.

You need to first find the ID of the previous record and then use that to find the ID of the next previous record. Than use that ID to do your math.
Code:
Dim PrevSysConID, SndPrevSysConID As Long, SysHopSpacing2 As Integer
'previous record
PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)
'next previous record
SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & PrevSysConID), 0)
'3rd network subtracted from the first
SysHopSpacing2 = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID))
 
Cool! Thanks! Ill try this soon and see how it works!
 

Users who are viewing this thread

Back
Top Bottom