Query on 2 different recordsets via code

[Ronald Dodge]

Main Question:

I would like to know if I can create a third recordset based on 2 other
recordsets via code without having to go through create table or what not.

Example:

Recordset 1 contains all of the records for the various conversion center
level reason code assignments in BEDB1
Recordset 2 contains all of the records of the different conversion centers
along with the code to what type of conversion center they each are.

Recordset 1 has the following fields:

Resource Reason ID
Resource ID
Reason ID
Mode ID

Recordset 2 has the following fields:

Resource ID
Type of Resource ID
Resource Name

The form itself has the following fields:

Resource Reason ID
Level of Resource (Global (1), Type of Conversion Center (2), or Conversion
Center(3))
Resource ID (This can only be filled in if Level of Resource has a valid
value that is NOT Empty or Null)
Mode ID
Reason ID
Enable (Yes/No)

Like to setup the following type of SQL Statement when Level of Resource =
2, Enable = -1 and the form is completely filled out, which then the user
clicks on the update command button, and this SQL statement would be ran in
the data validation process before the actual updating would take place:

SELECT Recordset1.*
FROM Recordset1 LEFT JOIN Recordset2 ON Recordset1.ResourceID =
Recordset2.ResourceID
WHERE Recordset1.ModeID = Form.ModeID AND Recordset1.ReasonID =
Form.ReasonID AND Recordset1.Enable = -1 AND (Recordset1.LevelOfResource = 1
OR (Recordset1.LevelOfResource = 3 AND Recordset1.ResourceID =
Recordset2.ResourceID))

Purpose:

When a reason in a particular mode (setup, run, [job] breakdown) is in the
process of being assigned to a type of conversion center, there should not
already be another resource reason ID with all of the same info regardless
if the resource id is enabled or not (this is checked for earlier in the
process, thus not included in this example other than to make note of it).
There should not be a resource reason ID at the global level to be enabled
when this is added as being enabled, and there should not be a single
conversion center of the type of conversion center that this reason is being
assigned to, that is enabled with this particular reason and mode.
Therefore, if the above SQL statement comes up with any records, then a
message will be sent to the user and ask for the user to respond
accordingly.

NOTES:

Reasons and Resource Reasons are kept in Production DB file while Resources
are kept in Assets DB file. The separation of the different BE DB files is
mainly to address the 2GB limitation of Access. The manipulation of the
different BE DB files are controlled via DAO and SQL coding. I would use
some better DB program than Access, but the owners are very stingy and I'm
having to make due with what I have to work with.

Using bound forms/controls are out of the question cause of the conflict
between mouse user friendliness and strict data validation check issue.
I know I could use linked tables, but would rather find ways to manipulate
the data directly without having to cause inefficiencies, thus I would also
like to avoid using the IN clause of SQL writing to an external BE DB file.

 

[Deville]

I would just create the recordset on the fly as I needed
it. Here is a some code to create and delete queries on
the fly. Once you create the temp Query your code can then
reference it then deleteit once its done with it. Hope it
helps.

Sub CreateRS()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("Test", "SELECT * FROM
TableName;")

DeleteRS ("Test")
Set qdf = Nothing
Set db = Nothing
End Sub
Sub DeleteRS(rs As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
db.QueryDefs.Delete rs
Set qdf = Nothing
Set db = Nothing
End Sub

-----Original Message-----
Main Question:

I would like to know if I can create a third recordset based on 2 other
recordsets via code without having to go through create table or what not.

Example:

Recordset 1 contains all of the records for the various conversion center
level reason code assignments in BEDB1
Recordset 2 contains all of the records of the different conversion centers
along with the code to what type of conversion center they each are.

Recordset 1 has the following fields:

Resource Reason ID
Resource ID
Reason ID
Mode ID

Recordset 2 has the following fields:

Resource ID
Type of Resource ID
Resource Name

The form itself has the following fields:

Resource Reason ID
Level of Resource (Global (1), Type of Conversion Center (2), or Conversion
Center(3))
Resource ID (This can only be filled in if Level of Resource has a valid
value that is NOT Empty or Null)
Mode ID
Reason ID
Enable (Yes/No)

Like to setup the following type of SQL Statement when Level of Resource =
2, Enable = -1 and the form is completely filled out, which then the user
clicks on the update command button, and this SQL statement would be ran in
the data validation process before the actual updating would take place:

SELECT Recordset1.*
FROM Recordset1 LEFT JOIN Recordset2 ON Recordset1.ResourceID =
Recordset2.ResourceID
WHERE Recordset1.ModeID = Form.ModeID AND Recordset1.ReasonID =
Form.ReasonID AND Recordset1.Enable = -1 AND

(Recordset1.LevelOfResource = 1

OR (Recordset1.LevelOfResource = 3 AND Recordset1.ResourceID =
Recordset2.ResourceID))

Purpose:

When a reason in a particular mode (setup, run, [job] breakdown) is in the
process of being assigned to a type of conversion center, there should not
already be another resource reason ID with all of the same info regardless
if the resource id is enabled or not (this is checked for earlier in the
process, thus not included in this example other than to make note of it).
There should not be a resource reason ID at the global level to be enabled
when this is added as being enabled, and there should not be a single
conversion center of the type of conversion center that this reason is being
assigned to, that is enabled with this particular reason and mode.
Therefore, if the above SQL statement comes up with any records, then a
message will be sent to the user and ask for the user to respond
accordingly.

NOTES:

Reasons and Resource Reasons are kept in Production DB file while Resources
are kept in Assets DB file. The separation of the different BE DB files is
mainly to address the 2GB limitation of Access. The manipulation of the
different BE DB files are controlled via DAO and SQL coding. I would use
some better DB program than Access, but the owners are very stingy and I'm
having to make due with what I have to work with.

Using bound forms/controls are out of the question cause of the conflict
between mouse user friendliness and strict data validation check issue.
I know I could use linked tables, but would rather find ways to manipulate
the data directly without having to cause inefficiencies, thus I would also
like to avoid using the IN clause of SQL writing to an external BE DB file.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000


.