displaying a recordset

[mattl72]

Hi

Does anyone know if there's a way to display a recordset from the code on the form? I am doing a SQL statement that selects Order and Item from a query:

strsql = "SELECT Query1.ORDER_NO, Query2.ITEM_NO FROM Query1 LEFT JOIN Querys ON Query1.ITEM_NO = Query2.ITEM_NO WHERE (((Query2.ITEM_NO) Is Null))"
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strsql, dbOpenDynaset)

I want to be able to display the records to a user so they know what records are causing problems

Any help would be appreciated

Thanks,
Matt

 

[cmmrfrds]

Assuming you are using Access 2000 and above, the Form has a recordset object. So, you can set the recordset you created to the Form's recordset object.

Put the code in the Form's OnOpen event. Create bound fields on the form to match the field names on the recordset.

Dim yy As Access.Form
Set yy = Forms!frm_IDTable.Form '- your Form name
'--
Set yy.Recordset = rs

 

[scking]

Is there some reason you don't want to assign the SQL to the RecordSource for the form? You could also open a report and assign the SQL to the RecordSource.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[mattl72]

scking - actually, i'm creating the recordset on the fly (triggered on a button click event) to check to see if an Item exists before updating it. I want to give the user some kind of warning telling them to enter an Item for specific Item No(s) if it doesn't exist. I don't know - maybe I'm making this more difficult than it needs to be.

cmmrfrds - I'm having trouble with assigning it to a form. Should I be putting it in the Form_Open event of the new form?

Thanks again,
Matt

 

[cmmrfrds]

Access 2000 or 2002, right?

Post the code you put in the Form's OnOpen event and we can go from there.

 

[mattl72]

Okay, its giving me a runtime error '424' - Object Required

I have the following code in the Open Event of a new form that I want to display to the user:

Dim yy As Access.Form

Set yy = Forms!Form2.Form
Set yy.Recordset = rs

It says there is yy.Recordset = Nothing when I debug? I have two text boxes on the form - 1 for order_no and 1 for item_no. I'm not sure what I'm missing...

 

[scking]

Quote
actually, i'm creating the recordset on the fly (triggered on a button click event) to check to see if an Item exists before updating it. I want to give the user some kind of warning telling them to enter an Item for specific Item No(s) if it doesn't exist.

Nothing prevents you from checking to see if an Item exists and giving the user some kind of warning before setting the RecordSource property and requerying. Both methods should work. ----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[cmmrfrds]

I believe I misread your requirements.
If you just need to see item/order records on the existing connection, then do as scking suggested and put the sql statement as the record source for the form in the onopen event of the new form.

Me.RecordSource = strsql '-string global or passed to form

If not
After reading back through, I am not sure anymore what you are trying to do. Can you outline the steps that you want to do. Also, in order to evaluate what is happening in the onopen event, would need to see all the code in the event - especially the recordset definition.

 

[mattl72]

Alright, I'm feeling stupid now. I didn't even think of just assigning the SQL Statement to the recordsource. I knew I was making it more difficult than it needed to be.

Thanks for your help guys