data not being retrieved from Access database

[darrel]

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:

If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
      
    While rs.EOF <> True
        For x = 0 To 7
        Text3(x).Text = rs.Fields("SUBJECT CODES").Value
        Next x
     rs.MoveNext
    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.

[Killer42]

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...

Your current code...
Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'

Exact match...
Select * from [SUBJECTS] where PrimaryID = 'ABA0101**'

Wilcard search...
Select * from [SUBJECTS] where PrimaryID Like 'ABA0101*'

[darrel]

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

If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
      
    While rs.EOF <> True
        For x = 0 To 7
        Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
        
        Next x
     rs.MoveNext
    Wend

yes and it is a wildcard choice, you code is this

"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.

[Killer42]

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 = 'Value'
or
WHERE FIELD LIKE 'WildcardValue'

[darrel]

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:

If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
      
    While rs.EOF <> True
        For x = 0 To 7
        Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
        Next x
     rs.MoveNext
    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.

[darrel]

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

to contribute heres my code

If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
      
  If rs.RecordCount > 0 Then
        For x = 0 To 7
        Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
        rs.MoveNext
        Next x
     
 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!!!

[Killer42]

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*'" ...

[shidec]

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*'

[darrel]

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:

'SUBJECT CODE CONNECTION
  If rs.State = 1 Then rs.Close
  rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenStatic, adLockOptimistic
  
  If rs.RecordCount < 0 Then
            For x = 0 To 7
        txtCode(x).Text = rs.Fields.Item("SUBJECT CODES").Value
       
        rs.MoveNext
        
        Next
        
        For x = 0 To 15
        txtCode(x).Locked = True
        txtSubjects(x).Locked = True
        txtType(x).Locked = True
        txtUnits(x).Locked = True
        Next
  End If
        
'SUBJECT CODE CONNECTION

'SUBJECT  CONNECTION
If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
      
  If rs.RecordCount > 0 Then
        
        For x = 0 To 7
        txtSubjects(x).Text = rs.Fields.Item("SUBJECT").Value
       
        rs.MoveNext
        
        Next x
        
        For x = 0 To 15
        txtCode(x).Locked = True
        txtSubjects(x).Locked = True
        txtType(x).Locked = True
        txtUnits(x).Locked = True
        Next x
  End If
'SUBJECT CONNECTION

'TYPE  CONNECTION
If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
      
  If rs.RecordCount > 0 Then
        
        For x = 0 To 7
        txtType(x).Text = rs.Fields.Item("TYPE").Value
       
        rs.MoveNext
        
        Next x
        
        For x = 0 To 15
        txtCode(x).Locked = True
        txtSubjects(x).Locked = True
        txtType(x).Locked = True
        txtUnits(x).Locked = True
        Next x
  End If
'TYPE CONNECTION

'UNITS  CONNECTION
If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
      
  If rs.RecordCount > 0 Then
        
        For x = 0 To 7
        txtUnits(x).Text = rs.Fields.Item("UNITS").Value
       
        rs.MoveNext
        
        Next x
        
        For x = 0 To 15
        txtCode(x).Locked = True
        txtSubjects(x).Locked = True
        txtType(x).Locked = True
        txtUnits(x).Locked = True
        Next x
  End If
'UNITS CONNECTION

Thank you very much, hope you can help me, and i hope you can picture what am trying to say.

[danp129]

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.

[darrel]

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

If rs.State = adStateOpen Then rs.Close
       rs.Open "Select * from [SUBJECTS] where ID like 'ABA0102%'", cnn, adOpenKeyset, adLockOptimistic
  While rs.EOF <> True
    For x = 0 To 15
        txtCode(x).Text = rs.Fields("SUBJECT CODES").Value
        On Error Resume Next
        rs.MoveNext
    Next x
  
  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!