Form / subform problem

[ksmith]

I’m stumped! I have been trying to make a form/subform application where the first record of a table or query writes to the first subform and the second record writes to the second subform , and the third record writes to the third subform and so on.
I have been looking at calendar form examples but I have not been able to figure out how to get the record source of the first subform to look at the first record, and the record source of the second subform to look at the second record, etc.
Table Structure and Sample Data… tblSampleData
ID 1 (Autonumber)
Item Hot Box (Text less than 10 characters)
Quantity 125 (Numeric)

ID 2
Item Wet Box
Quantity 225

ID 3
Item Box Cutter
Quantity 235

As of now I have a form (frmMaster), with 15 subforms named SF1, SF2, SF3… SF15.
Ultimately I want to display the Item and Quantity data in subforms that will look like buttons. The total number of records to cycle through will be less than 15 or 20. Any help will be greatly appreciated.

KSmith

[Robeen]

In the Data Source [Record Source?] in the Properties Pane of your sub Form - have you tried putting something like this:

For SubForm1:
Select * from [TableName] Where [FieldName] = "ID 1"

For SubForm2:
Select * from [TableName] Where [FieldName] = "ID 2"

?

[ksmith]

Thanks for the reply.
Yeah I can make that work BUT I may not always know what the ID number will be. And also there may be a different number of records that will be available to the Form / subform.

I have been playing around with using variables and trying to increment through a VBA procedure to get this to work.

Again, thanks for the reply and hopefuly I'll get this thing to working.

KSmith

[ksmith]

That's ok I got it working.

KSmith

[Robeen]

How did you get it working?

[ksmith]

On the form / subform that I have made to test all of this I have a form that has places for 36 subforms name SF1, SF2, SF3… SF36. Their visibility is set to False.

Set db = CurrentDb
Set rs = db.OpenRecordset("qryBarCode", dbOpenDynaset)

There’s a section of code that counts the records in the Recordset that I made from a query.
mRecordCount = rs.RecordCount

That record count number is used to turn set the “Visible = True” on the correct number of subforms.
For cnt = 1 To mRecordCount
Me.Controls("SF" & CStr(cnt)).Visible = True
Next

With rs
cnt = 1
For cnt = 1 To mRecordCount
Set frm = Forms!frmbarcode.Controls("SF" & CStr(cnt)).Form
mSF = "SF" & CStr(cnt)
mOlivia = "SELECT BarcodeNumber,Item,Quantity,SF FROM qryBarCode WHERE SF = " & Chr$(39) & mSF & Chr$(39)
frm.RecordSource = mOlivia
rs.MoveNext
Next
End With
rs.Close
.Close

All of the Sub Form Names begin with SF and I made the query have a field that also has a SF1, SF2…. For each record in the query.

The above code Selects the record from the recordset that has the same name as the subform and stores it to mOlivia (my wife).

The subforms recordsource pulls or gets the data for that ‘occurrence’ from mOlivia.

KSmith