Using variable to refrence field (1 Viewer)

[robtarr]

I have a sub routine that needs to be able to do the same calculations to a number of different fields, depending on what button the user clicks.

Right now it's hard coded:

Me!Name.Value = Me![70].Column(1) & " " & Me![70].Column(2)

How can I make that 70 a variable passed to the routine so I doin't have to simply rewrite the routine 13 times and hardcode each different one?

I hope this makes sense...

 

[Drevlin]

What actions do the buttons that drive this have? It seems that you should start with that code to pass the information on to this proceedure. There was a question asked not too long ago in this forum referencing how to pass the name of the pressed button to a function/proceedure, you could use the same concept to pass the controls that the buttons are driving to your code.

I don't have enough information to give you a more thorough explanation though.

 

[robtarr]

right now each button is executing a procedure when it is clicked. That procedure calls the main subroutine with a variable based on what button was clicked, so the parameter would be "70", "75", "80", etc. that number corresponds to a list box that I need to get data from, so how do I put that variable into my select statement?

matchRecord = "SELECT * FROM Matches " _
& " WHERE weightClass = " & 70 & " AND " _
& " wrestlerID = " & Me![70lbs ].Column(0) & " AND " _
& "meetID = " & Me!SchoolList.Value

Does this make sense?

 

[Drevlin]

It sounds like you just need to set up your main subroutine to accept a variable and then have your buttons pass their variables to it:


Private Sub Button70_Click()
myProc 70
End Sub

Sub myProc(lngNum as Long)
matchRecord = "SELECT * FROM Matches " _
& " WHERE weightClass = " & lngNum & " AND " _
& " wrestlerID = " & Eval("Me![" & lngNum & "lbs ].Column(0)") & " AND " _
& "meetID = " & Me!SchoolList.Value
'Run Code

End Sub

 

[robtarr]

I tried that, but I get this error

Run-Time Error '2482':

can't find the name 'Me' you entered in the expression

 

[robtarr]

instead of using 'Me', I used Forms!formname!subformname.form.value, and that worked fine.

Thanks for your help. I really appreciate it.