By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Can't set a form size... feel dumb :(

P: 15

I'm unable to resize the visible part of a form only to display what I want to show. (upper left)

Here what I did:
- I made a form with a tab control that has 3 pages
- Added all fields I needed below the "tab page"
- Copy/pasted each fields in each appropriate "tab page"

=> The tabbing control works and display the pages, but I can still scroll down the "hidden" fields. Access does not let me resize the form size as there are the "hidden" fields below.

Been trying most of the controls and searched the web for some time now but I saw no answers of how to fix this simple issue.

Thanks for your help.
Jun 9 '09 #1
Share this Question
Share on Google+
23 Replies

P: 167
hi deewe,
I tried to get the same results and find a solution for you. In short... try moving all the fields you don't want to be visible to the form's footer area.
Then make that footer area invisible by setting it's Visible property to false.
Try it out and tell us if this works. If not, we will find something else :-)
Jun 9 '09 #2

Expert 100+
P: 489
Hi Deewe;

Welcome to Bytes.

Since the controls are not visible you can just move them up the form to be under the tab control and then not have to worry about them.

You could set the scroll bar property of the form to "neither" that way you wont have the scroll bars to scroll with. With the scroll bars off you don't have to worry about resizing the form. Just resize the forms window to display what you want.
Jun 9 '09 #3

Expert Mod 15k+
P: 31,709
Controls being invisible to the operator doesn't mean they're invisible to Access.

It is not (nor has ever been) possible to position controls outside of the form itself. Another way of saying this is that the form will automatically set its dimensions to include all controls (hidden or otherwise).

This isn't something you can change. It's not really something you'd want to as far as I can see, as it would make absolutely no sense.

Having said this, it seems you already have some suggested solutions to your actual problem. Some quite good ones actually.

Welcome to Bytes!
Jun 9 '09 #4

P: 675
Here what I did:
- I made a form with a tab control that has 3 pages
- Added all fields I needed below the "tab page"
- Copy/pasted each fields in each appropriate "tab page"
The controls that you want hidden are duplicates of the controls on the tab page. Why do you need these controls duplicated? Although sometimes a duplicate control (two controls bound to the same field) is desirable, in this case it seems to hinder your form design.

The simple solution is to delete the unneeded controls.
Jun 9 '09 #5

P: 15
Thanks a lot to hjozinovic, DonRayner and NeoPa.

I used the form footer trick as I was still able to scroll down the form with the mouse wheel even having disabled the scroll bars.

I'm getting stuck on other things now ;) but I'm going to mess with them and see if I can do it by myself.

I wish it was possible but is seems you can't directly add fields into tabbed control field.

So as explained elsewhere I added all my fields in the form then copy/pasted them in each appropriate tab page.
Jun 9 '09 #6

P: 675
I would agree that sometimes Access seems to want to put new controls on the form, under the tab control. If, after selecting the control in the 'Toolbox', you click an incorrect tab (you have 3 tabs) and then the correct tab, you then click where you want the control, and it will work.

But I don't see where this matters. You have the controls on the tab page now, and they work.
=> The tabbing control works and display the pages
How they got there doesn't matter any more. Now delete the duplicates, and size your form.
Jun 9 '09 #7

P: 15
Honestly you lost me... thanks for trying though!
Jun 9 '09 #8

P: 675
1) Do you have the controls on the tab page now?
2) Do the controls (on the tab page) work?

If the answers to 1 and 2 are 'Yes', then:
a) Remove the controls you want to keep from showing. Delete them.
b) Size the form to include the tab page and any controls remaining.
Jun 9 '09 #9

P: 15
Surprisingly it worked, thanks.

Although I don't understand why I wasn't able to move directly the controls on the tab pages without having to copy paste them first.

On a side note I'm learning the hard way: try & errors... I just discovered even if your tables are well linked you still need to make queries to get the data in the forms. ;)

- I have a table with items and one with the different types. One item can be of only one type. Isn't it better to add an auto number primary key in the types tables and link the tables with it than the type names, this even if the names are unique?

- Would you use the same form to browse data entries and update/add items or is it better to have 2 separate forms?
Jun 10 '09 #10

Expert Mod 15k+
P: 31,709
  1. This is sometimes preferable. It's hard to say if this is right for you as the information provided is scarce and unclear.
    Some processes require reordering of the data. This is generally done by creating a temporary index on the fly. This will always use the PK to point to the individual records. With this in mind restricting the size of this index (An AutoNumber is 8 bytes) will restrict the amount of space required for the temp index and therefore its access times.
  2. This is often a matter of preference.
    I found a good suggestion the other day though :
    Change the background colour of the form (or some other visible flag) when it changes between edit and add modes.
Jun 10 '09 #11

P: 15
Thanks a lot for your inputs!

Now I have to find how to detect edit mode and change the background color;)
Jun 11 '09 #12

Expert Mod 15k+
P: 31,709
I was working on this recently so here is a snippet of code I did then :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Const conNewRec As Long = 8388608
  5. Private Const conExistingRec As Long = 8421440
  7. Private Sub Form_Current()
  8.     Call SetLocked
  9. End Sub
  11. Private Sub Form_AfterUpdate()
  12.     Call SetLocked
  13. End Sub
  15. Private Sub SetLocked()
  16.     Call ChangeColour(blnNewRec:=Me.NewRecord)
  17. End Sub
  19. Private Sub ChangeColour(blnNewRec As Boolean)
  20.     Dim lngColour As Long
  22.     With Me
  23.         lngColour = IIf(blnNewRec, conNewRec, conExistingRec)
  24.         .FormHeader.BackColor = lngColour
  25.         .Detail.BackColor = lngColour
  26.         .FormFooter.BackColor = lngColour
  27.     End With
  28. End Sub
  30. Private Sub cmdExit_Click()
  31.     Call DoCmd.Close
  32. End Sub
Jun 11 '09 #13

P: 15
Thanks NeoPa,

I'm not as far in my access knowledge yet but I'll surely try to understand your code later when I'm ready!
Jun 11 '09 #14

Expert Mod 15k+
P: 31,709
It's fairly straightforward if you don't let the number of lines frighten you.

Lines :
1-2: Standard option settings.
4-5: Defining the colour constants. Use other colours as you choose.
7-9 &
11-13: When a record is selected or saved we know we want the SetLocked() procedure to run.
15-17: The procedure simply calls the ChangeColour() procedure.
19-28: Determines the colour required, then sets the colours for the three standard sections on a form. No problem if the section isn't even used or shown.
30-32: Close when the cmdExit button is clicked. You may not need this.
Jun 11 '09 #15

P: 675
This is a response to Post #10
Surprisingly it worked, thanks.
I'm glad I could help.
Although I don't understand why I wasn't able to move directly the controls on the tab pages without having to copy paste them first.
Controls can be created directly on the tab control either by using the 'Toolbox' or the 'Field List'. If the control is on the form containing the tab control, you cannot drag the control onto the tab control. Because the control can be under the tab control, Access cannot know whether you are moving the control to under the tab control or onto it. Default is under.
Next time, use Cntl+X to delete them from the main form, and then Cntl+V to Paste them onto the tab control if they are not created on the tab control directly.
Jun 11 '09 #16

P: 15
Once again thanks NeoPa but I"m still learning the basics for now, like how to get data from 2 different linked tables make a form with tabs and have the joint table field 1 in tab 1 and show the relative field 2 data in tab 2.

Then I have to find how make it so when someone enters something that isn't in the list allow them to add an entry in the related table and even better open a new form to fill all the fields.

So as you can see I'm really far from doing some VBA yet ;)

Now that makes senses, thank you!
Jun 11 '09 #17

Expert Mod 15k+
P: 31,709
It's interesting you should express these ideas as basic. They seem fairly involved to me (Not rocket science maybe - but not so basic either). I expect you'll have difficulty getting these to work at all if you don't want to involve any VBA yet.

We're here if you need us anyway :)
Jun 11 '09 #18

P: 15
Back again ;)

I have some issue I can't solve with calculated fields.

I have a table with 1 row only and constants that would be updated by the user from time to time. For example currency conversion ratio from US $ to Euros .

In my main data table I store the price of items in US $.

As you can guess, in a form I'd like to display the price in US $ (easy) and the calculated price in Euros.

In all my tests I ended with nothing or a #Name? value.

I also try to display the currency ratio form the unlinked external table in a text field, and well as there is only one row in the currency table, I got no data in my forms.

I know it might be very simple but I'm stuck as all the examples I find are made to match values row by row and not with an external constant.

Thanks for the help!
Jun 18 '09 #19

Expert Mod 15k+
P: 31,709
It's true that such concepts require some slightly different approaches.

When linking that table into a query, simply add it as a full outer join. That would result in a Cartesian Product. As the table returns only a single record, it will simply add data from that table to any output record.

Watch out for updatability though. See Reasons for a Query to be Non-Updatable.

Another approach would be to use DLookup() where the values are required. Which approach is best depends on your specific requirements.
Jun 18 '09 #20

P: 15
Thanks NeoPa.

I have to admit I read your post more than 3 times to understand it, sorry for being a noobie ;)

I'd like to avoid making a query and have the system automatically calculate the field in the form. As if I update any price (or currency ratio) I don't want to have to re-run the query. (Maybe I'm wrong though)

Am I obliged to link the tables, even with an outer join?

Maybe I wasn't very clear, so I'll try to be more precise.

I have:
TableData (Main table)

TableConstants (Table with unique values stored)

FormData (Displays all TableData values for each record)

Then in the FormData I'd like to display the
  • PriceUS$
  • but also PriceUS$ * CurrencyRatio1
  • and PriceUS$ * CurrencyRatio2
  • along with CurencyName1
  • and CurencyName2

So what I'm trying to do is FieldEuro value = PriceUS$ (of related record) * CurrencyRatio1 (constant)

As you can see I'm stuck there.

On a side note that's the first part of the problem.
In the future I'd like to be able to make a catalog (report?) selecting separate items and have the calculated price shown.

For that part I was thinking to go that way:
1) select the records
2) choose the currency
3) calculate the price in the choosen currency (query)
4) Generate the report

Once again I'm not very fond of storing the precalculated prices as it's a waste of DB space for very small computing.

Note: I have 2 Access 2007 books with me now and none of them answers my first "simple" question...

P.S: By any chance do you have any nice links about naming conventions for fields, tables, queries? I'd like to try to make something neat.
Jun 18 '09 #21

Expert 100+
P: 1,287
The DLookup Function that NeoPa mentioned is an easy way to do this. You would make a text box (not visible probably), CurrencyRatio1, and set it's source to:
Expand|Select|Wrap|Line Numbers
  1. = DLookUp("CurrencyRatio1", "TableConstants")
Then your text box to display the value is:
Expand|Select|Wrap|Line Numbers
  1. = [PriceUS$] * [CurrencyRatio1]
That's a temporary solution, and I would definitely work on a query for the report.
Jun 18 '09 #22

Expert Mod 15k+
P: 31,709
I'm sorry about that. I do try to express myself clearly, but some of the ideas may not be familiar to you, thus leaving you without decent context to fit it into :(
If you make changes to the underlying data, I suspect you will probably need to do that anyway.
This illustrates where I led you astray. I use such joins so rarely, and Access doesn't really support them, so I get my terminology wrong sometimes. Let me clarify :
We want an unlinked (unJOINed) table. Not a full OUTER JOIN. It is the unlinked tables that produce the Cartesian Product mentioned earlier and not the full OUTER JOIN.

So No. Essentially just add the table in and don't add any joins to it.
I think the addition of [TableConstants] into your source query should do it. I doubt this will be an updatable query even before this change, so no problems created as far as I can see.
It doesn't work like that. The query the report is bound to should provide all the data required for the report. Other references can be used, but generally increase the time involved quite heavily (depending on number or records involved of course).
You could try Naming Conventions for Microsoft Access, but I can say I don't name fields with any prefix. I've also see dat used instead of dte for date variables. Lastly, I find that cbo is used more than cmb for ComboBox controls.

At the end of the day though, consistency is a good thing whichever scheme you hit upon.
Jun 19 '09 #23

P: 15
Thanks a lot ChipR, it worked like a charm.

I have to admit before posting I spent over 3 hours trying to make this working

No big deal, your help is very much appreciated, I'm the one that needs to learn all the language tied to Access.

Ok so I get that I'll have to make the query then run the report.

I guess I'll have to use some VBA as there will be a few parameters in the query,like: choice of currency, choice of language as so far I can have 3 currencies and 2 languages, so the query can select the appropriate fields for the checked records.

Thanks for the link and I totally agree with your above statement.
Jun 19 '09 #24

Post your reply

Sign in to post your reply or Sign up for a free account.