How to refresh a field automatically?

[sujith123]

Hi All,

I have a form which has a field named VendorID it gets data from the following query

SELECT TOP 2 V.VendorID, V.VendorName, COUNT(A.ClaimNumber) AS Total_assignment, V.VendorStatus
FROM dbo.VendorMaster V LEFT OUTER JOIN
dbo.VendorAssignment A ON V.VendorID = A.VendorID
GROUP BY V.VendorID, V.VendorName, V.VendorStatus
HAVING (V.VendorStatus = N'Active')
ORDER BY COUNT(A.ClaimNumber)

So, if we look at the code it should give me top 2 by ascending , but whats happening here is once i refresh it gives me top 2 and it stays the same until and unless i go to view mode and come back to the form mode( refresh) again. Can someone please help me with the automatic code or something which can make this one update automatically?

Thanks a lot for your help.

 

[Galaxiom]

The Refresh Method only show changes to existing records.

New records requires a Requery.

 

[sujith123]

Hi
I have seen many forums saying to use me.requery but I am not sure where to put this code. Can you please help me with this.
thanks

 

[Jass7869]

Try putting me.Refresh at the end after it performs everything. I am new here too...but I am picking it up

 

[Privateer]

I am guessing your "field" is really a combo box. Open the form in design mode and the properties sheet for that "field". Select the events tab and locate "On Got Focus". Click on the button with the three periods off to the right, select "Code Builder". This will open VBA and create an event procedure for that field's event. Type me.VendorOption.requery. This will cause the combo box to re-run your query every time a mouse clicks on it. Obviously I made up the name of the combo box, but you get the idea.