Recordset

[Rick West]

I need help making the following code work. Access 2003. The debugger keeps hanging me up on the Set vRS = vDB. xxxxx line (bolded below).

I do not see anything wrong with it.

Private Sub Select_LostFocus()

Dim vDB As Database
Dim vRS As DAO.Recordset
Dim Response As VbMsgBoxResult

Set vDB = CurrentDb
Set vRS = vDB.OpenRecordset("Q_MultipleSelects-OKtoLoad")
If Not (vRS.BOF And vRS.EOF) Then
Do While vRS.RecordCount > 1
MsgBox "You have selected the same pn more than once, please edit to one selection only"


vRS.Requery
Loop
End If
Exit Sub
End Sub

[ssanfu]

Ensure that you have a reference set to "Microsoft DAO 3.6 Object Library".

Then change this line:

Dim vDB As Database

to

Dim vDB As DAO.Database

Also, if you don't have it, the first two lines in every module should be:

Option Compare Database
Option Explicit

"Option Explicit" requires variable declaration, which helps ensure that you do not mistype variable names.

[RayMilhon]

I'm not 100% sure this is correct but I think the issue is that you are putting the Query name in an OpenRecordset Object. .OpenRecordset("Qry_Name") is incorrect should be .OpenREcordset("Select * From Qry_Name")

[ssanfu]

I'm not 100% sure this is correct but I think the issue is that you are putting the Query name in an OpenRecordset Object. .OpenRecordset("Qry_Name") is incorrect should be .OpenREcordset("Select * From Qry_Name")

You can use a query name. I do this a lot because it is easier than converting the query to a string that I can use in VBA.

The syntax is:
Set recordset = object.OpenRecordset (source, type, options, lockedits)

where source= A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records.

[Rick West]

OK, tried all these things - nada. FYI, I have similar code elsewhere in my db tied to a different query and it works but this one does not work. here is the code that works:

Private Sub cmdImport_Click()
On Error GoTo Err_cmdImport_Click

Dim vDB As Database
Dim vRS As DAO.Recordset

Set vDB = CurrentDb
Set vRS = vDB.OpenRecordset("Q_PNLength")
If Not (vRS.BOF And vRS.EOF) Then
Do While vRS.RecordCount > 0
DoCmd.OpenForm "F_PNLength", , , , , acDialog
vRS.Requery
Loop
End If

The only difference I see is that the non-working code looks to a query that counts check boxes. That query doesn't seem to work unless I move the focus off the sub-form and back. it's like the check box "hangs" up the record and doesn't allow the check to write to the table?? I discounted this as a cause since it isn't even getting to the Query line before the debugger stops it. but thought I'd mention it.

[ssanfu]

I had to add the reference to get the code to compile. I created a very simple query (id - autonumber, test - text) and the code ran without errors.

Try changing the query name to something else - a very simple query to see if it is the query that is causing the problem.

Otherwise, I see no problem with the code.

[Rick West]

That worked! Yippee, not all my efforts will be on getting the query to work. thanks. (Even though it's not completely fixed yet at least I know where to look now)

[RayMilhon]

good to know thanks.