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 22 Feb 2010 12:57 "Rick Brandt" <rickbrandt2(a)hotmail.com> wrote in message news:hltseg$6u7$1(a)news.eternal-september.org... > 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. You're right, thanks. I couldn't think of anything else.
From: Ron Weiner on 22 Feb 2010 21:13
I was just looking over this thread and your code again. Even if you do manage to get the sql statement working, the program will bomb on your very next line of code. "Me.ClientName = strClient" will fail! Even though strClient looks like it might be a string variable, it was declared as a DAO.Recordset. You can not assign a recordset as the value of (I am guessing here) a textbox on your form. You will need to: 1) Figure out what value is in Forms!frmJobs!Cust. Based on your Sql statement it MUST be NUMERIC. It can not be Null 2) Write real code that assign's the value in Customer to the (I am still guessing here) a textbox named ClientName. 3) Then you should explicitly close the recordset and destroy the object Rdub "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? >> >> >> . >> |