Bind the whole method with a text box

[Eric]

I wrote a query in Form_Open method but i didnt bind it with the
textbox. How to i bind this whole method with the text box, as right
now the textbox shows the last value of the recordset. The textbox is a
part of a subform and it shows three times the last value of the record
set.

Private Sub Form_Open(Cancel As Integer)
Dim qry As String, rst As Object
qry = "SELECT tbl_EquipmentChronology.Equipment1 FROM
tbl_EquipmentChronology INNER JOIN tbl_events ON
tbl_events.TicketNum=tbl_EquipmentChronology.TicketNum where
tbl_events.TicketNum=" &
Forms!tbl_PPVResearch_Edit!frm_Events!TicketNum & " and
tbl_events.PPVVOD_Outlet=tbl_EquipmentChronology.Outlet"
Set rst = CurrentDb.OpenRecordset(qry)
rst.MoveFirst
Do While Not rst.EOF
Forms!tbl_PPVResearch_Edit!frm_Events!Text2 = rst!Equipment1
rst.MoveNext
Loop

 

[Larry Linson]

I admit to having some difficutly understanding what you want to do. But
the code you show appears to be, setting one Control,
Forms!tbl_PPVResearch_Edit!frm_Events!Text2 on the Form to each value of
Field rst!Equipment1 in Records in the Recordset, one after another.

However, the Open event of a Form is too soon to reliably use Controls on
the Form, because they may not be initiated yet, so that may be your
problem.

If you could restate the post, perhaps I'd understand it better.

Is the Form bound to a RecordSource that includes the Field? If so, why not
simply use that Field as the ControlSource of the Textbox. If not, is there
a relationship such that you can join tbl_EquipmentChronology in the Query
used as RecordSource, so you can bind the Control?

If not, do you want the user to be able to update this Field, or simply view
it? If it's only for viewing, check help on DLookup, for use in the Control
Source property of the Text Box.

Larry Linson
Microsoft Access MVP

 

[Eric]

I think i couldn't use the textbox control in the running form. I have
to go for the DLookup but how to i pass arguments in the DLookup i
never use it before. I need help here

=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order
Details]!ProductID)

qry = "SELECT tbl_EquipmentChronology.Equipment1 FROM
tbl_EquipmentChronology INNER JOIN tbl_events ON

tbl_events.TicketNum=tbl_EquipmentChronology.TicketNum where
tbl_events.TicketNum=" &

Forms!tbl_PPVResearch_Edit!frm_Events!TicketNum & " and
tbl_events.PPVVOD_Outlet=tbl_EquipmentChronology.Outlet"

Thanks in advance.
-------------------------------------------------------------------------------------