MSAccess Query as recordset

[Guest]

I can't figure out how to do this!

Here's my situation:
I have a form (form1) that displays records (from table1) associated with a
specific record on a different table (table2). In the header of form1 I have
fields where the user can 'build' a new record to add to table1.

I want to verify before adding the new record that it doesn't conflict with
an existing record on table1 (basically, that the start/end dates don't
overlap a record in the table1).

I have a query (query1) in Access that I can open to see if the existing new
record (from form1) has dates that overlap an existing record of the same
type in table1.

I want to have the button from form1 that you click to add the record first
open query1 and see if it returned any results (found an overlapping record).
If it does, I will show a warning and stop the process. If no overlapping
records are found, I'll kick off an append query to add the records, then
refresh my form to show the new record.

I can't for the life of me get the query to open as a recordset. I must
have gone through a dozen examples on this part of the group and none work.

Any ideas?

 

[John Nurick]

If you just want to find out if there are any 'overlapping' records,
presumably all you need to do is to see if query1 returns any records.
You can do this with

If DCount("*", "query1") > 0 Then
...

The usual way to open a recordset on a query is along these lines:

Dim rsR As DAO.Recordset
Dim NumRecords As Long
Dim SomeNumber As Long

Set rsR = CurrentDB.OpenRecordset("MyQuery")
If rsR.EOF Then ' it's empty
NumRecords = 0
Else
'There is at least one record in the recordset
With rsR
'Move to the end to ensure we have an accurate record count
.MoveLast
NumRecords = .RecordCount
.MoveFirst

'Access field in current record
SomeNumber = .Fields("SomeNumber").Value
...
End With
End If

 

[Guest]

John,

First, thanks for replying!

Now, I modified your code (below) to fit my circumstances, but when I run it
I get an error "Run-time error '3061': Too few parameters. Expected 4."

===Start Code===
Private Sub Command100_Click()

Dim OVERLAP_RECORDSET As DAO.Recordset
Dim NumRecords As Long
Dim SomeNumber As Long

Set OVERLAP_RECORDSET =
CurrentDb.OpenRecordset("AddLenderAgreements_FindOverlap")
If OVERLAP_RECORDSET.EOF Then ' it's empty
NumRecords = 0
Else
'There is at least one record in the recordset
With OVERLAP_RECORDSET
'Move to the end to ensure we have an accurate record count
.MoveLast
NumRecords = .RECORDCOUNT
.MoveFirst
MESSAGE = MsgBox(NumRecords & " RECORDS" & Chr(13) & Chr(10) &
SOMENMBER & " 'SOME NUMBER'", vbOKOnly, "TEST")
'Access field in current record
SomeNumber = .Fields("SomeNumber").Value
End With
End If
===End Code===

Now, I think the problem is that the query I'm running uses fields from the
form that this code is launched from. I say that because the query has 4
variables it checks on the current form (LenderKey, NewAgreement, StartDate
and EndDate).

Do I have to put the actual SQL in this Sub, or can I just put parameters in
the query?

 

[Guest]

Quick follow up to my last post...adding the form's fields as parameters in
the query doesn't fix the problem.

 

[Dirk Goldgar]

In

Quick follow up to my last post...adding the form's fields as
parameters in the query doesn't fix the problem.

Did you try it like this?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim OVERLAP_RECORDSET As DAO.Recordset
Dim NumRecords As Long
Dim SomeNumber As Long

Set db = CurrentDb
Set qdf = db.QueryDefs("AddLenderAgreements_FindOverlap")

With qdf
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm
Set OVERLAP_RECORDSET = .OpenRecordset
End With

With OVERLAP_RECORDSET
If .EOF Then ' it's empty
NumRecords = 0
Else
' ... etc.
End If
.Close
End With