p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Passing recordset fields using a Variable (http://p2p.wrox.com/showthread.php?t=52525)

johnslaughter January 10th, 2007 10:29 AM

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!Myfieldinformation = HoldFieldInformation


End Function

Thanks for your help

mmcdonal January 11th, 2007 08:35 AM

I am not sure what you are asking. Are you asking about updating recordsets, or about scrolling to the proper recrod in a recordset for updating?


johnslaughter January 11th, 2007 09:28 AM

I have a table that contains screen locations of data. The name of those locations are in the first column of the table. I loop though that table to gather information from a mainframe screen.

Myfieldinformation = SICE04_Data_LocationsTable!SICE04_Name

The value of the variable "Myfieldinformation" changes as it program loops through the table gathering information...

I would like to use the Value that is in the Variable to add data to fields in anther record set using this line of code.

SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation

The program is looking for the field "Myfieldinformation" in the SICEOF_Pro_InformationTable to update the recordset... But I would like for it to use the Value "Myfieldinformation" was assigned when it looped through the first table. I tried putting brackets around it, bracket with quotes...

Thanks for all of your help

mmcdonal January 11th, 2007 09:31 AM

So let me say this another way:

You are looping through data returned on a screen (like command.exe) and you would like to create a new record with each loop? Or update an existing record with each loop?


johnslaughter January 11th, 2007 10:03 AM

yes.. I am adding a recordset... then I update information on that recordset

mmcdonal January 11th, 2007 01:12 PM

Well wait a second. You are creating a recordset, then updating it (which saves it.)

When you use this line:


you are adding a record to your recordset.

I think if you move the AddNew line into your loop, then you would achieve what you want here. While it is outside the loop, it seems to me it can only add one record during the first loop. After that, it should keep updating the same added record, so that you only see the last record in the last loop.

If should have this syntax:

Do While Not SICE04_Data_LocationsTable.EOF



Did that help?


johnslaughter January 11th, 2007 03:41 PM

Yea sort of....

I add the record set outside of the loop which puts it into a table with 100 fields that need to be updated...

I then loop using another table that lists the field names of all of the fields for the record just added looking at a mainframe screen by row, column and length and collecting the data. I would like to use the code...

  SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation

when I update the record set I just added if there is a way to have the variable value be used in place of the Myfieldinformation in the table write statment.

I can code it with the actual field name

SICE04_Pro_InformationTable!ACTUALFIELDNAME = HoldFieldInformation

But to do that I will have to repeat that line of code about 60 times with each field name in the table that I need to update...


mmcdonal January 11th, 2007 04:25 PM

I am still not sure what you want to do here (blonde moment).

Can you write psuedo code line by line of How it should work?

First show the structure of the database you are writing in. I get the part about parsing lines from a main frame screen.

Let me also ask, are you taking one line at a time from the main frame, and then want to break that into pieces?


johnslaughter January 12th, 2007 11:05 AM

This is the table that I am building. Note that the names of the columns are the same as the ScreenLocation record sets in the table that I am reading.

RecordName Field1 Field2 Field3 field4

This is the table I loop through to gather information for my record set “123-456789”
ScreenLocation row column length
Field1 1 1 15
Field2 2 1 20
Field3 2 25 5
field4 5 10 10

The first thing I do is add a record (123-456789) to my RecordTable. I then start my loop and find the information that needs to go ino Field1 for record "123-456789". On the first loop the variable HoldField is going to equal “Field1”. That value is the same name as the second column in RecordTable (Field1). The second loop through HoldField = Field2, which is the name of the third column in RecordTable. As I loop through the LoopTableI will now have the names of every column in RecordTable . I would like just a few lines of code that can use the value of “HoldField” each time it loops through the table and update the RecordTable so I don’t have to write separate code to update each column. Also if a “Field5” is added to the screen I am scraping, I just need to add a row (Field5) to LoopTable and a new column to RecordTable. The same if the location to Field2 changes. I just update the numbers in row, column or length. In both cases I did not have to revisit the code to update the changes to the screen.

Something like:
    RecordTable![“HoldField”] = ScreenInfo

I do not want to write the same code 50 times to update each column for therecord set 123-456789
    RecordTable!Field1 = ScreenInfo
    RecordTable!Field2 = ScreenInfo
    RecordTable!Field3 = ScreenInfo
    RecordTable!Field4 = ScreenInfo

RecordTable!RecordName = “123-456789”

Do While Not LoopTable.EOF
    HoldField = LoopTable!Screen!Location
    HoldRow = LoopTable!row
    HoldCol = LoopTable!column
    HoldLng = LoopTable!Length

    ScreenInfo = PcControl.GetText(HoldRow, HoldCol, HoldLng)

Edit Record “123-456789”
    RecordTable![“HoldField”] = ScreenInfo


mmcdonal January 12th, 2007 12:19 PM

Okay, I think I almost have it, and it is a design problem, not a code problem.

What does the screen shot look like, and then what do you want the data to look like in the table if it were in a datasheet view?


All times are GMT -4. The time now is 02:10 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.