Private Sub Name_AfterUpdate() use 1

[WAR40K]

I have a form that I want to use for data entry. In order to speed up the process I want the form to pull information from an existing table named MRF after the user enters a work order number. The relationship between the form the MRF table is wir_no=work_ord. I thought I could load a blank form, the user enters a work order number, tabs to the next box and data is automatically filled in from the MRF table based on the work order number entered. I have spent several hours sifting through this forum and have not recognized anything that would serve my purposes.

Thank you in advance

 

[lamarw]

Instead of usng the AfterUpdate why not try using the lost focus of the order number field to get the information from the MRF table?

 

[Lightning]

You will need to open a recordset based on the Work Order number entered by the user. Then use the fields of the recordset to populate each field on the form. Something like:

StrSQL = "Select YourField1, YourField2, etc FROM MRF WHERE Work_Ord = " & Me.Wir_No

Set Db = CurrentDb
Set Rs = Db.Openrecordset(strSQL, DbOpenDynaset)
Rs.MoveFirst
With Rs
    Me.Field1 = .Fields("YourField1")
    Me.Field2 = .Fields("YourField2")
    etc...
End With
Rs.Close
Set Rs = Nothing

In practical terms it shouldn't matter whether you put this in the AfterUpdate event or the LostFocus Event of your Work Order control. Personally, I would use the AfterUpdate event

HTH
Lightning

 

[WAR40K]

Thanks Lightning the code you provided is much cleaner than the subform solution I ended up implementing for my demo yesterday.

 

[WAR40K]

Lightning,

I implemented your code as follows:

<code>
Private Sub ref_wir_AfterUpdate()

Dim rs As Recordset

strSQL = "Select root_cause, corrective_action, investigation_details, conclusions, summary, distribution, ca_effective_date FROM WIR WHERE wir_no = " & Me.ref_wir

Set Db = CurrentDb
Set rs = Db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
With rs
Me.root_cause = .Fields("root_cause")
Me.corrective_action = .Fields("corrective_action")
Me.ca_effective_date = .Fields("ca_effective_date")
Me.investigation_details = .Fields("conclusions")
Me.conclusions = .Fields("conclusions")
Me.summary = .Fields("summary")
Me.distribution = .Fields("distribution")
End With
rs.Close
Set rs = Nothing
</code>

However, when I update the ref_wir field I get an error message "Method or data member not found". Is there something I'm missing? It seems that access does not know to do with this function.

 

[WAR40K]

Fixed "Method or data member Not found" error, but now have a new problem. I'm getting a "Too few parameters, expected 1" error message. The parameter should be getting the value of ref_wir from Me.ref_wir in the strSQL line. However, it does not seem to be doing so. Any ideas?

thank you.

 

[Lightning]

Is your Ref_Wir field a number field or a text field?

If it is a text field you will need to enclose the reference to Me.ref_wir in quotes

HTH
Lightning

 

[MichaelRed]

A variation which would be somewhat easier to implement would be a parameter query, supplying het parameters through the querydef implementation at run time. It is easier in that you can use the query builder to get the query corect without having to type the entire SQL statement (rembering the syntax). It is also a bit quicker, but that would only make a difference for high usage situations.





MichaelRed
mlred@verizon.net

 

[PHV]

Have you tried to replace this:
Dim rs As Recordset
By this:
Dim rs As DAO.Recordset
You have to reference the Micrososoft DAO x.y Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[WAR40K]

Thanks everyone for all your input, I finally got the code to work, I was missing the quotes for the Me!ref_wir parameter. Once I got that properly configured it worked like a charm.