Access VBA on Form Not Working

J

Joshua Barnette

I am trying to LOOP through every field on my UNBOUND form. I am doing
this to convert any ".." to "*" and then to concatenate the populated
fields into a search string for the filter. My code is below, along
with a list of fields found on the form. Any help is greatly
appreciated.

PS...I am open to changing the way this is setup.

Code:
Dim ACMD_Search_STR As String
Dim ACMD_Conv_Fiels As String
Dim ACMD_fld As Field

For Each ACMD_fld In Me.Form
ACMD_Conv_Field = ""
ACMD_Conv_Field = Replace(ACMD_fld, "..", "*")
ACMD_fld = ACMD_Conv_Field
ACMD_Conv_Field = ""
Next ACMD_fld

ACMD_Search_STR = ""

For Each ACMD_fld In Me.Form
If IsNull(ACMD_fld) Then
Else
If ACMD_Search_STR = "" Then
ACMD_Search_STR = """ & ACMD_fld.Name & " Like """ &
ACMD_fld.value & """
Else
ACMD_Search_STR = """ & ACMD_Search_STR & " And " &
ACMD_fld.Name & " Like """ & ACMD_fld.value & """
End If
End If
Next ACMD_fld

' This line is so I could view the output before running the DoCMD
below.
Me.Notes = ACMD_Search_STR

' DoCmd.OpenForm "ACMD Data", acNormal, , ACMD_Search_STR

' List of Fields on Form
' Me.Items_Needed_For_This_File
' Me.Doc
' Me.Weight
' Me.[File#]
' Me.Name
' Me.[OPart#]
' Me.[EPart#]
' Me.Voltage
' Me.RPM
' Me.HP
' Me.AMPS
' Me.MFG_By
' Me.[MFG_Part#]
' Me.[Model#]
' Me.Size
' Me.Type
' Me.Stud_Kit
' Me.[Fan#]
' Me.Three_Phase_Stator
' Me.Oil_Seal
' Me.DE_Bearing
' Me.Back_End_Bearing
' Me.Drive_Endbell
' Me.Back_Endbell
' Me.Other_Numbers_Unknown
' Me.Same_Motor_As
' Me.Notes
' Me.General_Overhaul
' Me.Exchange
' Me.Wholesale
' Me.[Computer_ID#]
 
D

Dirk Goldgar

(re-posting, as my original reply hasn't appeared)

Joshua Barnette said:
I am trying to LOOP through every field on my UNBOUND form. I am doing
this to convert any ".." to "*" and then to concatenate the populated
fields into a search string for the filter. My code is below, along
with a list of fields found on the form. Any help is greatly
appreciated.

PS...I am open to changing the way this is setup.

Code:
Dim ACMD_Search_STR As String
Dim ACMD_Conv_Fiels As String
Dim ACMD_fld As Field

For Each ACMD_fld In Me.Form
ACMD_Conv_Field = ""
ACMD_Conv_Field = Replace(ACMD_fld, "..", "*")
ACMD_fld = ACMD_Conv_Field
ACMD_Conv_Field = ""
Next ACMD_fld

ACMD_Search_STR = ""

For Each ACMD_fld In Me.Form
If IsNull(ACMD_fld) Then
Else
If ACMD_Search_STR = "" Then
ACMD_Search_STR = """ & ACMD_fld.Name & " Like """ &
ACMD_fld.value & """
Else
ACMD_Search_STR = """ & ACMD_Search_STR & " And " &
ACMD_fld.Name & " Like """ & ACMD_fld.value & """
End If
End If
Next ACMD_fld

' This line is so I could view the output before running the DoCMD
below.
Me.Notes = ACMD_Search_STR

' DoCmd.OpenForm "ACMD Data", acNormal, , ACMD_Search_STR

' List of Fields on Form
' Me.Items_Needed_For_This_File
' Me.Doc
' Me.Weight
' Me.[File#]
' Me.Name
' Me.[OPart#]
' Me.[EPart#]
' Me.Voltage
' Me.RPM
' Me.HP
' Me.AMPS
' Me.MFG_By
' Me.[MFG_Part#]
' Me.[Model#]
' Me.Size
' Me.Type
' Me.Stud_Kit
' Me.[Fan#]
' Me.Three_Phase_Stator
' Me.Oil_Seal
' Me.DE_Bearing
' Me.Back_End_Bearing
' Me.Drive_Endbell
' Me.Back_Endbell
' Me.Other_Numbers_Unknown
' Me.Same_Motor_As
' Me.Notes
' Me.General_Overhaul
' Me.Exchange
' Me.Wholesale
' Me.[Computer_ID#]
Click to expand...


I suspect that your problem is here:
Dim ACMD_fld As Field
Click to expand...

The only Field objects I know of are defined in the DAO and ADODB libraries,
and are not directly relevant to an Access form. It looks to me like you
are trying to loop through the controls on the form, which are defined by
the Access.Control data type. Try this:

Dim ACMD_fld As Control

For Each ACMD_fld In Me.Controls

That should get you past your first errors.

Next, though, you've got to fix a confusion between the empty string ("")
and Null. Don't set controls equal to "" if you intend later to test them
for Null -- Null is not equal to "". In fact, Null is not equal to
anything. Change this block of code:
For Each ACMD_fld In Me.Form
ACMD_Conv_Field = ""
ACMD_Conv_Field = Replace(ACMD_fld, "..", "*")
ACMD_fld = ACMD_Conv_Field
ACMD_Conv_Field = ""
Next ACMD_fld
Click to expand...

.... to this:

For Each ACMD_fld In Me.Controls
If Not IsNull(ACMD_fld) Then
ACMD_fld = Replace(ACMD_fld, "..", "*")
End If
Next ACMD_fld

That ought to fix most of your problems, though of course I may have
overlooked something.
 
S

Steve Sanford

Hi Joshua,

In addition to Dirk's comments, you might have some problems due to the use
of reserved words as field names. "Name", "Type" and "Size" are reserved
words in Access. Allen Browne has a list of reserved words for Access, Jet
and SQL.

See

http://allenbrowne.com/AppIssueBadWord.html


Also, the hash mark (#) is a delimiter for dates and should not be used for
object names; the other special chars should not be used either (including
spaces), except the underscore (_) and dash (-).

Another reason for not naming an object "Name" is that it is not very
descriptive. "Name" of what??? And if you have three tables with field names
of "Name", you will need to type the table name and the field name to
differentiate 'which' field you want to use (in code or a query).

There are lots and lots of naming conventions available to follow. I use a
three char prefix of the field type: "txt" for strings (txtFName), "lng" for
long integers (lngEmployeeID_PK), "int" for integers (intRepeatCycle), "dte"
for date/time (dteBegDate), ... You get the idea.

I also use "_PK" and "_FK" suffixs because it is easier for me to find the
linking fields after several months of not looking at some tables/ code.
(Yes, I suffer from CRS.)

The point is, find a naming convention you like and *use it*. :)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Joshua Barnette said:
I am trying to LOOP through every field on my UNBOUND form. I am doing
this to convert any ".." to "*" and then to concatenate the populated
fields into a search string for the filter. My code is below, along
with a list of fields found on the form. Any help is greatly
appreciated.

PS...I am open to changing the way this is setup.

Code:
Dim ACMD_Search_STR As String
Dim ACMD_Conv_Fiels As String
Dim ACMD_fld As Field

For Each ACMD_fld In Me.Form
ACMD_Conv_Field = ""
ACMD_Conv_Field = Replace(ACMD_fld, "..", "*")
ACMD_fld = ACMD_Conv_Field
ACMD_Conv_Field = ""
Next ACMD_fld

ACMD_Search_STR = ""

For Each ACMD_fld In Me.Form
If IsNull(ACMD_fld) Then
Else
If ACMD_Search_STR = "" Then
ACMD_Search_STR = """ & ACMD_fld.Name & " Like """ &
ACMD_fld.value & """
Else
ACMD_Search_STR = """ & ACMD_Search_STR & " And " &
ACMD_fld.Name & " Like """ & ACMD_fld.value & """
End If
End If
Next ACMD_fld

' This line is so I could view the output before running the DoCMD
below.
Me.Notes = ACMD_Search_STR

' DoCmd.OpenForm "ACMD Data", acNormal, , ACMD_Search_STR

' List of Fields on Form
' Me.Items_Needed_For_This_File
' Me.Doc
' Me.Weight
' Me.[File#]
' Me.Name
' Me.[OPart#]
' Me.[EPart#]
' Me.Voltage
' Me.RPM
' Me.HP
' Me.AMPS
' Me.MFG_By
' Me.[MFG_Part#]
' Me.[Model#]
' Me.Size
' Me.Type
' Me.Stud_Kit
' Me.[Fan#]
' Me.Three_Phase_Stator
' Me.Oil_Seal
' Me.DE_Bearing
' Me.Back_End_Bearing
' Me.Drive_Endbell
' Me.Back_Endbell
' Me.Other_Numbers_Unknown
' Me.Same_Motor_As
' Me.Notes
' Me.General_Overhaul
' Me.Exchange
' Me.Wholesale
' Me.[Computer_ID#]
Click to expand...
 
J

Joshua Barnette

I would like to thank both responders they both helped. I was able to
change the imported field names to eliminate reserved words and to
refine my code to be even shorter than I had hoped. My final working
code is posted below.

Thanks Again,
Joshua Barnette

Code:

Dim ACMD_Search_STR As String
Dim ACMD_fld As Control

ACMD_Search_STR = ""

For Each ACMD_fld In Me.Controls
If TypeOf ACMD_fld Is TextBox And Not IsNull(ACMD_fld) Then
ACMD_fld = Replace(ACMD_fld, "..", "*")

If ACMD_Search_STR = "" Then
ACMD_Search_STR = ACMD_fld.Name & " Like """ &
ACMD_fld & """"
Else
ACMD_Search_STR = ACMD_Search_STR & " And " &
ACMD_fld.Name & " Like """ & ACMD_fld & """"
End If

End If
Next ACMD_fld

DoCmd.OpenForm "ACMD Data", acNormal, , ACMD_Search_STR

ACMD_Search_STR = ""
 
Top