Buliding A Search For A Field Name and Extract Contents

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
GrandMaMa said:
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.
Click to expand...
[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.
. . .
 
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.
 
GrandMaMa said:
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)"
Click to expand...


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

HldScript = "TxtAC" & Format(IndxA, "00")
HldField = Me(HldScript)
 
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
 
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'.
Click to expand...

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



HTH
 
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
 
This is the line that is giving you the error:
Dim HldCntrl as Control
Click to expand...

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)
Click to expand...

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
 
Back
Top