Problem with recordset (fieldname = variable)

[FLM]

Hello,

I'm trying to create a general module, that i can use in different databases.

Sub FLogin(tablename, user, computer, InOut, type)
Dim rslogin As DAO.Recordset
Set rslogin = CurrentDb.OpenRecordset(tablename, dbOpenDynaset)

rslogin.AddNew
rslogin!user = fOSUserName()
rslogin!computer = fOSMachineName()
rslogin!InOut = type
rslogin.update

rslogin.close
End sub​

The problem now is that vba doesn't look at the value of the variables (tablename, user, computer, InOut), but sees the name of the variable as the fieldname:

ex.: call flogin("tbllogin","logname","logpc","logtype","LogIn")

In my recordset vba sees "rslogin!user" as "rslogin!user", but i want it to work with it as "rslogin!logname", the fieldname i give in my call.
The same problem with the other fiels (logpc, logtype).

How can i do this?

Any help appreciated.

Frank

 

[deBassMan]

Hi

can you not assign the incoming arguments to variables and use those in the rest of the code?

cheers

 

[FLM]

I think that the incoming arguments are already seen als variables.

The problem is that access doesn't look at those variables as variables, because they are behind the "!".

I had the same problem with code for a form:
myform.updatefield had to be changed to myform.Controls(updatefield)

I'm looking for the same "trick" for a recordset.

Frank

 

[deBassMan]

What do you get if you insert with the following?


Sub FLogin(tablename, user, computer, InOut, type)

msgbox tablename
msgbox user
msgbox computer
msgbox InOut
msgbox type

Dim rslogin As DAO.Recordset
Set rslogin = CurrentDb.OpenRecordset(tablename, dbOpenDynaset)

rslogin.AddNew
rslogin!user = fOSUserName()
rslogin!computer = fOSMachineName()
rslogin!InOut = type
rslogin.update

rslogin.close
End sub

 

[FLM]

The msgbox gives me the value of the variable (as it should)
and not the name of the variable

I only want to know how to adress a recordsetfield, if the fieldname is in a variable.

Frank

 

[deBassMan]

If I understand your problem ...

you cannot address a recordset's fieldname with a var

rearrange things so that you Find the correct record first and then plug in the values.

good luck matey

 

[FLM]

good luck matey

Thanks for trying.

Anybody else got an idea?

Frank

 

[ejstefl]

OK, No idea if this is the "right" way to do it, but it will work...

Sub FLogin(tablename, user, computer, InOut, type)
Dim rslogin As DAO.Recordset
Dim iUserField as integer, iComputerField as integer, iInOutField as integer

Set rslogin = CurrentDb.OpenRecordset(tablename, dbOpenDynaset)

For i = 0 to rs.Fields.Count - 1
     If rs.Fields(i).Name = user Then
          iUserField = i
     ElseIf rs.Fields(i).Name = computer Then
          iComputerField = i
     ElseIf rs.Fields(i).Name = InOut Then
          iInOutField = i
     End If
Next i

rslogin.AddNew
rslogin.Fields(iUserField) = fOSUserName()
rslogin.Fields(iComputerField) = fOSMachineName()
rslogin.Fields(iInOutField) = type
rslogin.update

rslogin.close
End sub

Of course, you'll want to add error handling in case you pass it a field name that does not exist in your recordset.

 

[grnzbra]

If it works, of course it's the "right" way. Now could you please tell me if you could use this same type of thing

rslogin.Fields(iUserField) = fOSUserName()

to address a global variable in a module instead of a field in a recordset.

 

[ejstefl]

grnzbra, I'm not sure what you are asking...

 

[grnzbra]

Sorry to hijack the thread, but I've asked about this before and got no response. This is very close to what I want to do. What you are doing here is referring to fields in a recordset. You could do the same thing with fields in a form. However, if the "fields" are declared global variables in a module, I don't know how to refer to them.

Module1.Fields(VariableName) =
or perhaps
Module1.Variables(VariableName)=

You are dealing with a collection of something, fields in recordset, fields on form, but what are you dealing with when you have variables in a module? How do you reference it.

 

[ejstefl]

Hmmm, I think I'm still missing the boat - sorry! Do you have an example that you could post?

 

[FLM]

OK, No idea if this is the "right" way to do it, but it will work...

Thanks ejstefl,

That was exactly what i was looking for.
I didn't use the loop, but make the folowing changes

rslogin.AddNew
rslogin.Fields(User) = fOSUserName()
rslogin.Fields(Computer) = fOSMachineName()
rslogin.Fields(InOut) = remark
rslogin.update

I couldn't use "type", because it's a protected name, so i changed it to remark.
It seems i can adress the field using it's name.

Frank

 

[grnzbra]

What I am doing now is feeding in a variable name and a value to a sub which references the global variable with a case statement

Select Case Variable
case "StartDate"
...StartDate = value
case "EndDate"
...EndDate = value
case "OfficeNo"
...OfficeNo = value
etc
End Select

Any time a new parameter is added to the system, more code must be added. What I would like to do is feed the parameter name in as a variable and use one line to load the variable.

I am using something similar to what you suggested for placing the textboxes on the form and for reading them. It's all in a very neat loop except the actual loading of the values into the global variables. What I want to do is exactly what is being done here except that instead of loading values into fields in a recordset in the manner you suggested (rslogin.Fields(iUsername) = ... where iUserField is a variable), I would like to load values into global values declared in a the module GlobalVariables in the Modules tab as:

Public StartDate as date
Public EndDate as date
Public OfficeNo as string

etc

Seems like it should be do-able; it's a collection just like the collection of textboxes on a form.