0

Ok I could use some advice please as I am not frequently used to VBA.

I have a form and within that form, I have a subform that is built by a make table. I am in no need to change this.

I however would like to add a sanity check before pressing the upload button that updates SQL server. Where I am getting stuck is on a IIf(Dlookup) function; I am trying to look into a local table and if three fields in the local table match the fields and criteria I have on my subform then check to see if 5 columns within my subform add up to one other field in my subform.

For an example; In the Local table there are the three fields as: "UNIT", "TYPE", "Center"

"Unit" Criteria is as follows: 1212, 1213, 1214 and so on

"TYPE" Criteria is as follows: Shop, Outside, Innerbank

"Center" Criteria is as follows: Electrical shop, machine shop, outside shop.

*** What I need to do is match those fields from the table mentioned above which is called "Ledgend" to the subform on my form and if that Unit, Type, and Center are all present within a row on my subform then calculate(SUM) 5 fields on my subform and if it matches the field called Total on my subform then leave it as is but if it does not then highlight the rows that are different and throw an error msg

If DLookup("Unit", [dbo_TableName]) = "Unit", FORM![SUBFORM_NAME]!Unit & "TYPE", [dbo_TableName]) = "TYPE", FORM![SUBFORM_NAME]!TYPE, & _
    "Center", [dbo_TableName]) = "Center", FORM![SUBFORM_NAME]!Center Then
    subform Sum(Dis + ABS + Center + SS + WRK) 
Else
    MsgBox ("Error")
End If
June7
  • 19,874
  • 8
  • 24
  • 34
Sebastian
  • 13
  • 7

1 Answers1

0

Sum() is not used to add fields, it is used to aggregate records. Syntax for DLookup is completely wrong. Reference to subform makes no sense. Sounds like you need a nested If Then. Following assumes code is behind subform.

If Not IsNull(DLookup("ID", "Ledgend", "Unit & Type & Center'" = Me!Unit & Me!TYPE & Me!Center & "'")) Then
    If Me!Total <> Dis + ABS + Center + SS + WRK Then
        MsgBox "Error"
        'code here to set BackColor property of each control
        Exit Sub
    End If
Else
    Exit Sub
End If

BTW, Ledgend is a misspelling of Legend.

Could use Conditional Formatting rule to manage setting Backcolor property.

June7
  • 19,874
  • 8
  • 24
  • 34
  • June7 Could you perhaps translate this to EXCEL. Perform a vlookup based on certain field names then check throughout the end of the worksheet check the values in those cells and if they do not equal FieldNameX then highlight yellow – Sebastian Feb 03 '20 at 13:28
  • If you have a new requirement then should create a new question. – June7 Feb 03 '20 at 17:32
  • https://stackoverflow.com/questions/60043441/formatting-an-export-file-from-access-to-excel-with-conditional-formatting?noredirect=1#comment106190737_60043441 – Sebastian Feb 03 '20 at 17:42