ACCESS FEED: [MS_AccessPros] Update QueryDef may be causing “Compile error: Method or data member not found”

Kamis, 04 September 2014

[MS_AccessPros] Update QueryDef may be causing “Compile error: Method or data member not found”

This works in Access 97, but I am upgrading to Access 2010 with Oracle backend. It stops on: ".[NAME TYPE] =" in the below module code. Any help is appreciated. Thanks, Mark

'Set NAMEUP = db.OpenQueryDef("BD_NAME_UPD")Set NAMEUP = db.QueryDefs("BD_NAME_UPD")

'CHANGED ABOVE TO DAO Access 2010 from Access 1.0 08262014 mbNAMEUP("STATE_ID") = Closing_InstNAMEUP("NAME_TYPE") = "ACTIVE"'Set NAME_BD = NAMEUP.CreateDynaset()Set NAME_BD = NAMEUP.OpenRecordset()'CHANGED ABOVE TO DAO Access 2010 from Access 1.0 08262014 mb

NAME_BD.Edit NAME_BD.[NAME TYPE] = "HISTORY" ' stops here

NAME_BD.[DATE CHANGE] = DATE_EFFECTIVENAME_BD.UPDATE

Below is query BD_NAME_UPD

PARAMETERS STATE_ID Text ( 255 ), NAME_TYPE Text ( 255 );SELECT DISTINCTROW NAME.[INST NAME], NAME.[NAME ID], NAME.[DATE CHANGE], NAME.[FACILITY ID], NAME.[NAME DESC], NAME.[NAME TYPE], NAME.[STATE ID NUMBER]

FROM NAMEWHERE (((NAME.[NAME TYPE])=[NAME_TYPE]) AND ((NAME.[STATE ID NUMBER])=[STATE_ID]))WITH OWNERACCESS OPTION;

Full module beginning is below

'Validate and Update for State to Federal Merger.Function VALIDATE_ST_FED()

'Dim db As Database, APPLIC_TYPE_ID As Single'Dim NAMEUP As QueryDef, NAME_BD As Dynaset, QNAMESORT As Dynaset'Dim ADDRESSUP As QueryDef, ADDRESS_BD As Dynaset'Dim FININSTUP As QueryDef, FININST_BD As DynasetDim db As DAO.Database, APPLIC_TYPE_ID As SingleDim NAMEUP As DAO.QueryDef, NAME_BD As DAO.Recordset, QNAMESORT As DAO.RecordsetDim ADDRESSUP As DAO.QueryDef, ADDRESS_BD As DAO.RecordsetDim FININSTUP As DAO.QueryDef, FININST_BD As DAO.Recordset 'CHANGED ABOVE TO DAO Access 2010 from Access 1.0 08262014 mb

Set db = CurrentDb()

On Error GoTo VAL_ERROR

APPLIC_PROC = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC PROCESSED]

If APPLIC_PROC = -1 Then

MsgBox "APPLICATION HAS ALREADY BEEN PROCESSED"

Exit FunctionElse

End If

APPLIC_TYPE_ID = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC TYPE ID]

'Debug.Print APPLIC_TYPE_IDIf APPLIC_TYPE_ID = 0 Then

MsgBox "ENTER APPLIC_TYPE_ID" Exit FunctionElse

End If

INST_NAME = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![INST NAME]If IsNull(INST_NAME) Then

MsgBox "ENTER INST NAME!"

Exit Function

ElseEnd If

ADDRESS_LINE_1 = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![ADDRESS LINE 1] CITY = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![CITY]

STATE = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![STATE] ZIP = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![ZIP]

If IsNull(ADDRESS_LINE_1) Then

MsgBox "ENTER ADDRESS_LINE_1!" Exit Function Else End If

If IsNull(CITY) Then

MsgBox "ENTER CITY!" Exit Function Else End If

If IsNull(STATE) Then MsgBox "ENTER STATE!"

Exit Function

Else End If If IsNull(ZIP) Then MsgBox "ENTER ZIP!" Exit Function Else End If

APPLICATION_STATUS = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC STATUS] If IsNull(APPLICATION_STATUS) Then

MsgBox "ENTER APPLICATION STATUS!"

Exit Function Else End If

DATE_EFFECTIVE = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![DATE EFFECTIVE] If IsNull(DATE_EFFECTIVE) Then

MsgBox "ENTER DATE_EFFECTIVE!" Exit Function Else End If

APPLIC_INST_TYPE = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC INST TYPE] If IsNull(APPLIC_INST_TYPE) Then

MsgBox "ENTER 2 CHARACTER INSTITUTION TYPE!" Exit Function Else End If

Closing_Inst = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC OLD STATE ID]

Debug.Print Closing_Inst If IsNull(Closing_Inst) Then MsgBox "SELECT THE CLOSING INSTITUTION!" Exit Function Else End If

INST_TYPE = Forms![FBUSINESS DEV MERGE ST FED UPDATE]![Embedded13].Form![APPLIC INST TYPE] If IsNull(INST_TYPE) Then

MsgBox "ENTER 2 CHARACTER INSTITUTION TYPE!" Exit Function Else End If

If INST_TYPE = "BK" Then Address_Type = "1"

ElseIf INST_TYPE = "CU" Then Address_Type = "2"ElseIf INST_TYPE = "TR" Then Address_Type = "7"ElseIf INST_TYPE = "CI" Then Address_Type = "9"Else Address_Type = "6"End If

'*****************************************************************

' CHANGE NAME TYPE TO "HISTORY" AND UPDATE EFFECTIVE DATE FIELD

'*****************************************************************

'Set NAMEUP = db.OpenQueryDef("BD_NAME_UPD")

Set NAMEUP = db.QueryDefs("BD_NAME_UPD")

'CHANGED ABOVE TO DAO Access 2010 from Access 1.0 08262014 mb

NAMEUP("STATE_ID") = Closing_Inst

NAMEUP("NAME_TYPE") = "ACTIVE"

'Set NAME_BD = NAMEUP.CreateDynaset()

Set NAME_BD = NAMEUP.OpenRecordset()

'CHANGED ABOVE TO DAO Access 2010 from Access 1.0 08262014 mb

NAME_BD.Edit