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
Hi, I will really appreciate ur help. I work on Access-2000 database and have a problem. I need to get the number of the week. So i used the funktion as a standard value as following: "=((Monat(Datum())*30,5-Tag(31-Datum()))/7)" It shows me for today 39 instead of 44. Could u please help me and tell me why this funktion doesnt work? Thanks in advance
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?
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.
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
I am writing a module to send information to Excel, and the information is based on a Crosstab query. But I keep getting a "syntax error in the TRANSFORM statement".
Function Edt_Royalties() As Integer Dim AplicExcel As Excel.Application Dim Planilha As Excel.WorkSheet Dim stArq As String Dim DB As Database
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.
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.
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?
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.
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.
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
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]));
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.
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.
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.
Basically i want the app to open small form that asks you to log in basic combo box to select user and text box to enter password well that works fine and i got it to open my main menu, but depending on whether you are a user or admin depends which menu opens.
So i used an if statement to do that part ie
If user = admin then DoCmd.OpenForm.......
ElseIf User = user then DoCmd.OpenForm..........
But because I use an If statement for their password
If TextBox = Password Then Open form main menu
Else RunMacro (displays an error message)
So I use the password one first but then i need the other to run in the place of opening the form, so in ecense
If TextBox = Password Then Open form main menu ----- but instead of this line i want If user = admin then DoCmd.OpenForm.......
ElseIf User = user then DoCmd.OpenForm..........
Else RunMacro (displays an error message)
but it will not work, if i could get a line to run the menu select i could put it in the password one
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.
just wondering why randomly certain modules in the form code stop working..
when you accidentally press return.. or do something in the wrong order.. or apply one rule to one control.. and then another control rule stops working..
so you have to delete the code.. right click on the control's event.. and re-insert the code into the event section.. and then it works..
I get a syntax error when I run this query from an Access Module but I don't get an error when I run it as a straight query. Can anyone see what's wrong with it? I am using Access 2003
Dim sqlStmt As String
sqlStmt = "SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight " sqlStmt = sqlStmt + "FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight " sqlStmt = sqlStmt + "FROM CCCWords " sqlStmt = sqlStmt + "WHERE Word IN ( Select word from CBCWords where vendor = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO,COUNT(*) * 25 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPhone " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "UNION " sqlStmt = sqlStmt + "SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight " sqlStmt = sqlStmt + "FROM CCCCleansedPostalCode " sqlStmt = sqlStmt + "WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '" sqlStmt = sqlStmt + strVendor sqlStmt = sqlStmt + "') GROUP BY ESTBLMT_NO " sqlStmt = sqlStmt + "]. AS dupWeight " sqlStmt = sqlStmt + "WHERE dupWeight.ESTBLMT_NO = [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "GROUP BY [CCC Companies].ESTBLMT_NO " sqlStmt = sqlStmt + "HAVING SUM(subWeight) >= 60 " sqlStmt = sqlStmt + "ORDER BY SUM(subWeight) DESC"
DoCmd.OpenForm "Show Probabilities", , , sqlStmt
Here is the code that runs ok in a straight query:
SELECT [CCC Companies].ESTBLMT_NO, SUM(subWeight) AS weight FROM [CCC Companies], [SELECT ESTBLMT_NO, COUNT(*) * 10 AS subWeight FROM CCCWords WHERE Word IN ( Select word from CBCWords where vendor = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 25 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 5) IN ( Select MID(STRIPPED_PHONE,1 ,5) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPhone WHERE MID(STRIPPED_PHONE,1, 7) IN ( Select MID(STRIPPED_PHONE,1 ,7) FROM CBCCleansedPhone WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO UNION SELECT ESTBLMT_NO, COUNT(*) * 50 AS subWeight FROM CCCCleansedPostalCode WHERE MID(STRIPPED_POSTAL,1, 6) IN ( Select MID(STRIPPED_POSTAL,1 ,6) FROM CBCCleansedPostalCode WHERE vendor_no = '100024') GROUP BY ESTBLMT_NO ]. AS dupWeight WHERE dupWeight.ESTBLMT_NO=[CCC Companies].ESTBLMT_NO GROUP BY [CCC Companies].ESTBLMT_NO HAVING SUM(subWeight)>=60 ORDER BY SUM(subWeight) DESC;
I have a module that authenticates users and controls who has full or read-only access to certain forms. My problem is this; things such as combo boxes and checkboxes that are meant to be used for any user are disabled in read-only mode.
I was thinking that I could use the tag property on my controls to establish which controls should always be enabled and then using my module to dynamically enable/disable the appropriate controls each time I load a form. Can anyone help me do this?
Basically all I really need to know is how to address the form in a module (since I can't use "Me.Controlname" and I don't know which form is being worked on until runtime. And also I don't want to lock records, since this won't allow other users to view the tables being used.
Also I don't want to use the Enable property because I don't want the data to be "greyed out". I could use the Locked property, but that poses the problem of multiple users trying to access the same table. So is there a way I can set certain fields to read-only without making the whole form read-only or locking the data?
By the way, I am working in Access 97
Here's a pseudo-code example of what I want
for each Cntrl in PassedForm.Controls if Cntrl.Tag <> "AlwaysEnable" then Disable Cntrl end if next
