Dynamic Subforms based on the number of records in Table

[cw via AccessMonster.com]

I have a frmProjectorControlMasterList based on my tblComPortProp.
My table can contain up to 15 records, and each record includes a field
called "ACTIVE".

If I display the Form in Continuous mode, it displays just the Active records,
which is what I want.
But I cannot add individual Command Buttons for each record, with unique code
behind each button.

How do I display a Continuous Subform (frmProjectorControlMasterList), based
on my Table (tblComPortProp), and have it create "Dynamic" Subforms for each
"Active" record?

- If I "uncheck" the Active field in one of the table records, then the
Subform "automatically" disappears from my main form? (next time it is opened)


- If I add a new record, then a new Subform shows up on the main form? (next
time it is opened)

Thanks,
cw

 

[Tony Toews [MVP]]

But I cannot add individual Command Buttons for each record, with unique code
behind each button.

A continuous form, or subform, can't have properties or code different
on each record.

However, you could have a long Select Case statement depending on a
value on each record, which then calls different code.

Select case me.RecordType
Case 1
do something
case 2
do something
....
case else
msgbox "unhandled RecordType:" & RecordType
end select

Tony

 

[cw via AccessMonster.com]

Tony, I need to re-phrase my question..
I meant to say, How do I display my Main form (frmProjectorControlMasterList
), with a regular subform (subfrmControlMaster1) below it, but one subform
for each record in my table (tblComPortProp) ? Like this:

- tblComPortProp has 4 records in it. (001, 002, 003, 004)

- frmProjectorControlMasterList (Main form)
---------------------------------------------
- subfrmControlMaster1 (displays record 001)
- subfrmControlMaster2 (displays record 002)
- subfrmControlMaster3 (displays record 003)
- subfrmControlMaster4 (displays record 004)

So, instead of using a continuous Form, I'm just using "multiple" subforms
instead.
(Currently I have created 16 subforms, added them all to my Main form, and
hard-coded the query behind each one to filter by the Active field. When the
Active field is 0, that subform does not show up on my Main form. (I have the
subforms set to shrink/grow so the Main form adjusts automatically)

Basically, I add the "Devices" (records) into the table, one record for each
device needing to control, up to 16 devices (records). Then when I open the
Main form, and it needs to display the correct number of subforms (one for
each record, and each one will have custom code).

Does that make sense..? Is there a better way to go about getting custom
subforms on my Main form?

Thanks again,
cw

 

[cw via AccessMonster.com]

I think the way I have it is going to work fine. (with all 16 subforms placed
on my Main form, but only display the ones that have data)

Last question would be:
- How to not call a subform when it is not available? Let me explain:

- I have this code behind a button on my Main form:
-------------------------------
Private Sub cmdAllON_Click()
Call Me.subfrmControlMaster1.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster2.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster3.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster4.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster5.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster6.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster7.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster8.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster9.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster10.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster11.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster12.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster13.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster14.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster15.Form.cmdPowerOn_Click
Call Me.subfrmControlMaster16.Form.cmdPowerOn_Click
End Sub


- How do I keep it from crashing when say, one or two of the subforms will
not be displayed (hence..Do Not Call those two?)
- Something like: NotNull or IsNull, or NZ(), or..?

Basically I need it to only "Call" the subforms that are listing on my Main
form.
Thanks,
cw

Tony, I need to re-phrase my question..
I meant to say, How do I display my Main form (frmProjectorControlMasterList
), with a regular subform (subfrmControlMaster1) below it, but one subform
for each record in my table (tblComPortProp) ? Like this:

- tblComPortProp has 4 records in it. (001, 002, 003, 004)

- frmProjectorControlMasterList (Main form)
---------------------------------------------
- subfrmControlMaster1 (displays record 001)
- subfrmControlMaster2 (displays record 002)
- subfrmControlMaster3 (displays record 003)
- subfrmControlMaster4 (displays record 004)

So, instead of using a continuous Form, I'm just using "multiple" subforms
instead.
(Currently I have created 16 subforms, added them all to my Main form, and
hard-coded the query behind each one to filter by the Active field. When the
Active field is 0, that subform does not show up on my Main form. (I have the
subforms set to shrink/grow so the Main form adjusts automatically)

Basically, I add the "Devices" (records) into the table, one record for each
device needing to control, up to 16 devices (records). Then when I open the
Main form, and it needs to display the correct number of subforms (one for
each record, and each one will have custom code).

Does that make sense..? Is there a better way to go about getting custom
subforms on my Main form?

Thanks again,
cw
[quoted text clipped - 16 lines]

Tony

 

[Tony Toews [MVP]]

Tony, I need to re-phrase my question..
I meant to say, How do I display my Main form (frmProjectorControlMasterList
), with a regular subform (subfrmControlMaster1) below it, but one subform
for each record in my table (tblComPortProp) ? Like this:
,,,,

So, instead of using a continuous Form, I'm just using "multiple" subforms
instead.

My objection is to you using 16 subforms one for each record. Why not
figure out how to use 1 subform which displays the 16 records.

Tony

 

[cw via AccessMonster.com]

Tony, I too hated the idea of not using a single subform, but I can't seem to
find a way around it?

Since each subform/record has to have:
- cmdPwrOn
- cmdPwrOff
- ImageOff
- ImageOn
(And the code behind those Command buttons uses the record data specifically
to work)
I have been unable to get each item above to function "independent" of the
other records, when displayed as a continuous form, etc.

My 16 hard-coded subforms shrink up nicely, and all of the Commands & Lights
work perfectly.
I'm still open to learning a new skill in coding/Access 2007. And I
appreciate your help. You were the one who actually directed me to Richard
Grier's XMCommCRC, and it works great! Thanks..

Also, do you (or anyone else) have any ideas on how to Call the Command
buttons from the Active subforms only? (see previous post above)

Thanks again for everything,
cw

 

[Tony Toews [MVP]]

Tony, I too hated the idea of not using a single subform, but I can't seem to
find a way around it?

Since each subform/record has to have:
- cmdPwrOn
- cmdPwrOff
- ImageOff
- ImageOn
(And the code behind those Command buttons uses the record data specifically
to work)
I have been unable to get each item above to function "independent" of the
other records, when displayed as a continuous form, etc.

So what do those buttons do then?

Also, do you (or anyone else) have any ideas on how to Call the Command
buttons from the Active subforms only? (see previous post above)

I'm still not understanding the problem.

Tony

 

[cw via AccessMonster.com]

The first button: cmdPwrOn grabs the communications data from the
subform/record, and sends the correct "CODE" to an Overhead Projector, or TV
device via a serial cable. Each record in the table is a specific DEVICE to
control. (Currently only turning the device ON or OFF)

So each subform has an ON button & OFF button, along with two LED images that
go red or green.
On the Main Form, I have an ALL ON button & ALL OFF button, that needs to
click each of the individual ON or OFF buttons at once, but only for the
subforms that are "ACTIVE"?

Here is a link to view the Main Form w/10 Active subforms:
http://cerberus.clearwave.com/jerry/PCM_Main_Form.jpg

Heres the code behind a cmdPwrOn button:
-------------------------------------------
Public Sub cmdPowerOn_Click()
'******** Turn on the Green Power On LED image icon ********
Me.ImageOn.Visible = True
Me.ImageOff.Visible = False
'******** Assign Com Port Settings and Values to variables and Send Power On
command code to device ********

'******** Assign Com Port and Com Settings from subForm record data to String
Variables
Dim strCommPort As Integer
Dim strCommSettings As String

strCommPort = Chr(34) & Me.txtConCom & Chr(34)
strCommSettings = Chr(34) & Me.txtConBau & "," & Me.txtConPar & "," & Me.
txtConDat & "," & Me.txtConSto & Chr(34)

'******** Send the Command Code to the Serial Device in either ASCII or HEX
Select Case Me.txtOptionAsciiHex
Case Is = 1
XMCommCRC1.InBufferCount = 0 'Flush the Buffer

If (Not XMCommCRC1.PortOpen) Then

Dim Instring As String ' Buffer to hold input string
XMCommCRC1.CommPort = strCommPort ' Use Com Port variables from
subform.
XMCommCRC1.Settings = strCommSettings ' Use baud, parity, data,
and stop bit from variables subform.
'XMCommCRC1.InputLen = 0 ' Tell the control to read entire buffer
when Input is used.
XMCommCRC1.PortOpen = True ' Open the port.
XMCommCRC1.Output = Me.txtConPwrOn & vbCr ' Send the ON command
to the device. Ensure that the device responds with "OK".
'Buffer$ = Buffer$ & XMCommCRC1.InputData ' Wait for data to come
back to the serial port.
'MsgBox "SMS Port Open", vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
Else
'MsgBox "Port Already Open, It is now closing, and will re-open",
vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
'XMCommCRC1.PortOpen = True
Call cmdPowerOn_Click
End If
Case Is = 2
XMCommCRC1.InBufferCount = 0 'Flush the Buffer

If (Not XMCommCRC1.PortOpen) Then

Dim Instring As String ' Buffer to hold input string
XMCommCRC1.CommPort = strCommPort ' Use Com Port variables from
subform.
XMCommCRC1.Settings = strCommSettings ' Use baud, parity, data,
and stop bit variables from subform.
'XMCommCRC1.InputLen = 0 ' Tell the control to read entire buffer
when Input is used.
XMCommCRC1.PortOpen = True ' Open the port.
XMCommCRC1.Output = Chr$(2) & Me.txtConPwrOn & Chr$(3) & vbCr '
Send the ON command to the device. Ensure that the device responds with "OK".
'Buffer$ = Buffer$ & XMCommCRC1.InputData ' Wait for data to come
back to the serial port.
'MsgBox "SMS Port Open", vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
Else
'MsgBox "Port Already Open, It is now closing, and will re-open",
vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
'XMCommCRC1.PortOpen = True
Call cmdPowerOn_Click
End If
End Sub
-----------------------------------------------

Thanks..

Tony, I too hated the idea of not using a single subform, but I can't seem to
find a way around it?

[quoted text clipped - 8 lines]

I have been unable to get each item above to function "independent" of the
other records, when displayed as a continuous form, etc.

So what do those buttons do then?

Also, do you (or anyone else) have any ideas on how to Call the Command
buttons from the Active subforms only? (see previous post above)

I'm still not understanding the problem.

Tony

 

[cw via AccessMonster.com]

By using some crude IF/End IF statements, I have this close to working (see
code below).
It runs thru the "Visible" subforms fine, turning On all devices, but stops
with "Error: 2427 - You entered an Expression with No Value" on the first
subform that does not exist?

That error is actually coming from the cmdPowerOn button on
subfrmControlMaster10, (I currently have 9 Active subforms). It is basically
unable to find that Subform, so when the "All On" code runs, it is not
properly "excluding" the "InActive" subforms?

-------------------------------------------
Private Sub cmdAllON_Click()
If Me.subfrmControlMaster1.Form.Visible = True Then
Call Me.subfrmControlMaster1.Form.cmdPowerOn_Click

If Me.subfrmControlMaster2.Form.Visible = True Then
Call Me.subfrmControlMaster2.Form.cmdPowerOn_Click

If Me.subfrmControlMaster3.Form.Visible = True Then
Call Me.subfrmControlMaster3.Form.cmdPowerOn_Click

If Me.subfrmControlMaster4.Form.Visible = True Then
Call Me.subfrmControlMaster4.Form.cmdPowerOn_Click

If Me.subfrmControlMaster5.Form.Visible = True Then
Call Me.subfrmControlMaster5.Form.cmdPowerOn_Click

If Me.subfrmControlMaster6.Form.Visible = True Then
Call Me.subfrmControlMaster6.Form.cmdPowerOn_Click

If Me.subfrmControlMaster7.Form.Visible = True Then
Call Me.subfrmControlMaster7.Form.cmdPowerOn_Click

If Me.subfrmControlMaster8.Form.Visible = True Then
Call Me.subfrmControlMaster8.Form.cmdPowerOn_Click

If Me.subfrmControlMaster9.Form.Visible = True Then
Call Me.subfrmControlMaster9.Form.cmdPowerOn_Click

If Me.subfrmControlMaster10.Form.Visible = True Then
Call Me.subfrmControlMaster10.Form.cmdPowerOn_Click

If Me.subfrmControlMaster11.Form.Visible = True Then
Call Me.subfrmControlMaster11.Form.cmdPowerOn_Click

If Me.subfrmControlMaster12.Form.Visible = True Then
Call Me.subfrmControlMaster12.Form.cmdPowerOn_Click

If Me.subfrmControlMaster13.Form.Visible = True Then
Call Me.subfrmControlMaster13.Form.cmdPowerOn_Click

If Me.subfrmControlMaster14.Form.Visible = True Then
Call Me.subfrmControlMaster14.Form.cmdPowerOn_Click

If Me.subfrmControlMaster15.Form.Visible = True Then
Call Me.subfrmControlMaster15.Form.cmdPowerOn_Click

If Me.subfrmControlMaster16.Form.Visible = True Then
Call Me.subfrmControlMaster16.Form.cmdPowerOn_Click
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
---------------------------------------

The first button: cmdPwrOn grabs the communications data from the
subform/record, and sends the correct "CODE" to an Overhead Projector, or TV
device via a serial cable. Each record in the table is a specific DEVICE to
control. (Currently only turning the device ON or OFF)

So each subform has an ON button & OFF button, along with two LED images that
go red or green.
On the Main Form, I have an ALL ON button & ALL OFF button, that needs to
click each of the individual ON or OFF buttons at once, but only for the
subforms that are "ACTIVE"?

Here is a link to view the Main Form w/10 Active subforms:
http://cerberus.clearwave.com/jerry/PCM_Main_Form.jpg

Heres the code behind a cmdPwrOn button:
-------------------------------------------
Public Sub cmdPowerOn_Click()
'******** Turn on the Green Power On LED image icon ********
Me.ImageOn.Visible = True
Me.ImageOff.Visible = False
'******** Assign Com Port Settings and Values to variables and Send Power On
command code to device ********

'******** Assign Com Port and Com Settings from subForm record data to String
Variables
Dim strCommPort As Integer
Dim strCommSettings As String

strCommPort = Chr(34) & Me.txtConCom & Chr(34)
strCommSettings = Chr(34) & Me.txtConBau & "," & Me.txtConPar & "," & Me.
txtConDat & "," & Me.txtConSto & Chr(34)

'******** Send the Command Code to the Serial Device in either ASCII or HEX
Select Case Me.txtOptionAsciiHex
Case Is = 1
XMCommCRC1.InBufferCount = 0 'Flush the Buffer

If (Not XMCommCRC1.PortOpen) Then

Dim Instring As String ' Buffer to hold input string
XMCommCRC1.CommPort = strCommPort ' Use Com Port variables from
subform.
XMCommCRC1.Settings = strCommSettings ' Use baud, parity, data,
and stop bit from variables subform.
'XMCommCRC1.InputLen = 0 ' Tell the control to read entire buffer
when Input is used.
XMCommCRC1.PortOpen = True ' Open the port.
XMCommCRC1.Output = Me.txtConPwrOn & vbCr ' Send the ON command
to the device. Ensure that the device responds with "OK".
'Buffer$ = Buffer$ & XMCommCRC1.InputData ' Wait for data to come
back to the serial port.
'MsgBox "SMS Port Open", vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
Else
'MsgBox "Port Already Open, It is now closing, and will re-open",
vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
'XMCommCRC1.PortOpen = True
Call cmdPowerOn_Click
End If
Case Is = 2
XMCommCRC1.InBufferCount = 0 'Flush the Buffer

If (Not XMCommCRC1.PortOpen) Then

Dim Instring As String ' Buffer to hold input string
XMCommCRC1.CommPort = strCommPort ' Use Com Port variables from
subform.
XMCommCRC1.Settings = strCommSettings ' Use baud, parity, data,
and stop bit variables from subform.
'XMCommCRC1.InputLen = 0 ' Tell the control to read entire buffer
when Input is used.
XMCommCRC1.PortOpen = True ' Open the port.
XMCommCRC1.Output = Chr$(2) & Me.txtConPwrOn & Chr$(3) & vbCr '
Send the ON command to the device. Ensure that the device responds with "OK".
'Buffer$ = Buffer$ & XMCommCRC1.InputData ' Wait for data to come
back to the serial port.
'MsgBox "SMS Port Open", vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
Else
'MsgBox "Port Already Open, It is now closing, and will re-open",
vbOKOnly, "Port State"
XMCommCRC1.PortOpen = False
'XMCommCRC1.PortOpen = True
Call cmdPowerOn_Click
End If
End Sub
-----------------------------------------------

Thanks..
[quoted text clipped - 10 lines]

Tony