recordset and subform 1

[andzejek]

How to refer recodset of the form when you process action on/of the subform

Andrew

 

[PHV]

And the REAL question is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andzejek]

I have an recordset(rs) on main form which I'm showing in the textboxes of main form. In the subform, I have(I show) a table with part_num field which is corresponding to part_num field of the recordset rs of the main form. Now, when user will click on some field of the subform, I want to show corresponding record of the recordset on main form. Main form and subform are not related.
I knew, it will be complicated!

Andrew

 

[andzejek]

well, i did not think, it will be so complicated!

Andrew

 

[dhookom]

Are you really creating ADO or DAO recordsets or are your form and subform bound to Record Source queries or SQL statements.

Is your subform displaying continuous records while your main form is showing a single record?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[andzejek]

I'm using Ado recordset. Main form is unbound and shows single record from recordset(rs). Subform is bounded to a table and display cont. records(looks like a spreadsheet and shows all records of the table). There is no link(parent - child) between main and subform.

 

[dhookom]

Why not bind the main form rather than using an ADO recordset? Do you need to edit records in the main form or just display them?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[andzejek]

Main Form just display records and I can use it with different tables just by changing rs.open.

 

[andzejek]

So there is no way to call recordset of the main form from OnClick event of subform?

 

[MajP]

If you bind the main form then simply:

Private Sub fkParentName_Click()
Me.Parent.Recordset.FindFirst ("part_Num = " & Me.part_Num)
End Sub

 

[dhookom]

Or, you could set the filter property of the main form:

Me.Parent.Filter ="Part_Num =" & Me.Part_Num
Me.Parent.FilterOn = True

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MajP]

or you could change the recordsource of the parent form

Me.Parent.RecordSource = "qryPartNumber"

and qryPartNumber would be something like:
SELECT Part_Nu
FROM tblParts
WHERE part_Nu =[forms]![frmParts].[subFrmParts].[form].[fkPart_Nu]

 

[andzejek]

but my main for does not have a recordsource and textboxes are unbound.
Me.texbox.Value = rs("part_num") - this is how I'm using texboxes on this form, so in order to change value of the textbox I have to move to right record in recordset.

 

[MajP]

Look at my first post. I used the forms recordset, but it could have been any recordset.

Public RS as dao.recordset (or ADO recordset)

somewhere in code set the recordset

Private Sub fkParentName_Click()
RS.FindFirst ("part_Num = " & Me.part_Num)
End Sub

Why are you choosing to go unbound? Seems like extra work. You can still use multiple tables on the same form by changing the recordsource.

 

[andzejek]

rs.findfirst is popping up error message: "object required".

Why I choose to go unbound? I'm beginner in Access and I thought I will have more flexibility with unbound form, as my tables have different fields but forms similar functions.

Andrew

 

[MajP]

Can you post the code where you set you recordset and where you are trying to use the findfirst?

I am kind of curious how you have different tables with different fields using the same form. Obviously this can be done unbound, but logically this seems strange. I would assume that these tables are logically very similar, if you are displaying them in a similar fashion. I am wondering if your data is normalized. Can you explain these different tables?

 

[andzejek]

Here is the code on main form:


Option Compare Database

Public CN As New ADODB.Connection
Public rs As New ADODB.Recordset

Private Sub Form_Load()


Set CN = CurrentProject.Connection
rs.Open "select * from INVT_MAIN", CN, adOpenStatic, adLockOptimistic


rs.MoveFirst


End Sub


For ex. tables customers and vendors are very much alike so with small modification I can use one form to show them both. Also, with recorset I can use field numbers not names, read different tables and show them in this same textboxes.
Andrew

 

[MajP]

try using find instead of findfirst. I think findfirst is only in DAO not ADO

 

[andzejek]

I did, this same.

 

[PHV]

I'm beginner in Access
So, why deal with unbound forms ?