Question Refresh Form Data

[anonamux]

Hello, I am working on a Purchase Order database in Access 2010 for a small company.

I have a purchase order form which gets data from the vendor table.

So when a user selects a vendor from the combo box, I want the corresponding information (vendor phone #, address, etc.) to display in the text box below in-case it needs to be edited by the user.

I can get it to display the correct information by creating a macro that closes the form and re-opens it, but then I have to cycle through all the records to get back to the one I was working on. Tried using "GoToPage: Last" in the macro builder but it will not cooperate.

Any help is greatly appreciated!

 

[cpberg1]

Read up on me.requery

You would want to use it in the after update event of your combo box.

Chris

 

[Pat Hartman]

The simplest solution is to change the RecordSource of the form to be a query that joins to the vendor table. That allows you to choose fields from each table and bind them to controls on the form. I generally lock down "lookup" fields to prevent accidental changes but if you want to allow edits, that's fine, leave the Locked property set to No which is the default.

Once you've change the RecordSource, you can get rid of the macros that are populating the fields.

Read up on me.requery

Requering the form reruns the form's RecordSource query. That will reposition the form back to the first record which is exactly the problem the OP has.

 

[anonamux]

I thought that my record source is already a query that gets data from the vendor table, otherwise how would it get that data?

Like so:

SELECT [Purchase Orders].*, Vendors.[Contact Name], Vendors.[Contact Name], Vendors.[Vendor Name], Vendors.Address, Vendors.City, Vendors.[Zip Code], Vendors.[State/Province], Vendors.Phone FROM Vendors INNER JOIN [Purchase Orders] ON Vendors.VendorID = [Purchase Orders].Vendor.Value;


I tried in VB on the After Update of the combo box:

Me.Requery <-- Error
Me.Vendor.Requery <-- nothing happens
Me.Requery = Me.Requery <-- Crashes

I read up a little on Me.Requery and DoCmd.Requery, am I doing this wrong?

I just want it to update the Vendors.[Contact Name], Vendors.[Address],Vendors.[City], Vendors.[Zip Code], Vendors.[State/Province], Vendors.[Phone] text boxes.

Would RefreshSharePointList be a viable option? that refreshes the data that is shared on the form correct?

 

[Pat Hartman]

The vendor in this case is a "lookup". The form is used to enter/update purchase orders and you want to display the vendor information for reference. On the form will be a combo bound to the VendorID in the PO table. When you choose a vendor from that combo, Access automatically populates the name, address, etc. fields. There is no need for macros or to close and reopen the form. Make sure that the combo is bound to the correct column.

 

[anonamux]

"Make sure that the combo is bound to the correct column."

Does that mean the Row Source of the combo box?

SELECT [Vendors].[VendorID], [Vendors].[Vendor Name] FROM Vendors ORDER BY [Vendor Name];

"Vendors" is the name of the table... so I mean that should be right. I deleted all the macros and requery bits in VB. Still not populating though.

Thanks for being so helpful by the way.

 

[Pat Hartman]

No. The property that binds a control to a table field is the ControlSource. The RowSource is simply a list of possible values.

 

[anonamux]

Ahah! my Control Source was "Vendor" when the table name is "Vendors" so I changed it so it now reads "=[Vendors]"

This may have fixed it, but now upon clicking any of the drop down choices nothing happens, is this because my OnClick Event is blank?


Private Sub Vendor_Click()


End Sub

(Vendor is the name of the combo box)

I suppose from now on I should start tables with "tbl" and forms with "frm" as a good practice eh?

 

[Pat Hartman]

Remove the =. Having = in the first character tells Access that this is a calculated control. You will not be able to select a value from the combo and that value will not be saved.

For the sake of sanity (mine), I use a consistant naming convention for all objects and I recommend it to others frequently. The method you use is not as important as the consistancy with which you apply it.

 

[anonamux]

The = doesn't make a difference, still not clickable. Still not populating fields.

 

[Pat Hartman]

Open the query outside the form. Can you update that field? Make sure you are selecting the vendor from the PO table NOT the vendor table.