I need to use Form_formname in the VBA now when just formname was ok before

[chin chin]

Hi,

I'm a beginner using 2007 and I've just got to the bottom of this weird problem. However, it will be a big inconvenience if I can't get the database working like the old one was so would appreciate suggestions.

On the old version of this database I was able to make references in the VBA like this:

Forms![formname].[controlname]

but in the new one it gives me a compile error "qualifier must be collection" if I do this and highlights ![formname]. After a lot of head scratching I've found that I need to do this instead:

Forms.[form_formname].[controlname]

I have no clue what has caused the difference, and there is extensive coding in the old database that was working just fine.

Any advice gratefully received.

Chin Chin.

 

[boblarson]

Hi,

I'm a beginner using 2007 and I've just got to the bottom of this weird problem. However, it will be a big inconvenience if I can't get the database working like the old one was so would appreciate suggestions.

On the old version of this database I was able to make references in the VBA like this:

Forms![formname].[controlname]

but in the new one it gives me a compile error "qualifier must be collection" if I do this and highlights ![formname]. After a lot of head scratching I've found that I need to do this instead:

Forms.[form_formname].[controlname]

I have no clue what has caused the difference, and there is extensive coding in the old database that was working just fine.

Any advice gratefully received.

Chin Chin.

You do NOT use form_formname. That will cause irrational things to occur. The syntax

Forms!FormName.ControlName is STILL the correct way. But if you want to use VARIABLES for those, then it is like this:

Forms(YourFormVariable).Controls(YourControlVariable).Value = X

So, let's say you have a form named frmMain and a control named cboTest, to write it explicitly you would use:

Forms!frmMain.cboTest

to use variables:

Function MyTest(strFormName As String, strControlName As String) As Integer
   Dim i As Integer
   
   i = Forms(strFormName).Controls(strControlName).Value
 
   MyTest = i
End Function

 

[chin chin]

Thanks for the info. So why do you think I am getting the error? I have checked the spellings a hundred times over and they are fine so I am totally stumped.

 

[boblarson]

Thanks for the info. So why do you think I am getting the error? I have checked the spellings a hundred times over and they are fine so I am totally stumped.

Is this a 2003 database that is now being used in 2007 without converting?

Can you post exact code which you are using which doesn't work?

Have you checked for reference problems?

Have you decompiled the database (I find that if something should logically work - like it was working and now doesn't and changes haven't been made) that a decompile can help. But make a backup copy before attempting it.

 

[chin chin]

Is this a 2003 database that is now being used in 2007 without converting?

No, made from scratch in 2007.

Can you post exact code which you are using which doesn't work?

Here's an example (it's happening on more than one form):

Forms!Learners.List18 = Null

Have you checked for reference problems?

Just done it now - thanks for the idea. The references are the same default four as the previous version that works fine.

Have you decompiled the database (I find that if something should logically work - like it was working and now doesn't and changes haven't been made) that a decompile can help. But make a backup copy before attempting it.

Not sure how to do that, but I've only just started coding and the problems have occurred straight away so not sure if something like this could work?

Chin chin.

 

[boblarson]

I use what I wrote in 2007 and 2010 databases as well as in 2003. So, the next question is - have you used any Access Reserved Words as field or object names which could be messing Access up? For example if you have any field in a table or query named Forms that could mess things up.

Can you post a copy of the database that gets this error so we can try to replicate the error and perhaps track it down quicker?

 

[chin chin]

I've not used any reserved words as far as I know, but thanks for the suggestion.

Here it is. It is only intended to be a demonstration of the proposed UI so there is no data. The first form to open is 'Learners', then if you click on one of the enrolments it will load the 'QuickViewAim' form. When that is closed I am trying to Null the listbox which is the problem I'm having. The code is commented out at the moment.

Thanks a lot,

Chin chin.

 

[boblarson]

Okay, I found your problem. It isn't something which was easy to track down but you named your VBA project FORMS which screws everything up. Go into the VBA Window and right click on the part in the project Explorer which says Forms(Forms) and select Forms Properties and change the name of the project to something OTHER than an Access Reserved Word.

 

[chin chin]

Oops - I should have definitely noticed that after your last post. Thanks a lot Bob, you're a lifesaver.

Chin chin.

 

[boblarson]

Oops - I should have definitely noticed that after your last post. Thanks a lot Bob, you're a lifesaver.

Chin chin.

It was definitely an interesting problem and now I know where to immediately look if someone has that problem again.