Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 4th, 2004, 04:36 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populating text box from RecordSet

Hello, I need help

Im working on an ACCESS form and I am trying to populate a text box with 2 fields from a recordset. I am trying to use a "For Loop" to do this and its not writing all the records in the recordset to the text box. It only writes the first record. The ESSN is used for employee social security number which is getting entered by the user typing it into txtSSN.

Here is my code:
myRS2.ActiveConnection = CurrentProject.Connection
mySQL2 = "Select ESSN,PNO,HOURS From Works_On Where ESSN=" & txtSSN
myRS2.Open (mySQL2)

'Write employee project(s) and hours to text field
For empCounter = 0 To myRS2.EOF
    txtWorkHrs = "Project" & myRS2("PNO") & "-" & myRS2("HOURS") & "Hours"
    myRS2.MoveNext
Next

Can anyone here help me with this and show me the correct way of doing this? It would be greatly appreciated. This is for a project that Im working on for my class in College.

Thank you for your time.
Eric


 
Old April 4th, 2004, 05:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I may be understanding this wrong, but are you trying to add new records based on your recordset? if so, you'd probably be after something like this to replace your loop:

Code:
For empCounter = 0 to myRS2.EOF
   CurrentDB.Execute ("INSERT INTO YourTable (YourField) VALUES ('Project" _
     & myRS2("PNO") & "-" & myRS2("HOURS") & "Hours')")
   myRS2.MoveNext
Next
Form_YourForm.Requery
I am a loud man with a very large hat. This means I am in charge
 
Old April 4th, 2004, 05:33 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Im not trying to add records.
Im just trying to populate the txtSSN text box with the records that match the Social security Number that the user typed in.

So basically im just trying to read the current record set and match the SSN that user gave to any records in the record set that match it.

In the text box I want to display ProjNo and Hours from that record set that match that SSN.

Im sorry if this does not make sense.

 
Old April 4th, 2004, 07:42 PM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you tried stepping through your code in debug mode? This could help you determine if your for loop is actually looping through all the records. From what I see, here is my analysis.

You state the text box is populated with the data from the first record. This tells me your app is leaving the for loop after the first record. As your loop is written, I would expect to see the data from the last record in the text box. In order to build a list, you would need to construct your loop in a manner similar to the following:
Code:
Do Until myRS2.EOF

    If empCounter = 0 then

        txtWorkHrs = "Project" & myRS2("PNO") & "-" & myRS2("HOURS") & "Hours"

    Else

        txtWorkHrs = txtWorkHrs & Chr(10) & "Project" & myRS2("PNO") & "-" & myRS2

    End If

    empCounter = empCounter + 1

    myRS2.MoveNext

Loop
HTH



Darrell L. Embrey
 
Old April 4th, 2004, 08:23 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Darrell

Thank so much.
That worked. The problem was how originally had the loop set up. It was existing out of the loop after one iteration.

I understand it now.

Thanks for the help.
Eric


 
Old April 4th, 2004, 08:52 PM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One of the rare occassions where I have the right answer.

Darrell L. Embrey




Similar Threads
Thread Thread Starter Forum Replies Last Post
Please : populating selection box kumar_siva HTML Code Clinic 2 December 29th, 2005 05:45 AM
populating an array from text box olddog VB.NET 2002/2003 Basics 1 July 27th, 2005 08:30 AM
Populating a text box from a RecordSet ersp Access 1 April 4th, 2004 04:22 PM
How to bind text box to recordset fields? cici VB How-To 2 December 1st, 2003 02:10 AM
populating a box sporkman43 Classic ASP Basics 0 August 18th, 2003 02:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.