Solved Pop-Up Datasheet Form Positioning and Scrollbars Questions

[Marshall Brooks]

Please see the attached database.

This is ultimately pretty minor, but I am having problems getting the datasheet view pop-up form for my audit log results to center properly. I found ten or so threads asking the same types of questions, but nothing that really resolved the issue.

Audit Log Thread: https://www.access-programmers.co.u...update-of-a-field.329588/page-10#post-1909410

If you look at the attached, the frmTimerProgressBar centers perfectly.

The frmAuditLog has the following problems:

• The form was originally too wide. In the Form_Open() Procedure, I set the Me.InsideWidth to 14850 which fixed it. This was trial-and-error with various values, but it seems to work regardless of display resolution.
• The form has a horizontal scroll bar that I can't get rid of. Me.Scrollbars = 0 or 2 do not work. (I want a vertical scroll bar only if required). I think this is b/c the actual form IS wider than shown and I am just changing the .InsideWidth to make it display smaller. I don't know how to shrink it overall, though.
• The form is offset to the left of center. I think this is also due to the form being wider than what it is displayed at.
• The form is longer (taller) than necessary. I don't know how to correct this. I can reduce the height with Me.InsideHeight, but if I add additional records, the form will be too short then. I included links and info in the VBA for how to do this with a listbox, but I didn't see a way to adapt that to a pop-up form.

Thank you in advance.

 

[theDBguy]

When I removed all you code, this is what I get. Does it look off to the left of center to you?

 

[Marshall Brooks]

Just checking back, does nobody know how to fix this.

I figured out I can use a continuous form instead of datasheet view and that let's me set the forms width correctly with no scroll bar, but the height is still much longer than the number of records, but not as bad as the datasheet form. I'd be okay with using that if it can set the height for the number of records required.

 

[Marshall Brooks]

Stumbuled on this: https://www.access-programmers.co.u...tion-of-continuous-form-too-big.156542/page-2 - looks like nothing can be done if using tabbed windows for the database, which is my preference.

Does anyone know what controls the "zebra stripes" on the form view (alternating row colors)?

 

[theDBguy]

Just checking back, does nobody know how to fix this.

I figured out I can use a continuous form instead of datasheet view and that let's me set the forms width correctly with no scroll bar, but the height is still much longer than the number of records, but not as bad as the datasheet form. I'd be okay with using that if it can set the height for the number of records required.

You didn't respond to my post, so I wasn't sure if you were still having a problem.

 

[theDBguy]

Stumboled on this: https://www.access-programmers.co.u...tion-of-continuous-form-too-big.156542/page-2 - looks like nothing can be done if using tabbed windows for the database, which is my preference.

Does anyone know what controls the "zebra stripes" on the form view (alternating row colors)?

Hmm, seems like that was a different problem than what you were having in this thread.

 

[Marshall Brooks]

My bad! I responded, but I must have forgotten to click "Post Reply".

To reply to #2 - If you delete the centering code that I had, I think it will not display properly if you have two monitors and the primary monitor is on the left and the database is on the right, but I can't test that right now. When you deleted the code, it is centered horizontally, but it is wider than the content, which is why I added the .insidewidth line, but then I get the scrollbar. Without the centering code, the form is about 1/3 of the screen too high, and either way the form is about 20 records longer than it should be.

Reply #3 and #4 - I was experimenting with a continuous forms view instead of using datasheet view. With that, I can set the width of the form properly without a scrollbar, but I still have the form taller vertically than it needs to be.

Thank you!!!

 

[theDBguy]

My bad! I responded, but I must have forgotten to click "Post Reply".

To reply to #2 - If you delete the centering code that I had, I think it will not display properly if you have two monitors and the primary monitor is on the left and the database is on the right, but I can't test that right now. When you deleted the code, it is centered horizontally, but it is wider than the content, which is why I added the .insidewidth line, but then I get the scrollbar. Without the centering code, the form is about 1/3 of the screen too high, and either way the form is about 20 records longer than it should be.

Reply #3 and #4 - I was experimenting with a continuous forms view instead of using datasheet view. With that, I can set the width of the form properly without a scrollbar, but I still have the form taller vertically than it needs to be.

Thank you!!!

Thanks for the clarification.

As for this:

Does anyone know what controls the "zebra stripes" on the form view (alternating row colors)?

The "Alternate Back Color" property will control the stripe colors on a continuous form, but I am not sure where the setting for datasheet is.

 

[Marshall Brooks]

Wouldn't have thought of Alternate Back Color - Thank you again!!!

 

[Josef P.]

Is that what you mean:
* resize form to height of records
* center form inside Access application window

 

[Marshall Brooks]

Sounds right, but I'm getting this:

I commented out the .insideHeight line in the OnOpen event, but I still get the error.

Otherwise, the code looks like it should do what I need, though!!!

 

[Josef P.]

On the off chance: Delete [Event Procedure] text from OnOpen and insert again. Or cut and paste the code in the form module.

 

[Marshall Brooks]

Wasn't thinking last night and just looked at the form code. This morning I tried to do a compile:
It gives me a type mismatch in the module here:

Public Function TwipsPerPixelX() As Single
Dim DC As LongPtr
DC = GetDC(HWND_DESKTOP)
TwipsPerPixelX = 1440& / GetDeviceCaps(DC, LOGPIXELSX)
ReleaseDC HWND_DESKTOP, DC
End Function

Above it has:
Declare PtrSafe Function GetDC Lib "user32" (ByVal Hwnd As Long) As Long

So either DC needs to be Long or GetDC needs to be LongPtr, but I'll leave that for you or others to figure out.

 

[arnelgp]

Declare PtrSafe Function GetDC Lib "user32" (ByVal Hwnd As LongPtr) As LongPtr

 

[Marshall Brooks]

Thanks - now getting Type Mismatch here:
Public Function ConvertPixelsToPoints(ByVal sngPixels As Single, _
ByVal sXorY As String) As Single
Dim hdc As LongPtr

hdc = GetDC(0)
If sXorY = "X" Then
ConvertPixelsToPoints = sngPixels * (72 / GetDeviceCaps(hdc, 88))
End If
If sXorY = "Y" Then
ConvertPixelsToPoints = sngPixels * (72 / GetDeviceCaps(hdc, 90))
End If
Call ReleaseDC(0, hdc)
End Function

 

[arnelgp]

Declare PtrSafe Function GetDeviceCaps Lib "gdi32" Alias _
"GetDeviceCaps" (ByVal hdc As LongPtr, ByVal nIndex As Long) As Long

 

[Marshall Brooks]

It has that line. Might be easier for someone else to re-work and re-post the example.

 

[arnelgp]

you mean post #10 sample, i re worked it.

 

[Marshall Brooks]

Thank you - sample works now.

Getting closer. Centering is fine. Still has a horizontal scroll bar, even though there is no data on the right. Now has a vertical scroll bar, even though all the records should be able to be displayed:

 

[arnelgp]

here is a modified version that i made.