problems reading a field in Data Base

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

Guest

hello!

I have a Data Base that holds records. In the program (VBA) I read data from
the DB. usualy all works fine. but sometimes I need to read a field and the
program shows me that the field is empty although it's not!!! I have data in
it!!!

how can I fix the problom?

thanks!
Yonina.
 
I think you're going to have to provide more details.

What's the code you're using to read the data? What's the code that's
telling you the field is empty?
 
It is possible that you have a corrupted index. Have you tried doing a
Compact & Repair - Tools>Database Utilities>Compact & Repair Database.
 
yes, I tried.


Pete said:
It is possible that you have a corrupted index. Have you tried doing a
Compact & Repair - Tools>Database Utilities>Compact & Repair Database.
Click to expand...
 
here is the code:
rs.Open "SELECT * FROM Company WHERE com_name='" & Me!tmpnewcomp & "' and
country_id=" & Nz(Me!tmpnewcomp.Column(2), "186"), getConnection, adOpenStatic

MsgBox ("rs(""com_rep""): " & rs("com_rep") & vbCrLf & " RepIDLoggedOn: " &
RepIDLoggedOn)

If (rs("com_rep") <> RepIDLoggedOn) Then
Cancel = True
MsgBox "This company is assigned to another rep."
tmpnewcomp.Undo
End If

the problom is the "if" statement. I know there is data in rs("com_rep") but
in the msgbox I don't see the data. I see blank.
 
My guess is that the double quotes is fouling things up. change the
msgbox to

Take out the "rs(""comp_rep""): " &
Which should clean it up. Also try adding Debug.Print rs("comp_rep")
which will print out the result in the immediate window.

קובץ said:
here is the code:
rs.Open "SELECT * FROM Company WHERE com_name='" & Me!tmpnewcomp & "' and
country_id=" & Nz(Me!tmpnewcomp.Column(2), "186"), getConnection, adOpenStatic

MsgBox ( ["rs(""com_rep""): " &] rs("com_rep") & vbCrLf & " RepIDLoggedOn: " &
RepIDLoggedOn)

If (rs("com_rep") <> RepIDLoggedOn) Then
Cancel = True
MsgBox "This company is assigned to another rep."
tmpnewcomp.Undo
End If

the problom is the "if" statement. I know there is data in rs("com_rep") but
in the msgbox I don't see the data. I see blank.




:

I think you're going to have to provide more details.

What's the code you're using to read the data? What's the code that's
telling you the field is empty?
Click to expand...
Click to expand...
 
I don't realy care about the msgbox. I used it only to see what is in the
field.
I need the "if" statement to work and it doesn't work because the data is
NULL although when look in the DB it isn't NULL.



David C. Holley said:
My guess is that the double quotes is fouling things up. change the
msgbox to

Take out the "rs(""comp_rep""): " &
Which should clean it up. Also try adding Debug.Print rs("comp_rep")
which will print out the result in the immediate window.

קובץ said:
here is the code:
rs.Open "SELECT * FROM Company WHERE com_name='" & Me!tmpnewcomp & "' and
country_id=" & Nz(Me!tmpnewcomp.Column(2), "186"), getConnection, adOpenStatic

MsgBox ( ["rs(""com_rep""): " &] rs("com_rep") & vbCrLf & " RepIDLoggedOn: " &
RepIDLoggedOn)

If (rs("com_rep") <> RepIDLoggedOn) Then
Cancel = True
MsgBox "This company is assigned to another rep."
tmpnewcomp.Undo
End If

the problom is the "if" statement. I know there is data in rs("com_rep") but
in the msgbox I don't see the data. I see blank.




:

I think you're going to have to provide more details.

What's the code you're using to read the data? What's the code that's
telling you the field is empty?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



hello!

I have a Data Base that holds records. In the program (VBA) I read data

from

the DB. usualy all works fine. but sometimes I need to read a field and

the

program shows me that the field is empty although it's not!!! I have data

in

it!!!

how can I fix the problom?

thanks!
Yonina.
Click to expand...
Click to expand...
Click to expand...
 
You're certain there's the data?

Try assigning the SQL statement to a variable:

Dim strSQL As String

strSQL = "SELECT * FROM Company WHERE com_name='" & _
Me!tmpnewcomp & "' and country_id=" & _
Nz(Me!tmpnewcomp.Column(2), "186")

Debug.Print strSQL

rs.Open strSQL, getConnection, adOpenStatic

Go to the Debug window, copy the SQL into the query window and run it.
 
rs.Open "SELECT * FROM Company " & _
"WHERE com_name='" & Me!tmpnewcomp & "' " & _
" AND country_id=" & Nz(Me!tmpnewcomp.Column(2), "186"), _
_
getConnection, adOpenStatic
Click to expand...


You don't have any guarantee at this point that rs actually contains any
records. I don't know enough about ADO but if you attempt to read an empty
recordset do you get a trappable error or a set of NULL values? In any
case, I'd do something like

if rs.BOF Then
MsgBox "Can't find anything"
Stop ' or something useful!!
end if

Just a thought...


Tim F
 
hi!

I did the check you offered. That was the problom!!! it was EOF.
But how come it is EOF, I know there is data in the field??? (I put data in
there)
 
What does your SQL look like? Are the values you're getting for
Me!tmpnewcomp and Nz(Me!tmpnewcomp.Column(2), "186") what you're expecting?

Me!tmpnewcomp.Column(2) gives you the value from the 3rd column of the
combobox: is that what you meant?
 
yes, these lines worked for the past few months with no problom.
all of the sudden I can't read the record.
other records are read fine.
how can I fix this?
 
yes, these lines worked for the past few months with no problom.
all of the sudden I can't read the record.
other records are read fine.
how can I fix this?
Click to expand...

I must say that I had great difficulty in envisaging when one would want to
query based on two fields both from the same row of a list box... It's not
impossible, clearly, but just that it's beyond my imagination.

Lots of things may have changed since it stopped working. Has a field value
been changed from NULL to ""? Have you _really_ looked at the SQL you are
passing to the dbEngine before doing it? Do you routinely put something
like this in for just these moments:

Debug.Assert vbYes=MsgBox(adoSQL, vbYesNo, "Is this okay?")

All the best


Tim F
 
The problem then would be (most likely) in the SQL statement. Start
small, alter the SQL statement to SELECT * FROM company; to ensure that
you're grabbing all records. If you're still having a problem, then I
would think that the connection string is suspect.
 
Well, that changes things. Are you aware of any changes that have been
made? Are there other tables in the SAME database as the COMPANY table
and is other code functioning OK? If you have other code that accesses
tables in the SAME db as the COMPANY table, then something has happened
such as the DB being moved maybe a server or directory being renamed.
 
Back
Top