I have an Access 2003 FE & BE database running on a PC. The source for a Combo box is a simple query that selects all 6 fields from a Sales Source linked table. I have an After Update Event which checks the OldValue from the Combo Box to see if it is a particular type of sales source, as it uses the information to update counters. When a user selects a different value in the Combo Box and so triggers off the After Update event, I get the 3112 error on the OpenRecordset statement. Can anyone offer advice, please? Is there a conflict between the query and the statement? Coding in the after-update event calls a function that contains the following: Function GetSalesSource(SalesSourceId As Long) As String Dim db2 As DAO.Database Dim rst2 As DAO.Recordset Dim pathname2 As String Dim dblocation2 As String pathname2 = Application.CurrentProject.Path dblocation2 = pathname2 & "\Tables\system tables.mdb" GetSalesSource = "N" Set db2 = OpenDatabase(dblocation2) Set rst2 = db2.OpenRecordset("Sales Source") rst2.Index = "SSrc_Id" rst2.Seek "=", SalesSourceId If rst2!SSrc_Description = "Repair Only" Then GetSalesSource = "Y" End If rst2.Close db2.Close End Function
in he line Set rst2 = db2.OpenRecordset("Sales Source") what does "Sales Source" stand for??? if it is a tabe or a query you should use [] because of the space in the name. i.e. "[Sales Source]" Rui "hlamo" wrote: > I have an Access 2003 FE & BE database running on a PC. The source for a > Combo box is a simple query that selects all 6 fields from a Sales Source > linked table. I have an After Update Event which checks the OldValue from the > Combo Box to see if it is a particular type of sales source, as it uses the > information to update counters. > When a user selects a different value in the Combo Box and so triggers off > the After Update event, I get the 3112 error on the OpenRecordset statement. > Can anyone offer advice, please? Is there a conflict between the query and > the statement? > > Coding in the after-update event calls a function that contains the > following: > > Function GetSalesSource(SalesSourceId As Long) As String > > Dim db2 As DAO.Database > Dim rst2 As DAO.Recordset > Dim pathname2 As String > Dim dblocation2 As String > > pathname2 = Application.CurrentProject.Path > dblocation2 = pathname2 & "\Tables\system tables.mdb" > > GetSalesSource = "N" > Set db2 = OpenDatabase(dblocation2) > Set rst2 = db2.OpenRecordset("Sales Source") > > rst2.Index = "SSrc_Id" > rst2.Seek "=", SalesSourceId > If rst2!SSrc_Description = "Repair Only" Then > GetSalesSource = "Y" > End If > > rst2.Close > db2.Close > > > End Function
Thanks Rui, it's the name of a table. I will make the necessary change. "Rui" wrote: > in he line > Set rst2 = db2.OpenRecordset("Sales Source") > > what does "Sales Source" stand for??? > > if it is a tabe or a query you should use [] because of the space in the name. > i.e. "[Sales Source]" > > > Rui > > > "hlamo" wrote: > > > I have an Access 2003 FE & BE database running on a PC. The source for a > > Combo box is a simple query that selects all 6 fields from a Sales Source > > linked table. I have an After Update Event which checks the OldValue from the > > Combo Box to see if it is a particular type of sales source, as it uses the > > information to update counters. > > When a user selects a different value in the Combo Box and so triggers off > > the After Update event, I get the 3112 error on the OpenRecordset statement. > > Can anyone offer advice, please? Is there a conflict between the query and > > the statement? > > > > Coding in the after-update event calls a function that contains the > > following: > > > > Function GetSalesSource(SalesSourceId As Long) As String > > > > Dim db2 As DAO.Database > > Dim rst2 As DAO.Recordset > > Dim pathname2 As String > > Dim dblocation2 As String > > > > pathname2 = Application.CurrentProject.Path > > dblocation2 = pathname2 & "\Tables\system tables.mdb" > > > > GetSalesSource = "N" > > Set db2 = OpenDatabase(dblocation2) > > Set rst2 = db2.OpenRecordset("Sales Source") > > > > rst2.Index = "SSrc_Id" > > rst2.Seek "=", SalesSourceId > > If rst2!SSrc_Description = "Repair Only" Then > > GetSalesSource = "Y" > > End If > > > > rst2.Close > > db2.Close > > > > > > End Function
Rui, I made the change and got the following error Run-time error '3078': The Microsoft Jet database engine cannot find the input table or query "[Sales Source]". Make sure it exists and that its name is spelled correctly. Any ideas? "Rui" wrote: > in he line > Set rst2 = db2.OpenRecordset("Sales Source") > > what does "Sales Source" stand for??? > > if it is a tabe or a query you should use [] because of the space in the name. > i.e. "[Sales Source]" > > > Rui > > > "hlamo" wrote: > > > I have an Access 2003 FE & BE database running on a PC. The source for a > > Combo box is a simple query that selects all 6 fields from a Sales Source > > linked table. I have an After Update Event which checks the OldValue from the > > Combo Box to see if it is a particular type of sales source, as it uses the > > information to update counters. > > When a user selects a different value in the Combo Box and so triggers off > > the After Update event, I get the 3112 error on the OpenRecordset statement. > > Can anyone offer advice, please? Is there a conflict between the query and > > the statement? > > > > Coding in the after-update event calls a function that contains the > > following: > > > > Function GetSalesSource(SalesSourceId As Long) As String > > > > Dim db2 As DAO.Database > > Dim rst2 As DAO.Recordset > > Dim pathname2 As String > > Dim dblocation2 As String > > > > pathname2 = Application.CurrentProject.Path > > dblocation2 = pathname2 & "\Tables\system tables.mdb" > > > > GetSalesSource = "N" > > Set db2 = OpenDatabase(dblocation2) > > Set rst2 = db2.OpenRecordset("Sales Source") > > > > rst2.Index = "SSrc_Id" > > rst2.Seek "=", SalesSourceId > > If rst2!SSrc_Description = "Repair Only" Then > > GetSalesSource = "Y" > > End If > > > > rst2.Close > > db2.Close > > > > > > End Function
I have tried and tested the code. it works for me. Are you sure that table exists? check the name of the table or query. "hlamo" wrote: > Rui, I made the change and got the following error > > Run-time error '3078': > The Microsoft Jet database engine cannot find the input table or query > "[Sales Source]". Make sure it exists and that its name is spelled correctly. > > Any ideas? > > "Rui" wrote: > > > in he line > > Set rst2 = db2.OpenRecordset("Sales Source") > > > > what does "Sales Source" stand for??? > > > > if it is a tabe or a query you should use [] because of the space in the name. > > i.e. "[Sales Source]" > > > > > > Rui > > > > > > "hlamo" wrote: > > > > > I have an Access 2003 FE & BE database running on a PC. The source for a > > > Combo box is a simple query that selects all 6 fields from a Sales Source > > > linked table. I have an After Update Event which checks the OldValue from the > > > Combo Box to see if it is a particular type of sales source, as it uses the > > > information to update counters. > > > When a user selects a different value in the Combo Box and so triggers off > > > the After Update event, I get the 3112 error on the OpenRecordset statement. > > > Can anyone offer advice, please? Is there a conflict between the query and > > > the statement? > > > > > > Coding in the after-update event calls a function that contains the > > > following: > > > > > > Function GetSalesSource(SalesSourceId As Long) As String > > > > > > Dim db2 As DAO.Database > > > Dim rst2 As DAO.Recordset > > > Dim pathname2 As String > > > Dim dblocation2 As String > > > > > > pathname2 = Application.CurrentProject.Path > > > dblocation2 = pathname2 & "\Tables\system tables.mdb" > > > > > > GetSalesSource = "N" > > > Set db2 = OpenDatabase(dblocation2) > > > Set rst2 = db2.OpenRecordset("Sales Source") > > > > > > rst2.Index = "SSrc_Id" > > > rst2.Seek "=", SalesSourceId > > > If rst2!SSrc_Description = "Repair Only" Then > > > GetSalesSource = "Y" > > > End If > > > > > > rst2.Close > > > db2.Close > > > > > > > > > End Function
