f



Forms and Control Question

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 


0
me2025 (20)
1/15/2007 3:27:58 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

8 Replies
339 Views

Similar Articles

[PageSpeed] 41

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"
....

0
1/15/2007 4:09:49 PM
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" <michael.matthys@hotmail.com> wrote in message 
news:1168877388.011015.82250@q2g2000cwa.googlegroups.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"
> ...
> 


0
me2025 (20)
1/15/2007 4:11:58 PM
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.

0
1/15/2007 4:26:47 PM
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
0
Tim
1/15/2007 4:36:35 PM
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" <michael.matthys@hotmail.com> wrote in message 
news:1168878404.812356.168100@m58g2000cwm.googlegroups.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.
> 


0
me2025 (20)
1/15/2007 4:45:44 PM
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" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message 
news:eogaij$doq$1@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 


0
me2025 (20)
1/15/2007 5:13:29 PM
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
0
Tim
1/15/2007 5:17:27 PM
"SBC News Groups" <me@you.net> wrote

 > 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


0
bouncer (4168)
1/15/2007 10:00:41 PM
Reply: