Compile error when running form

[dakoris73]

Hello there,

Hopefully someone can assist me with this particular situation:
I have a search form that checks three text boxes to see if there is
anything entered in there, and then checks two more text boxes to set up a
date range for the search. However, when you enter the information and try to
run the search, the VB code is showing "Compile Error: Expected Array". I
haven't setup an array on this section of code, and I'm not sure as to why it
is asking for an Array. Is this something that I should be considering?????

Here is the code that I have:

Private Sub cmdSubmit_Click()

'Create variables for the search parameters
Dim txtAgentID, txtLnGroup, txtProgram As String

txtAgentID = Me.txtAgentID.Value
txtLnGroup = Me.txtLineGroup.Value
txtProgram = Me.txtSoftware.Value

'Check to see which field is filled in, and how to react to information
If txtAgentID = "" And txtLnGroup = "" And txtProgram = "" Then
MsgBox "No information was entered. Please Re-enter your information for
what your looking for. ", vbOKCancel, "Error Message"

Else
Dim strAgtID, strTxtAgtID As String
strAgtID = Me.txtAgentID.Value
strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] = """ & strAgtID & """;"

Dim strLineGroup, strTxtLineGroup As String
strLineGroup = Me.txtLineGroup.Value
strTxtLineGroup = "SELECT AgentID, Date, Software, ProbDesc, LineGroup
FROM EOSMain WHERE EOSMain.[LineGroup] =""" & strLineGroup & """;"

Dim strProgram, txtPrgrm As String
strProgram = Me.txtSoftware.Value
strPrgrm = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[Software] =""" & strProgram & """;"

End If

' Create parameters for Date Selection
Dim strFilter As String
Dim IsDate As String

'Here is the section that I am having problems with:

If IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then
strFilter = "BETWEEN #" & Me.txtDateStart & "# And #" &_
Me.txtEndDate & "#"
ElseIf IsDate(Me.txtDateStart) And Not IsDate(Me.txtEndDate) Then
strFilter = ">= #" & Me.txtDateStart & "#"
ElseIf Not IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then
strFilter = "<= #" & Me.txtEndDate & "#"
End If

If Len(strFilter) = 0 Then 'No dates were entered, include everything
Me.FilterOn = False
Else0
Me.Filter = "EOSMain.[Date] " & strFilter
Me.FilterOn = True
End If
Me.Requery

'Take the above variables and send them to the correct Report
Dim stDocName As String
stDocName = "SoftwareSearch"
DoCmd.OpenReport stDocName, acPreview

Loop
End Sub

Any help would be appreciated.
Thank you

[Allen Browne]

Here's a few things to get you started:

1. You have a variable named txtAgentID in this module, and a control with
the same name on the form. Not a good idea.

2. You may have expected to declare 3 strings with the line:

Dim txtAgentID, txtLnGroup, txtProgram As String

However, the first 2 are untyped, so Access gives you 2 variants and a
string. Perhaps you meant:
Dim txtAgentID As String, txtLnGroup As String, txtProgram As String

3. If no data was entered, the value will be Null. Attempting to assign Null
to a string variable will fail, so this won't work when txtSoftware has
nothing in it:
txtProgram = Me.txtSoftware.Value

4. The line:

If txtAgentID = "" And txtLnGroup = "" And txtProgram = "" Then

is testing for zero-length strings, but not for Nulls.
Further, if you want the test to detect when any one of the controls is
null, you need to use Or. Try:
If IsNull(Me.txtAgentID) Or IsNull(Me.txtLineGroup) Or
IsNull(Me.txtSoftware) Then

5. After the Else, some of these issues start over again.
Apply the principles above to the whole procedure, and see how far you can
go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dakoris73" <dako...@discussions.microsoft.com> wrote in message
news:B54630AB-E418-4BC5...@microsoft.com...

[dakoris73]

Hello Allen;
Thank you for your prompt response. I have implemented the changes you
suggested, however, I am a little confused with #1, where you mention that I
have a control and module of the same name. I do apologize as I am still
learning this particular part of the programming area, and not sure where
this is located.

Also, when I rerun the code with the changes, i am still running into
problems with the IsDate section of my code stating that it is still looking
for an Array. Any thoughts???????

I've included the code for this area:

' Create parameters for Date Selection
Dim strFilter As String
Dim IsDate As String

If IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then

strFilter = "BETWEEN #" & Me.txtDateStart & "# And #" &_
Me.txtEndDate & "#"
ElseIf IsDate(Me.txtDateStart) And Not IsDate(Me.txtEndDate) Then
strFilter = ">= #" & Me.txtDateStart & "#"
ElseIf Not IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then
strFilter = "<= #" & Me.txtEndDate & "#"
End If

If Len(strFilter) = 0 Then 'No dates were entered, include everything
Me.FilterOn = False
Else0
Me.Filter = "EOSMain.[Date] " & strFilter
Me.FilterOn = True
End If
Me.Requery

Thanks again....

[Allen Browne]

Part 1: Variable and control with the same name
The line:

Dim txtAgentID, txtLnGroup, txtProgram As String

creates 3 variables.

One is named txtAgentID.
In this line:
txtAgentID = Me.txtAgentID.Value
you assign the value in the control named txtAgentID to the new variable you
just created. I therefore assume you have a control (a text box?) named
txtAgentID, so it seems that you have a variable and a text box that both
have the same name.

You probably don't need the variable. You can just use:
Me.txtAgentID
without having to Dim txtAgentID.

Part 2: IsDate
Oh dear. You tried to create a string name IsDate?
IsDate() is a function in VBA, so not a good name for a string.
You don't seem to be using any string named IsDate, so just remove this
line:
Dim IsDate As String

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dakoris73" <dako...@discussions.microsoft.com> wrote in message

news:A5C86455-5B11-4641...@microsoft.com...

[dakoris73]

Hello Allen;

Thank you again for your assistance with this. I have made the corrections
that you suggested, and now I am getting an error message "Run-time error
'94': Invalid use of NULL"......... I'm not sure as to where this error is
coming from, or where this is being generated from. Is there something that
you might be able to point me into???

Thanks for everything.

[dakoris73]

Allen;

Sorry for the immediate post that I made...... As I was looking through the
debugger, I noticed that I have a yellow arrow pointed to the first line of
my original code:

txtAgentID = Me.txtAgentID.Value (Here is what is highlighted yellow)

txtLnGroup = Me.txtLineGroup.Value
txtProgram = Me.txtSoftware.Value

'Check to see which field is filled in, and how to react to information

If IsNull(Me.txtAgentID) Or IsNull(Me.txtLineGroup) Or
IsNull(Me.txtSoftware) Then

MsgBox "No information was entered. Please Re-enter your information for
what your looking for. ", vbOKCancel, "Error Message"

Else

Dim strAgtID As String, strTxtAgtID As String

strAgtID = Me.txtAgentID.Value
strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] = """ & strAgtID & """;"

Dim strLineGroup As String, strTxtLineGroup As String

strLineGroup = Me.txtLineGroup.Value
strTxtLineGroup = "SELECT AgentID, Date, Software, ProbDesc, LineGroup
FROM EOSMain WHERE EOSMain.[LineGroup] =""" & strLineGroup & """;"

Dim strProgram As String, txtPrgrm As String

strProgram = Me.txtSoftware.Value
strPrgrm = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[Software] =""" & strProgram & """;"

End If

I am thinking that the problem might be with this if/else statement,
however, I am not too sure yet. Is there anything you might think of that
could fix this?????

Mikey

[Allen Browne]

Presumably you changed the declaration line to that txtAgentID is now a
String variable. If you look back to the 3rd item in my original post, I
mentioned you were going to run into this problem also.

Not sure if you understood my 2nd post when i said:
You probably don't need the variable.
You can just use:
Me.txtAgentID
without having to Dim txtAgentID.

--

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dakoris73" <dako...@discussions.microsoft.com> wrote in message

news:8C2B1BC5-06F8-4384...@microsoft.com...

[dakoris73]

Hello Allen;

Thank you again for your response. I removed that variable declaration, and
now it is working somewhat. Now, whenever I try to enter any data into either
of the three textboxes that I have set up, I get the message from the MsgBox
for no information on the text boxes. I'm not sure if that is due to the fact
that the textboxes are unbound, and not sure if they need to be tied to
anything at this point. But it is getting better and closer to where I want
it to be. Do you think I should ahve these textboxes set up as an unbound
field, or should I set some properties to something that would be referenced
by this query?

Thanks so much once again......

[Allen Browne]

Sounds like you are trying to run before you tried walking.

Start with a bound form (bound to a table), with bound controls (bound to
fields.) You may find that you don't actually need any code. Particularly if
you used the Validation Rule property of the fields in your table, you can
probably avoid any code at all.

Another good way to learn how to set up a database is to open the Northwind
sample database that installs with Access. Open the Relationships window
(Tools menu.) Study how the data is tied together. When you understand that,
start to look at the simple bound forms such as Customers. Once that's
clear, move on to the forms that have subforms, such as Orders.

That's as far as I can take you for this thread.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dakoris73" <dako...@discussions.microsoft.com> wrote in message

news:36BC6DE2-34B1-45D5...@microsoft.com...

[dakoris73]

Thank you for all of your assistance with this. I do appreciate all the help,
and have been learning quite a bit reading these posts and getting the
assistance I need.

Thank you again....

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

[eos]