Buliding A Search For A Field Name and Extract Contents

[Guest]

I have to compare different tests we give our middle school students.

I have a form with 155 Fields. I have to seach each field (That fit 5
catagories) to see what the contents are and then find the range(s) which are
stored in a table.

Here is the code I have so far.

Rem: Store All The Field Names for a catagory. The Contents of
Me!TxtAC09 is 356.

Rem: I Check every field of a catagory Set.

Do Until IndxA = 20
IndxA = IndxA + 1
If IndxA < 10 Then
HldField = "TxtAC0" & IndxA
Else
HldField = "TxtAC" & IndxA
End If

HldWork = "Me!" & '" & HldField & "'
Rem: My problem is the above Line. I want the contents of HldWork (356),
not TxtAC09.

Rem: Below I then get the record I need.
GetRangeRec (HldWork)

Loop

End Sub

GetRangeRec
Set rstI = db.OpenRecordset("SELECT * FROM VermontSchls WHERE '" & HldWork &
"' = HSARCD")
MsgBox rstI!LSCHOOL & " " & HldField Rem: Test to see If
I Got The Right Record

End Sub

I did this once before but I just cannot remember the syntex to do it again.

Any Help Appreciated;

Thanks in Advance,


Granny

 

[Guest]

Hi Granny,

Try this:

Rem: Store All The Field Names for a catagory. The Contents of
Me!TxtAC09 is 356.

Rem: I Check every field of a catagory Set.
IndxA = 0
Do Until IndxA = 20
IndxA = IndxA + 1

HldWork = Me("TxtAC" & Format(IndxA, "00"))

Rem: My problem is the above Line. I want the contents of HldWork (356),
not TxtAC09.

Rem: Below I then get the record I need.
GetRangeRec (HldWork)

Loop

End Sub


BTW, instead of typing REM: you can use a single quote ('):

Rem: this is a comment
' And so is this!!!!



HTH

 

[Marshall Barton]

I have to compare different tests we give our middle school students.

I have a form with 155 Fields. I have to seach each field (That fit 5
catagories) to see what the contents are and then find the range(s) which are
stored in a table.

Here is the code I have so far.

Rem: Store All The Field Names for a catagory. The Contents of
Me!TxtAC09 is 356.

Rem: I Check every field of a catagory Set.

Do Until IndxA = 20
IndxA = IndxA + 1
If IndxA < 10 Then
HldField = "TxtAC0" & IndxA
Else
HldField = "TxtAC" & IndxA
End If

HldWork = "Me!" & '" & HldField & "'
Rem: My problem is the above Line. I want the contents of HldWork (356),
not TxtAC09.
Rem: Below I then get the record I need.

[snip]

Try this:

For IndxA = 1 To 20
HldField = "TxtAC" & Format(IndxA, "00")
HldWork = "Me(HldField)
Rem: Below I then get the record I need.
. . .

 

[Guest]

Sorry Guys I did not explain it well enough!

First I have to determine what Field I need. I find out that it is Field
"TxtAC09".

In the variable HldScript I Have the Field Name 'TxtAC09'.

The Contents of TxtAC09 is the number 356.

I need for the Field Name HldField to be 356, not TxtAC09.

However, thanks for the code showing the use of Format, I knew it but forgot
it, so I saved about 200 lines of code.

HldScript = "Me!TxtAC" & Format(IndxA, "00")
HldField = "Me(HldScript)"

Thanks for your help!

Granny

I did this once before about 4 years ago, so I know it can be done.

 

[Marshall Barton]

Sorry Guys I did not explain it well enough!

First I have to determine what Field I need. I find out that it is Field
"TxtAC09".

In the variable HldScript I Have the Field Name 'TxtAC09'.

The Contents of TxtAC09 is the number 356.

I need for the Field Name HldField to be 356, not TxtAC09.

However, thanks for the code showing the use of Format, I knew it but forgot
it, so I saved about 200 lines of code.

HldScript = "Me!TxtAC" & Format(IndxA, "00")
HldField = "Me(HldScript)"

That has some syntax amomalies. Let's see if this is any
better:

HldScript = "TxtAC" & Format(IndxA, "00")
HldField = Me(HldScript)

 

[Guest]

Marshall;
The Variable HldField is a value of Me(HldScript) not 356.

Just wish I could remember how I did it before!

Thanks for Helping

Granny

 

[Guest]

I found an error (too much cut & paste), so here is the code again with a
couple more changes

'------------------------------------------

' Store All The Field Names for a catagory. The Contents of Me!TxtAC09
is 356.
' I Check every field of a catagory Set.
IndxA = 0
Do Until IndxA = 20
IndxA = IndxA + 1

' this is the control name
HldField = "TxtAC" & Format(IndxA, "00")

' this gets the value in TxTAC
HldWork = Me("TxtAC" & Format(IndxA, "00"))

' Tell me the results
msgbox "HldField = " & HldField & vbcrlf & vbcrlf & "HldWork = " &
HldWork

' Below I then get the record I need.
GetRangeRec (HldWork, HldField)

Loop

End Sub
'------------------------------------------

'if 'HSARCD' is a text string, then it also needs to be enclosed in quotes

Sub GetRangeRec(pWork as string, pField as string)
Set rstI = db.OpenRecordset("SELECT * FROM VermontSchls WHERE '" &
pWork & "' = 'HSARCD'")
MsgBox rstI!LSCHOOL & " " & pField Rem: Test to see If I Got
The Right Record

End Sub
'------------------------------------------

In the variable HldScript I Have the Field Name 'TxtAC09'.

I did not see a variable "HldScript" in the posted code????



HTH

 

[Marshall Barton]

It sound like you still have quotes in the second line.

 

[Guest]

Steve;
I have never worked with controls and my manuals and Google did not
give me much!

' Here is the code as I have it this minute!

Dim HldCntrl as Control
Dim IndxA as Byte
Dim HldField as String

IndxA = 0

Do Until IndxA = 20
IndxA = IndxA + 1
HldCntrl = "TxtAC" & Format(IndxA, "00") Rem Line I get
error message
HldField = Me("TxtAC" & Format(IndxA, "00"))
MsgBox HldCntrl & " - " & HldField
' GetRangeRec(HldField, arCTL)
Loop

Rem My Sub-Routine has always worked great when I get the right results.

Error-Message

Object variable or with Block variable not set!

Sorry Steve, just never worked with Controls and I am having a hard time
understanding them!


Thanks Again

Granny

 

[Guest]

This is the line that is giving you the error:

Dim HldCntrl as Control

You are taking a string, "TxtAC", and the counter (IndxA) formatted as a two
char string, concantating them and storing the result in a *string* variable
(HldCntrl).

So the line should be:

Dim HldCntrl as String


Another error might be in this line:

' GetRangeRec(HldField, arCTL)

What is "arCTL"? Not having seen your complete Sub (either one), it is hard
to tell what the variables are and what the code is doing.


Would you explain a little more? Maybe what you are trying to do, some
initial data and what the results should look like?

HTH