textbox read in as a string?

[James R.]

Hi,

Not sure if this is the correct newsgroup, I apologize if it is not.

I have an access database form that is used as a Front End application, it
is connected to a backend database of SQL Server 2005.

My problem is that I'm getting a "Type Mismatch" error; which indicates that
the data being read into the VBA code is either being read in as a string
when it should be an integer, or vice versa.

Now, the problem part is that the data is coming from a textbox on a form
the textbox is called "Mult".

The code is:

Dim RushStatus as Integer
RushStatus = DLookup("TAT", "WorkOrders", "WorkOrder = '" &
Forms!Samples!Child1.Form!WorkOrder & "'")
If RushStatus = 0 Then Me!Mult = 2.5
Me!Mult.DefaultValue = Me!Mult

Ok, so the RushStatus reads in just fine as an integer value. If I debug,
the Me!Mult will be shown as NULL.

What makes this even more puzzling is that the "Mult" column in the SQL
Backend isn't NULL!!! I've checked every single record and all of them have a
value of 1 or higher.

Now, to throw in a little more confusing item. Is the original backend was
an Access backend. If I run this same code on that (the Access version)
backend then Me!Mult gets read in as an Integer value (not as NULL).

I'm at a loss as to how to further troubleshoot this. I'd appreciate any
ideas someone would kick around for this.

I've used SQL Profiler and watched the TSQL coming in and it is just fine,
I've ran the TSQL through Management Studio and got back the proper results.

For some reason, this textbox just won't see that there is a value in this
column.

Thanks in advance for any suggestions you might have!
James

 

[Ken Snell \(MVP\)]

Comments inline.

--

Ken Snell
<MS ACCESS MVP>

Hi,

Not sure if this is the correct newsgroup, I apologize if it is not.

I have an access database form that is used as a Front End application, it
is connected to a backend database of SQL Server 2005.

My problem is that I'm getting a "Type Mismatch" error; which indicates
that
the data being read into the VBA code is either being read in as a string
when it should be an integer, or vice versa.

Now, the problem part is that the data is coming from a textbox on a form
the textbox is called "Mult".

The code is:

Dim RushStatus as Integer
RushStatus = DLookup("TAT", "WorkOrders", "WorkOrder = '" &
Forms!Samples!Child1.Form!WorkOrder & "'")
If RushStatus = 0 Then Me!Mult = 2.5
Me!Mult.DefaultValue = Me!Mult

Ok, so the RushStatus reads in just fine as an integer value.

Do be careful. The Integer data type in ACCESS will not hold as large a
number as the Int data type in SQL Server.

If I debug,
the Me!Mult will be shown as NULL.

In the above code, Me!Mult is not set to a value unless RushStatus is a zero
value. So a NULL value for Me!Mult is very possible and probable.

What makes this even more puzzling is that the "Mult" column in the SQL
Backend isn't NULL!!! I've checked every single record and all of them
have a
value of 1 or higher.

Where in your code are you using the value from the Mult field in the SQL
Server table?

Now, to throw in a little more confusing item. Is the original backend was
an Access backend. If I run this same code on that (the Access version)
backend then Me!Mult gets read in as an Integer value (not as NULL).

Again, I ask -- where in your code are you reading / using the Mult field at
all?

 

[James R.]

Thank you for the response.

I wasn't aware of the size difference between the integer value of Access
and SQL; that is nice new knowledge. Although, I'm not worried about this
because the value of Mult won't exceed 5; so I could've even had it in SQL as
a smallint. Since this project is an adaption project I didn't have any input
on the original backend and the integer conversion was a result of not
knowing (at the time I started this project) what the maximum value of Mult
would be.

As for where am I reading the Mult value? That's coming from a Form that is
bound to a linked table on the Front End. If I go into dataview I can see
there is a value showing in the proper field; so I know for 100% that there
is a value. And since the binding of the box was already done prior to my
taking on this project, and it works just fine if the backend is in Access; I
have to assume that the textbox is properly bound...especially since the
other textboxes on this form are all showing the proper data.

In the VBA code if I call Me!Mult then it should read in the value from the
bound field for that textbox. And because I'm trying to set the value
directly to the default value (Me!Mult.DefaultValue = Me!Mult) shouldn't it
be read in (and handled) as an integer?

Since I'm getting a NULL result when I try to read the value of the textbox
"Mult", it seems as though it's not loading a value...but, again...I've
verified there is a value, even in the SQL backend it has a default value of
1...so you can't possibly even enter a new record with the Mult being NULL.

I'd also like to clarify that this is a small part of the big picture, it's
just where I've pinpointed the error being caused at. Now, another
interesting thing is that if I set a value in the TAT field (see last posting
for the DLookup statement that uses this), such as a value of 0, then the
whole thing processes fine. So this strongly suggests that even though Mult
is being read in as NULL it is being processed as an Integer data type
because the code logic has RushStatus set a value for Mult...unless Access is
like C# in that it can change data types on the fly.

With all of that said, the problem is that Mult is a textbox on a form that
is bound to a linked table to a SQL backend. The linked table has a value for
the field (column) called "Mult". If I use data view the value of Mult shows
up. If I try to process via VBA then using the command Me!Mult results in a
NULL. Now, if I go back to the original backend which was an Access backend;
yet, leaving the Access Front End alone...everything works fine! Why, and how
do I fix this to work with the SQL backend? Arrggghhh! Access is frustrating
at times.

I'm dazed and confused on this one; I thought I was pretty clever so far and
have met my match on this one. I'd appreciate any thoughts on this.

Thanks for the help!
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

Comments inline.

--

Ken Snell
<MS ACCESS MVP>

Hi,

Not sure if this is the correct newsgroup, I apologize if it is not.

I have an access database form that is used as a Front End application, it
is connected to a backend database of SQL Server 2005.

My problem is that I'm getting a "Type Mismatch" error; which indicates
that
the data being read into the VBA code is either being read in as a string
when it should be an integer, or vice versa.

Now, the problem part is that the data is coming from a textbox on a form
the textbox is called "Mult".

The code is:

Dim RushStatus as Integer
RushStatus = DLookup("TAT", "WorkOrders", "WorkOrder = '" &
Forms!Samples!Child1.Form!WorkOrder & "'")
If RushStatus = 0 Then Me!Mult = 2.5
Me!Mult.DefaultValue = Me!Mult

Ok, so the RushStatus reads in just fine as an integer value.

Do be careful. The Integer data type in ACCESS will not hold as large a
number as the Int data type in SQL Server.

If I debug,
the Me!Mult will be shown as NULL.

In the above code, Me!Mult is not set to a value unless RushStatus is a zero
value. So a NULL value for Me!Mult is very possible and probable.

What makes this even more puzzling is that the "Mult" column in the SQL
Backend isn't NULL!!! I've checked every single record and all of them
have a
value of 1 or higher.

Where in your code are you using the value from the Mult field in the SQL
Server table?

Now, to throw in a little more confusing item. Is the original backend was
an Access backend. If I run this same code on that (the Access version)
backend then Me!Mult gets read in as an Integer value (not as NULL).

Again, I ask -- where in your code are you reading / using the Mult field at
all?

 

[Ken Snell \(MVP\)]

Comments/answers inline.

--

Ken Snell
<MS ACCESS MVP>

Thank you for the response.

I wasn't aware of the size difference between the integer value of Access
and SQL; that is nice new knowledge. Although, I'm not worried about this
because the value of Mult won't exceed 5; so I could've even had it in SQL
as
a smallint.

SQL Server's Int datatype is comparable to ACCESS' LongInteger data type.

Since this project is an adaption project I didn't have any input
on the original backend and the integer conversion was a result of not
knowing (at the time I started this project) what the maximum value of
Mult
would be.

As for where am I reading the Mult value? That's coming from a Form that
is
bound to a linked table on the Front End. If I go into dataview I can see
there is a value showing in the proper field; so I know for 100% that
there
is a value. And since the binding of the box was already done prior to my
taking on this project, and it works just fine if the backend is in
Access; I
have to assume that the textbox is properly bound...especially since the
other textboxes on this form are all showing the proper data.
OK.



In the VBA code if I call Me!Mult then it should read in the value from
the
bound field for that textbox. And because I'm trying to set the value
directly to the default value (Me!Mult.DefaultValue = Me!Mult) shouldn't
it
be read in (and handled) as an integer?

The DefaultValue property must be a string value. Therefore, the correct
syntax for the assignment is this:

Me!Mult.DefaultValue = Chr(34) & Me!Mult & Chr(34)

or

Me!Mult.DefaultValue = """" & Me!Mult & """"

Since I'm getting a NULL result when I try to read the value of the
textbox
"Mult", it seems as though it's not loading a value...but, again...I've
verified there is a value, even in the SQL backend it has a default value
of
1...so you can't possibly even enter a new record with the Mult being
NULL.

Are you getting a NULL value for this expression:
Me!Mult

Or are you getting a NULL value as the result of the DefaultValue property
for a new record?

I'd also like to clarify that this is a small part of the big picture,
it's
just where I've pinpointed the error being caused at. Now, another
interesting thing is that if I set a value in the TAT field (see last
posting
for the DLookup statement that uses this), such as a value of 0, then the
whole thing processes fine. So this strongly suggests that even though
Mult
is being read in as NULL it is being processed as an Integer data type
because the code logic has RushStatus set a value for Mult...unless Access
is
like C# in that it can change data types on the fly.

With all of that said, the problem is that Mult is a textbox on a form
that
is bound to a linked table to a SQL backend. The linked table has a value
for
the field (column) called "Mult". If I use data view the value of Mult
shows
up. If I try to process via VBA then using the command Me!Mult results in
a
NULL. Now, if I go back to the original backend which was an Access
backend;
yet, leaving the Access Front End alone...everything works fine! Why, and
how
do I fix this to work with the SQL backend? Arrggghhh! Access is
frustrating
at times.

I'm dazed and confused on this one; I thought I was pretty clever so far
and
have met my match on this one. I'd appreciate any thoughts on this.

Thanks for the help!
James