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.

 

[AlexanderBB]

Thanks Denis, that was indeed it. I'm using Access 2007 for the first time and all sorts of different things are happening.
Can I remove/ not have the last record ? The one Showing a * on the LH side). I made the same table in Access 2003 and that doesn't have it.

 

[AlexanderBB]

Reading back through the messages the recommendation is to bind a control to a table as the 'right' way, with no VBA code.
So this must be a really dumb question, but the control source holds the field name , how do you specify which record ?

 

[SydneyGeek]

When the form is bound to the table, you can browse through the records -- see navigation controls on bottom left of form.
If you make changes to a control value, they affect the current record.
You can easily set up a combo box to find a particular record and navigate to it. See this for an example: Using combo boxes in Access, Part 1 -- DataWright Information Services

Denis

 

[AlexanderBB]

I don't quite follow Denis, wouldn't that mean picking the value from the combo box ?
In whicht case, wouldn't it be better to just write the value directly and not be bound to anything ?

 

[SydneyGeek]

Yes, but the trick to it is what the combo box does with the value.
If you follow it through, this is the sequence:
1. You type the name you want to search
2. The combo takes the associated ID, which is the stored value
3. It then moves the form to that ID, without having to filter down to just one record.

You can't do that easily with a textbox, because you would have to first look up the ID then run 2 and 3 above, and also ensure that you didn't have any typos (which would result in an error).

And the other thing is that, with an unbound form, you have to retrieve the contents of every control and populate the form. Then, when you finish editing, you take every value and write it back to the underlying table. You can do it, but a lot of work is involved. You typically see those sorts of setups in networked databases with high numbers of concurrent users. It's overkill for simpler situations.

Denis

 

[AlexanderBB]

Denis, I may have this all wrong but is what you're suggesting is a way to pick the value at run time? I have the default values to populate the Form with as it opens.
They're already in a table so binding them to controls seemed correct and what everyone here is advising. In Excel I would just set the Control Source to e.g. Worksheet!A20 and mission over.

 

[SydneyGeek]

No, not to pick the values at run time. The form will populate with the contents of the table.
But because you will have many records in the table (and hence in the form), it often helps to be able to locate the record you want. That is what i was describing.

Denis

 

[AlexanderBB]

Ah.. I think I've cottened onto something. The reason you, Joe4 and HiTechCouch advised binding a control to a table is because one control is bound to one table with many records? Yes?
My table has one record with many fields for many controls. Some controls has just one value (e.g. display of file paths) so there's no list to choose from. So the record source would not be a table, but a query on a particular 'cell' in the table. Is that preferable having an ubound control and populating it directly? I ask because I'd like to do this the 'right way'.
Also. HiTechCouch said if I wanted to scroll a Frame that was bad practice. Then I found setting a frame invisible did not, as it does in Excel, hide all the controls within the frame. I have a requirement for both, how would that be done in Access? I did think of a subform, but I can't see any backcolour property. If if doesn't have that, perhps other properties needed aren't available. (I've never used one before to know).

 

[SydneyGeek]

If you are only working with one record, a subform isn't going to help much because they are usually intended to display many records related to the one one the form.
Examples would be students in a class, line items on an invoice, maintenance items in a shut-down schedule.
But it sounds like you want to group controls so that you can see / edit one set at a time. One option is to use a tabbed form. You can have multiple tabs, each with a descriptive label, and move between groups of controls that way. There are quite a few tutorials out there -- here is one; https://support.office.com/en-au/article/Create-a-tabbed-form-eb9499a8-6854-4d42-b9c7-86bab76fb6b4

As for bound / unbound forms, bound forms are just much easier to use for data entry. Forget about having to save everything, one control at a time, when you finish data entry. Access just does it.

Denis

 

[AlexanderBB]

Thanks Denis, I've added a tab control and it lends itself nicely to what I'm doing, I've even managed to make the text boxes rich ones and added some colour and bolding etc.

But I struck a snag and I think it applies to all text boxes. They won't show a vertical scroll bar until it has the focus.
I'd like the scroll bar to be visible regardless, so it's obvious further text exists to scroll down and read.

So I applied SetFocus in Form Load, but this also selects (highlights) all the text. And is reapplied as you tab between the pages.

Is there any way to setfocus just to the scrollbar ? Or another way to have the scrollbar in view?