Variable Reference Table

[Biggles]

I have the following table:
CONV_VALUE CONV_STRING TAG FIELD_VALUE_SOURCE
188743770 Task Number4 SOXIT rs_plan(3)!soxit
188743769 Task Number3 SOXHRS rs_plan(3)!soxhrs
188743982 Task Number6 OTHER rs_plan(3)!other
188743743 Task Text5 Ori_grp orig_projid
188743768 Task Number2 ITHRS rs_plan(3)!yr1ithrs
188743771 Task Number5 CA_HRS rs_plan(3)!intCaud_hrs
188743740 Task Text4 Audit Mgr rs_plan(3)!txtMGR_NAME
188743737 Task Text3 AudName rs_plan(3)!txtaudit_entity
188743767 Task Number1 AUDHRS rs_plan(3)!yr1audhrs
188743731 Task Text1 AE_NO rs_plan(2)!txtaud_no
188743734 Task Text2 AE_ABBV rs_plan(3)!ae_abbv

I open this dataset up in a recordset, but I want VBA to use the value that
the term in FIELD_VALUE_SOURCE represents, not the actual value.

for instance, if I am looking at the first record and SOXIT = 400, when I
create the SSQL statement, it should look like this: (the very last value is
400)

INSERT INTO MSP_TEXT_FIELDS
(PROJ_ID,TEXT_CATEGORY,TEXT_REF_UID,TEXT_FIELD_ID,TEXT_VALUE)
VALUES (1,1,1,188743767,400)

however, I am getting this:

INSERT INTO MSP_TEXT_FIELDS
(PROJ_ID,TEXT_CATEGORY,TEXT_REF_UID,TEXT_FIELD_ID,TEXT_VALUE) VALUES
(1,1,1,188743767,'rs_plan(3)!soxit')

Am I grasping at straws? Can I make access essentially resolve that
variable twice? I am trying to figure out a way to associate the variable
(such as RS_PLAN(3)!SOXIT) with the CONV_VALUE, without writing massive dual
array statements, which I don't actually know if I can do. This is for
moving things from Access to Project.

 

[Allen Browne]

You could use a DLookup() expression to get Access to look up the value from
a field in a table.

Details in:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Biggles]

Apparently Dlookup does not exist in MS Project VBA. I have to find
something else, thanks for the help though.

 

[Douglas J. Steele]

Sounds, then, like you're trying to pull the values from Access using VBA in
Project.

What about reversing that: push the values to Project from Access?

 

[Biggles]

I was able to write a very long select case statement, which turned out ok.
I guess this is a larger question, why does Project VBA not have things like
NZ and DLOOKUP?

 

[Douglas J. Steele]

I'm surprised it doesn't have Nz, but it doesn't have DLookup because it
doesn't have native tables that you could use DLookup against.

It's just like why Access VBA doesn't have all of the same functions as in
Excel VBA: because Access is not intended to work on ranges of values like
Excel is.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I was able to write a very long select case statement, which turned out ok.
I guess this is a larger question, why does Project VBA not have things
like
NZ and DLOOKUP?