Solved Pop-Up Datasheet Form Positioning and Scrollbars Questions

[Marshall Brooks]

Centering and vertical size is solved. Is there any way to get rid of the horizontal scroll bar?

And everyone's effort is greatly appreciated!

 

[arnelgp]

you can adjust the width of the form (on form view) until the horizontal scrollbar is removed.
then determine its InsideWidth on the Immediate window.

?forms!frmAuditLog.insidewidth

manually put the value on your ResizeAndCenterDatasheetForm() sub:

Form2Center.InsideWidth = 10560

 

[Marshall Brooks]

SUCCESS!!! Reply#20 Vertical Height works fine. Reply#22 Vertical Height has a vertical scroll bar.

I used your instructions in Reply#22 and the demo in Reply #20 and got this:

In ResizeAndCenterDatasheet Form, I also changed:
NewHeight = (Form2Center.RowHeight * .RecordCount) +420
to
NewHeight = (Form2Center.RowHeight * .RecordCount) + 100

Which go rid of the extra line at the bottom of the pop-up. (With values below 100 I got horizontal and vertical scroll bars and with values over 100 I got partial rows beneath the last record.

And I commented out the Form.InsideWIdth value in the Form_Open() module in the form itself.

It looks great to me now!!!

Thank you all for the assistance!!!

 

[Marshall Brooks]

One last follow-up question. This is meant to be a display-only form. i.e I have additions, deletions, and edits turned off. Added records are inserted using data macros on the back-end table.

In the example, the row1, column1 value is highlighted and I can move between the values (which I might want to do to copy data out of the form. Is there any way to remove the highlight from the first value?

Thank you all again!

 

[Marshall Brooks]

Better - In Form Load, I added
Me.REFERENCE.SelLength = 0

It still has the I-Beam cursor in the first cell, but at least the entire value isn't highlighted.

I tried some of the other things that I'd done previously like setting focus on a zero-width and height text box or a zero-width and height button but got an error that it couldn't set focus to those - maybe b/c it's in datasheet view?

 

[isladogs]

For info, you can move the focus away from the datasheet by using it as a subform

Alternatively by using a split form with the single form section hidden

 

[Marshall Brooks]

@isladogs - Thanks, but I'm going to leave it the way it is now:

I don't want it as a subform.

Split form could work, but we (mostly the others in this thread) put a lot of work into this and the split form still has the first cell selected with a different background, it just gets rid of the cursor, which is a plus, but ...

 

[isladogs]

Hi Marshall
Obviously up to you but both forms only took a couple of minutes to create
Looks like you found the alternate row colors button for datasheets

 

[Marshall Brooks]

Obviously up to you but both forms only took a couple of minutes to create

LOL - only took YOU a couple of minutes to create ... (Actually, creating the datasheet form was simple, it was getting it to size and position properly that was a headache.)

Looks like you found the alternate row colors button for datasheets

Actually, no, I didn't. The datasheet form was always using the alternate row colors. I was having trouble with the positioning and was considering going to a continuous forms view and @theDBguy mentioned the setting for that, but I'm not finding it in the database.

 

[isladogs]

LOL - only took YOU a couple of minutes to create ... (Actually, creating the datasheet form was simple, it was getting it to size and position properly that was a headache.)

Actually, no, I didn't. The datasheet form was always using the alternate row colors. I was having trouble with the positioning and was considering going to a continuous forms view and @theDBguy mentioned the setting for that, but I'm not finding it in the database.

It should only take a couple of minutes for anyone to create a split form and hide the single form section

As for the datasheet alternate row colors, Its not the easiest thing to find. Open the datasheet normally (not in design view).
Click the Form Datasheet ribbon then the wrongly named Alternative Row Color button

As for myself, I rarely use datasheets. With a bit of effort you can get all the functionality of a datasheet in a continuous form .... and more besides

 

[Marshall Brooks]

As for the datasheet alternate row colors, Its not the easiest thing to find. Open the datasheet normally (not in design view).
Click the Form Datasheet ribbon then the wrongly named Alternative Row Color button

You aren't kidding. I don't see a Form Datasheet option. Even if I go to Customize the Ribbon, I don't see a Form Datasheet option that I can add. If I open the form in Design view, I see a Format tab and on that tab, there is Background> and Alternate Row Color, but it is grayed out and can't be selected (regardless of what the default view is selected as).

It's working so I'm not overly concerned about it, but it would be nice to know for future reference.

 

[isladogs]

Open a datasheet form from the navigation pane. The Form Datasheet ribbon item should appear automatically.
It does NOT appear for any other type of form - not even a split form as that isn't a true datasheet
As already stated it doesn't appear in design view. Datasheets are designed to be formatted at runtime

 

[Marshall Brooks]

This is the form (basically) that @arnelgp posted in Reply #20. I'm not sure if you would call it a datasheet form or not. It's a regular pop-up form with the default view set to datasheet view.

 

[isladogs]

Set the popup property to No. Open the form and you will see the Form Datasheet ribbon. Set the alternate colour you want.
Once you are happy with it, you can revert to popup. The alternate colour will be retained

 

[Marshall Brooks]

I was going to post something sarcastic like "Of course, that was totally obvious", but it didn't even work???

 

[isladogs]

I even downloaded arnelgp's example purely to test if there was anything unusual about it before I answered.
Your screenshot is in design view. Close and reopen it normally!
If it still fails try creating a new datasheet form with no code and which isn't a popup and it should work.
If not, suggest you do an Office repair.

 

[Marshall Brooks]

I feel stupid. It's there and it works!!!!

I was used to only seeing Alternate Background Colors in design view and grayed out.

As you said, when I changed it to pop-up > No and opened it normally, the Form Datasheet option popped out and there it was.

I never would have figured it out on my own, though!!!!

Thank you agian!

 

[isladogs]

No problems.
I've just written an article explaining the issue and why it happens together with the workaround

Form Datasheet ribbon missing in popup form

This article explains why the form datasheet ribbon does not appear with popup forms. The article also provides a workaround as the ribbon is needed for datasheet conditional formatting, setting alternate row colors etc.

www.isladogs.co.uk

 

[Marshall Brooks]

@isladogs - Thanks, great stuff as always!!!