Hello tcellr777 (Your name? We use first names here :-) )
You need to change these two lines:
NAME_BD.[NAME TYPE] = "HISTORY"
NAME_BD.[DATE CHANGE] = DATE_EFFECTIVE
to use an exclamation mark, not a dot:
NAME_BD![NAME TYPE] = "HISTORY"
NAME_BD![DATE CHANGE] = DATE_EFFECTIVE
At some point (from memory Access 2000) VBA started enforcing the difference between a method or property of a class (.) and a member of a collection (!)
All the best!
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of tcellr777
Sent: Friday, 5 September 2014 05:34
To: MS_Access_Professionals@yahoogroups.com
Subject: [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
NAME_BD.[NAME TYPE] = "HISTORY" ' stops here
NAME_BD.[DATE CHANGE] = DATE_EFFECTIVE
NAME_BD.UPDATE
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar