Addressing Access Forms

[AlexanderBB]

I'm doing a convert from Excel to Access and wonder if there's any templates or guides to
creating an Excel-like UserForm in Access ?

Also in Excel e.g. "With frmMyForm" lets you address the Forms controls with ".". I can't get this working in Access. Is it
used differently or not available ?

Thanks.

 

[Joe4]

I think that creating Access Forms is actually much easier than creating Forms in Excel. Unlike Excel, you don't need to explicitly link each textbox with a value. If your Form is bound, you can elect to show all fields, and then just drag and drop them to the Form wherever you like.

If you are having issues with a specific task, please provide more details (and any relevant VBA code or VBA code attempts).

 

[HiTechCoach]

When learning Access forms, you really need to forget everything you know about Excel UserForms.

AN Aces for can add, edit and delete records with NO VBA code. How? Because Access can bind a record source to the form and the control on the form. All by setting properties. NO VBA CODE!

Bound forms is what makes Access forms so powerful.

 

[AlexanderBB]

When learning Access forms, you really need to forget everything you know about Excel UserForms.

A lot of the Forms I'll be making aren't bound to a table, and the various controls are populated from VBA and primarily used for User input or data display.

One in particular I need to emulate has dozens of controls, all in a Frame to enable scrolling. And so there can be a mix of forecolours. I imagine that could be
a bit tricky. I've heard that "rich text boxes" allow more attributes but never got to grips with them (or seen any good examples). Plan of attack is see how it comes together and lots of Googling.

Can I ask something else? Can you use a flexigrid or Datagrid in Access 2007? I know you can't in excel but can in (older) VB6.

 

[HiTechCoach]

Also in Excel e.g. "With frmMyForm"

In Access you can use teh shortcut Me to refere to a form so


With Me

A lot of the Forms I'll be making aren't bound to a table, and the various controls are populated from VBA and primarily used for User input or data display.

One in particular I need to emulate has dozens of controls, all in a Frame to enable scrolling. And so there can be a mix of forecolours. I imagine that could be
a bit tricky. I've heard that "rich text boxes" allow more attributes but never got to grips with them (or seen any good examples). Plan of attack is see how it comes together and lots of Googling.

Can I ask something else? Can you use a flexigrid or Datagrid in Access 2007? I know you can't in excel but can in (older) VB6.

 

[HiTechCoach]

Not sure what happend to my previous post. I will try again

Also in Excel e.g. "With frmMyForm"

In Access you can use the shortcut Me to refer to a form so

With Me
            .ControlName  = "Spmething"
            .ControlName2 = "Something else"
End With

A lot of the Forms I'll be making aren't bound to a table, and the various controls are populated from VBA and primarily used for User input or data display.

One in particular I need to emulate has dozens of controls, all in a Frame to enable scrolling. And so there can be a mix of forecolours. I imagine that could be
a bit tricky. I've heard that "rich text boxes" allow more attributes but never got to grips with them (or seen any good examples). Plan of attack is see how it comes together and lots of Googling.

The key to harnessing a database is to use tables. Even is the data is only input then discarded or read in for display and the deleted. Whenever you can use tables and bound forms. Whenever you use Access you want to use tables.

Not sure what you mean by "all in a Frame to enable scrolling". Maybe that is the way yu would do it in Excel but not really the Access way. Access has the built-in ability to allow scrolling by using a sub form. Again this would be easy if you were taking advantage of tables.

Can I ask something else? Can you use a flexigrid or Datagrid in Access 2007? I know you can't in excel but can in (older) VB6.

Sure you can. Would I recommend it or use it myself for you project? NO, not until I determined Access could not do it with the built-in form controls and tables.

In the past 25+ years as a full time Access developer I have rarely seen any Access professional developers or Microsoft Access MVPs that use any third-party grid controls. They have learned the power of Access forms in continuous view or or datasheet view and bound to a recordset.

I would urge you to learn Access's true power. It will be a huge leap from the Excel way of doing things. In the end it will make your life much easier when working in Access.

I do have AN example using a a datagrid control (not built in Access controls) is you can't figure out how to do it with Access.

 

[AlexanderBB]

Thanks for the advice, I will take it on board and experiment with data in a table bound to a control). The reason for Datagrid was to have resizable columns with click and drag method.
It's something I could not do in Excel (but did see working in VB6). Thank you for offer of Datagrid example. I'll get back to you when at that stage.
An alternate way to "all in a Frame to enable scrolling" would be welcome as it was a real design mess with about 50 different controls in a very small area.It was the only way I could see to do it and have the different forecolours required. Now I see continuous view mode has colour attributes so perhaps that's worth a try.. I have a huge workbook to convert so it'll take months. But allow me to use some better methods I've since learned.

 

[SydneyGeek]

Adding my 2 cents...
Definitely, use bound controls for the data where you can.
When running searches, unbound combo boxes provide some very powerful filtering functionality.
Another area where Access forms are much richer than Excel Userforms is in the events associated with each control and with the forms themselves. Access VBA is heavily event-driven, and you can build some really slick stuff once you understand when and how to use events.

Denis

 

[HiTechCoach]

I totally agree with Denis.

Adding my 2 cents...
Access VBA is heavily event-driven, and you can build some really slick stuff once you understand when and how to use events.

I like to say that you just sprinkle a few lines of VBA code into the proper events to do very powerful stuff.

Coming from Access, I was really surprised at how much VBA code it takes to do stuff with Excel Userforms.

 

[Joe4]

Coming from Access, I was really surprised at how much <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code it takes to do stuff with Excel Userforms.

Yes, it really is a "night-and-day" difference. I started off doing a lot in Excel and VBA before learning Access many years ago. I must say, since I have learned Access, I have not created an Excel UserForm. Access's Forms are so much easier to create and use.