Forms and Control Question

[SBC News Groups]

Can someone please tell/show me how I would do the following...

I have a table called tblOne. In tblOne I have three fields, two, three,
four.

I have a form called frmMain. I have three blank controls on the form. I
want to assign the names from the table to the controls on the form, ex.
ctlfive displays two's data, and so on. Does that make sense to anyone?

I want to do this with VBA. The reason is that I will have other tables and
I want to use as few forms as possible so I setup up a form with many
controls and I turn off the ones that I do not need.

The ones I do need, I need to assign to the table columns.

Thanks for any help on this...

Mike
m charney at sbc global dot net

[Mike]

I guess you could do this through setting the record source of the form
and the control sources of the controls, although it is probably not
example of good coding.

E.g.
frmMain.RecordSource = "tblOne"
frmMain.ctlFive = "fldTwo"
....

[SBC News Groups]

I can do that but with 24 fieds on tne form it can be a lot of programming.

I was wondering if there was a way to loop through and have them assign that
way. Lots less programming lines.

Mike

"Mike" <mi*************@hotmail.comwrote in message
news:11*********************@q2g2000cwa.googlegrou ps.com...

>I guess you could do this through setting the record source of the form
and the control sources of the controls, although it is probably not
example of good coding.

E.g.
frmMain.RecordSource = "tblOne"
frmMain.ctlFive = "fldTwo"
...

[Mike]

What you could do is store the information in some kind of master table
and then loop through the information and assign the field-values to
the corresponding controls.

[SBC News Groups]

That would be duplicating information that is already in teh DB.

I know there has to be a way to do this, I just can't seem to figure it out.

Thanks anyway.

Mike

"Mike" <mi*************@hotmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

What you could do is store the information in some kind of master table
and then loop through the information and assign the field-values to
the corresponding controls.

[Tim Marshall]

SBC News Groups wrote:

I have a form called frmMain. I have three blank controls on the form. I
want to assign the names from the table to the controls on the form, ex.
ctlfive displays two's data, and so on. Does that make sense to anyone?

I want to do this with VBA. The reason is that I will have other tables and
I want to use as few forms as possible so I setup up a form with many
controls and I turn off the ones that I do not need.

From a development point of view, it's probably a more cost effective
use of your time to simply copy the form and save it under a different
name, change the recordsource table and controlsources of the text
boxes/form controls.

The question that comes to my mind is are you sure you've designed your
data structure properly? The first thing that pops up with a red flag
is it sounds as if the different tables might actually all be the
same... Do not have tables with the same structure and types of data.
It's a stupid butthead like method of doing things.

Otherwise, I would use openargs on the form to populate stuff. The
following is air code.

Given tables tbl_stuff, tbl_other_stuff, tbl_things, all with different
fields and frmMain with UNBOUND text boxes txtA, txtB, txtC

Have a calling form with three buttons:

btnStuff with Caption "Data Enter Stuff" and on click event:
docmd.OpenForm "frmMain",,,,,,"Stuff"

btnOtherStuff with Caption "Data Enter Other Stuff" and on click event:
docmd.OpenForm "frmMain",,,,,,"OtherStuff"

btnThings with Caption "Data Enter Things" and on click event:
docmd.OpenForm "frmMain",,,,,,"Things"

frmMain would need code similar to the following for the on open event.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

select case me.openargs

Case "Stuff"
me.recordsource = "tbl_Stuff" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"
Case "OtherStuff"
me.recordsource = "tbl_Other_Stuff" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"
Case "Things"
me.recordsource = "tbl_Things" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"

end select

end sub

Again, IF YOU HAVE MULTIPLE TABLES WITH THE SAME INFORMATION IN THE
FIELDS, YOUR DATA DESIGN IS SHAGGED UP, PERIOD.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[SBC News Groups]

Hi Tim,

No I have different tables and they do not have the same information in
them. One is customer data, one is clerk data, one is consignor data. I
wanted to use the same form and just reassign the controls based on the
records source.

Me.RecordSource = "tblCustomers"

I started to program that code the way you have it but it gets really long
with 24 different text/lable controls, buttons and other misc things. I have
to change the TOP, LEFT, WIDTH on each control.

I wanted to make just one form, but I guess it is looking like I might not
be able to do that.

Thanks,

Mike.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:eo**********@coranto.ucs.mun.ca...

SBC News Groups wrote:

>I have a form called frmMain. I have three blank controls on the form. I
want to assign the names from the table to the controls on the form, ex.
ctlfive displays two's data, and so on. Does that make sense to anyone?

I want to do this with VBA. The reason is that I will have other tables
and I want to use as few forms as possible so I setup up a form with many
controls and I turn off the ones that I do not need.

From a development point of view, it's probably a more cost effective use
of your time to simply copy the form and save it under a different name,
change the recordsource table and controlsources of the text boxes/form
controls.

The question that comes to my mind is are you sure you've designed your
data structure properly? The first thing that pops up with a red flag is
it sounds as if the different tables might actually all be the same... Do
not have tables with the same structure and types of data. It's a stupid
butthead like method of doing things.

Otherwise, I would use openargs on the form to populate stuff. The
following is air code.

Given tables tbl_stuff, tbl_other_stuff, tbl_things, all with different
fields and frmMain with UNBOUND text boxes txtA, txtB, txtC

Have a calling form with three buttons:

btnStuff with Caption "Data Enter Stuff" and on click event:
docmd.OpenForm "frmMain",,,,,,"Stuff"

btnOtherStuff with Caption "Data Enter Other Stuff" and on click event:
docmd.OpenForm "frmMain",,,,,,"OtherStuff"

btnThings with Caption "Data Enter Things" and on click event:
docmd.OpenForm "frmMain",,,,,,"Things"

frmMain would need code similar to the following for the on open event.

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

select case me.openargs

Case "Stuff"
me.recordsource = "tbl_Stuff" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"
Case "OtherStuff"
me.recordsource = "tbl_Other_Stuff" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"
Case "Things"
me.recordsource = "tbl_Things" 'the name of the table
me.txtA.controlsource = "=Two"
me.txtB.controlsource = "=Three"
me.txtC.controlsource = "=Four"

end select

end sub

Again, IF YOU HAVE MULTIPLE TABLES WITH THE SAME INFORMATION IN THE
FIELDS, YOUR DATA DESIGN IS SHAGGED UP, PERIOD.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

SBC News Groups wrote:

I started to program that code the way you have it but it gets really long
with 24 different text/lable controls, buttons and other misc things. I have
to change the TOP, LEFT, WIDTH on each control.

I wanted to make just one form, but I guess it is looking like I might not
be able to do that.

If you're going to need to change widths and other appearance
characteristics, it's really much easier to go with different forms,
IMO. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Larry Linson]

"SBC News Groups" <[e-mail]

No I have different tables and they do not have the same information in
them. One is customer data, one is clerk data, one is consignor data. I
wanted to use the same form and just reassign the controls based on the
records source.

Me.RecordSource = "tblCustomers"

What you want to do can be done, but it does not allow you to use the
appropriate kind of Controls for different types of data, so if your Fields
are not all text Fields with individually entered text, it is unlikely to be
user-friendly. And, if they are, it will still be, at best, difficult to
adjust the size of the displayed Controls to the underlying data... unlike
Reports, the "CanGrow" and "CanShrink" properties you see on Controls on
Forms do not work.

But, since this is "only" a matter of usability, rather than a matter of
design that will eventually "rise up to bite you in the tender places", the
following VBA code does what you want for a form with six Labels and six
Text Boxes... for Tables with fewer Fields, the remaining Labels are blank
and the remaining Text Boxes empty. If I were doing this for real, live
users, I'd certainly at least make any unused text boxes invisible.

But, before you get excited about a method that works, let me repeat, your
users will not thank you for using such a method for your own convenience --
and I suspect, in time, that will be true even if you are the only user.

The name of the underlying Table or Query is passed as a text string in the
OpenArgs argument of a DoCmd.OpenForm, and the following event code in the
"General Form" works for me. You'll need to add your own error handling,
etc.:

Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
End Sub

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim i As Integer, intMax As Integer
Set rs = Me.RecordsetClone
intMax = IIf((rs.Fields.Count - 1) 5, 5, rs.Fields.Count - 1)
For i = 0 To intMax
Me("Label" & Format((i + 1), "#0")).Caption = rs.Fields(i).Name
Me("TextBox" & Format((i + 1), "#0")).ControlSource =
rs.Fields(i).Name
Next i
rs.Close
Set rs = Nothing
End Sub

and, you could do the Form_Load code in fewer lines by using
Me.RecordsetClone directly, but this seemed more readable to me.

Larry Linson
Microsoft Access MVP