Module Error (1 Viewer)

[Rich]

OK, try changing the names inCom to MyCom and PriceEV say to MyPrice, you'll have to change the references in the module as well of course

 

[Fastone]

Also no change. This is really depressing me a lot.

Thanks though

 

[Rich]

What message do you get when you compile and save all modules

 

[Fastone]

I don't get any message when I compile the modules. I only get the undefined message when I want to use the query.

 

[Rich]

Is that when using CalCom() which is of course the correct function name?

 

[Fastone]

This is when using CalCom() and any other suggestions that I have gotten so far.

 

[Rich]

Which suggests that Access can't find the function, try using the code builder on an unbound textbox to see if the function is listed in the Functions lists

 

[Fastone]

I have already tried this because I assumed I was making a typo or something.

 

[Rich]

Have you tried importing everything into a new blank db?

 

[Rich]

On second thoughts, I suspect you may have a missing reference problem, try here for a solution
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Fastone]

Thanks for that piece, but it didn't help me solve the problem (it was very useful in understanding the reasoning behind libraries).
I haven't imported the DB into another blank DB. Do you mean just copy each tabel or do you mean export DB and import DB?

Thanks

 

[Rich]

Create a blank new db, from the file menu, get external data, select all, for every tab, remember to compile and save, compact etc, clutching at straws really, but it's worth a try

 

[Fastone]

Unfortunately, no change . This is really starting to make me mad.

I really appreciate the help.

Maybe somebody can write a different piece of CalCom code to get to the same result (I don't know it so unfortunately I can't try that).
Could this be a computer related problem rather than an access related problem?

 

[Rich]

Can you post a copy here, I only have 97 though, so you'll have to save as that ver.

 

[Fastone]

I did this before with the same DB and I didn't get any error (before I created Module). If I want to do this now, I get an message that I don't have all the libraries for Access 97. Maybe a problem can be found here. I have attached it anyways so if you get any error or problem please let me know.
I'm also assuming you are using the English version of Access, this is exported from the Dutch version so it would appreciate it if any 'normal' formula's don't work.

 

[Rich]

a, You forgot to change the all the references in the new module when you changed MyPrice etc,
b, You gave the module the same Name as the function,
c, you also had a copy of the function but with a different name.
having sorted that you now have a problem with the joins in the Commission query, I've created a copy with most of the tables removed which enables the function to work as is. I've set the function to output as currency which I assume will default to Rand on your pc anyway, removing the need to Format the query field.
Hopefully you can sort out the join problem and move on to the next part of the jigsaw, my advice is to use a combination of select case statements or If Then Else in the module, instead of multiple Iif's within the query.
HTH

 

[Fastone]

a: thanks I'll try
b: this was the original module, this module was cut out of the DB to try and see if it was working
c: this copy was used to test if it would make any difference to use the same name or not.

Overall: big thanks, I'll try and build up the DB from this.

 

[Fastone]

At least we got some change into the messages. When I try to run the query now it says:

'types in expressies don't match'.

I have gone through that document again and made sure all libraries discussed are selected but this doesn't make a difference.

Thanks

 

[simongallop]

That message usualy means: In a query that you are trying to link a string field with a numeric field. In VBA that you are trying to carry out numeric calculations on a string or something similar. Check the declared types of the fields in the underlying tables and make sure that they match. Classic example 1 table has and ID field as numeric the other table has ID field as text.

HTH

 

[Fastone]

The Module is defined As Currency
The query columns which are used in the calculation are defined the same way but maybe Rich nows because he says

output as currency which I assume will default to Rand on your pc anyway, removing the need to Format the query field.

I'll look and play around, maybe I'll find the solution but if anybody in the means time has any tips or suggestions, please let me know.

Thanks