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.Edit
  SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation
  SICE04_Data_LocationsTable.MoveNext

Loop
  SICE04_Pro_InformationTable.Update
'Stop

End Function

Thanks for your help
John


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?



mmcdonal

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
John



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?

mmcdonal

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:

SICE04_Pro_InformationTable.AddNew

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

   SICE04_Pro_InformationTable.AddNew
   ...
   SICE04_Pro_InformationTable.Update

   SICE04_Data_LocationsTable.MoveNext
Loop

Did that help?


mmcdonal

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.Edit
  SICE04_Pro_InformationTable!Myfieldinformation = HoldFieldInformation
  SICE04_Data_LocationsTable.MoveNext

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...

thanks
John


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?



mmcdonal

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.

RecordTable
RecordName Field1 Field2 Field3 field4
 123-456789



This is the table I loop through to gather information for my record set “123-456789”
LoopTable
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.AddNew
RecordTable!RecordName = “123-456789”
RecordTable.Update

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
Update

Loop





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?




mmcdonal

johnslaughter January 12th, 2007 12:39 PM

All of the data I am capturing is just text...

The screen looks like this

 SICE04 CUSTOMER SEARCH ENTRY 10:29 CS 01/12/2007

 PRO NUMBER CHK DIGIT SUFFIX BILL DATE ORG DST
 054 - 704279 7 01/11/07 ATL MED

 PPD/COL: P SERVICE TYPE: SG


 SHIPPER: NAME SIEMENS
             ADDR 3130 N BERKELEY LAKE RD NW
             CITY DULUTH ST GA ZIP 30096

 CONSIGNEE: NAME MARVINS INC
             ADDR 2015 HIGHWAY 19 N
             CITY MERIDIAN ST MS ZIP 393074959

 BILL TO: NAME SIEMENS INC % DATA2LOGISTICS
             ADDR PO BOX 61050
             CITY FORT MYERS ST FL ZIP 33906


  EXIT CITY ST ZIP COUNTRY
  ______________________ __ ______ ___



  TENDERED BY NAME
  _____________________________________


  SHIPPER ROUTING ____ TIME CRITICAL: _

 ORIGIN CARRIER LINE
 SCAC DATE BILL NBR AMOUNT
 ____ __ / __ / __ ________________ _______

 ORIGIN CARTAGE
 SCAC BILL NBR AMOUNT
 ____ ________________ _______

 FUNC ENTER-NEXT PF1-HELP PF2-MENU PF5-CUST SEARCH PF6-RESET
 KEYS: PF9-PRNT SESS PF15-ERRORS PF16-CODE MANUAL PF18-COR PF24-FORCE
 MSG ==>

the loop file looks like this:

SICE04_Name SICE04_Row SICE04_Col SICE04_Length
ProNumberProPrefix 4 2 3
ProNumber 4 8 6
ProNumberProChkDigit 4 22 1
ProNumberProSuffix 4 32 2
BillDate 4 40 8
OrgTerminal 4 55 3
DstTerminal 4 63 3
Ppd-Coll 6 11 1
ServiceType 6 36 3
ShipperName 9 19 45
ShipperAddr 10 19 45
ShipperZip 11 57 9
ConsigneeName 13 19 45
ConsigneeAddr 14 19 45
ConsigneeZip 15 57 9
BillToName 17 19 45
BillToAddress 18 19 45
BillToZip 19 57 9
ExitCity 23 3 22
ExitSt 23 27 2
ExitZip 23 31 6
ExitCountry 23 39 3
TenderedByName 28 3 37
ShipperRouting 31 19 4
TimeCritical 31 46 1
OrigCarrierSCAC 35 2 4
OrigCarrierMonth 35 8 2
OrigCarrierDate 35 13 2
OrigCarrierYear 35 18 2
OrigCarrierBillNbr 35 22 16
OrigCarrierAmount 35 40 7
OrigCartageSCAC 39 2 4
OrigCartageBillNbr 39 8 17
OrigCartageAmount 39 27 7


The table I am adding to looks like this

FullProNumber ProNumberProPrefix ProNumber ProNumberProChkDigit ProNumberProSuffix BillDate OrgTerminal DstTerminal Ppd-Coll ServiceType ShipperName ShipperAddr ShipperZip ConsigneeName ConsigneeAddr ConsigneeZip BillToName BillToAddress BillToZip ExitCity ExitSt ExitZip ExitCountry TenderedByName ShipperRouting TimeCritical OrigCarrierSCAC OrigCarrierMonth OrigCarrierDate OrigCarrierYear OrigCarrierBillNbr OrigCarrierAmount OrigCartageSCAC OrigCartageBillNbr OrigCartageAmount
043-397127



mmcdonal January 12th, 2007 12:45 PM

Okay, where did the loop file come in and what is it for?

Given the first screen, you can capture the data and post it to the table you have.

What is the loop table supposed to do?





mmcdonal


All times are GMT -4. The time now is 02:33 PM.

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