Can't understand the error message

A

Ayo

When I open this form I get a "Data type mismatch in creteria expression" and
when I click on the button on the form I get "The expression you enetered
refers to an object that is closed or doesn't exist" error in the code. When
I click on Debug, it takes me to this line of code:
Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM [OutofScope
Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" &
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" &
Me.cmbProjectName & "'" & ""

This is the same exact code that I used in another form the only diferrence
being Me.InScope_Table_subform.Form.RecordSource = "SELECT * fROM [InScope
Table]. In the other form I don't get any errors and everything works ok. SO
I don't understand what I error messages I am getting with this form is about.
Any ideas?
Thanks
 
D

Douglas J. Steele

As written, your SQL statement assumes that [Project Number], [Task Number],
[National Site ID] and [Project Name] are all text fields. If one (or more)
of them is a numeric field, remove the single quotes around the value you're
passing.
 
A

Ayo

They are all text. Like I wrote earlier, There two forms with identical codes
in every respect expect for "InScope" and "OutofScope". The InScope form
works fine, no problems. That is why I can't understand the error in the
OutofScope form.

Douglas J. Steele said:
As written, your SQL statement assumes that [Project Number], [Task Number],
[National Site ID] and [Project Name] are all text fields. If one (or more)
of them is a numeric field, remove the single quotes around the value you're
passing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ayo said:
When I open this form I get a "Data type mismatch in creteria expression"
and
when I click on the button on the form I get "The expression you enetered
refers to an object that is closed or doesn't exist" error in the code.
When
I click on Debug, it takes me to this line of code:
Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM [OutofScope
Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" &
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" &
Me.cmbProjectName & "'" & ""

This is the same exact code that I used in another form the only
diferrence
being Me.InScope_Table_subform.Form.RecordSource = "SELECT * fROM [InScope
Table]. In the other form I don't get any errors and everything works ok.
SO
I don't understand what I error messages I am getting with this form is
about.
Any ideas?
Thanks
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Ayo,

Do you have Option Explicit shown at the top of your code module, usually as
the second line of code? If not, please see this "gem tip" to re-configure
your VBA editor so that you will get these two most important words added to
all new code modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

If you are missing Option Explicit, then add it to this module and to other
modules as well.

Does your code compile without any errors? From within any code module,
click on Debug | Compile {ProjectName}, where {ProjectName} is the name of
your VBA project. Fix any compile errors, or comment out the offending code,
before continuing your tests.

To make your criteria easier to debug, declare a string variable, set it
equal to your expression, and then add a Debug.Print statement, like this:


Dim strSQL As String

strSQL = "SELECT * fROM [OutofScope Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" & _
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" & _
Me.cmbProjectName & "'" & ""

Debug.Print strSQL

Insert this before the line of code that reads:

Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM .....

Add a break point to the above line of code. Try clicking on your command
button once again. Then inspect the SQL statement that should have been
written to the Immediate Window, which you can open with <Ctrl><G>. Do you
see anything obvious that looks wrong? If not, copy the SQL statement from
the Immediate Window by selecting it and using <Ctrl><C>. Stop the code from
running. Minimize the VBA window. Create a new query, but dismiss the Add
Tables dialog. In query design view, click on View | SQL View. Paste the SQL
statement that you copied from the Immediate Window into the SQL View of the
new query. Attempt to save and then run the query. Do you get any errors?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Ayo said:
When I open this form I get a "Data type mismatch in creteria expression" and
when I click on the button on the form I get "The expression you enetered
refers to an object that is closed or doesn't exist" error in the code. When
I click on Debug, it takes me to this line of code:
Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM [OutofScope
Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" &
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" &
Me.cmbProjectName & "'" & ""

This is the same exact code that I used in another form the only diferrence
being Me.InScope_Table_subform.Form.RecordSource = "SELECT * fROM [InScope
Table]. In the other form I don't get any errors and everything works ok. SO
I don't understand what I error messages I am getting with this form is about.
Any ideas?
Thanks
Click to expand...
 
A

Ayo

Thanks Tom. But I just finished trying everything you suggested, and I am
still getting the same error on the same line of code. I don't understand
this at all.

Tom Wickerath said:
Hi Ayo,

Do you have Option Explicit shown at the top of your code module, usually as
the second line of code? If not, please see this "gem tip" to re-configure
your VBA editor so that you will get these two most important words added to
all new code modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

If you are missing Option Explicit, then add it to this module and to other
modules as well.

Does your code compile without any errors? From within any code module,
click on Debug | Compile {ProjectName}, where {ProjectName} is the name of
your VBA project. Fix any compile errors, or comment out the offending code,
before continuing your tests.

To make your criteria easier to debug, declare a string variable, set it
equal to your expression, and then add a Debug.Print statement, like this:


Dim strSQL As String

strSQL = "SELECT * fROM [OutofScope Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" & _
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" & _
Me.cmbProjectName & "'" & ""

Debug.Print strSQL

Insert this before the line of code that reads:

Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM .....

Add a break point to the above line of code. Try clicking on your command
button once again. Then inspect the SQL statement that should have been
written to the Immediate Window, which you can open with <Ctrl><G>. Do you
see anything obvious that looks wrong? If not, copy the SQL statement from
the Immediate Window by selecting it and using <Ctrl><C>. Stop the code from
running. Minimize the VBA window. Create a new query, but dismiss the Add
Tables dialog. In query design view, click on View | SQL View. Paste the SQL
statement that you copied from the Immediate Window into the SQL View of the
new query. Attempt to save and then run the query. Do you get any errors?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Ayo said:
When I open this form I get a "Data type mismatch in creteria expression" and
when I click on the button on the form I get "The expression you enetered
refers to an object that is closed or doesn't exist" error in the code. When
I click on Debug, it takes me to this line of code:
Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM [OutofScope
Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" &
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" &
Me.cmbProjectName & "'" & ""

This is the same exact code that I used in another form the only diferrence
being Me.InScope_Table_subform.Form.RecordSource = "SELECT * fROM [InScope
Table]. In the other form I don't get any errors and everything works ok. SO
I don't understand what I error messages I am getting with this form is about.
Any ideas?
Thanks
Click to expand...
Click to expand...
 
A

Ayo

Thanks Tom. The problem was in the subform query. I fixed it.

Tom Wickerath said:
Hi Ayo,

Do you have Option Explicit shown at the top of your code module, usually as
the second line of code? If not, please see this "gem tip" to re-configure
your VBA editor so that you will get these two most important words added to
all new code modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

If you are missing Option Explicit, then add it to this module and to other
modules as well.

Does your code compile without any errors? From within any code module,
click on Debug | Compile {ProjectName}, where {ProjectName} is the name of
your VBA project. Fix any compile errors, or comment out the offending code,
before continuing your tests.

To make your criteria easier to debug, declare a string variable, set it
equal to your expression, and then add a Debug.Print statement, like this:


Dim strSQL As String

strSQL = "SELECT * fROM [OutofScope Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" & _
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" & _
Me.cmbProjectName & "'" & ""

Debug.Print strSQL

Insert this before the line of code that reads:

Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM .....

Add a break point to the above line of code. Try clicking on your command
button once again. Then inspect the SQL statement that should have been
written to the Immediate Window, which you can open with <Ctrl><G>. Do you
see anything obvious that looks wrong? If not, copy the SQL statement from
the Immediate Window by selecting it and using <Ctrl><C>. Stop the code from
running. Minimize the VBA window. Create a new query, but dismiss the Add
Tables dialog. In query design view, click on View | SQL View. Paste the SQL
statement that you copied from the Immediate Window into the SQL View of the
new query. Attempt to save and then run the query. Do you get any errors?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Ayo said:
When I open this form I get a "Data type mismatch in creteria expression" and
when I click on the button on the form I get "The expression you enetered
refers to an object that is closed or doesn't exist" error in the code. When
I click on Debug, it takes me to this line of code:
Me.OutofScope_Table_subform.Form.RecordSource = "SELECT * fROM [OutofScope
Table] WHERE [Project Number] ='" & _
Me.cmbProjectNumber & "'" & " AND [Task Number] ='" &
Me.cmbTaskNumber & "'" & " AND [National Site ID] ='" & _
Me.txtNationalSiteID & "'" & " AND [Project Name] ='" &
Me.cmbProjectName & "'" & ""

This is the same exact code that I used in another form the only diferrence
being Me.InScope_Table_subform.Form.RecordSource = "SELECT * fROM [InScope
Table]. In the other form I don't get any errors and everything works ok. SO
I don't understand what I error messages I am getting with this form is about.
Any ideas?
Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Furstrated!!! Help. 3
Combining two types of functions. Select Query and DLookup 4
Desperate!!! Subreport Help. 1
Access VBA error "Microsoft Access can't find the field '|' referred to in your experssion" 0
Error message prevents form creation 2
Receiving error message 1
Open Form Code Error 5
DELETE FROM a table WHERE... 1

Top