Converting Queries to Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i build the a database that contains alot of select queries and this cause
higher file size, i use select queries to locate my the value or data on my
tables and use it as reference in computing just like thies query:
SELECT tbl_installationhistory.p_n, tbl_installationhistory.e_n,
tbl_installationhistory.dpt, tbl_installationhistory.l_n,
tbl_installationhistory.l, tbl_installationhistory.i_date
FROM tbl_installationhistory
WHERE (((tbl_installationhistory.p_n)=[Forms]![frm_withdrawal]![partnumber])
AND ((tbl_installationhistory.e_n)=[Forms]![frm_withdrawal]![machinenumber])
AND
((tbl_installationhistory.l_n)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![assy_name])
AND
((tbl_installationhistory.l)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![location]))
ORDER BY tbl_installationhistory.i_date DESC;

to know what is the value of i_date i use the dlookup() method

is there any recordset equivalent to this type of query? then tell me how to
do it.

thanks,
resti
 
Resti,

If your query is called, for example, "qryMyQuery", then you only have to
plug the query name into a recordset:
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryMyQuery")

Me!txtMyTextBox = rs!i_date

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Graham,

what if i'm looking for a value in my table, can i use multiple criteria
just know the result? if there is any please tel me.
thanks,

Resti

Graham R Seach said:
Resti,

If your query is called, for example, "qryMyQuery", then you only have to
plug the query name into a recordset:
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryMyQuery")

Me!txtMyTextBox = rs!i_date

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Resti M. Guay said:
i build the a database that contains alot of select queries and this cause
higher file size, i use select queries to locate my the value or data on
my
tables and use it as reference in computing just like thies query:
SELECT tbl_installationhistory.p_n, tbl_installationhistory.e_n,
tbl_installationhistory.dpt, tbl_installationhistory.l_n,
tbl_installationhistory.l, tbl_installationhistory.i_date
FROM tbl_installationhistory
WHERE
(((tbl_installationhistory.p_n)=[Forms]![frm_withdrawal]![partnumber])
AND
((tbl_installationhistory.e_n)=[Forms]![frm_withdrawal]![machinenumber])
AND
((tbl_installationhistory.l_n)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![assy_name])
AND
((tbl_installationhistory.l)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![location]))
ORDER BY tbl_installationhistory.i_date DESC;

to know what is the value of i_date i use the dlookup() method

is there any recordset equivalent to this type of query? then tell me how
to
do it.

thanks,
resti
Click to expand...
Click to expand...
 
Resti,

Of course you can:

To supply a parameter to a query:
Set rs = db.OpenRecordset("SELECT * FROM qryMyQuery WHERE somevalue =
123")

To search a table using a parameter:
Set rs = db.OpenRecordset("SELECT * FROM tblMyTable WHERE somevalue =
123")

To return specific fields from a table, using a numeric parameter in a
form's textbox:
Set rs = db.OpenRecordset("SELECT field1, field2 FROM tblMyTable WHERE
somevalue = " & Me!txtMyTextBox

To return specific fields from a table, using an alpha parameter in a form's
textbox:
Set rs = db.OpenRecordset("SELECT field1, field2 FROM tblMyTable WHERE
somevalue = """ & Me!txtMyTextBox & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Resti M. Guay said:
Graham,

what if i'm looking for a value in my table, can i use multiple criteria
just know the result? if there is any please tel me.
thanks,

Resti

Graham R Seach said:
Resti,

If your query is called, for example, "qryMyQuery", then you only have to
plug the query name into a recordset:
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryMyQuery")

Me!txtMyTextBox = rs!i_date

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Resti M. Guay said:
i build the a database that contains alot of select queries and this
cause
higher file size, i use select queries to locate my the value or data
on
my
tables and use it as reference in computing just like thies query:
SELECT tbl_installationhistory.p_n, tbl_installationhistory.e_n,
tbl_installationhistory.dpt, tbl_installationhistory.l_n,
tbl_installationhistory.l, tbl_installationhistory.i_date
FROM tbl_installationhistory
WHERE
(((tbl_installationhistory.p_n)=[Forms]![frm_withdrawal]![partnumber])
AND
((tbl_installationhistory.e_n)=[Forms]![frm_withdrawal]![machinenumber])
AND
((tbl_installationhistory.l_n)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![assy_name])
AND
((tbl_installationhistory.l)=[Forms]![frm_selectlocationmain]![frm_selectlocation].[Form]![location]))
ORDER BY tbl_installationhistory.i_date DESC;

to know what is the value of i_date i use the dlookup() method

is there any recordset equivalent to this type of query? then tell me
how
to
do it.

thanks,
resti
Click to expand...
Click to expand...
Click to expand...
 
Back
Top