Can I access fields dynamically? 2

[Rohdem]

I'm sure this question has probably been asked before, but I don't have time to hunt for the answer.

Here is an simplified example of my problem:

dim fieldname as string
dim rec as recordset
dim ordernumber as integer

fieldname = "OrderId"
set rec = currentdb.openrecordset("tblorders&quot
rec.movefirst
ordernumber = rec![fieldname]

What I want it to do is look up the information based on whatever I specify for the variable 'fieldname', i.e. if I set 'fieldname = cost', then it would return rec!cost, if I set 'fieldname = orderid, then it would return rec!orderid, etc. I have also tried declaring fieldname as a variant. I have struggled with this problem for several hours and have become quite frustrated. Any help you can give me would be greatly appreciated!!

Thanks
[sig]<p>Mike Rohde<br><a href=mailto:rohdem@marshallengines.com>rohdem@marshallengines.com</a><br>[/sig]

 

[TheInsider]

Hi,
You are trying to dynamically rewrite the code itself at runtime. You have to think of the programming logic, at runtime the string &quot;OrderID&quot; is going to be a string stored starting at the address of &quot;fieldname&quot;, and &quot;rec![fieldname]&quot; is going to be an address too. In Pascal, C, or C++, you could use pointers, but you don't have that option in Visual Basic, and it would not matter anyway because even with pointers; you can't replace an address with the name of an adress holder (variable) in a string format. I don't think that I did a good job explaining this, but I think you could do what you want with a series of IF statements or a SELECT CASE statement, you just need to go about it in a different way.
[sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]

 

[Rohdem]

I realize that a select case statement would work, but with 40 possibilites that may change, I was looking for something a little cleaner and shorter.

Thanks for your input. [sig]<p>Mike Rohde<br><a href=mailto:rohdem@marshallengines.com>rohdem@marshallengines.com</a><br>[/sig]

 

[TheInsider]

Hi,
Maybe this code can help a bit:

Sub FindField()
Dim r As Recordset, f As Field, s As String

Set r = CurrentDb.OpenRecordset(&quot;Client&quot

s = &quot;SIN&quot;

For Each f In r.Fields
If (f.Name = s) Then
MsgBox r.Fields(f.OrdinalPosition - 1).Value
End If
Next
r.Close
End Sub

Believe it or not, this works! It took me a few minutes to write this... here you go. It will do exactly what your pseudo-code above was supposed to do. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]

 

[TheInsider]

Please note that &quot;SIN&quot; stands for &quot;Social Insurance Number&quot; and is a field name. [sig]<p>Rob Marriott<br><a href=mailto:rob@career-connections.net>rob@career-connections.net</a><br>[/sig]

 

[Rohdem]

Thanks Rob, your a lifesaver!!!!! [sig]<p>Mike Rohde<br><a href=mailto:rohdem@marshallengines.com>rohdem@marshallengines.com</a><br>[/sig]

 

[chell]

Hello Mike;

Forget the loop! Go with RS.fields(str).value where str is a string varible and RS is a recordset.

Where ever the &quot;xx![Fieldname]&quot; syntax is used a xx(strvar) syntax can be used. For example, Forms![myform] returns the same thing as Forms(&quot;myform&quot. The advantage is that you can use string varibles. The downside is that Access help file says there is a slight performance penalty in this method.

Chell

 

[TheInsider]

Sounds like a much better (cleaner) method to me. Guess I have to give you the Tip master vote this time! =)

Rob Marriott
rob@career-connections.net