URGENT! Help with Variable Field and Values into a Table..? Plz...

[Rock6431]

All,

This is an Urgent request for help and all help would be appreciated.

I have a variable table that is created upon user input and then they have their own data that they will be passing to this table. I capture the data and variable tablenames into 2 seperate arrays.

PND(50) = Field Data
PNF(50) = Field Name

I need to find a way to put the Value of PND into the Field of PNF that had a table customly created for it.

For example:

The process created a table "X" with the field name of "CustomerName". Later on, on a different form I have the array's which capture "Joe Blow" in PND(1) with PNF(1) equaling "CustomerName". How do I get the 2 arrays to send the data into the table named "X" ?

Here's what I've been trying...

rstII = openrecordset for table X yada,yada....
jx = integer loop for array
jy = recordcount -1
fld = field
flds = rstII.fields
strfn = string

rstII.AddNew
For jx = 1 To jy - 1
For Each FLD In FLDS
If FLD.Name = PNF(jx) Then
Debug.Print FLD.Name
strfn = FLD.Name
rstII.Fields(strfn).Value = PND(jx)
End If
Next
Next
rstII.Update


I know that the proper syntax would be...

with rstII
.addnew
!customername = "Joe Blow"
.update
end with

However, Since the FieldName "[customername]" is also a variable... It will not accept the above as the process will not read the field name as a variable string type of "PNF(1)".

TIA,
Mike

 

[cthaxter]

If I understand correctly, you're trying to populate a table that's already been created, with data from the array PND(), and you've stored the destination field names in another array PNF(), where each index in the first array corresponds to an index on the second.

A better way to do that would have been to create a two-dimensional array. A two-dimensional array is a table. I know that wasn't your main question, but it may make your code easier to understand, and you won't have to worry about the indexes going wrong. What if the data for PNF(37) ends up in PND(36), for whatever reason?

You should set up the array as follows:

DIM PN(50,3)

which would hold 50 rows of 3 columns (fields) of data. You can put the field names in the (0,y) index of the array.

PN(0,1)="CustomerName"
PN(0,2)="CustID"
PN(0,3)="PhoneNumber"
PN(1,1)="Mary Johnson"
PN(1,2)="55703"
PN(1,3)="800-555-1212"
PN(2,1)="Joe Blow"
PN(2,2)="38489"
PN(2,3)="900-555-1212"

and so forth.

Then, to place these into your recordset, you would do as follows:

with rstII
for x = 1 to numberofrecords
.AddNew
for y = 1 to numberoffields
.Fields(PN(0,y)).Value = PN(x,y)
next y
.Update
next x
end with

Does this answer your question? If customername is a variable name, that doesn't matter. If "customername" is the value of, say, PN(0,1), then that won't have any bearing on whether a variable has that name. Your error may have been completely different; the .AddNew and .Update were placed incorrectly, for example.

I hope this helps.

 

[Rock6431]

CThaxter:

Thanks. It makes sense to keep the arrays together and thought of this after the fact and will work to meld them as appropriate. It doesn't really matter in my current form as they are validated across 3 different tables per say. These interact with these arrays prior to them being sent to the storage table. I guess we could call them tables but in reality it is through OLE2 automation with Outlook, Word, and inside Access itself and uses the Entry ID from Outlook as the key.

Again, thanks for the clarity in your reply. I wish I could have better stated the question but you did hit it right on.... I was rather frustrated in writing the inquiry. I tried it many different times/ways prior to giving in on writing.

I'll try this in the morning.

Mike