checkboxes created on the fly

[reta]

I have a db and i would like to count the number of fields related to that ID. In my onloans table i have the the ID of the person and the serial number of the product and i would like to count the number of serial numbers the person has borrowed against their ID. Once this is done i would like to create checkboxes so the user can check which product they would like to return. Therefore the checkboxes will have to be dynamic and for every ID a different number of checkboxes will be displayed.
I know i have to use an array but i dont know how to print the checkboxes and were to put the code.

code eg
Dim c As Integer
Dim numRecords As Integer
SQL = select * from Onloan where Serial = '" & Serial &"'
set rs = new recordset
rs, SQL etc
numRecords = rs.recordcount
for c = 1 to numRecords
print checkbox
loop
Thanks for your help
Raeda

 

[VBslammer]

Create a temporary table that has the fields you want to display for each item along with a Yes/No field for the checkbox, and use it to populate a subform in continuous forms mode. After the user checks the pertinent checkboxes, have them click a button to trigger your update, which can be based on the recordsetclone of the subform.

VBSlammer

Unemployed in Houston, Texas

 

[reta]

Hi VBslammer
Could i create a query which selects only the items for that particular ID. But how would i create the checkboxes on the fly. Is there any code that you could give me.
Thanks
reta

 

[VBslammer]

What if there were 100 items in the recordset, or 1000? Would you want to try and fit that many checkboxes on a form?

What I do, as suggested, is create a temporary table in the database that is only used to get input from the user. If you add a subform to the form and set its recordsource to the temporary table, the checkboxes will be added automatically, because you'll have a Yes/No field in the table.

If you insist on creating checkboxes on-the-fly, the easiest way is to add them manually and make them invisible, then set their visible properties to True when you need to show them.

The benefit of using a temp table and subform is that you don't have to worry about space allocation for the controls, you can have 100 or 1000 and it won't matter - Access will handle it for you. Simple is Better!

VBSlammer

Unemployed in Houston, Texas

 

[reta]

Im sure there is away of creating them on the fly with an array. if i put one unbound check box on the form is there a way to print that more than once on the form with the array.
Thanks
Reta

 

[VBslammer]

The only way to create them on-the-fly is to open the form in design mode, and it's difficult to position them without much trial and error.

Bear in mind that you can't do this from within the form module - it's usually done from a routine in a standard module or a library database, due to the fact that the form has to enter design mode. Also note that unless you save the form before presenting it to the user, the user will be prompted to save the newly added controls when they close the form - which you don't want to do if the controls are only temporary.

If you still want to play with it, here's a simplified example of how it's done:

Sub AddCheckboxes(ByVal ttl As Integer)
  Dim frm As Form
  Dim chk As CheckBox
  Dim lbl As Label
  Dim i As Integer
  
  DoCmd.OpenForm "frmAddCheckboxes", acDesign
  Set frm = Forms("frmAddCheckboxes")
  
  For i = 1 To ttl
    Set chk = CreateControl(frm.Name, acCheckBox, acDetail, , , 0.25 * 1440, (i * 0.25) * 1440)
    chk.Name = "check" & i
    Set lbl = CreateControl(frm.Name, acLabel, acDetail, chk.Name, , 0.4 * 1440, (i * 0.25) * 1440, 1440, 240)
    lbl.Caption = "Checkbox " & i
  Next i
  
  DoCmd.OpenForm frm.Name, acNormal
  
End Sub

Note that the checkboxes and labels need to be added separately, and the labels have to be linked to their parent using the 4th argument of CreateControl().

VBSlammer

Unemployed in Houston, Texas

 

[MichaelRed]

While I would, in general, agree with VBSlammer, re the continous sub form, there are a few threads in these (Tek-Tips) fora regarding the complete programatic development of simple forms / reports.

Search for "CreateReport" of "CreateForm", you should be able to track them down.





MichaelRed