Indirection of Field Names in Code 1

[BryanEaton]

Hello,

I am having some trouble with indirection. I read the thread from Aug 7 02 which referred to a control on a form, I am trying to apply it to populating a table from another table. I am in Access 97.

Source Table Plan has these fields:
Contract - Text 10 eg 1231231234
CC - Text 8 eg 8110
Name - Text 40 Fred
WEF - Date 04/21/03
StatusCode - Text 1 P
PlanHours - Single 40

Target Table StaffPlan has these fields:

ContractNum - Text 10
CC - Text 8
EmpName - Text 40
StatusCode - Text 1
TotalPlan - Single
then a field for every week in field WEF from table "Plan" in yymmdd format eg '030407' '030414' '030421' etc - each of which is Single to hold the hours from the plan table.

Here is my code segment:

Dim MyWeeksHours As Field
Set rst = dbs.OpenRecordset("Plan", DB_OPEN_DYNASET)
Set rstTarget = dbs.OpenRecordset("StaffPlan", DB_OPEN_DYNASET)

With rst
.MoveFirst
Do Until .EOF
rstTarget.MoveFirst
Do Until rstTarget.EOF
If ![CONTRACT] = rstTarget![ContractNum] And ![CC] = rstTarget![CC] And ![Name] = rstTarget![EmpName] Then
rstTarget.Edit
MyWeeksHours = Format(![WEF], "yymmdd&quot
rstTarget!MyWeeksHours = ![PlanHours]
rstTarget.Update
End If
rstTarget.MoveNext
Loop
rstTarget.close
.MoveNext
Loop
.close
End With

Set dbs = Nothing

It falls over on the
MyWeeksHours = Format(![WEF], "yymmdd&quot
part saying "Object variable or With block variable not set"

I am sure that I am overlooking something really simple...
MyWeeksHours should hold the field name that I am trying to set.

Many thanks

Bryan

 

[MichaelRed]

w/o cpmment on the CODE, hte concept is directly aginst relational db standards., it violates relational 'rules'. See advanced search key word "COBB", and return a plethora of references to the 'rules'.

To sugget that you would consider the process described in the discussion portion of your post strongly suggestss that you should study relational database concepts theough third party refernce manuals, formal course work on the subject, (alas always the LAST RESORT) even help in Ms. A. (which DOES include a brief beginner's tutorial).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BryanEaton]

Hello Michael,

Let me leap backwards to what I actually want out of this!

I need to be able to populate a table with the info in table 'Plan': ie. Contract, Cost Code, Name and hours per week for Planned, Actual and Forecast. Then print a report, or output the data to a spreadsheet, with summaries of the hours per Cost Code and Contract by week thus.

[tt]Contract: 1231231234
Cost Code: 8110
3/7 3/14 3/21 3/28
Fred P 40 40 40 40
A 38 38
F 38 38
George P 20 20 20 20
A 25 24
F 22 20
Mary P 20 20 20 10
A 15 15
F 15 15
8110 Total P 80 80 80 70
A 78 77
F 75 73

Cost Code: 8120
Henry P 40 40 40 40
A 40 40
F 40 40
Martin P 40 40 40 40
A 40 40
F 40 40
PatrickP 40 40 40 40
A 40 40
F 40 40
8120 Total P120 120 120 120
A120 120
F 120 120

1231231234 P200 200 200 190
A198 197
F 195 195
[/tt]
I tried using a subreport, repeating it across the main report, but that is limited to 30 'repeats', I need to hit 40 to 50 (weeks).

I thought about a crosstab query, which I tried, but how do I print a report with the breaks and subtotals I need.

Sorry to make you reel backwards in distain, but it seemed the only way to go in my desparation. I have read some books on relational db, but only being a user, not a programmer, I am at a loss how to get around the problem. The MSAccess online help didnt seem to provide any answers, maybe I just dont know what question to ask.

If you can shed any light on my actual problem, I would appreciate it.

Seperatly, if you could comment on the indirection question, I would be interested in how to do it anyway.

Many thanks

Bryan

 

[MadJock]

Bryan,

Just at quick glance, I think your problem is fairly simple -

The reason you are getting that error is that MyWeekHours is a Field (i.e. an object). When using an object you must use set (e.g. set MyWeekHours = blah)

The next problem is
using the format() function will return a string. The MyWeekHours variable you have declared is an object of type Field, therefore you cannot set it to be a string.

Hope this helps,

Graeme

website:

http://www.graeme-taylor.co.uk

 

[MichaelRed]

The name of a field is a string, You have delcared [MyWeekHours] as a field, and yet not used it (in the stateemtnwhere it is assigned) as part ot the recordset fields collection. Thus the failure. It is not avctually being addressed as an OBJEST (field). Change the declaration to String and that statement should be O.K., however you then have the problem in hte following line, which excects the (now string varable) to act as a field reference (properly qualified as a member of the recordset), so htat must also be modified to refer to the "ordinal" for the field using the literal (string) with the name, as in:

rstTarget!(MyWeeksHours) = ![PlanHours]

On the other hand, the procedure -even as shown- requires the prior existance of the recordset StaffPlan with the fields which would appear on your crosstab report to already exist, so populating the recordset will fail whenever the recordset Plan is modified to include new ([WEF]) fields, the process will fail on the procedure will fail on other issues.

More trouble would appear to lie in the future, in that you only edit fields matching your criteria, so some fields could be left without editing due to selection criteria and thus have an (adverse) impact on the report output.

I see the criteria (contract, cc and name?) for selecting only source records which match the contract for updating, but not for setting these in the target recordset, so -the assumption- is that the target recordset includes all relevant values fro these fields, and you are simply updating the ones you (momentarily) need for the specific report run, this begs the issue of maintaining the the synchronization of these fields between the recordsets -external to the information posted here. The easiest example of this would arise from general Employee Action events in the company which are (presumably) reflected in the PLAN, but then need to ALSO be carried over to the StaffPlan, An Employee was hired and assigned to work on a project so PLAN is updated, but StaffPlan is not. OOPs - Sllop ... report goes bang!

Then, there is the concept of laying out your report " ... I need to hit 40 to 50 (weeks) ... ". The column headings are formatted for 6 chars and (one assumes) at least a single space for seperation), or at least 7 chars per 'week' listed plus the ~~ 60 chars for the identifiers (Contract, name ...) so the report needs the ~~ 60 + (50 * 7) ~= 400+ characters across the page. Assuming you will use at least legal size pper in landscapr more, you end up with ~~ 13.5 in of printable (horizontal) area. Something like 30 ?Characters per inch?. Does this imply reading glasses for the consumers?

Altogether, 'it' seems a bit much to me, but perhaps you have thought through the process from a different perspective and already resolved these (and other) issues.





Finally, my response is not in "distain", but (perhaps) closer to panic or fear. I think I am generally a realist, and expect individuals to cope with thier requirements on the basis of the tools available to them. I DO understand (and practice) the idiom ("When all I have is a hammer, everthing looks like a nail&quot. Attempting to tell another person about the possabillity of a screwdriver is not distain, but a simple gesture to attempt to help. There are always those who will continue to change the light bulb with the hammer, regardless of the effectivness and saftey issues involved.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BryanEaton]

Thank you for your advice.

I tried Dim MyWeekhours As String and rstTarget!(MyWeeksHours) = ![PlanHours], but that gives me a Compile error of "Type-declaration character does not match declared data type", I tried surrounding with "" and []and I tried using a number in place of MyWeeksHours, all to no avail.

I am reading about Field Ordinality in the on line help and try what I can glean from there.

A bit more background: The staff plan is currently in Excel, the date is only shown as mm/dd, and it is printed on 11x17 in landscape. (The print is small but readable) I need this data in my Access database to provide Planned, Actual and Forecast data for the appropriate week in some of my reports, and to graph the cumulative of same with other data from elsewhere in the db. The cumulation and graphing I have in hand, works fine, its trying to replicate a spreadsheet in a report that is causing me grief. Rather than re-enter the data and keep Excel synchronized with Access manually, I wanted to have the staff plan maintained in Access only. But I still need to print the report as close to its spreadsheet format as possible.

Im thinking that having the date AS the fieldname is unwise, maybe use the week number (1,2,3...) would be better? or using an array (Ive never had much luck with arrays either, I do understand the concept, just not applying it to my apps).

The procedure I am writing will be run from the OnOpen event of the report, and it deletes the [StaffPlan] table, then recreates it, with a Single type field for each week within the min and max date range of field [WEF] in table [Plan].

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("StaffPlan&quot

With tdf
.Fields.Append .CreateField("ContractNum", dbText, 10)
.Fields.Append .CreateField("CC", dbText, 8)
.Fields.Append .CreateField("EmpName", dbText, 40)
.Fields.Append .CreateField("HrsStatus", dbText, 1)
.Fields.Append .CreateField("TotalHrs", dbSingle)
'create a field for each week in our range
Do Until MyCounter = MyWeeks
.Fields.Append .CreateField(Format(MyWEF, "yymmdd&quot, dbSingle)
MyCounter = MyCounter + 1
MyWEF = DateAdd("WW", 1, MyWEF)
Loop
End With

' Append TableDef object to TableDefs collection of database.
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
Set dbs = Nothing

And this works fine in creating a table with a field to hold the data, being dynamic enough to change according to project needs. I agree that pushing it to more than 50 weeks will make it less readable.

I was brought up on Artemis database many years ago which had an easy to use Indirection method where you could assign a string to a variable and then use it as a field name. I also used indirection in Lotus 123 years ago, again it seemed simple then. My aging brain is having trouble now!

Maybe Im at a point that I am using a screwdriver as a hammer! So long as I progress I can end up using the screwdriver properly, which I hope I do. Ive also looked up distain in the dictionary - dosnt impart what I intended.

Thanks again.

Bryan

 

[MichaelRed]

Shakesperian (much ado ...) it appears that you are simply rushed and not considering the whole of the apple, and fincing only hte seeds (of angst?).Again, withoout ANY belief that the situation is useful -execpt as an illustration of POOR technique- I generated the table "Plan" from your "listing" and initial post. I then (slightly) modified the code to generate the "StaffPlan" table (to stand as a SEPERATE procedure), and made sufficient modification to your original procedure to populate "StaffPlan".

I remain convinced that the entire concept is a poor approach gone sadly wary through the philosphy "There is never time to do it right -BUT there is always time to do it over".

without portfolio or pulpit, alas ye humble servant can only heckle from hte sidelines:

Public Function basStaffPlan()

    'Source Table Plan has these fields:
    'Contract   - Text 10  eg 1231231234
    'CC         - Text 8   eg 8110
    'Name       - Text 40  Fred
    'WEF        - Date     04/21/03
    'StatusCode - Text 1   P
    'PlanHours  - Single   40

    'Target Table StaffPlan has these fields:

    'ContractNum - Text 10
    'CC          - Text 8
    'EmpName     - Text 40
    'StatusCode  - Text 1
    'TotalPlan   - Single
    'then a field for every week in field WEF from table "Plan" _
     in yymmdd format eg '030407' '030414' '030421' etc - each of _
     which is Single to hold the hours from the plan table.

    'Here is my code segment:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstTarget As DAO.Recordset

    Dim MyWeeksHours As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Plan", dbOpenDynaset)
    Set rstTarget = dbs.OpenRecordset("StaffPlan", dbOpenDynaset)

    With rst
        .MoveFirst
        Do Until .EOF
'            rstTarget.MoveFirst
'            Do Until rstTarget.EOF
'                If ![Contract] = rstTarget![ContractNum] And ![CC] = rstTarget![CC] And ![Name] = rstTarget![EmpName] Then
                    rstTarget.AddNew
                        MyWeeksHours = Format(![Wef], "yymmdd")
                        rstTarget!ContractNum = !Contract
                        rstTarget!CC = !CC
                        rstTarget!EmpName = !Name
                        rstTarget!StatusCode = !StatusCode
                        rstTarget(MyWeeksHours) = ![PlanHours]
                    rstTarget.Update
'                End If
'                rstTarget.MoveNext
'            Loop
'            rstTarget.Close
            .MoveNext
        Loop
        .Close
    End With
    
    Set dbs = Nothing

End Function
Public Function basMkStafPln(MyWeeks As Integer, Wef As Date) As Boolean

    'Target Table StaffPlan has these fields:
    'ContractNum - Text 10
    'CC          - Text 8
    'EmpName     - Text 40
    'StatusCode  - Text 1
    'TotalPlan   - Single
    'then a field for every week in field WEF from table "Plan" _
     in yymmdd format eg '030407' '030414' '030421' etc - each of _
     which is Single to hold the hours from the plan table.

    Dim dbs As DAO.Database
    Dim tdf As TableDef

    Dim MyWks As Integer
    Dim MyWef As Date
    Dim MyCounter As Integer

    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("StaffPlan")

    MyWef = Wef

    With tdf
        .Fields.Append .CreateField("ContractNum", dbText, 10)
        .Fields.Append .CreateField("CC", dbText, 8)
        .Fields.Append .CreateField("EmpName", dbText, 40)
        .Fields.Append .CreateField("StatusCode", dbText, 1)
        .Fields.Append .CreateField("TotalPlan", dbSingle)

        'create a field for each week in our range
        Do Until MyCounter = MyWeeks
            .Fields.Append .CreateField(Format(MyWef, "yymmdd"), dbSingle)
            MyCounter = MyCounter + 1
            MyWef = DateAdd("WW", 1, MyWef)
        Loop
    End With
    
    ' Append TableDef object to TableDefs collection of database.
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    Set dbs = Nothing

End Function
'__________________________________________________
Public Function basStaffPlan()

    'Source Table Plan has these fields:
    'Contract   - Text 10  eg 1231231234
    'CC         - Text 8   eg 8110
    'Name       - Text 40  Fred
    'WEF        - Date     04/21/03
    'StatusCode - Text 1   P
    'PlanHours  - Single   40

    'Target Table StaffPlan has these fields:

    'ContractNum - Text 10
    'CC          - Text 8
    'EmpName     - Text 40
    'StatusCode  - Text 1
    'TotalPlan   - Single
    'then a field for every week in field WEF from table "Plan" _
     in yymmdd format eg '030407' '030414' '030421' etc - each of _
     which is Single to hold the hours from the plan table.

    'Here is my code segment:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstTarget As DAO.Recordset

    Dim MyWeeksHours As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Plan", dbOpenDynaset)
    Set rstTarget = dbs.OpenRecordset("StaffPlan", dbOpenDynaset)

    With rst
        .MoveFirst
        Do Until .EOF
'            rstTarget.MoveFirst
'            Do Until rstTarget.EOF
'                If ![Contract] = rstTarget![ContractNum] And ![CC] = rstTarget![CC] And ![Name] = rstTarget![EmpName] Then
                    rstTarget.AddNew
                        MyWeeksHours = Format(![Wef], "yymmdd")
                        rstTarget!ContractNum = !Contract
                        rstTarget!CC = !CC
                        rstTarget!EmpName = !Name
                        rstTarget!StatusCode = !StatusCode
                        rstTarget(MyWeeksHours) = ![PlanHours]
                    rstTarget.Update
'                End If
'                rstTarget.MoveNext
'            Loop
'            rstTarget.Close
            .MoveNext
        Loop
        .Close
    End With
    
    Set dbs = Nothing

End Function
[code]

Hopefully you will stumble early, often and badly.  Thus seeing the overly numerous pitdfalls of the affair.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BryanEaton]

Thanks for your help and advise, and apologies for not giving stars earlier. For quick relief I went the route of a crosstab query for my report. I have yet to successfully get a dynamic solution working... a work in progress when I have the time. Again many thanks.