OpenRecordSet syntax error
in [Forms]
|
Prev: marvendas@gmail.com Kit completo de Solenoides ( solenoid ) + chicotePara Cambio automatico 01M hidramatico Audi A3 Vw Golf gti turbo 16774
Next: Global variables - again - sorry
From: Stuart McCall on 21 Feb 2010 15:23 "Micki" <Micki(a)discussions.microsoft.com> wrote in message news:EA17A338-0409-4520-B54E-63DBFF3E06B4(a)microsoft.com... > No, I have spaces in the VBA (see below); it's just that the debug message > runs everything together. I tried your quotes configuration as well & I'm > still getting a missing operator error. > > "Ron Weiner" wrote: > >> Your are missing a bunch of spaces in your sql statement Change... >> >> "SELECT Customer" & "FROM tblCustomers" & "WHERE CustomerID=" & >> "Forms!frmJobs!Cust" >> >> To >> >> "SELECT Customer FROM tblCustomers WHERE CustomerID=" & >> Forms!frmJobs!Cust >> >> Rdub >> >> >> "Micki" <Micki(a)discussions.microsoft.com> wrote in message >> news:8C6356C9-F419-4536-AB78-00A18EDDF4CD(a)microsoft.com... >> > Unfortuantely, that doesn't work either. The debug error I'm getting >> > is >> > >> > Run-time error '3075': >> > Syntax error (missing operator) in query expression >> > 'CustomerFromtblCustomersWHERECustomerID='. >> > >> > Further help would be greatly appreciated. >> > >> > "Micki" wrote: >> > >> >> I am trying for a simple way to have an unbound control auto-filled >> >> based >> >> on >> >> another field on the form. I keep getting a syntax error no matter >> >> how I >> >> have manipluated my OpenRecordSet statement. Currently, I have >> >> >> >> Private Sub Form_Current() >> >> Dim strClient As DAO.Recordset >> >> >> >> Set strClient = CurrentDb.OpenRecordset ("SELECT Customer" & "FROM >> >> tblCustomers" & "WHERE CustomerID=" & "Forms!frmJobs!Cust") >> >> Me.ClientName = strClient >> >> >> >> End Sub >> >> >> >> Can some one please point out my error? Debug messages from Access do not remove spaces, so no matter that you think you've included spaces, you haven't. If it's true you have a table called tblCustomers that contains fields CustomerID and Customer, and it's also true that you have a frmJobs which contains a control Cust, then Ron's code will function perfectly.
From: Micki on 21 Feb 2010 17:08 I don't know what to tell you, guys. I am still getting a missing operator message. Currently my statement is: Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust) And the debug message is: Run-time Error '3075' Syntax error (missing operator) in query expression 'CustomerID =' "Stuart McCall" wrote: > "Micki" <Micki(a)discussions.microsoft.com> wrote in message > news:EA17A338-0409-4520-B54E-63DBFF3E06B4(a)microsoft.com... > > No, I have spaces in the VBA (see below); it's just that the debug message > > runs everything together. I tried your quotes configuration as well & I'm > > still getting a missing operator error. > > > > "Ron Weiner" wrote: > > > >> Your are missing a bunch of spaces in your sql statement Change... > >> > >> "SELECT Customer" & "FROM tblCustomers" & "WHERE CustomerID=" & > >> "Forms!frmJobs!Cust" > >> > >> To > >> > >> "SELECT Customer FROM tblCustomers WHERE CustomerID=" & > >> Forms!frmJobs!Cust > >> > >> Rdub > >> > >> > >> "Micki" <Micki(a)discussions.microsoft.com> wrote in message > >> news:8C6356C9-F419-4536-AB78-00A18EDDF4CD(a)microsoft.com... > >> > Unfortuantely, that doesn't work either. The debug error I'm getting > >> > is > >> > > >> > Run-time error '3075': > >> > Syntax error (missing operator) in query expression > >> > 'CustomerFromtblCustomersWHERECustomerID='. > >> > > >> > Further help would be greatly appreciated. > >> > > >> > "Micki" wrote: > >> > > >> >> I am trying for a simple way to have an unbound control auto-filled > >> >> based > >> >> on > >> >> another field on the form. I keep getting a syntax error no matter > >> >> how I > >> >> have manipluated my OpenRecordSet statement. Currently, I have > >> >> > >> >> Private Sub Form_Current() > >> >> Dim strClient As DAO.Recordset > >> >> > >> >> Set strClient = CurrentDb.OpenRecordset ("SELECT Customer" & "FROM > >> >> tblCustomers" & "WHERE CustomerID=" & "Forms!frmJobs!Cust") > >> >> Me.ClientName = strClient > >> >> > >> >> End Sub > >> >> > >> >> Can some one please point out my error? > > Debug messages from Access do not remove spaces, so no matter that you think > you've included spaces, you haven't. > > If it's true you have a table called tblCustomers that contains fields > CustomerID and Customer, and it's also true that you have a frmJobs which > contains a control Cust, then Ron's code will function perfectly. > > > . >
From: Stuart McCall on 21 Feb 2010 19:54 "Micki" <Micki(a)discussions.microsoft.com> wrote in message news:DFEBDE1C-5E6F-4144-AD35-5774D229BD96(a)microsoft.com... >I don't know what to tell you, guys. I am still getting a missing operator > message. Currently my statement is: > > Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers > WHERE CustomerID =" & Forms!frmJobs!Cust) <slapping forehead> Try: Set strClient = CurrentDb.OpenRecordset("SELECT CustomerID,Customer FROM tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust) (sometimes VBA reports Missing Operator when it really means Missing Operand)
From: Rick Brandt on 22 Feb 2010 07:15 Stuart McCall wrote: > > "Micki" <Micki(a)discussions.microsoft.com> wrote in message > news:DFEBDE1C-5E6F-4144-AD35-5774D229BD96(a)microsoft.com... >>I don't know what to tell you, guys. I am still getting a missing >>operator >> message. Currently my statement is: >> >> Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM >> tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust) > > <slapping forehead> Try: > > Set strClient = CurrentDb.OpenRecordset("SELECT CustomerID,Customer FROM > tblCustomers WHERE CustomerID =" & Forms!frmJobs!Cust) > > (sometimes VBA reports Missing Operator when it really means Missing > Operand) There is no requirement that a field in the WHERE clause must appear in the SELECT list.
From: Rick Brandt on 22 Feb 2010 07:16
Micki wrote: > I don't know what to tell you, guys. I am still getting a missing > operator > message. Currently my statement is: > > Set strClient = CurrentDb.OpenRecordset("SELECT Customer FROM tblCustomers > WHERE CustomerID =" & Forms!frmJobs!Cust) > > And the debug message is: > > Run-time Error '3075' > Syntax error (missing operator) in query expression 'CustomerID =' The form you are referencing is open right? And there is a numeric value currently in the Cust field/control? |