13 Replies - 814 Views - Last Post: 11 February 2013 - 09:50 PM Rate Topic: -----

#1 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Problem filling a textbox connected to DB

Posted 09 February 2013 - 08:57 AM

Hi
I'm trying to fill a textbox according to another textbox value while searching the Table
first textbox is txtMjrCode and the other is txtMjrName, I want the txtMjrName fills if the txtMjrCode value does exist in tblMajors.
I've wrote the code below but it has a big problem! for example if there is a "100" for mjrCode
in the table and I type "100" in txtMjrCode, the txtMjrName shows "x" and if I go on and type "1000" and this value does not exist in the table, I want the second textbox get empty but it doesn't and continues showing "x"!


Private Sub txtMjrCode_Change()

 Set rs3 = New ADODB.Recordset
 With rs3:
  .Open "Select * from tblMajors", cn, 2, 3
   Do While Not .EOF
  If StrComp(!MjrCode, txtMjrCode.Text) = 0 Then
    txtMjrName.Text = !MjrName
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
    Exit Do
  Else
    .MoveNext
   End If
  Loop
End With

  
 

End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Problem filling a textbox connected to DB

#2 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 09 February 2013 - 09:03 AM

BobRodes
andrewsw

View Postazadehv, on 09 February 2013 - 08:57 AM, said:

Hi
I'm trying to fill a textbox according to another textbox value while searching the Table
first textbox is txtMjrCode and the other is txtMjrName, I want the txtMjrName fills if the txtMjrCode value does exist in tblMajors.
I've wrote the code below but it has a big problem! for example if there is a "100" for mjrCode
in the table and I type "100" in txtMjrCode, the txtMjrName shows "x" and if I go on and type "1000" and this value does not exist in the table, I want the second textbox get empty but it doesn't and continues showing "x"!


Private Sub txtMjrCode_Change()

 Set rs3 = New ADODB.Recordset
 With rs3:
  .Open "Select * from tblMajors", cn, 2, 3
   Do While Not .EOF
  If StrComp(!MjrCode, txtMjrCode.Text) = 0 Then
    txtMjrName.Text = !MjrName
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
    Exit Do
  Else
    .MoveNext
   End If
  Loop
End With

  
 

End Sub

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3253
  • View blog
  • Posts: 10,908
  • Joined: 12-December 12

Re: Problem filling a textbox connected to DB

Posted 09 February 2013 - 09:34 AM

There is no need to select, and loop through, all the returned records. Use a WHERE clause to restrict the records returned:

.Open "SELECT * FROM tblMajors WHERE MjrCode='" & txtMjrCode.Text & "'", cn, 2, 3
'you don't need the apostrophes if MjrCode is a number

Then do rs3.MoveLast and check rs3.RecordCount. If the RecordCount is 1 then populate the 2nd textbox, otherwise set it to an empty-string "".

PS avoid the use of *, particularly when you are only using a few fields.

PPS If there weren't that many rows in tblMajors I would retrieve them all, once, and just filter them. MSDN

This post has been edited by andrewsw: 09 February 2013 - 09:37 AM

Was This Post Helpful? 1
  • +
  • -

#4 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 09 February 2013 - 11:48 AM

View Postandrewsw, on 09 February 2013 - 09:34 AM, said:

There is no need to select, and loop through, all the returned records. Use a WHERE clause to restrict the records returned:

.Open "SELECT * FROM tblMajors WHERE MjrCode='" & txtMjrCode.Text & "'", cn, 2, 3
'you don't need the apostrophes if MjrCode is a number

Then do rs3.MoveLast and check rs3.RecordCount. If the RecordCount is 1 then populate the 2nd textbox, otherwise set it to an empty-string "".

PS avoid the use of *, particularly when you are only using a few fields.

PPS If there weren't that many rows in tblMajors I would retrieve them all, once, and just filter them. MSDN


thanks andrewsw :)
I modified the code as your advice, but the rs3.MoveLast caused runtime error 3021: Either EOF or BOF is true, or the current record has been deleted,etc..
so I disable it as comment and it worked, what's the problem with this Line (r3.MoveLast)?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3253
  • View blog
  • Posts: 10,908
  • Joined: 12-December 12

Re: Problem filling a textbox connected to DB

Posted 09 February 2013 - 12:20 PM

rs3.MoveLast is not necessary for your example as there is only one, or zero, rows retrieved.
Was This Post Helpful? 1
  • +
  • -

#6 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 09 February 2013 - 08:17 PM

View Postandrewsw, on 09 February 2013 - 12:20 PM, said:

rs3.MoveLast is not necessary for your example as there is only one, or zero, rows retrieved.


what if I want this works for to text boxes Simultaneously?

these two empty eachother when I type a new value which is not in the table (the time I want to define a new record for the table!)

Private Sub txtMjrCode_Change()

 Set rs3 = New ADODB.Recordset
 With rs3:
  .Open "Select mjrName,mjrCode,mjrProvider from tblMajors where mjrCode='" & txtMjrCode.Text & "'", cn, 2, 3
   'rs3.MoveLast
   If rs3.RecordCount = 1 Then
    txtMjrName.Text = !MjrName
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
   Else
    
    txtMjrName.Text = ""
    cboType.Text = ""
   End If
   
  End With
Private Sub txtMjrName_Change()

  
  Set rs3 = New ADODB.Recordset
 With rs3:
  .Open "Select mjrName,mjrCode,mjrProvider from tblMajors where mjrName='" & txtMjrName.Text & "'", cn, 2, 3
   'rs3.MoveLast
   If rs3.RecordCount = 1 Then
    txtMjrCode.Text = !MjrCode
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
   Else
    txtMjrCode.Text = ""
    
    cboType.Text = ""
   End If
   
  End With

End Sub



Was This Post Helpful? 0
  • +
  • -

#7 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 09:42 AM

As Andrew says, you don't need the MoveLast, but here's why you're having the error. If there are zero rows retrieved in a recordset, .BOF and .EOF will both be true. In no other situation will this be the case, so this is the test for an empty recordset. If you attempt to navigate to a record in an empty recordset, you'll get that error. So, you have to test for this. For example:
Private Sub txtMjrName_Change()
 With rs3
.   'If rs3.RecordCount = 1 Then
    If Not (.BOF And .EOF) Then
    txtMjrCode.Text = !MjrCode
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
   Else
    txtMjrCode.Text = ""
    
    cboType.Text = ""
   End If
   
  End With

End Sub

Recordcount isn't consistently reliable, as some types of cursors (the thingie that points at a record in a recordset) don't recognize Recordcount accurately in all situations. I would recommend (and indeed, it is considered best practice) that you change your test as I've shown above.

Another recommendation is that you shouldn't use text boxes unless you are allowing users to type stuff in. Your second text box only displays data, and therefore should be a label. You can put borders around labels if you want to.

This post has been edited by BobRodes: 10 February 2013 - 09:44 AM

Was This Post Helpful? 2
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3253
  • View blog
  • Posts: 10,908
  • Joined: 12-December 12

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 09:50 AM

@BobRodes I had forgotten about that neat line:

If Not (.BOF And .EOF) Then

(it's been a while!) Thank you.

This post has been edited by andrewsw: 10 February 2013 - 09:51 AM

Was This Post Helpful? 1
  • +
  • -

#9 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 09:58 AM

View PostBobRodes, on 10 February 2013 - 09:42 AM, said:

As Andrew says, you don't need the MoveLast, but here's why you're having the error. If there are zero rows retrieved in a recordset, .BOF and .EOF will both be true. In no other situation will this be the case, so this is the test for an empty recordset. If you attempt to navigate to a record in an empty recordset, you'll get that error. So, you have to test for this. For example:
Private Sub txtMjrName_Change()
 With rs3
.   'If rs3.RecordCount = 1 Then
    If Not (.BOF And .EOF) Then
    txtMjrCode.Text = !MjrCode
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
   Else
    txtMjrCode.Text = ""
    
    cboType.Text = ""
   End If
   
  End With

End Sub

Recordcount isn't consistently reliable, as some types of cursors (the thingie that points at a record in a recordset) don't recognize Recordcount accurately in all situations. I would recommend (and indeed, it is considered best practice) that you change your test as I've shown above.

Another recommendation is that you shouldn't use text boxes unless you are allowing users to type stuff in. Your second text box only displays data, and therefore should be a label. You can put borders around labels if you want to.


Hi BobRodes :) thnks and thanks andrewsw
the mnjCode is a key field so I won't retrieve more than one record n I don't need to check the BOF and EOF, in addition I need the text boxes bcoz the user should be able to add new record, but the other problem now is:

what if I want this code works for two text boxes Simultaneously?

I want the txtMjrCode fills the txtMjrName and vice versa, but these two text boxes empty each other when I type a new value which is not in the table (the time I want to define a new record for the table!)
I don't have any solution in mind :|

Private Sub txtMjrCode_Change()

 Set rs3 = New ADODB.Recordset

 With rs3:
  .Open "Select mjrName,mjrCode,mjrProvider from tblMajors where mjrCode='" & txtMjrCode.Text & "'", cn, 2, 3


   If rs3.RecordCount = 1 Then
    txtMjrName.Text = !MjrName

    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"

   Else
    txtMjrName.Text = ""

    cboType.Text = ""

   End If

  End With
End sub
Private Sub txtMjrName_Change()

  Set rs3 = New ADODB.Recordset
 With rs3:

  .Open "Select mjrName,mjrCode,mjrProvider from tblMajors where mjrName='" & txtMjrName.Text & "'", cn, 2, 3 
   
   If rs3.RecordCount = 1 Then
    txtMjrCode.Text = !MjrCode
    If !MjrProvider = True Then cboType = "pro" Else cboType = "elem"
   Else
    txtMjrCode.Text = ""
    cboType.Text = ""
   End If
  End With
End Sub


Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 10:10 AM

Are you saying that you do not need to check if there are no records (ZERO records) retrieved? If so, then why are you checking the Recordcount in your code? Let's deal with this first, and the we'll unravel your other requirement.
Was This Post Helpful? 1
  • +
  • -

#11 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 10:26 AM

View PostBobRodes, on 10 February 2013 - 10:10 AM, said:

Are you saying that you do not need to check if there are no records (ZERO records) retrieved? If so, then why are you checking the Recordcount in your code? Let's deal with this first, and the we'll unravel your other requirement.


of course I was wrong! I didn't consider such situation ( zero records).
just sorry. sounds I'm confused by this project which won't pay a single penny to me
but I promised to complete it.
Was This Post Helpful? 0
  • +
  • -

#12 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 10:39 AM

Ok, so make the test for .EOF and .BOF a habit. Now, let's see. First, don't use Change event, use KeyPress event. Second DON'T (repeat DON'T) access the database every time the user hits a key. The user will have to wait several seconds every time he types something. Instead, use Andrew's Filter suggestion.

Now that I've explained the right way to do all this, well, it really isn't going to work for what you want to do. You are planning to enter a new value in your database each time the user types in a letter! The user needs to tell you when he's done typing, and then have you look up what he's typed or enter it as new. So, I would do this:

1. User types something in a text box: empty both text boxes (use KeyPress event to do this).
2. Add a command button that the user presses when he's done entering a value.
3. In this command button's Click event, check to see if the value is in the database (if you use Andrew's suggestion of a Filter--which I recommend, if you don't have too many records--Filter the recordset, and then check .BOF and .EOF to see if the record is there. If it isn't, show a "Not Found" message and ask if the user wants to add a new record. If he does, add it.
Was This Post Helpful? 1
  • +
  • -

#13 azadehv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-February 13

Re: Problem filling a textbox connected to DB

Posted 10 February 2013 - 11:57 AM

View PostBobRodes, on 10 February 2013 - 10:39 AM, said:

Ok, so make the test for .EOF and .BOF a habit. Now, let's see. First, don't use Change event, use KeyPress event. Second DON'T (repeat DON'T) access the database every time the user hits a key. The user will have to wait several seconds every time he types something. Instead, use Andrew's Filter suggestion.

Now that I've explained the right way to do all this, well, it really isn't going to work for what you want to do. You are planning to enter a new value in your database each time the user types in a letter! The user needs to tell you when he's done typing, and then have you look up what he's typed or enter it as new. So, I would do this:

1. User types something in a text box: empty both text boxes (use KeyPress event to do this).
2. Add a command button that the user presses when he's done entering a value.
3. In this command button's Click event, check to see if the value is in the database (if you use Andrew's suggestion of a Filter--which I recommend, if you don't have too many records--Filter the recordset, and then check .BOF and .EOF to see if the record is there. If it isn't, show a "Not Found" message and ask if the user wants to add a new record. If he does, add it.


OK! thanks again, your kindness
gonna go to change the codes as your recommendations.
Was This Post Helpful? 0
  • +
  • -

#14 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Problem filling a textbox connected to DB

Posted 11 February 2013 - 09:50 PM

You're very welcome. Hope the suggestions get you sorted out.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1