Modules & VBA :: DLookup Error - Wrong Number Of Arguments
Jul 1, 2013
Access 2010
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo
Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents
Field: RankOrder
Here is what I have:
Code:
Private Sub RankOrder_BeforeUpdate(Cancel As Integer)
Dim lngRankDup As Long
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0))
If lngRankDup <> 0 Then
MsgBox TestEventID & " already exists in the database"
End If
End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
Can you help me? I get the error wrong type of arguments in my function as follows Private Sub Command0_Click() Call ANewDBWithPass("c:BELest", "secret") End Sub
Function ANewDBWithPass(ByVal tName As String) '// adds the current date to the name of the database Dim wsp As Workspace Dim db2 As Database Set wsp = DBEngine.Workspaces(0) If Right(tName, 4) = ".mdb" Then ' Remove .mdb from the name tName = Left(tName, Len(tName) - 4) End If ' Add date and extension tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb" ' Create database ',Set db2 = wsp.CreateDatabase(tName, dbLangGeneral) Set db2 = wsp.CreateDatabase(tName, dbLangGeneral & ";pwd=" & strPassword) db2.Close End Function
I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number
datetime = 03/05/2013 11:26 database number = 41397.4763888889 Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)
now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.
Code: Private Sub Command288_Click() Dim s As String Dim t As Integer Dim ws As Worksheet s = LaunchCD(Me) MsgBox (s)
[code]....
also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.
Code: Private Sub LoadData_Click() Dim w As String w = Forms![frmList1]![Combo0] w = "[" & w & "]" Dim dtA As String
Part of my access application does invoicing. I have an invoice number table that wholes one field "InvoiceNum". On my invoice report i have the following code:
Code: Private Sub Report_Open(Cancel As Integer) '// lookup invoice number when invoice opens intInvoiceNum = Nz(DLookup("InvoiceNum", "tblInvoiceNum"), 0) End Sub
[Code] .....
Problem: My report open with groupby ClientID and ProjectNum. Multiple invoices print on the same report. If any invoice spills over to second page the Format Code runs again inserting an incremental invoice number to the second page. How can i prevent this from happening?
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
The basic idea is that I need to insert a record into a table and then return the Key field so that I can use it to populate another table. This was working just fine for a while, then it stopped and naturally I can't think of anything I did to make it not work. I get an Invalid Use of Null error at the bolded step.
Dim SQL As String Dim SpecID As Long Dim VerifySpec As Variant ' Check for empty strings If Len(Me.txtNewPartNumber & vbNullString) = 0 Then Response = MsgBox("Part Number cannot be blank.", vbExclamation, "Missing Data") Exit Sub End If
I am building a personnel system to keep track of staff, their contracts and any equipment they have. on the form 'frmStaff' I have the subform 'tblMobilesStaffSubform'. On this subform is a numeric field called 'ManufacturerID'. Now, I have a text box on 'tblMobilesStaffSubform' to reference the table 'pklManufacturers' so that it displays the name of the manufacturer ('Manufacturer') where the 'ManufacturerID' on the form matched the 'ManufactureID' in the table 'pklManufacturer'. Simple right? wrong.
I have dozens of very similar DLookups in this database and none of them have a problem, just this one:
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'") End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]") End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")) End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
I have a python script "runAll" that takes two arguments: processID (the primary key of one table as a string) and a filename (a full file name path as a string).
Is it possible to run a python method with arguments from inside a VBA script (activated by a button press)?
My python script is called XMLGenerator.py, and the method I want to run is called runAll(processID,filename).
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate() Dim strOrg As String strOrg = Me.tOrgName.value If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code: Public Function Soundex(strOrg As String) As String Dim Result As String, c As String * 1 Dim Location As Integer
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then MsgBox "Go Away", vbOKOnly Else Go on and do the real work End If End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
I have 2 similar numbers in a table with the following parameters:
Double, Fixed, DecPlaces = 2 One number (GLminor) = 0.10 Second Number (GLmaj) = 0.50
When i use the following code to create and pull the numbers from a recordset the (Gmin) is OK and displays as 0.1 but the (Gmaj) has a problem and displays as 0. So when i'm attempting to use it in an equation (OtherNumber) / (Gmaj) i'm getting a divide by zero error.
Code: sSQL1 = "SELECT * FROM tblRScales WHERE ID = " & RScaleID Set rs1 = CurrentDb.OpenRecordset(sSQL1, dbOpenDynaset) rs1.MoveFirst Gmaj = rs1!GLmaj Gmin = rs1!GLminor
I've tried using CDec(OtherNumber) / CDec(Gmaj) and still get the same error.
As both numbers have the same properties in the table parameters i'm completely lost as to why one seems OK while the other seems to round down to 0.
I'm having an issue with the database being read only and a type mismatch due to OpenRecordset. I know this database use to work when it when it was just
CurrentSerNum = RecSet(0) +1 was doing the incrementing.
I need the database to increment in a different way now.
Q=manufacture location 1st number is new=0 or used=1 2nd and third number =82 4th is single digit year aka 2015 = 5 6 and 7th are work week aka 25 for this week 8,9,10 are incremental numbers for the units made that workweek, so 001,002,003. Then week 26 starts the increment starts back at 001.
Code: Private Sub MakeThoseProducts_Click() On Error GoTo Err_MakeThoseProducts_Click Dim NumericSerial As Double Dim WorkWeek As String Dim WorkYear As String Dim WorkWeekNumber As Integer Dim WorkYearNumber As Integer Dim Location As String
I wrote a query to return the TOP 32 items for a key. Works fine:-
SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=1 GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;
I then plugged this into a query in the WHERE clause using In (Select Top .....;); [changing the =1 condition to pick up the correct id from the emcompassing query]:-
SELECT [my Disks Coefficients].[my Disk ID], [my Disks Coefficients].[thier Disk ID], [my Disks Coefficients].coefficient FROM [my Disks Coefficients] WHERE [my Disks Coefficients].[thier Disk ID] In (SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=[my Disks Coefficients].[my Disk ID] GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;);
This runs fine but the the number of items returned is for each key is less than if I run the Top query by itself for each key seperately? So when I run it stand-alone for ID 1 I get 127 rows (there are many equal coefficients); when I run it as a sub-query I get only 121 rows for ID 1!
I have been working on this problem for about the last hour, and have gotten absolutely no-where - I cannot figure out what is wrong with my DLOOKUP.
I am using it in a query, as part of an IIf statement, but to simplify things whilst testing, I have just bunged the dlookup into a text box on a form, but itself.
The code I am using is:
=DLookUp("[SHORTNAME]","CPL_NOMINEE","[FULLNAME]=" Like "*" & "[AccID]")
What I am trying to do is return the SHORTNAME, from a table called "CPL_NOMINEE", which is a linked table (to Oracle), where the field FULLNAME has the value of ACCID at the end.
An example of each field would be:
SHORTNAME: CNL FULLNAME: H Noms Ltd 500222 ACCID: 500222
With the code above, I just get a blank box - as if it can't find anything in the dlookup, but I can SEE the data is there in the table - what's up?!?
Am currently having trouble with a DLookup function as follows
Table ======
Products ProductID Description Nett
I have a form (Purchase Order Detail Subform) where by I want to enter the Product ID manually and then for the Description and Nett fields to populate automatically I've started on the description field: =DLookUp("[Description]","Products","[ProductID] =" & [Forms]![Purchase Order Detail Subform]![ProductID]) but this isn't right, being a bit of a newbie to access its probably because I've done something really silly... any idea? Thanks
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?
Code: Dim totalPackages As Integer Dim tempPrice As Double
I made a form for a table which contains client questionnaire responses, "Dec4Docs". I wanted to include the names of these clients in the form, just to make everything easier for whoever is doing data entry, but client names are in a different table, "BasicInformation". Both the questionnaire table and the client information table use a common ID number, "WISnum". I found a number of online tutorials which instructed me to use the DLookUp function for what I'm doing.This site here, for example, suggests the following formula:
^This was meant to find FirstName in the BasicInformation table where WISnum is equal to the WisnumField of the Dec4DocsForm form. Instead, all I got was a flashing error message that partially broke the program's GUI. I could not find a solution on Google, so I played around for awhile, bug checking by adding and removing things to the formula, until I finally got a little lucky and figured this out:
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
I wonder if you can help me. In my query, for this calculated field, i get an error message about a syntax error missing operator in query expression. what did i do wrong? thanks
