DLookup problem

[AndyCompanyZ]

I am trying to write some code that will update a table if there is no record currently there.
I am using Dlookup to check the table tblCourseDelegate to see if there is a delegate who is scheduled on an event and if not then to schedule them. If they are already scheduled then I want a message box to alert the user. The query I mention works fine in isolation but the code I have throws up an "Expected: list seperator or )" error and points to the EventID after the AND (highlighted in red).

Private Sub btnScheduleQuery_Click()
On Error GoTo btnScheduleQuery_Click_Err
If DLookup("DelegateID","CourseDelegate","DelegateID="& DelegateID &") AND ("EventID","CourseDelegate","EventID="& EventID &") Then
MsgBox "This delegate id already scheduled for this course"
Else
DoCmd.OpenQuery "EventDel", acViewNormal, acEdit
DoCmd.RunSQL "UPDATE Event SET Event.PlacesAvailable = Event.PlacesAvailable -1 WHERE EventID=forms!CoursesDelegate.cmboEvent.form!EventID;"

btnScheduleQuery_Click_Exit:
Exit Sub
btnScheduleQuery_Click_Err:
MsgBox Error$
Resume btnScheduleQuery_Click_Exit

End Sub

Does anyone know where I'm going wrong please

 

[AndyCompanyZ]

Sorted the syntax. But now it doesn't work because it has "wrong number of arguements or invalid property assigment" and points to DLookup in the code window. Can I not call a Dlookup in a click button.

 

[DCrake]

Invalid syntax

If DLookup("DelegateID","CourseDelegate","DelegateID= "& DelegateID &"[B][COLOR="Red"])[/COLOR][/B] AND ("EventID","CourseDelegate","EventID="& EventID &") Then

valid syntax

If DLookup("DelegateID","CourseDelegate","DelegateID= " & DelegateID & " AND EventID=" & EventID ) Then

 

[AndyCompanyZ]

Thanks yes I did that just before you posted but now I have another problem see above

 

[DCrake]

DoCmd.RunSQL "UPDATE Event SET PlacesAvailable =  -1 WHERE EventID=
" & Me("Event ID")

 

[AndyCompanyZ]

Tried that but gives me epected end of statement error

 

[DCrake]

DoCmd.RunSQL "UPDATE Event SET PlacesAvailable =  -1 WHERE EventID=
" & [B]Me("Event ID")[/B]

What is the actual control name?

 

[AndyCompanyZ]

The controlname is EventID from a combobox (cmboEvent) that is part of a cascading combobox (it is the last one in the series). It wasn't working right and you might have solved that because it asked me for a parameter before but worked if i put a valid value in the msgbox that came up. I can see that this would perhaps solve the parameter box problem.

This is the line I now have
DoCmd.RunSQL "UPDATE Event SET Event.PlacesAvailable = Event.PlacesAvailable -1 WHERE EventID=" &("Me.EventID");"
It points to the ;" and I have tried other combinations like "; and ; but still the same

 

[DCrake]

What kind of query is eventDel?

Realistically I would code it as follows

Dim Rs as DAO.Recordset

Set Rs = CurrentDb.Openrecordset("Select * From Events Where EventID = " & Me.EventID)

If Not Rs.EOF and Not Rs.BOF Then
   Rs.Edit
   Rs("PlacesAvailable") = Rs("PlacesAvailable") - 1
   Rs.Update
Endif
Rs.Close
Set Rs = Nothing

 

[AndyCompanyZ]

The query is :
INSERT INTO EventDelegate ( DelegateID, EventID, Scheduled )
VALUES ([Forms]![CoursesDelegate]![DelegateID], [Forms]![CoursesDelegate]![cmboEvent], 1);
Which is to put the DelegateID and EventID that have been selected through the comboboxes into the EventDelegate table and to mark as scheduled (There are other options but 1 is scheduled). This all works and is needed as I need to record instances of the delegates being scheduled on courses even if that status changes later (for recording purposes). I thought about the recordset solution you mention but I have no experience with writing to recordsets so wanted to avoid it. If I went down that route can I still call it from the same button click event or would it have to be another routine.

 

[DCrake]

You would just substitute it for the RunSQL section.

 

[AndyCompanyZ]

I tried it but not sure if it works or not as I can't get past the compile error on the Dlookup "Wrong number of arguments or invalid property assignment" which highlights the DLookup in blue and points to Private Sub btnScheduleQuery_Click() in yellow.

 

[DCrake]

Show me your DLookup statement

 

[AndyCompanyZ]

This is the whole routine:

Private Sub btnScheduleQuery_Click()
On Error GoTo btnScheduleQuery_Click_Err
If DLookup("DelegateID", "EventDelegate", "DelegateID=" & DelegateID And "EventID", "EventDelegate", "EventID=" & EventID) Then
MsgBox "This delegate is already scheduled for this course"
Else
DoCmd.OpenQuery "EventDel", acViewNormal, acEdit
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From EventDelegate Where EventID = " & Me.EventID)
If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Rs("Event.PlacesAvailable") = Rs("Event.PlacesAvailable") - 1
Rs.Update
End If
Rs.Close
Set Rs = Nothing
End If
btnScheduleQuery_Click_Exit:
Exit Sub
btnScheduleQuery_Click_Err:
MsgBox Error$
Resume btnScheduleQuery_Click_Exit

End Sub

 

[DCrake]

If Nz(DLookup("DelegateID", "EventDelegate", "DelegateID=" & Me.DelegateID & " And EventID=" & Me.EventID),0) <> 0 Then

Why do you keep wanting to repeat the code?

 

[AndyCompanyZ]

I want the lookup to see if there is a duplicate record in the table and only update if there isn't. Basically I only want to schedule the delegate onto an event if they aren't already on it. I'm sure there's an easier way to do this. I also want to record each instance that a delegate has been scheduled in case they later cancel and then the user has a means of checking that that delegate keeps cancelling and what event(s) they were scheduled for.

 

[AndyCompanyZ]

I tried your code but it says method or data member not found on Me.DelegateID which seems odd beacuse DelegateID is certainly on the form.

 

[DCrake]

If Nz(DLookup("DelegateID", "EventDelegate", "DelegateID=" & Me.DelegateID & " And EventID=" & Me.EventID),0) <> 0 Then

Psuedo code:

Look in the EventDelegate table to see if there is a DelegateID where the delegate id equals the one I picked on the form and that the event that has been chosen on the form matches the eventID in the table.

The Nz() returns a 0 if no match is found so that you don't get an error.

You could also use DCount() as well

 

[DCrake]

Post me your db to look at

 

[AndyCompanyZ]

Thanks for your help but I need to do things now I'll try and post more tomorrow if you are still about