Code only works in Module and not in Form, why?

[kleaverjr]

I am pounding my head on the desk. So when I use the following code IN the Form that the controls are in I get a "Type Mismatch" Error. But if I move it to a Module, it works fine. I much rather load the combobox array (there are 15 comboboxes being loaded). Although they all are loaded with the same "list" the "list can change (so I can't just input the list in the properties list) depending on what the user selects. But all of that doesn't matter as the error is where this line of code is being located.

This code does NOT work (I have simplified it because i use very long variables, and there are other controls on the Form that impact the code, but again the code works, but not in the Form, only in a separate module as I will explain, but I would like the code in the form if i can. And I have tried using Me.Controls, same error.



I am using the term RECORDSET to represent the actual code that extracts the data from the Recordset from the DB. That part works fine too.

For comboboxindex = 1 To 15

 BrandIndex = 0

  Do

      if RECORDSET.EOF then exit do

      AddBrand(BrandIndex) = RECORDSET

     comboBrandName = "ComboBoxBrand" & comboboxindex

     Form_AddInvoice.controls(comboBrandName).additem (AddBrand(BrandIndex)), BrandIndex
  
    BrandIndex = BrandIndex + 1

    RECORDSET.MoveNext

  Loop

  RECOREDSET.MoveFirst
 
Next comboboxindex

Now when I make THIS change it works:

For comboboxindex

 BrandIndex = 0

  Do

     if RECORDSET.EOF then exit do

       AddBrand(BrandIndex) = RECORDSET

      comboBrandName = "ComboBoxBrand" & comboboxindex

     CALL UPDATEINVOICE

    BrandIndex = BrandIndex + 1

    RECORDSET.MoveNext

  Loop

  RECOREDSET.MoveFirst
 
Next comboboxindex

***********************************************
And this is a MODULE and NOT in the form..Also note All of the variables in this module are Global for the entire Project.




UPDATE INVOICE:

   Form_AddInvoice.controls(comboBrandName).additem (AddBrand(BrandIndex)), BrandIndex

So what am I doing wrong. How can I rework the code so it's all in one place? Thanks again!

Ken L

[June7]

Have you step debugged? Do variables get correct values?

I can't see anything wrong. Although, I use Forms! instead of Form_. Me. should work.

Suggest you provide db for analysis.

[kleaverjr]

Have you step debugged? Do variables get correct values?

I can't see anything wrong. Although, I use Forms! instead of Form_. Me. should work.

Suggest you provide db for analysis.

Yes, I have done all that. I will try using Forms! instead and see if that works. I don't understand why the code works only in the module and I get a Type Mismatch error when it's in the Form itself as I copy and paste the Form_ line of code from the Form to the Module, and it works fine. It's bugs like this that really annoy me because as you said it should work but it doesn't.

[davegri]

Perhaps a form can't refer to itself in that manner. Have you tried

Me.controls(comboBrandName).additem (AddBrand(BrandIndex)), BrandIndex

[June7]

Form_
Forms!
Me.

All work for me in form module.

Now I wonder why I got the habit of using Forms!, because it does not trigger intellisense tips and Form_ does.

[kleaverjr]

Perhaps a form can't refer to itself in that manner. Have you tried

Me.controls(comboBrandName).additem (AddBrand(BrandIndex)), BrandIndex

That seems to have done the trick. I would like to keep this thread "open" for a bit as I'm adding some other code which might create more bugs where the combobox array is concerned.

Thanks again!

Ken L.

[June7]

Does not hurt to start a new thread. New thread might get more attention.

You said you already tried Me.Controls - what was different?

[kleaverjr]

Does not hurt to start a new thread. New thread might get more attention.

You said you already tried Me.Controls - what was different?

I really don't know why me.controls worked while Forms_ didn't. So far adding to this part of the code hasn't produced any issues, so i guess I'll mark it resolved and if something new comes up, i'll create a new thread/ Just thought Admins might prefer not so many new threads being created for similar issues.

Thanks for the help.

Ken L

[June7]

You said you tried Me.Controls and it errored. What did you do different so it worked?

[Minty]

Now I wonder why I got the habit of using Forms!, because it does not trigger intellisense tips and Form_ does.

I think you need to be careful using the Form_ syntax, as I have a sneaky suspicion it can instantiate another hidden instance of the form in some circumstances.
There is quite a long-winded discussion of it here
https://www.access-programmers.co.uk.../#post-1747913

And a discussion of some of the issues
https://www.accessforums.net/showthread.php?t=84803