DLookup problem

[AndyCompanyZ]

I have now changed routine and now have:
Dim i As Integer
Dim db As DAO.Database
Set db = CurrentDb
i = DCount("DelegateID" , "tblEventDelegate" , "DelegateID & EventID = [forms]![CoursesDelegate]![DelegateID] & [forms]![CoursesDelegate]![EventID]")
If DelegateID <> "" And Me.EventID <> "" Then
If i > 0 Then
MsgBox i & " This delegate is already scheduled on this course" & vbCrLf & "Please check individual's course needs before entry.", , "System Notice"
Cancel = True
Me.Undo
Exit Sub
End If
Else

This now brings up the message box no matter what I enter which I think is because it says whatever is selected from the combobox will produce the message. I want it to look up the table and see if a record exists for an instance of both the eventId and the delegateID in the eventdelegate table but can't see how to do this.

 

[DCrake]

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From TblEventDelegate Where DelegateID = " & Me.DelegateID & " And EventID = " & Me.EventID)

If Not Rs.EOF And Not Rs.BOF Then
   '///Record Exists
Else
   '///New Record
End If

This will give you the same results

 

[AndyCompanyZ]

Thanks again. I know this is dragging on and it should be easy to do but now when i put that it in it says "Method or data member not found" on Me.DelegateID. and I imagine it will on Me.EventID.

 

[DCrake]

On your form CoursesDelegate do you have controls named DelegateID and EventID?

You need to refer to the controls on the form not the fields they are bound to. So if you have a textbox called TxtDelegate that has the DelegateID as its controlSource you need to refer to the TxtDelegate Not DelegateID. This is why I previously asked you to send a copy of your app.

 

[AndyCompanyZ]

Yes I have a control called txtDelegateID and that is what I have called it. I'm just stripping down the db to show you.

 

[AndyCompanyZ]

Here is a copy of the db zipped.

 

[DCrake]

Your form eventDelegate does not have TxtDelegate or txtEvent control names

Your form CoursesDelegate has a control named txtDelegateID but no control called txteventID but you do have a combo named cmboevent.

take a look at the coursesdelegate form

 

[AndyCompanyZ]

The Event delegate from is not a working version it was just created by me to have a look at the output of the Eventdelegate table and will not be used. The Coursedelegate form is the one i will be using which has the cmboEvent. I have changed the code to read
Set Rs = CurrentDb.OpenRecordset("Select * From EventDelegate Where DelegateID = " & Me.txtDelegateID & " And EventID = " & Me.cmboEvent) but that just hangs when I click the button and no record is added.

 

[AndyCompanyZ]

Actually I just realised my VBA was disabled but now it says there is a problem with the msgbox line what is the right syntax I have
MsgBox = vbCancel & "This delegate is already scheduled on this Event"
It says "Function call on left hand side of argument must return variant or object" and have no idea what that means

 

[AndyCompanyZ]

Aha nearly got it now. It returns the msgbox now I have changed it if the delegate is already scheduled which is good and updates the table but says syntax error (missing operator) in query expression 'EventID=' but still updates OK so not sure where that problem is coming from.

 

[AndyCompanyZ]

But I've just noticed that the Event.PlacesAvailable field is not updating because I took out the declaration for the update as it said i already had the declaration:
Dim Rs As DAO.Recordset
Should I call that a different name for the second part of the routine ie rs1 or something or would that not work either here is what I have now:
Private Sub btnScheduleQuery_Click()
On Error GoTo btnScheduleQuery_Click_Err
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From EventDelegate Where DelegateID = " & Me.txtDelegateID & " And EventID = " & Me.cmboEvent)
If Not Rs.EOF And Not Rs.BOF Then
MsgBox vbCancel & "This delegate is already scheduled on this Event"
Else

DoCmd.OpenQuery "EventDel", acViewNormal, acEdit

Set Rs = CurrentDb.OpenRecordset("Select * From EventDelegate Where EventID = " & 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
If I take the declaration out the first part works but then it says "Item not found in this collection"

 

[DCrake]

Rs("PlacesAvailable") = Rs("PlacesAvailable") - 1

 

[AndyCompanyZ]

Are you sure about that because Event. is another table which I can't see the code pointing to. I cahnged it anyway but it still comes up with Syntax operator missing operator in query expression 'EventID=' .
I think the query is the ("Select * From EventDelegate Where EventID = " & EventID) part.
I presumed this is because of the combobox so changed it to cmboEvent but this throws up "Item not found in this collection". so it must be relating to the eventID on the EventDelegate table.
I know I am so close to this but yet so far.
Thanks for all your help though

 

[AndyCompanyZ]

I have changed the code now to:
Private Sub btnScheduleQuery_Click()
On Error GoTo btnScheduleQuery_Click_Err
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From EventDelegate Where DelegateID = " & Me.txtDelegateID & " And EventID = " & Me.cmboEvent)
If Not Rs.EOF And Not Rs.BOF Then
MsgBox vbCancel & "This delegate is already scheduled on this Event"
Else

DoCmd.OpenQuery "EventDel", acViewNormal, acEdit
Dim Rs1 As DAO.Recordset
Set Rs1 = CurrentDb.OpenRecordset("Select * From EventDelegate Where EventID = " & cmboEvent)
If Not Rs1.EOF And Not Rs1.BOF Then
Rs1.Edit
Rs1("Event.PlacesAvailable") = Rs1("Event.PlacesAvailable") - 1
Rs1.Update
End If
Rs1.Close
Set Rs1 = Nothing
End If
btnScheduleQuery_Click_Exit:
Exit Sub
btnScheduleQuery_Click_Err:
MsgBox Error$
Resume btnScheduleQuery_Click_Exit

End Sub

So that I have two instances of recordsets as it wouldn't work with one running of the same declaration.

 

[DCrake]

Set Rs1 = CurrentDb.OpenRecordset("Select * From Event Where EventID = " & cmboEvent)

Rs1 is pointing at the wrong table Cange it to Event not EventDelegate

 

[AndyCompanyZ]

It still throws up "Item not found in this collection". I'm not sure it should be Event table though as I need it to lookup which delegates are scheduled and then subtract them from the Event table and the delegates are scheduled via the EventDelegate table. Unless my logic is wrong.

 

[AndyCompanyZ]

Have solved it now as you said it was the event table that I needed and not the eventdelegate table.

Thanks again