Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 10th, 2007, 10:29 AM
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
  #2 (permalink)  
Old January 11th, 2007, 08:35 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old January 11th, 2007, 09:28 AM
Registered User
 
Join Date: Jan 2007
Location: overland park, ks, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old January 11th, 2007, 09:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old January 11th, 2007, 10:03 AM
Registered User
 
Join Date: Jan 2007
Location: overland park, ks, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old January 11th, 2007, 01:12 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old January 11th, 2007, 03:41 PM
Registered User
 
Join Date: Jan 2007
Location: overland park, ks, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #8 (permalink)  
Old January 11th, 2007, 04:25 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old January 12th, 2007, 11:05 AM
Registered User
 
Join Date: Jan 2007
Location: overland park, ks, USA.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Reply With Quote
  #10 (permalink)  
Old January 12th, 2007, 12:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing variable to a public variable Hudson40 Access VBA 2 February 25th, 2005 06:23 AM
assign a recordset to a variable biggen55 VB How-To 1 March 26th, 2004 02:26 PM
How to bind text box to recordset fields? cici VB How-To 2 December 1st, 2003 02:10 AM
using fields in recordset to populate textbox stoneman Access 2 October 7th, 2003 11:03 AM



All times are GMT -4. The time now is 09:45 PM.


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