By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

data not being retrieved from Access database

P: 72
Hi there vb genius... I have a problem, i'am currently doing a program that has a database on it. i'am using access database, my problem is the records on my database table doesnt appear on my form...

The scenarion is this.. my database table name is "SUBJECTS" and has a field name "SUBJECT CODES" and "ID" for my primary id.

heres my code:

Expand|Select|Wrap|Line Numbers
  2. If rs.State = adStateOpen Then rs.Close
  3.        rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  5.     While rs.EOF <> True
  6.         For x = 0 To 7
  7.         Text3(x).Text = rs.Fields("SUBJECT CODES").Value
  8.         Next x
  9.      rs.MoveNext
  10.     Wend
the records must be display in the text box area, but it seems not to be working, i think i'am missing something in my code.. when i try to compile it, it doesnt give any error at all, so it means my database code is correct, i pressume.. but it doesnt do anything at all, it doesnt display all the records in my text boxes. Can someone help me out how can i display it in my text boxes. thank you very much.
May 12 '07 #1
Share this Question
Share on Google+
10 Replies

Expert 5K+
P: 8,434
Hi Darrel.

Looks as though you misunderstood what I said about the Like operator. Is this supposed to return a bunch of records matching a wildcard value, or is "ABA0101**" actually the exact value which you expect to find in them? In other words, is this supposed to find records which start with "ABA0101"?

If it is supposed to match an exact value, then you don't use Like at all, just =.

If it is a wildcard, then you do need to use Like, but your syntax is wrong. Here is the syntax for the two options...

Expand|Select|Wrap|Line Numbers
  1. Your current code...
  2. Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'
  4. Exact match...
  5. Select * from [SUBJECTS] where PrimaryID = 'ABA0101**'
  7. Wilcard search...
  8. Select * from [SUBJECTS] where PrimaryID Like 'ABA0101*'
May 12 '07 #2

P: 72
thank you fo rthe reply. but sad to say it doesnt wrong also,, here my code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  4.     While rs.EOF <> True
  5.         For x = 0 To 7
  6.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  8.         Next x
  9.      rs.MoveNext
  10.     Wend
yes and it is a wildcard choice, you code is this

Expand|Select|Wrap|Line Numbers
  2. "Select * from [SUBJECTS] where PrimaryID Like = 'ABA0101*'", 
Ive noticed that you you disregard the single quote on the "PrimaryID Like" word and the equal sig, i have followed that but it gives me an error. but when i place a signle quote and equal sign on it. the error is gone but still it doenst display t he records..

i hope you can help me with this please... i'am being desperate.
May 15 '07 #3

Expert 5K+
P: 8,434
You can't use both the "=" and the "Like" operator. They are two different types of comparison. Use one or the other, depending on whether you want an exact match or a wildcard "fuzzy" match.

The syntax is...
WHERE FIELD LIKE 'WildcardValue'
May 15 '07 #4

P: 72
Thank you for that i was able to display the records on my text boxes but now my problem is it only display one record, an it is the last records of my field.

heres my code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  4.     While rs.EOF <> True
  5.         For x = 0 To 7
  6.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  7.         Next x
  8.      rs.MoveNext
  9.     Wend
e.i. it only display HUM411T, in all my textboxes and thats not want i want... in my code it must display 8 diffenrent subject code but what i arrived is only one, 8 the same subjects code... and the record that is being displayed is not the one whose i my query HUM411T does not belong to the range of my query in my code now its different...

I hope you can give me some hints... i think am getting into it little by little than you for your patience.
May 15 '07 #5

P: 72
Mr. Killer i have figure out whats wrong with my code:

to contribute heres my code

Expand|Select|Wrap|Line Numbers
  2. If rs.State = adStateOpen Then rs.Close
  3.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  5.   If rs.RecordCount > 0 Then
  6.         For x = 0 To 7
  7.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  8.         rs.MoveNext
  9.         Next x
  11.  End If
i just place rs.MoveNext to my For Loop and uses .RecordCount to populate the records..

Again thank you so much for you help and patience i owe you a lot... Hail to you!!!
May 15 '07 #6

Expert 5K+
P: 8,434
It's good to see you progressing, but your SQL syntax is still wrong.

Here's your syntax, followed by the correction.

...[SUBJECTS] WHERE 'PrimaryID LIKE ABA0101*'" ...

...[SUBJECTS] WHERE PrimaryID LIKE 'ABA0101*'" ...
May 15 '07 #7

P: 26
Just a little explanation

"Select * from [SUBJECTS] WHERE 'PrimaryID LIKE ABA0101*'"
it will display all of your record

"Select * from [SUBJECTS] WHERE PrimaryID LIKE 'ABA0101*'"
it will display record that PrimaryID match the condition, that is 'ABA0101*'
May 15 '07 #8

P: 72
Hi i still have problems:

1. the records that are display in my text boxes doesnt change.
2. the records in the text boxes are not being refresh.

Now my question is how will i able to refresh or clear the previous records that are been displayed on my text boxes.

its like this the first records that are in my primary id 'ABA0101*'", are 7 subject codes, and the other one which is 'ABA0102*'" have 9. But when i chose the 'ABA0102*'" records it just add up to the previous records that are been being display and view first. i hope you understand, coz am having difficulty in expalining the scenario..

to be more specific its like this:

'ABA0101*'" - has 7 records on it
'ABA0102*'" - has 9 records on it.

when i run it the records of ABA0101 is being displayed together with ABA0102 thats my problem... i want to eliminate that.

So How can i eliminate, refresh, clear or something like that to the records that are being populated or displayed in my textboxes when i want to view other records. Heres my code:

Expand|Select|Wrap|Line Numbers
  3.   If rs.State = 1 Then rs.Close
  4.   rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenStatic, adLockOptimistic
  6.   If rs.RecordCount < 0 Then
  7.             For x = 0 To 7
  8.         txtCode(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  10.         rs.MoveNext
  12.         Next
  14.         For x = 0 To 15
  15.         txtCode(x).Locked = True
  16.         txtSubjects(x).Locked = True
  17.         txtType(x).Locked = True
  18.         txtUnits(x).Locked = True
  19.         Next
  20.   End If
  25. If rs.State = adStateOpen Then rs.Close
  26.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  28.   If rs.RecordCount > 0 Then
  30.         For x = 0 To 7
  31.         txtSubjects(x).Text = rs.Fields.Item("SUBJECT").Value
  33.         rs.MoveNext
  35.         Next x
  37.         For x = 0 To 15
  38.         txtCode(x).Locked = True
  39.         txtSubjects(x).Locked = True
  40.         txtType(x).Locked = True
  41.         txtUnits(x).Locked = True
  42.         Next x
  43.   End If
  47. If rs.State = adStateOpen Then rs.Close
  48.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  50.   If rs.RecordCount > 0 Then
  52.         For x = 0 To 7
  53.         txtType(x).Text = rs.Fields.Item("TYPE").Value
  55.         rs.MoveNext
  57.         Next x
  59.         For x = 0 To 15
  60.         txtCode(x).Locked = True
  61.         txtSubjects(x).Locked = True
  62.         txtType(x).Locked = True
  63.         txtUnits(x).Locked = True
  64.         Next x
  65.   End If
  69. If rs.State = adStateOpen Then rs.Close
  70.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  72.   If rs.RecordCount > 0 Then
  74.         For x = 0 To 7
  75.         txtUnits(x).Text = rs.Fields.Item("UNITS").Value
  77.         rs.MoveNext
  79.         Next x
  81.         For x = 0 To 15
  82.         txtCode(x).Locked = True
  83.         txtSubjects(x).Locked = True
  84.         txtType(x).Locked = True
  85.         txtUnits(x).Locked = True
  86.         Next x
  87.   End If
Thank you very much, hope you can help me, and i hope you can picture what am trying to say.
May 15 '07 #9

Expert 100+
P: 321
You need to fix your sql query before moving on. The query you are using most likely isn't filtering anything out. Read the last two posts from Killer42 and Shidec again.

It would be nice to know what exactly is in the "Subject Codes". If you open access and look at that field what do the first couple rows have in that field? Need to know exactly what it is, don't truncate it with a wild card when providing this sample.
May 15 '07 #10

P: 72
Thank you for that advise, i have figure out what is wrong with my sql query. to contribute here's my final code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] where ID like 'ABA0102%'", cnn, adOpenKeyset, adLockOptimistic
  3.   While rs.EOF <> True
  4.     For x = 0 To 15
  5.         txtCode(x).Text = rs.Fields("SUBJECT CODES").Value
  6.         On Error Resume Next
  7.         rs.MoveNext
  8.     Next x
  10.   Wend
The error is i need to enclose the condition with single quotes e.i 'ABA0102%', and place a % sign on the wildcard selection...

For the people who help and give some advise thank you very... hope you can help with my future programs. thank you very much, This site ROCKS! PEACE OUT!
May 16 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.