Combining 2 recordsets (1 Viewer)

[AndyCompanyZ]

I am trying to run two recordset queries that produce different results but from the same table. The first one works but I can't get the second to work I don't know whether I need to run both at the same time or if there is something I need to add:

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 "This delegate is already scheduled on this Event", vbExclamation
Exit Sub
Rs.Close

Else
 
 Dim Rs3 As DAO.Recordset
 Set Rs3 = CurrentDb.OpenRecordset("Select * From EventDelegate Where DelegateID = " & Me.txtDelegateID & " And EventStartDate = " & Me.txtEventStartDate)
 If Not Rs3.EOF And Not Rs3.BOF Then
 MsgBox "Already Scheduled for an Event on same day"
 Exit Sub
 Rs3.Close
 
 End If
 End If

This is a work round from my other thread for anyone who is following this.
Thanks for any help or advice.

 

[spikepl]

A communication hint: banish "it does not work" from your postings, because only you know what it means. You want something, it does something, but you are probably not happy with it - what does all this mean for an outsider?

Always state very specifically

1. What you want to happen
2. What does actually does take place

What IS executed in your code? Are there error messages? What is the actual specific problem? Does the query fail or it was just not executed ?What are the values of pertinent variables?

 

[AndyCompanyZ]

OK The first recordset does what it should ie if there is a delegate already scheduled then it brings up the messagebox and then exits the sub. But if it passes that without needing a message it runs the rest of the code and runs an update query that updates the EventDelegates table even if there is a record with the eventstartdate already recorded.
As far as what i want to happen I want to check the Eventdelegate table and see first of all if there is a delegateID recorded for an event of the same EventID and if so give a message (which works) and secondly check the same table to see if there is a DelegateID that has been scheduled onto an event on the same day as the txtEventStartdate on the form ( which doesn't seem to do anything).

 

[spikepl]

which doesn't seem to do anything

So, again what does it mean? Code ALWAYS does something, but not always what you expect.

Set Rs3 = CurrentDb.OpenRecordset("Select * From EventDelegate Where DelegateID = " & Me.txtDelegateID & " And EventStartDate = " & Me.txtEventStartDate)  
If Not Rs3.EOF And Not Rs3.BOF Then  MsgBox "Already Scheduled for an Event on same day"  Exit Sub

Do you get into this part of code at all? Check with the debugger
If it jumps over the If RS3.Eof and does not get inside, then it means the query did not find what you expected. Then check if data indeed is OK, and if query indeed is OK

 

[Galaxiom]

Change

 .... EventStartDate = " & Me.txtEventStartDate)

To

.... EventStartDate=" & Format(Me.txtEventStartDate, "\#mm\/dd\/yyyy\#")

 

[spikepl]

OK .. now that the wicked GalaxiomAtHome has spoiled my fun ...

When 5 things put together do not do what you want, then the method is to check each one by itself. A 747 is put together after testing each part, not just by throwing them all together in hope that the lot will fly. Code is no different.

 

[AndyCompanyZ]

I debugged it and it stepped inside and I checked the data and it was correct. Because the EventStartDate is a date/time field should there be anything else in the query like "" maybe

 

[AndyCompanyZ]

I got my reply in after that last update.

 

[spikepl]

So the credits go to ...*drumroll.....* GalaxiomAt Home

 

[spikepl]

And queries you can always check by quickly putting it together in the query designer, and once that works, copying the code from the SQLview.

 

[Galaxiom]

BTW your procedure structure is implausible.

Anything immediately following Exit Sub won't ever be executed.
So rs.Close is never encountered. Fortunately it deoesn't really matter.

Personally I am not a big fan of Exit Sub in the middle of code. I liken this kind of exit to jumping out a window and prefer the If structures take the excution through to a single Exit door.

 

[AndyCompanyZ]

Thank you so much GlaxiomAtHome saved me another fruitless afternoon. Now I know I will remember it but without knowing its a bit difficult as anything is.

 

[Galaxiom]

Yep, Jet (now ACE) sql is very fussy.

Dates should be presented as #mm/dd/yyyy#
At a pinch it will take #yyyy/mm/dd# but the native form is the former.

Note the backslashes. These indicate the subsequent character is literal.

\/ means "/". The forward slash on its own is only a replacement character and if your regional date separator is anything other than a forward slash then the sql will fail.

So ALWAYS use this for dates:
Format(datefield, "\#mm\/dd\/yyyy\#")

If you need time included then:
Format(datetimefield, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

BTW If you are talking to SQL server then use single quotes instead of the hash.