Compiling error

[Alain]

Hi to all,

Can anyone explain to me why I am getting a compile error on the following
SELECT statement code:

Forms![Copy of frmOwnedProperties].RecordSource = CurrentDb.Execute_
"SELECT * FROM [Q-Properties(Active)] WHERE IdProperty = " & temp

the error I get is Expected: line number or label or statement or end of
statement

BTW, I am using this code to requery my form in the AfterUpdate event of my
combo to requery the form. my problem is when the user input a new value in
the combo that is not in the list, the NotInList event send me to the next
recordset when it is triggered, is this a normal situation ? Is there a
better way to keep the same recordset on screen after a new input in the
combo ??

Here is the code I use for the NotInList event

Private Sub Combo511_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not in the list "
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add to list or No to
not add to list."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new zoning?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblZoning", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!ZoningType = NewData
rs.update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Many thanks

Alain

 

[Brendan Reynolds]

Well, the immediate problem here is that you're missing a space between
Execute and the underline character. After you fix that, though, you'll
encounter two more problems. The RecordSource property is a String, and the
Execute method doesn't return one (it doesn't return anything) and the
Execute method can only execute an action query, not a select query.

Try instead ...

Forms![Copy of frmOwnedProperties].RecordSource = "SELECT * FROM
[Q-Properties(Active)] WHERE IdProperty = " & temp

.... in other words, just assign the SQL statement directly to the
RecordSource property.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[John Vinson]

Forms![Copy of frmOwnedProperties].RecordSource = CurrentDb.Execute_
"SELECT * FROM [Q-Properties(Active)] WHERE IdProperty = " & temp

the error I get is Expected: line number or label or statement or end of
statement

Irksome little feature: The line-continuation signal is an underscore
*preceded by a blank*, not just an underscore. Change
CurrentDb.Execute_ to CurrentDb.Execute _

and you'll be fine.

John W. Vinson[MVP]

 

[Alain]

Thanks gentlemen for the info,
I guess I'll be a little more cautious when typing

Alain

Forms![Copy of frmOwnedProperties].RecordSource = CurrentDb.Execute_
"SELECT * FROM [Q-Properties(Active)] WHERE IdProperty = " & temp

the error I get is Expected: line number or label or statement or end of
statement

Irksome little feature: The line-continuation signal is an underscore
*preceded by a blank*, not just an underscore. Change
CurrentDb.Execute_ to CurrentDb.Execute _

and you'll be fine.

John W. Vinson[MVP]