UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Compile error - syntax error    
 
   
bjh29
post Mar 1 2007, 11:24 AM
Post#1



Posts: 117
Joined: 5-February 07
From: Michigan


Any help is appreciated here. I am trying to prevent a duplicate entry in my table. I am getting "compile error - syntax error" I believe the problem is in my SELECT
Thanks!
Option Compare Database
Option Explicit
Private Sub ParentID_BeforeUpdate(Cancel As Integer)
SELECT Tasks.SessionID, Tasks.ClassTimeID, ParentVolunteer.ParentID FROM Tasks INNER JOIN ParentVolunteer ON Tasks.TaskID = ParentVolunteer.TaskID;
If (((Tasks.SessionID) = [Forms]![Tasks]![SessionID]) And ((Tasks.ClassTimeID) = [Forms]![Tasks]![ClassTimeID]) And ((ParentVolunteer.ParentID) = [Forms]![Tasks]![ParentVolunteer subform].[Form]![ParentID])) Then
MsgBox "This parent already assigned a task for this hour"
Else
End If
End Sub
Go to the top of the page
 
MattJ
post Mar 1 2007, 11:34 AM
Post#2


UtterAccess VIP
Posts: 4,621
Joined: 12-November 03
From: North Carolina, USA


It is indeed. You cannot use a select statement in this manner. I suggest using the DCount function. THe exact syntax will need to be tweaked depending on the data types of your fields.
If Dcount("*","Tasks","SessionID = " & [Forms]![Tasks]![SessionID] & " AND ClassTimeID = " & [Forms]![Tasks]![ClassTimeID] & AND ParentID = " [Forms]![Tasks]![ParentVolunteer subform].[Form]![ParentID] & ") > 0 Then
MsgBox "This parent already assigned a task for this hour"
Else
End If
Go to the top of the page
 
vtd
post Mar 1 2007, 12:00 PM
Post#3


Retired Moderator
Posts: 19,667
Joined: 14-July 05



The Select statement is an SQL String which can only be processed by the ACCDB Engine, not VBA.
That you can do is to construct a Query with the following string:
CODE
SELECT Tasks.SessionID, Tasks.ClassTimeID, ParentVolunteer.ParentID
FROM Tasks INNER JOIN
ParentVolunteer ON Tasks.TaskID = ParentVolunteer.TaskID
WHERE (Tasks.SessionID = [Forms]![Tasks]![SessionID])
AND (Tasks.ClassTimeID = [Forms]![Tasks]![ClassTimeID])
AND (ParentVolunteer.ParentID = [Forms]![Tasks]![ParentVolunteer subform].[Form]![ParentID])

then save this Query with the name, says, Query1. You can then use the DCount() function in your code as follows:
CODE
Private Sub ParentID_BeforeUpdate(Cancel As Integer)
  
  If DCount("*", "Query1") > 0 Then
    MsgBox "This parent already assigned a task for this hour"
  End If
  
End Sub
Go to the top of the page
 
bjh29
post Mar 1 2007, 12:48 PM
Post#4



Posts: 117
Joined: 5-February 07
From: Michigan


Ah.. You figured out my train of thought...I originally created a qry and went to sql view and copied the code into my module. I have done the reverse for debugging strings - go figure, it doesn't quite work both ways.
Thanks both..I have learned a good lesson today.
Another thought. This works OK. but still puts in the duplicate record which may be what my user wants, but can I change MsgBox to allow user to say "no - I don't want to create a duplicate" ?
Thanks again.
CODE
Option Compare Database
Option Explicit
Private Sub ParentID_BeforeUpdate(Cancel As Integer)
If DCount("*", "ParentTasksqry") > 0 Then
MsgBox "Parent is already assigned a task for this hour"
End If
End Sub
Go to the top of the page
 
balaji
post Mar 1 2007, 03:55 PM
Post#5


UtterAccess VIP
Posts: 5,637
Joined: 23-January 03
From: Chicagoland, USA


Try the following:

CODE
Private Sub ParentID_BeforeUpdate(Cancel As Integer)
im response as integer
If DCount("*", "ParentTasksqry") > 0 Then
response = MsgBox ("Parent is already assigned a task for this hour.  Do you still want to add?",vbyesno)
if response <> vbyes then
me.undo
end if
End If
End Sub
Go to the top of the page
 
bjh29
post Mar 1 2007, 05:12 PM
Post#6



Posts: 117
Joined: 5-February 07
From: Michigan


Perfect! Thanks!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 03:48 PM