Help with Function.

[Anthony]

Hi I have the following Function, Which i know if probably a VERY long way
around, Could anyone help me with this....

The WriteAuditUpdate Does not work.

The idea of this is to search for clients that are 7 days or over in their
payment (Which is [qry7DaysBehind-z])

I then want to update the Table [tblCustomers].AccountinDefault to -1 for
any clients contained in the query [qry7DaysBehind-z].

But I also want to write to an Audit table that this has happened for each
client. This part isn't working.. But the rest is.

Any assistance would be greatly appreciated.
Thanks in advance,
Anthony.


The code is:

Function SevenDayDefault()
On Error GoTo Err_SevenDayDefault

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim SQL2 As String
Dim SQL4 As String
Const txtTableName = "Auto"

Set db = CurrentDb
Delete_7DayDefault
SQL4 = "SELECT [CustomerID],[CustomerIDMAIN],[ContractNumber] " & "INTO
7DayDefault " & "FROM [qry7DaysBehind-z]"

DoCmd.RunSQL SQL4

DoCmd.OpenForm "7DayDefault", acNormal, , , , acHidden

SQL2 = "UPDATE TblCustomer " & "INNER JOIN qry7DaysBehind ON
TblCustomer.ContractNumber = qry7DaysBehind.ContractNumber " & "SET
TblCustomer.AccountinDefault = -1 " & " WHERE (((TblCustomer.ContractNumber)
In ([qry7DaysBehind]![ContractNumber])));"

Set qdf = db.CreateQueryDef("", SQL2)

For Each prm In qdf.Parameters

WriteAuditUpdate txtTableName, Forms![7DayDefault].CustomerMainID,
Forms![7DayDefault].CustomerID, 0, "InDefault-Auto", "0", "-1"

prm.Value = Eval(prm.Name)

Next prm

qdf.Execute dbFailOnError

Set qdf = Nothing
Set db = Nothing

DoCmd.Close acForm, "7DayDefault"
Exit_SevenDayDefault:
Exit Function

Err_SevenDayDefault:
MsgBox Err.Description
DoCmd.Close acForm, "7DayDefault"
Resume Exit_SevenDayDefault

End Function

 

[Guest]

Hi I have the following Function, Which i know if probably a VERY long way
around, Could anyone help me with this....

The WriteAuditUpdate Does not work.

The idea of this is to search for clients that are 7 days or over in their
payment (Which is [qry7DaysBehind-z])

I then want to update the Table [tblCustomers].AccountinDefault to -1 for
any clients contained in the query [qry7DaysBehind-z].

But I also want to write to an Audit table that this has happened for each
client. This part isn't working.. But the rest is.

Any assistance would be greatly appreciated.
Thanks in advance,
Anthony.


The code is:

Function SevenDayDefault()
On Error GoTo Err_SevenDayDefault

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim SQL2 As String
Dim SQL4 As String
Const txtTableName = "Auto"

Set db = CurrentDb
Delete_7DayDefault
SQL4 = "SELECT [CustomerID],[CustomerIDMAIN],[ContractNumber] " & "INTO
7DayDefault " & "FROM [qry7DaysBehind-z]"

It looks like you are deleting the table, then recreating it. I would use a
Delete query and an Append query.

DoCmd.RunSQL SQL4

DoCmd.OpenForm "7DayDefault", acNormal, , , , acHidden

SQL2 = "UPDATE TblCustomer " & "INNER JOIN qry7DaysBehind ON
TblCustomer.ContractNumber = qry7DaysBehind.ContractNumber " & "SET
TblCustomer.AccountinDefault = -1 " & " WHERE (((TblCustomer.ContractNumber)
In ([qry7DaysBehind]![ContractNumber])));"

What is the difference between the query "[qry7DaysBehind-z]" and
"[qry7DaysBehind]"?? Would you post the SQL of both queries?

Set qdf = db.CreateQueryDef("", SQL2)

For Each prm In qdf.Parameters

WriteAuditUpdate txtTableName, Forms![7DayDefault].CustomerMainID,
Forms![7DayDefault].CustomerID, 0, "InDefault-Auto", "0", "-1"

What is the code for the sub "WriteAuditUpdate"?

prm.Value = Eval(prm.Name)

Next prm

qdf.Execute dbFailOnError

Set qdf = Nothing
Set db = Nothing

DoCmd.Close acForm, "7DayDefault"
Exit_SevenDayDefault:
Exit Function

Err_SevenDayDefault:
MsgBox Err.Description
DoCmd.Close acForm, "7DayDefault"
Resume Exit_SevenDayDefault

End Function

Also, what is the recordsource for the form "7DayDefault"? If it is a query,
would you also post the SQL for it?

I want to get hte field names correct....