Form OK in Excel, but not in Access. (1 Viewer)

kirkm · Oct 20, 2019

Access Module:

Code:

 Sub zz()
    Form_frmExtSingles.Tester = "1234"
    DoCmd.OpenForm "MyForm", acFormDS
 End Sub

Access Form

Code:

Option Compare Database
Option Explicit
Public dd As String

Private Sub Form_Activate()
    MsgBox dd & vbCrLf & "Activate"
End Sub
Private Sub Form_Load()
    MsgBox dd & vbCrLf & "Form Load"
End Sub
Public Property Let Tester(x As String)
    dd = x
    MsgBox "I am setting property with " & dd
End Property

After MyForm has opened, string dd is nothing (in either Form_Load or Activate)

However in Excel, the only differences being

Code:

 Sub zz()
    MyForm.Tester = "1234"
    MyForm.Show
 End Sub

In the Userform_Activate routine the string dd is present.

Stepping through the code in Access shows the Let Property is called, but the value is lost after the Form opens.

Most likely the Access behaviour is correct, but is there any way for it to have the same result as Excel?
Thanks.

theDBguy · Oct 20, 2019

Hi. I'm a little lost. How is the Property Let being called? Are you saying you are seeing the MsgBox come up?

kirkm · Oct 20, 2019

Hi, DBGuy. The let property is called on the first line of Sub zz.

However I'm really fed up with Access today!. Now the stupid thing won't even run! It closes immediately it opens, unless I hold shift. Then it shoots through if I open a Form or try Compact&Repair.

So I'll have to fix this first.

If you find a solution to my question though, it's be really helpful.

theDBguy · Oct 20, 2019

kirkm said:
Hi, DBGuy. The let property is called on the first line of Sub zz.

However I'm really fed up with Access today!. Now the stupid thing won't even run! It closes immediately it opens, unless I hold shift. Then it shoots through if I open a Form or try Compact&Repair.

So I'll have to fix this first.

If you find a solution to my question though, it's be really helpful.

Hi. Thanks! Now, I see it. It would be easier to find out why it's behaving this way if we could touch it. Is that possible?

kirkm · Oct 20, 2019

You mean it's not meant to fail - it should work. OK I'll make a new accdb that does nothing else and see. If it still fails will attach it to next message.

BTW I imported all my stuff into a new Access and it all works again.

theDBguy · Oct 20, 2019

kirkm said:
BTW I imported all my stuff into a new Access and it all works again.

Hi. As I was trying to say earlier, that approach usually works. Otherwise, you will have to go back to a good backup copy. Cheers!

kirkm · Oct 20, 2019

I'm getting the same results so have attached db.

Sub zz should show "1234".
I have noticed (withe the Form not loaded) Form_Open seems to run twice...

theDBguy · Oct 20, 2019

kirkm said:
I'm getting the same results so have attached db.

Sub zz should show "1234".
I have noticed (withe the Form not loaded) Form_Open seems to run twice...

If you don't mind, I'll check it out tomorrow. It's quitting time over here.

theDBguy · Oct 20, 2019

kirkm said:
I'm getting the same results so have attached db.

Sub zz should show "1234".
I have noticed (withe the Form not loaded) Form_Open seems to run twice...

Hi. Good morning. I think I see what you mean now and have an idea what the problem is. Your sub zz() has the following lines:

Code:

    Form_MyForm.Tester = "1234"
     DoCmd.OpenForm "MyForm", acFormDS

The problem with that is you're creating two separate instances of the same form. Using the syntax Form_FormName creates an instance of the class module of that form. Using DoCmd.OpenForm simply opens that form. Those two are separate from each other, so the Load event that fires when you open the form cannot see the dd variable from the other instance. To view it, you will have to try something like:

Code:

MsgBox Form_MyForm.dd

I made some changes to your file, but not sure if it helps. Cheers!

kirkm · Oct 20, 2019

Hi DbGuy

>you're creating two separate instances of the same form. Using the syntax Form_FormName creates an instance of the class module of that form. Using DoCmd.OpenForm simply opens that form. Those two are separate from each other,

You *can* have two instances of the same Form ? Is that because one is called "Form_MyForm" and the other called just "MyForm" ?

I think I see what you've done rather that Open the Form, you Set a Variant = the Form and then show it ? Would that be correct?
What if you wanted the Form to be Datasheet View ?

I think in Excel that code doesn't open two forms, but if it does it knows what to do and shows the Msgbox with the Form under it. Excel's always seems much easier to work with. No datasheet Form view though..

BTW you asked if that same database included the datasheet Form I couldn't resize.. I did add it but then found adding Docmd.MoveSize allowed control of width & height. Resizing columns was not a positive step, sometimes it did, sometimes not. Then there was blank space to the right of the last column, and a Horiz scrollbar (even if you set Scrollbars to Vertical only). Am I too fussy expecting these things to work or format properly?

theDBguy · Oct 20, 2019

kirkm said:
Hi DbGuy

>you're creating two separate instances of the same form. Using the syntax Form_FormName creates an instance of the class module of that form. Using DoCmd.OpenForm simply opens that form. Those two are separate from each other,

You *can* have two instances of the same Form ? Is that because one is called "Form_MyForm" and the other called just "MyForm" ?

I think I see what you've done rather that Open the Form, you Set a Variant = the Form and then show it ? Would that be correct?
What if you wanted the Form to be Datasheet View ?

I think in Excel that code doesn't open two forms, but if it does it knows what to do and shows the Msgbox with the Form under it. Excel's always seems much easier to work with. No datasheet Form view though..

BTW you asked if that same database included the datasheet Form I couldn't resize.. I did add it but then found adding Docmd.MoveSize allowed control of width & height. Resizing columns was not a positive step, sometimes it did, sometimes not. Then there was blank space to the right of the last column, and a Horiz scrollbar (even if you set Scrollbars to Vertical only). Am I too fussy expecting these things to work or format properly?

Hi. I am no Excel programmer, so I cannot compare it against Access. What exactly is the purpose of this entire exercise? Maybe we can tell you how we would go about doing it. For example, I would probably not use a Property Let statement in a form. Instead, I might use the OpenArgs property.

kirkm · Oct 20, 2019

My goal is a Datasheet View Form, that can have height, width and Position specified, plus the width of the fields, or columns adjustable. (There's 4 of them).
And pass it a Recordset for it's data source. (This won't work with OpenArgs Property).
I kind of have this already, using a Global Recordset, but wanted to pass it to the Form as you would an argument for a function. Then took many hours trying to get the Form "right" which is still a sort of fudge. Be interesting to see how someone else would do this and maybe why I had such a hard time with it.

MajP · Oct 21, 2019

My goal is a Datasheet View Form, that can have height, width and Position specified, plus the width of the fields, or columns adjustable. (There's 4 of them).
And pass it a Recordset for it's data source. (This won't work with OpenArgs Property).
I kind of have this already, using a Global Recordset, but wanted to pass it to the Form as you would an argument for a function.

How is that related to the database you posted? That thing makes no sense to me. BTW, a form is a class. You can open multiple instances of the class, just like any other class.

kirkm · Oct 21, 2019

The attachment was an example of failure passing a string to a Form. Once that was sorted I'd try passing a recordset.
OK on multiple classes.

theDBguy · Oct 21, 2019

kirkm said:
My goal is a Datasheet View Form, that can have height, width and Position specified, plus the width of the fields, or columns adjustable. (There's 4 of them).
And pass it a Recordset for it's data source. (This won't work with OpenArgs Property).
I kind of have this already, using a Global Recordset, but wanted to pass it to the Form as you would an argument for a function. Then took many hours trying to get the Form "right" which is still a sort of fudge. Be interesting to see how someone else would do this and maybe why I had such a hard time with it.

Okay, challenge accepted. Not really sure what the final goal is, but I gave it a try. The attached is just one possible approach. I'm sure somebody else could come up with a better solution. I hope this is close enough to what you want.

kirkm · Oct 21, 2019

Hey, that's beaut, thanks. Good to see someone elses concept.. it's given me some ideas to try with building and passing the recordset. I also use a function but don't call it from the form. Instead I use a Global which the Form uses, so will change that. You're only showing the Height and width values. My Form is Popup & model (over another Form).. but I had no end of trouble setting the Height/Width/Position until using Docmd.MoveSize. But it's still not 100% as it won't resize the columns and keep the changes.. and it acquires a horizontal scroll bar (even though it supposedly Off) leaving a huge empty space to the right as the scrollbar is moved.
No big deal but would be worth knowing why they're there and how come some actions and Property settings are ignored.

Again, thanks for your help and time.. Cheers

kirkm · Oct 22, 2019

Hi DBGuy,
At the risk of going on and on about this.. I was able to implement the sample you gave in FormDemo when you call a Function to set the Recordset after the Form loads.
However I need to know the RecordCount before opening the Form. Only if it's 2 or more is the Form needed. This meant I was setting the recordset once to get a count, then again as the Form loads. That didn't seem right. Incidentally why is Me.OpenArgs always (or mostly) Null the first time you run the code?

I changed to your example in Database3, using Set Frm, .visible method and passing the recordset as property.
This worked fine, after I realised that way neither Form_Load or Form_Activate were called. Form_Current seems to work though.

So I think it's okay like this and was great getting your input.

theDBguy · Oct 22, 2019

kirkm said:
Hi DBGuy,
At the risk of going on and on about this.. I was able to implement the sample you gave in FormDemo when you call a Function to set the Recordset after the Form loads.
However I need to know the RecordCount before opening the Form. Only if it's 2 or more is the Form needed. This meant I was setting the recordset once to get a count, then again as the Form loads. That didn't seem right.

You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.

Incidentally why is Me.OpenArgs always (or mostly) Null the first time you run the code?

Hmm, it's never null when I ran it. How are you checking this?

I changed to your example in Database3, using Set Frm, .visible method and passing the recordset as property.
This worked fine, after I realised that way neither Form_Load or Form_Activate were called. Form_Current seems to work though.

So I think it's okay like this and was great getting your input.

Not sure I follow this one. Where did you get Database3?

kirkm · Oct 22, 2019

> You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.
That's what I did.
>Hmm, it's never null when I ran it. How are you checking this?
After typing in the code then running it. You'd checked for null first, I thought this was why.
>Not sure I follow this one. Where did you get Database3?

From you. See Msg #9

theDBguy · Oct 22, 2019

kirkm said:
> You could try assigning the recordset to a variable, so you can manipulate it first before assigning it to the form.
That's what I did.

So, does that mean it's working now? If not, can you show us how you used a variable? I can't imagine why using a variable won't work.

>Hmm, it's never null when I ran it. How are you checking this?
After typing in the code then running it. You'd checked for null first, I thought this was why.

No. I check for Null in case the user opens the form by double-clicking on it from the Navigation Pane. This is actually a common practice.

>Not sure I follow this one. Where did you get Database3?

From you. See Msg #9

I see. But, didn't you say you modified it? If so, can you post the modified version, so I can see what you mean? By the way, if you're using the Current event, just be aware it will fire again each time the user navigates to a different record on the form.

Form OK in Excel, but not in Access. (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

I’m here to help

I’m here to help

Attachments

Registered User.

I’m here to help

Registered User.

You've got your good things, and you've got mine.

Registered User.

I’m here to help

Attachments

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

Similar threads

Users who are viewing this thread