Execute VBA for each row in continuous form ?

[Exie]

Hi Folks,

I have a table, bound to a continuous form. This works fine.

However when I load the form, I want to execute code in a module and put the result in a control on that row.

Eg. My table/result set might have 5 rows, each row has a part number. For the first row, product 021055 I want to look up some extra information (using COM object) then if the user changes the data, use an onExit event to push that data back (via COM object).

Would anyone have any examples ? Advice on how best to do this ?

 

[MajP]

There is not enough information to provide a suggestion.
Describe your table structure and what you want to look up.

look up some extra information

Information from a local table?
Information from an odbc connection to a SQL database?
Information contained in Outlook appointment?
Information from multiplying all related records by pi and dividing by e*10^23?
Information contained in a web page located at

http://www...?

My first guess would be that the control is simply bound to a function that passes the record ID or some other fields.
=myFunction([myID],[fldOne],[fldTwo]....). But if you want a good answer you need to spend some time to shape a good question.

 

[Exie]

Sorry MajP,

To try and clarify.... I have 1 linked table (ODBC) to Oracle. I have this connected to my form and it lists the records correctly.

In the header of my form, I have some combo boxes which control filters on the data.

The extra information has to come from a COM object which is supplied by another application. It provides methods like GetField() which will hopefully allow me to get associated data for each product that is listed from the Oracle data source.

More specifically, the GetField() data is for Sales Forecasting, this app is trying to make is easier to view/enter this information across a large list of products.

Hope that makes it clearer. I was thinking I might need to create a sub-form or something, as I cant figure out how to get the product number for the given row in a continuous form.

 

[PHV]

Did you try to use the Current event procedure of the continuous form ?
Another way is to bound your form to a query that uses the GetField function.

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

 

[Exie]

Thanks folks.

Not quite sure how to do that though. Perhaps the attached screen shot would help ?

My current code refers directly to a control on the form like:

[Forms]![ForecastForm]![SKU].Value

There is a query, which is bound to the form. I tried to access it, but dont really know where I'm going....

Dim rstMe As DAO.Recordset
Set rstMe = Me.RecordsetClone

How would I use this getField() ?

 

[NXMold]

Make the control source a function, you can pass data from a field in the current record easily.

txtSomeTextBox.ControlSource = "fGetSomeData(SKU)"

Then make a function to retrieve the data you want from some other program, modify it if needed, and return it as the result of the function.

Function fGetSomeData(strNeededInfo)

Dim strUsefulStuff as String

strUsefulStuff = some.program.call(strNeededInfo)
fGetSomeData = strUsefulStuff

End Function

Anyway, this is wat MajP already suggested, and it only displays data. Updating would be another problem altogether.