Return Multiple Records

[rgbanse]

I'm using an unbound form with an SQL string. In query method the query will return 4 records(correectly). In the form, it appears to be only returning the first record. I'm sure I have a problem using the 'With' statement.
Thx in advance
RGB

 

[Rolliee]

How are you puttiing the info in the form?

Rollie E

 

[RichCraig]

code?

 

[rgbanse]

Set RcdSt = ThisDatabase.OpenRecordset(SqlString, dbOpenDynaset)

xref_item_nbr = (RcdSt!im_item_nbr)

 

[OhioSteve]

Why is the form unbound? The way to set a control source is with the control source property.

 

[rgbanse]

To say that the form is unbound is not totally correct. It is bound to the SQL string. The form returns and displays to expected results unless more than one record should be returned. On those SQL string results, it appears to only return one of the expected records.
thx
RGB

 

[Rolliee]

Set RcdSt = ThisDatabase.OpenRecordset(SqlString, dbOpenDynaset)

If this recordset has more than one record, you move back and forth with:

RcdSt.movefirst
do while not RcdSt.eof
' whatever with each record
RcdSt.movenext
loop

RcdSt.close

rollie@bwsys.net

 

[rgbanse]

I have created and unbound form with a 'Button' object and an unbound text box named DrawingNbrBox
The 'OnClick' event for the Item Button is as follows:

Private Sub ItemButton_Click()
Dim ThisDatabase As Database
Dim RcdSt As Recordset
Dim SqlString As String
Dim RequestedItem As String
Set ThisDatabase = CurrentDb
'User input for Item Number
RequestedItem = InputBox("Enter Item Number&quot
SqlString = "SELECT Drwngs.* FROM Drwngs WHERE (((Drwngs.xref_item_nbr)=""" & RequestedItem & ""&quot); "
Set RcdSt = ThisDatabase.OpenRecordset(SqlString, dbOpenDynaset)
With RcdSt
RcdSt.MoveFirst
Do While Not RcdSt.EOF
Me.DrawingNbrBox = (RcdSt!xref_dwg_nbr)
RcdSt.MoveNext
Loop
End With
RcdSt.close
End Sub

Using an item number known to have 4 records in a standard query will result in four(4) records. The above method returns four(4) records but one at a time. It will not list all 4 records at the same time on the form. The sql string is in deed returning the proper number of records, I just can't seem to get them displayed at the same time on the same form.
thx again
RGB

 

[Rolliee]

This code moves you through the form's recordset one at a time but so fast you can not see it. Put a msgbox in the code and watch it - just before you move to the next record.

Rollie E

 

[rgbanse]

Thanks Rollie - ended up making a sub form to show all records at the same time
RGB