Problem assigning value to form control

[suepowell]

Hi,

I am trying to write a routine that I can call to update a series of index's as required.

I am trying to pass to the routine a variable containing the control name on the form to be updated, and I am having trouble putting the value I want into the form variable rather then just assigning it to a variable name.

The code I am using is below, and the result I want is that the updated index number is put into the control passed in via the variable "controlname" an example of this is me.enquiryID

Private Sub IndexUpdate(IndexType As String, ControlName As String)
Dim strsql As String
Dim con As Object
Dim rstIndexNo As New Recordset
Set con = Application.Application.CurrentProject.Connection

strsql = "select IndexNo from tblIndexes where index = '" & IndexType & "'"
rstIndexNpen strsql, con, adOpenKeyset, adLockOptimistic
rstIndexNo!IndexNo = ((rstIndexNo!IndexNo) + 1)
ControlName = rstIndexNo!IndexNo
rstIndexNo.Update


End Sub

I realise what I have above just changes the valus of the varialbe controlname but I'm not sure how to get what I want.

Thanks for your help.

Sue

 

[wazz]

if i'm not mistaken, you don't have to pass anything just calculate it (in your function) and use the result. not sure how you're using this though.

yourfunction() as type
- set objects
- get the value you need
- add one to the value
(- update rs?)
- yourfunction = your new value

yourform
- yourcontrol = yourfunction

 

[gemma-the-husky]

where i've seen this you need to pass the form to the procedure as well as the field

so the sub header is

Private Sub IndexUpdate(frm as form, IndexType As String, ControlName As String)

and the statement to assign the value is
frm(controlname) = retrievednumber


Note that this assigns the value to a control on a form, rather than a variable

 

[CyberLynx]

You can either pass the Control Name to the Sub-Procedure or the Control object itself.

If you pass just the control name then your Sub-Procedure will need to know what Form this Control resides in, for example:

To call the IndexUpdate procedure:

Call IndexUpdate("WhateverTheIndexTypeIs", Me.enquiryID.Name)

The Me.enquiryID.Name is passed as a string since this is what the current procedure argument expects. The within the IndexUpdate procedure itself:

Forms("MyFormName").ControlName = rstIndexNo!IndexNo

Hard coding the Form name directly into the Procedure like this makes the procedure dedicated to a control on only one specific Form. To make the procedure work for any Form, you will then want to add an additional parameter argument named FormName. Like this:

Private Sub IndexUpdate(IndexType As String, FormName As String, ControlName As String)

The call to IndexUpdate would then be:
Call IndexUpdate("WhateverTheIndexTypeIs", "MyFormName", Me.enquiryID.Name)

and the code line to set the data into the control within the procedure would be:

Forms(FormName).ControlName = rstIndexNo!IndexNo

All this can be made a lot easier if the IndexUpdate argument ControlName was declared as a (Control) Object rather than a String, for exampl:

B]Private Sub IndexUpdate(IndexType As String, Ctrl As Control)[/B]

The Call to IndexUpdate would then be:

B]Call IndexUpdate("WhateverTheIndexTypeIs", Me.enquiryID)[/B]

with this call you're passing the Form (Me) and the control object within that Form (enquiryID).

and the code line to set the data into the control within the procedure would be:

Ctrl = rstIndexNo!IndexNo


Hope this helps.

.

 

[suepowell]

Thanks to all of you for some really usefull replies.

As Wazz came back with the first reply I have tried his suggestion and it gives me a nice simple but effective way of doing things.

I will post my working code here so it might help others.

This is the calling sub, so each time I call the function I know where I am so can hardcode the control name.

Private Sub cmdNextOrderNo_Click()
Me.OrderNo = IndexUpdate("orderno")
End Sub

This is the function.

Public Function IndexUpdate(IndexType As String)
Dim strsql As String
Dim con As Object
Dim rstIndexNo As New Recordset

If rstIndexNo.State = adStateOpen Then Set rstIndexNo = Nothing
Set con = Application.Application.CurrentProject.Connection

strsql = "select IndexNo from tblIndexes where index = '" & IndexType & "'"
rstIndexNpen strsql, con, adOpenKeyset, adLockOptimistic
rstIndexNo!IndexNo = ((rstIndexNo!IndexNo) + 1)
IndexUpdate = rstIndexNo!IndexNo
rstIndexNo.Update


End Function

This means I can call it from anywhere and it will still work.

Thanks again, I knew I needed to think about it a bit differently, but just didn't know what to try next.

Sue