Filling Text boxes from recordset

[cstickman]

Happy Friday Everyone, So I was searching the web and came across some code that I would like to implement in a future project. So I made a test table in a new access database and started to play with it. I do not understand a few items and was hoping someone could help me out to get it to work and understand it. So below is the code and I can then explain what I understand and what I am confused on. If I can understand it and know how to use it that would be great.

Option Compare Database
Const MaxAcctCount = 5
Const AcctTextBoxPrefix = "tctacctnum"

Private Sub cmdadd_Click()
Dim RS As DAO.Recordset, SQL As String
Dim I As Integer

For I = 1 to MaxAcctCount

SQL = *SELECT acctnumber FROM tbltest GROUP BY acctnumber"

SET RS = CurrentDb.OpenRecordset(SQL)

Me.Controls(AcctTextBoxPrefix & I).Value = RS!acctnumber
Next I
End Sub

So I understand the Const, recordset, SQL and the Integer. When I run the code it just adds the first account number 5 times. The table is set up as ID, acctnumber, custname. The account number is just 1, 2, 3, 4, and 5 and made up names like Donald Duck, Mickey Mouse and etc.

I am just trying to get the 5 tctacctnum to populate with 1, 2, 3, 4, and 5. Then my next step would be to add a WHERE clause and add the names associated to the account number.

So I do not understand the Next I and I tried RS.MoveNext and nada. So can anyone help with what I am doing wrong and am I even using the code correctly? Thanks

 

[Ranman256]

You don't use code to assign text boxes.
It is automatic. The form is bound to the query.
Then the auto form will put bound textboxes on the form.

Don't make work you don't have to do.

 

[pbaldy]

Since you're trying to learn, more like:

SQL = *SELECT acctnumber FROM tbltest GROUP BY acctnumber"

SET RS = CurrentDb.OpenRecordset(SQL)
For I = 1 to MaxAcctCount
  Me.Controls(AcctTextBoxPrefix & I).Value = RS!acctnumber
  RS.MoveNext
Next I

Though certainly you wouldn't normally populate this way. This would error if the recordset had fewer records than your counter, but I guess it's a learning experience.

 

[cstickman]

pbaldy

Thanks for responding. I tried the RS.MoveNext and same results. You also mentioned you would not populate this way. What would be a more efficient way to populate the text boxes?

 

[pbaldy]

You tried that code and got the same result??

As ranman said, most of the time you'd simply use a bound form to work with values from a table.

 

[cstickman]

Yes, it just displays 1 in all the text boxes and does not do the 1, 2, 3, 4 and 5.

 

[pbaldy]

Can you attach the db here?

 

[cstickman]

Unfortunately no, my work has strict policies on our machines that block a lot of stuff. I could do it at home, but I only have Access 2007 and I am not sure it would work at all on 2007.

 

[pbaldy]

What is your exact code now?

 

[cstickman]

This is what I have that is producing 1, 1, 1, 1, and 1

Option Compare Database
Const MaxAcctCount = 5
Const AcctTextBoxPrefix = "tctacctnum"

Private Sub cmdadd_Click()
Dim RS As DAO.Recordset, SQL As String
Dim I As Integer

For I = 1 to MaxAcctCount

SQL = *SELECT acctnumber FROM tbltest GROUP BY acctnumber"

SET RS = CurrentDb.OpenRecordset(SQL)

Me.Controls(AcctTextBoxPrefix & I).Value = RS!acctnumber
RS.MoveNext
Next I
End Sub

 

[pbaldy]

Did you notice that I opened the recordset before the loop? You're opening inside the loop, so you keep getting the same record.

 

[cstickman]

Ha, well look at that it worked. I did not even think about that, but it was a learning experience. Thank you very much as always!!

The next step will be to add the customer name with the WHERE Clause. I am going to try and play with it first before reaching out for help.

 

[pbaldy]

Happy to help!

 

[cstickman]

I am back and tried the WHERE clause, but am getting a run time error 425 - object required.

I did the WHERE clause as a function as I thought that would be the best to cycle through the txt fields.

Function code which is saved as globals

Function BuildWhereClause() As String

BuildWhereClause = " WHERE acctnumber = " & tctacctnum.Value

End Function

Here is the code in the private sub on the form

Dim RS As DAO.Recordset, SQL As String
Const MaxTextCustCount = 5
Const CustTextBoxPrefix = "txtcustname"

SQL = "SELECT acctnumber, custname FROM tbltest" + BuildWhereClause

Set RS = CurrentDb.OpenRecordset(SQL)

While Not RS.EOF
Me.Controls(CustTextBoxPrefix & RS!acctnumber).Value = RS!custname
RS.MoveNext
Wend
End Sub

Any suggestions?

 

[pbaldy]

On what line? The function would need the full form reference if it's in a standard module. This doesn't really make sense:

Me.Controls(CustTextBoxPrefix & RS!acctnumber).Value = RS!custname

If I keyed in account 12345, you'd need a textbox with the name "txtcustname12345", which seems unlikely. I'd use & rather than + to concatenate. + is a math operator; while it can be used to concatenate, it won't always work the same.

As has already been mentioned, you are really complicating something that can be done simply with a bound form. If there are needs that require an unbound form, I'd only display a single record, so it would simply be "txtCustName" that I dropped the name into.