[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

OpenRecordset Where problem

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

  • 2
1 Solution
I think you need to add the specific candidate to the where statement as well.

Leigh PurvisDatabase DeveloperCommented:
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
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.

Leigh PurvisDatabase DeveloperCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now