Access :: Basic Problem With Module And The If Function

Calling Function In External Module

Jan 4, 2006

Is there a way to call a function stored in a module in the back-end access file from within a form in the front-end access file?

I want to be able to slip in a change to the code, and not have to then copy the changed module out to every front end file.

I am thinking that this is something that is in fact trivial to do, but for the life of me, I am drawing a blank on how to do it. Or even what search terms to use to search the help files.

Any help is appreciated.

Thanks,
David

View 3 Replies View Related

Modules & VBA :: Update Form From A Module Function

Jun 16, 2013

I have this code below that is working however the calculation are updating on my form late.

Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.

The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.

Control

Code:
Call GeraAuditCalc
DoCmd.RefreshRecord

Function

Code:
Function GeraAuditCalc()
Dim fHrs As Double
Dim Ttl As Double
Dim Ttl1 As Double
Dim Ttl2 As Double

[Code] ....

View 4 Replies View Related

Modules & VBA :: Undefined Function For A Module Called In A Query?

Sep 8, 2013

This module is giving me the "undefined function" error message when I try to run my query. I don't know why, but I have checked that there are no references with "missing" and there are not. I also added the word "Public" to the function becasue that was advised by another forum user. I thought it worked perfectly the first time I ran this query, but now it is not working and I do not recall making any changes. I have called the module basFunctions:

Option Compare Database
'************************************************* *********
'Declarations section of the module
'************************************************* *********
Option Explicit
'================================================= =========
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'================================================= =========
Public Function DateAddW(ByVal TheDate, ByVal Interval)
Dim Weeks As Long, OddDays As Long, Temp As String

[code].....

View 3 Replies View Related

Modules & VBA :: Custom Function Module - Find Records With Specific Keywords

Feb 28, 2014

I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.

I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.

These would all need to be a "like" with an " * " on each end of the word.

With SQL it would look something like:

Code:
select a.address1
from main_tbl as a
where a.address1 like '* north *'
or a.address1 like '* park *'
or a.address1 like '* south *';

I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.

View 2 Replies View Related

Forms :: Manage MsgBox With Blank Required Fields By Function In A Module

May 11, 2013

I created this function to manage a MsgBox containing all required fields with no data:

Code:
Function FormValidation(TheForm As Form) As Boolean
Dim boolResponse As Boolean
Dim strError As Variant
Dim ctl As Control
boolResponse = True
strError = Null

[code]...

Then, I have a Form_BeforeUpdate event, where I wanna place the function, which contains also some VBA code to manage duplicates records:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset, dbs As DAO.Database, strICAO As String
Dim ctl As Control, txtMsg As String

[code]....

Now, how to add the function in this event to get these two results:

1. if required fields are blank and I press OK on the MsgBox, the routine must stop;
2. the focus must go in the first required blank control.

I tried some options but I get different kind of malfunctions: no custom message for duplicate records but only the access default one, "go to next record" feature with tab key not working and so on.

View 6 Replies View Related

Queries :: Undefined Function In Query And Visual Basic

Apr 3, 2014

I'm doing some debugging over the phone.My friend has Access 2003.What does not work:When he codes the MID function in a query he gets an error like this:"Undefined function 'Mid' in expression."

In the Visual Basic editor, he gets the same error: MID is undefined.What DOES work:I walked him through the use of LEFT and RIGHT in the query and they work.In Excel, MID works.Might there be some odd ball Access configuration mess up, or maybe more likely, his Access installation is incomplete.

View 3 Replies View Related

Problem With Calling A Visual Basic Function From A Form Field

May 30, 2005

Dear All.

I have a visual basic function, placed in a module.
I need to call it from a form field, and pass a value to it and the function has to return a value.

I tried it but it doesn't work
the value I get on the field is " #Name? "

any help will be very appreciated.

Thanks!
CS.

View 8 Replies View Related

Whether To Code In A Form Module Or A Standard Module?

Dec 14, 2007

I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.

I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.

Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?

So, what do you tend to do in such situation?

View 14 Replies View Related

Error: Member Already Exists In An Object Module From Which This Object Module Derive

Oct 1, 2004

I am creating an form in a database and whenever one of my procedure's run it creates this error message:

The expression ON Load you entered as the event property setting produced the following error:
Member already exists in an object module from which this object module derives.

*The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure].
*There may have been an error evaluating the function, event, or macro.

An ideas?

View 7 Replies View Related

Security Module

Jul 17, 2006

Hi, I hope someone can help. I know there are masses of threads on security but I can't find one that is similar to my problem.

I have a SQL 2005 database that uses an access front end AND an intranet front end, dependent on the user.

I need to write a security module to run both so that whether a user is on the access version or the intranet version the security will remain the same. The security will be maintained from within forms in access.

Has anyone done something similar or seen any articles on something like this that will help me.

Many thanks

:confused:

View 1 Replies View Related

Can't Get To VB Module In Form

Oct 21, 2005

I am in Acess2000 and trying to go to put vb in. When I hit the code icon,
I get the message: "name of db" failed to create the VB module '|'.

Is anyone familiar with this message and what do I need to do. I just added this form, and in the other forms that have vb code already, I can get to them okay.

Thanks in advance for your help.

View 2 Replies View Related

Call Module?

Sep 8, 2005

I have a bunch of Forms set up in Access that are updating tables etc......

At present 100% of the code is sitting on the Form. I have some code that is repeated because of a string parsing issue. What I am thinking of doing is writing this code as a couple modules and then call them when needed.

Does that make sense to write them as modules and call them?

How do you call a module???

Thanks

View 4 Replies View Related

Concatenation Module

Aug 18, 2006

Please can anyone give me a piece of code to combine the text from memo fields in 25 different queries and put it in one text box for printing?
Thanks.

View 2 Replies View Related

How To Run A Module Using A Menu Bar

Aug 26, 2006

Hello,

I have created menu bars for my access application and now would like to transfer many pieces of code in modules so that they can run using macros. I beleive this is the only way I can make run my code from the menu bar. Correct?

I am having problems with the modules since I have never used them and would like some help.

Here is a sample of code I now have on a push button in a form:

Dim mydb As DAO.Database, MyRs As DAO.Recordset
Dim strCode As String
Dim strFilter As String
Dim stDocName As String
Dim stLinkCriteria As String

Set mydb = CurrentDb
Set MyRs = mydb.OpenRecordset("master")

stDocName = "MASTER"

stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

If Not IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "TEMPLATES", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendtemplates")
End If
End If

If Not IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "195", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append195")
End If
End If

If Not IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "795", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Append795")
End If
End If

If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
Forms("EFORMS").Visible = False
Else
If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then
DoCmd.OpenQuery ("Appendssa21tax")

End If
End If
Forms!eforms.lstPreInterview.Value = Null
DoCmd.Close
DoCmd.OpenForm ("Eforms")
DoCmd.RunMacro ("CloseEforms")
DoCmd.OpenForm ("Eforms")

End Sub

What I need is to add this code to a module, and make it run using a manu bar (I assume via macro). Thanks.

View 1 Replies View Related

Module Not Found Error

Aug 29, 2005

I was doing a compact and repair when I got this error message.

Now when I try to open my database all I get is this message:
"Module not found"
:eek:

Am I screwed or is there maybe possible silver bullet??

Thanks for any help

View 1 Replies View Related

Form's Module Disappeared

Sep 21, 2005

Hi,

My mainform, which controls virtually all operations, has dissappeared.

Since it�s supposed to load when starting the database, the form actually loads, but you can�t see it in the forms window.

when I try to edit the code I get the message "module not found".

What do I do?

Thanks

Fuga.

View 4 Replies View Related

Small Module Help (simple I Think)

Nov 25, 2005

hello all
i need a small thing to change in a module but am not too familiar with vb so i reached a dead end, here's the story:

i have a 'dog pedigree' database that i was working on, now there's a report where a dog's parents, their parents, and their parents need to be shown

that turned out to be out of my league so i paid a guy to do it for me, it worked except he didn't do exactly what's need and i can't get to him now :S

anyway, in the report page,
you can notice that the name of each parent is shown, but i want the registration number of the parents to show as well,
i tried modifying the module but it didn't work (am not a vb user), so can anyone help me add the reg number to the name of the parents?

also, in the report, the dog's color is appearing as a number instead of an actual color so if that can be fixed as well ...

to understand better:
record 1 in dogs, click the preview diploma button
you'll notice "tat" as dogname and "ry245" below that as registration number
now sire is "pat" and dam is "titu", i want their registration numbers to show as well (and same for the rest of the parents)

i uploaded the file here for a closer look:
http://www.designcrafts.org/dogs1.zip

thanks :)

View 14 Replies View Related

How To Use Query(given By Name) In Module Funktion?

Oct 11, 2005

Hi
I how such code:

Public Function fConList()
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim strSQL As String
Dim strText As String

strSQL = "SELECT Contact.To FROM Contact" '
Set DB = CurrentDb

'Open a Recordset and loop through it to fill the text box txtTest

Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)

How to put some other query given by name to instead of putting sql directly into string?
I have also textfield that has this =fConList() as source.
Is it possible that i will have some combo box or list of queries and one button. When i press this button the name of the query in the function will change to the chosen One from the list.
Any Ideas?
i'm beginner so,
Thanks in Advance for some examples

View 2 Replies View Related

Compare Module Results

Jun 9, 2006

I have created a query that includes a module that determine what the actual order date should be. This works correctly. Now, I want to take that date in another query to only show rows where RPODate exceed the recommended date.

I keep getting an error stating the query is to complex. If I run it without the >Checkdate in the criteriea for RPODate it's fine.

Any ideas?

Query 1 with module
SELECT qryInstallationRPOrdered.ProjectID, qryInstallationRPOrdered.Phase, qryInstallationRPOrdered.Unit, qryInstallationRPOrdered.Tract, qryInstallationRPOrdered.Release, qryInstallationRPOrdered.DelDate, qryInstallationRPOrdered.InstallDate, qryInstallationRPOrdered.State, qryInstallationRPOrdered.RPODate, qryInstallationRPOrdered.NVDate AS CheckDate
FROM qryInstallationRPOrdered
WHERE (((qryInstallationRPOrdered.State)="NV"));

Query 2
SELECT qryInstallationRPONV.ProjectID, qryInstallationRPONV.Phase, qryInstallationRPONV.Unit, qryInstallationRPONV.Tract, qryInstallationRPONV.Release, qryInstallationRPONV.DelDate, qryInstallationRPONV.InstallDate, qryInstallationRPONV.State, qryInstallationRPONV.RPODate, qryInstallationRPONV.CheckDate
FROM qryInstallationRPONV
WHERE (((qryInstallationRPONV.CheckDate)>[RPODate]));

View 1 Replies View Related

Report Module Won't Sort

Nov 28, 2004

I've been using Access for a while now, to track some names, addresses, locations, etc. Recently, when I try to generate a report, I step through the field selection, etc, and when I get to the "sort" page, if I just keep going, it proceeds fine. If I select a field to sort on, however, I get the following message:

"The expression Before Update you entered as the event property setting produced the following error: The function you entered can't be used in this expression.

*The expression may not result in the mane of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluation the function, event, or macro."

I don't think I've changed anything to do with the program, or any of its Office brethren. Can anybody give me some advice? The report is generally useless without the ability to sort/group.

Thanks, all.

View 3 Replies View Related

Macro To Run Module - Urgent

Aug 2, 2007

How to run a module in the macro?
Which step can run a module.

There is only option to openmodule action.

Please clarify

View 1 Replies View Related

Is Form Name Available In Called Module

Aug 27, 2015

Is the calling Form name or the event handler's sub name available in called module? I need it in order to determine the form's name as in [Forms]!someformname!some control.

View 7 Replies View Related

Reference A Control In A Subform From A Module

Aug 4, 2005

Hi,

I'm trying to reference a control in a subform from a module but I keep getting errors!

Here's what I've tried.. and what error I've been getting:

First try:
GetCboEntries = Forms.frmMain!frmSub.Form![cboEntries].Column(1)
(where frmSub is the name of the actual form in the subform control)
Error: Object doesn't support this property or method

Second try:
GetCboActivities = Forms.frmMain!chdMain.Form![cboEntries].Column(1)
(where chdMain is the name of the subform control)
Error: Object doesn't support this property or method

Third try:
GetCboEntries=Forms!NameOfMainForm!NameOfSubform![cboEntries].Column(1)
Error: Run-time error '2450':
Microsoft Access can't find the form 'frmMain' referred to in a macro expression or Visual Basic code.

Any help would be much appreciated!

Thanks in advance!

View 1 Replies View Related

Editable Default Value From Lookup Module

May 22, 2006

I'm still learning here, so please don't get annoyed if I don't know what I'm talking about.

I have a Module that I created that looks up a value in a table.. It is shown below.

Public Function DescLookup()

DescLookup = DLookup("[Projdescription]", "ProjDescTable", "[OrderN] = [Forms]![Production]![ProjIncList]")
End Function

I want this to be the default value for a field, however I want the field to be editable and I will later have a button that calls on an update query that will update the field with whatever changes are made to the Text Box, however when I set DescLookup() as the default value of the text box, it will not let me edit the text box in the form.

Am I describing this correctly? Can anyone help?