Concatenate an integer to Me.txtBox issues 1

[storl2]

I am trying to concatenate a integer that increases by one each time a loop runs to the end of two Me.txtBox's. I am not sure of the syntax to do this, and I couldn't find anything in help, any of the books I have, or any of these forums.

I know this is wrong, but here is what I have:

Do Until rst1.EOF

rst1.MoveNext

int1 = int1 + 1

Me.txtQuestionID & int1 = rst1![fldQuestionID]
Me.txtQuestion & int1 = rst1![fldQuestionTxt]

Loop

What it is doing is looping through a recordset and filling in text boxes on a form that are labeld txtQuestionID1, txtQuestionID2, etc. The number of questions is dynamic, so I am having to make it where it will fill in and make visible the necessary boxes bases on how many there actually are. Any ideas on the syntax? Thanks!

 

[RoyVidar]

Try:

[tt]Me("txtQuestionID" & int1).value = rst1![fldQuestionID]
Me("txtQuestion" & int1).value = rst1![fldQuestionTxt][/tt]

Roy-Vidar

 

[storl2]

Gives error 2113: The value you entered isn't valid for this field.

Here is the code for the whole sub:

Private Sub Form_Load()

Dim rst1 As New ADODB.Recordset
Dim sql1 As String
Dim int1 As Integer

'Open the recordset, sorted by Question type and then number, so all types are grouped together.

sql1 = "SELECT tblQuestions.fldQuestionType, tblQuestions.fldQuestionID, tblQuestions.fldQuestionTxt FROM tblQuestions ORDER BY tblQuestions.fldQuestionType, tblQuestions.fldQuestionID;"

rst1.Open sql1, CurrentProject.Connection

rst1.MoveFirst

Me.txtQuestionID1 = rst1![fldQuestionID]
Me.txtQuestion1 = rst1![fldQuestionTxt]

'Loop through the records, making each box visible if necessary and filling them with correct text.

int1 = 1

Do Until rst1.EOF

    rst1.MoveNext
    
    int1 = int1 + 1
    
    Me("txtQuestionID" & int1).Value = rst1![fldQuestionID]
    Me("txtQuestion" & int1).Value = rst1![fldQuestionTxt]
 
Loop
    
    
'Clean everything up before exiting
    
rst1.Close
Set rst1 = Nothing
    

End Sub

 

[storl2]

RoyVidar,

It looks like it is actually filling in the boxes, until it gets past the last record. It is at that point that it gives the error. I have 30 questions right now, and after it does the 30th box, int1 becomes 31, and the error pops up. Shouldn't teh "Do Until rst1.EOF" stop that or am I understanding EOF incorrectly.

Thanks for the other syntax, by the way.

 

[RoyVidar]

Which field?

Are the controls bound? Do you stuff text into a control bound to a numeric field, or too much text into a control bound to a text field?

Which line does it bomb on?

To be able to help, well need more info. Have you had time to read faq181-2886 yet? #14;-)

BTW - issuing a movenext without testing for .eof can be a bit dangerous, better place one before the loop, and one as the last line within the loop.

Roy-Vidar

 

[RoyVidar]

You beat me there, but look at my last paragraph;-)

Roy-Vidar

 

[Cogivina2004]

Hi Roy-Vidar,

Thank for solving one of my problem.

Me("txtQuestionID" & int1).Value = rst![fldQuestionID]

My other question is how would you concatenate the int1 to the field name, e.g, rst!(int1) instead of rst![fldQuestionID]. My rst has 20 fields with each field named 1, 2, 3...,20.

 

[RoyVidar]

Same way

[tt]rst("fldQuestionID" & int1).value[/tt]

- but this to me indicates you may have an unnormalized table structure...

Roy-Vidar

 

[storl2]

RoyVidar, that's great! Putting the movenext before the do loop was my next step, and you confirmed the idea. It was bombing at the line:

Me("txtQuestionID" & int1).Value = rst1![fldQuestionID]

I put a movenext before the do loop, and another at the end of the loop, and now the sub works great. Thanks for the help!