Hi
B***dy users!.
This really isn't how a database works. Forgive me if i'm telling you something you already know but the whole point of the machine table is that you can add as many machines for each user as you need there really is no simple way to start adding and deleting controls on a form at run time i.e when the user is using the form. You are going to get some serious headaches later on if you do manage to do what you want viz data integrity.
If you can't convince them that they are ludite idiots who should let you do things properly, then tell them to stick it up their A***
Only kidding but it might be worth telling them what they are asking for is not what Access was designed to do.
Any way this may be of some use but tread with caution.
I'm not sure how this is going to work then. You have to scroll through the user records in order to display the computers that the user has regardless of how you actually display the computer information. The only way to avoid the <> arrows is to use a continous form to display the user data and on the change event of the continous form you do a requery of the subform.
Any way. This is only an idea sorry don't have time to actually try it.
1) Create 1 tabcontrol with (5?) pages each with the a sub form on each ( subforms should be columnar with no navigation buttons or addnew record button (could have delete button i guess).
2) Create code to count number of records available on the form
something like (if using access 2000 or newer ,select microsoft dao 3.6 (maybe 3.51 in xp) from tools>addins in module)
You may need this on the form open event as well as the form current event
Dim rst(1) as dao.recordset
Dim db as dao.database
Dim Varbookmark as variant
Dim Tabcount as Integer
set db = currentdb
' create a rst of all the computers owned(?) by the customer
set rst(0) = db.openrecordset( Select machines.* from machines WHERE (((machine.cuid)=" & me.cuid & "));")
' hide the tab controls
Me.TabCtl0.Pages(0).Visible = true
Me.TabCtl0.Pages(1).Visible = true
' Display blank form +tab if new customer
if rst(0).recordcount = 0 then
Me.TabCtl0.Pages(0).Visible = true
exit sub
end if
' if there is a record then make the tab visible
do until tabcount = rst(0).recordcount
Me.TabCtl0.Pages(tabcount).Visible = true
' create a copy of the subforms recordset
'Not sure about this forms!frmmain.. syntax you'll need to check this out.
set rst(1) = forms!frmmain!Forms("subform" & tabcount+1).recordsetclone
rstmove = 0
rst(1).movefirst
' move the record to the equivalent tab number i.e tab0 will display record 1
do until rstmove = tabcount
rst(1).movenext
rstmove = rstmove+1
loop
'Not sure about this forms!frmmain.. syntax you'll need to check this out.
' make the sub forms rst position = to the copy of the rst(1)
forms!frmmain!Forms("subform" & tabcount+1).Bookmark = rst(1).Bookmark
set rst(1) = nothing
tabcount = tabcount +1
loop
' tabcount will be 6 if all 5 computers are used
'if not you will need to display the next computer + tab and a new record
if tabcount < 6 then
Me.TabCtl0.Pages(tabcount).Visible = true
forms!frmmain!Forms("subform" & tabcount+1).addnew
end if
set rst(0) = nothing
set rst(1) = nothing
set db = nothing
This is in no way correct or perfect it's just the basis of an idea. But i strongly suggest that you talk to the user and explain how access actually works you never no your luck.
Good luck
Chris
-- they don't like using the |< < > >| * controls (not windows enough for them). Thats why they wanted me to make it create a new tab for each computer, with the computer name as the caption so they can easily select the one they want.
