View Single Post
  #1 (permalink)  
Old January 10th, 2007, 10:29 AM
johnslaughter johnslaughter is offline
Registered User
 
Join Date: Jan 2007
Location: overland park, ks, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing recordset fields using a Variable

I would like to edit a record set field by passing the field name as a variable.. The variable "Myfieldinformation" is also the field name in the record set SICE04_Pro_InformationTable that I want to update with the collected information. So how can I use this line of code so it knows Myfieldinformation is the field in the table based on the information that is in the Variable?

  SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation

Here is my code:

Public Function CollectSICE04ScreenInfo()

Dim MyDB As Database
Dim SICE04_Data_LocationsTable As Recordset 'Contains locations of all data fields on screen
Dim SICE04_Pro_InformationTable As Recordset 'Stores data found on the above screen
Set MyDB = CurrentDb
Set SICE04_Data_LocationsTable = MyDB.OpenRecordset("SICE04_Data_Locations", dbOpenTable)
Set SICE04_Pro_InformationTable = MyDB.OpenRecordset("SICE04_Pro_Information", dbOpenTable)

Dim Myfieldinformation As String
Dim MyFieldRow As String
Dim MyFieldCol As String
Dim MyFieldLng As String
Dim HoldFieldInformation As String

SICE04_Pro_InformationTable.AddNew 'adds the recordset "FullProNumber" to the table
SICE04_Pro_InformationTable!FullProNumber = MyProNumber

Do While Not SICE04_Data_LocationsTable.EOF
  Myfieldinformation = SICE04_Data_LocationsTable!SICE04_Name 'Name of field
  MyFieldRow = SICE04_Data_LocationsTable!SICE04_Row 'Location on screen by Row
  MyFieldCol = SICE04_Data_LocationsTable!SICE04_Col 'Location on screen by column
  MyFieldLng = SICE04_Data_LocationsTable!SICE04_Length 'Length of Field on screen
  HoldFieldInformation = "" 'clears the variable

  HoldFieldInformation = Trim(PCControl.GetText(MyFieldRow, MyFieldCol, MyFieldLng))
  'Collects the screen information by Name, Row, Column and Length

  SICE04_Pro_InformationTable.Edit
  SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation
  SICE04_Data_LocationsTable.MoveNext

Loop
  SICE04_Pro_InformationTable.Update
'Stop

End Function

Thanks for your help
John

Reply With Quote