OpenRecordset Where problem

Posted on 2006-05-04
Last Modified: 2011-10-03
I am trying to test to to make sure that a candidate has attended an orientation. If they have move forward... If not, Stop... give a message.  I can get it to work for all candidates Attended or not. Or I can get it to work for none.   Any Ideas?

varDone = "[Orient_Status] <> 'Attended'"

Set rst4 = DBEngine(0)(0).OpenRecordset("Select * From tblOrientation WHERE " & varDone)

 If rst4.RecordCount <> 0 And Me.Status.Value = "Accepted" Then
           MsgBox "A Orientation Date has not been entered for " & Me.First_Name & varDone, vbInformation, "Job Order"
           Me.Status = Me.Status.OldValue

 End If

Question by:DominicConstable
    LVL 8

    Expert Comment

    I think you need to add the specific candidate to the where statement as well.

    LVL 44

    Accepted Solution

    You need to check for the sepcific candidate yes?
    They're listed on the form you're working in I imagine?
    Something like

    varDone = "[Orient_Status] = 'Attended' AND [CandidateID] = " & Me.CandidateID

    Set rst4 = CurrentDb.OpenRecordset("Select * From tblOrientation WHERE " & varDone)

    If rst4.RecordCount = 0 Then
        Msgbox "Candidates ain't listed as having attended one"
        Exit Sub '<-- or Function whatever
    End If
    LVL 77

    Expert Comment

    Hi DominicConstable,

    I'm not clear about the context here.
    You obviously have a form open when you run this code.
    Are you hoping to process the record that is visible on the form? Is he form based on tblOrientation?

    But at the moment there is nothing to match the visisble record to the record being processed in the recordset.

    Can you explain please.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Indeed - my suggestion was pure supposition.
    If it were at all valid - then although quicker, you could probably just sidestep the recordset and use a DCount.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now