Modules & VBA :: Dlookup Resulting In Null Value
Aug 15, 2015
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code:
Private Sub txtProductName_Click()
Dim iProdType As Integer
Dim ProductID As Integer
iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value])
Note ProductID in an integer
How can I diagnose this or what could the solution be?
View Replies
ADVERTISEMENT
Aug 11, 2014
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
[code]....
View 6 Replies
View Related
Dec 4, 2013
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " INSERT INTO [tblTicket]"
[Code] ....
View 4 Replies
View Related
Nov 26, 2007
Hi,
I wrote the code below in a module. The text box that this part of the code is getting its value from has a DLookup in its control source. The problem is that when the DLookup does not match the criteria it looks for, it returns Null. The code tries to assign Null to a variable of type currency and that returns an error. Is there anyway to convert the Null to 0?
What do you suggest?
Returns DLookup value:
Forms![frmHouse]![qryHouse4].Form![txtTotalVO].Text
All the function:
Public Function CalculateDepositPlusVO()
Dim vAgreedPrice As Currency
Dim vtxtTotalVO As Currency
Dim vtxtAgreedPricePlusVO As Currency
Forms![frmHouse]![qryHouse4].Form![AgreedPrice].SetFocus
vAgreedPrice = Forms![frmHouse]![qryHouse4].Form![AgreedPrice].Text
Forms![frmHouse]![qryHouse4].Form![txtTotalVO].SetFocus
vtxtTotalVO = Forms![frmHouse]![qryHouse4].Form![txtTotalVO].Text
vtxtAgreedPricePlusVO = vAgreedPrice + vtxtTotalVO
Forms![frmHouse]![qryHouse4].Form![txtAgreedPricePlusVO].SetFocus
Forms![frmHouse]![qryHouse4].Form![txtAgreedPricePlusVO].Text = vtxtAgreedPricePlusVO
End Function
Any help will be very much appreciated,
B
View 4 Replies
View Related
Oct 26, 2005
I have a form that updates a table called BC - Issue Data Table. I am pulling in a carrier number from another table "BC - Carrier Data Table" using the DLookup function "on current" event in my form. The problem arises if the Dlookup doesn't find a matching record, it just adds the next record in my issue table. My issue table has an issue number that is an autonumber field. Each time this Dlookup doesn't find a match it adds a new issue that is blank. I want to run a Macro if the Dlookup is null instead of it erroneously creating a record. I have a field called issue_Index on the form which is carried over from another form previously filled out by the user. That is the criteria that is linked in both tables. Any ideas how to code if the below returns a null value to run a macro. Also should this be in "on current" or another form event location?
Me.CarrierNum = DLookup("[* Carrier Number] ", "BC - Carrier Data Table", "[* Carrier Index] = [Issue_Index]")
View 7 Replies
View Related
Nov 16, 2014
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
View 2 Replies
View Related
Apr 6, 2015
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
View 3 Replies
View Related
Mar 26, 2015
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else
p.s it unbound form
View 3 Replies
View Related
Jul 5, 2013
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset
[Code] ....
View 1 Replies
View Related
May 2, 2007
hello,
I have a combo box that looks at a table with an SQL statement. In the form, the combo box shows the headers of the resulting table. Is there any way that the user can click on any header (let's say 'LastName' or 'Date' ) so the drop down list would be sorted by that header that the user clicks on??
Thank you very much
View 1 Replies
View Related
Nov 28, 2007
I tried the search and while I found some things related to my issue, I couldn't really come to a conclusion on my issue.
I have all the databases on the network set to compact on close. This is resulting in a new copy of the compacted database with the generic "db1.mdb" file name every time the db is compacted. It also does not compact the correct one.
So, basically...it is copying the db, compacting it, but not deleting the old one and renaming the new one.
If I copy the database to my hard drive, it compacts, deletes, and renames sucessfully.
The only thing I can think of at this point is there's some issue with the server. But this is happening on multiple servers.
View 14 Replies
View Related
Sep 13, 2006
I am assisting with the design of a db, where depending on two criteria, one of two letters needs to be printed. If one check box is positive, a letter is to be generated. If it is empty then a second checkbox will be checked, resulting in a different letter.
I appreciate any assistance.
:o
View 1 Replies
View Related
Jun 1, 2007
I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.
http://office.microsoft.com/en-us/templates/TC010175341033.aspx?CategoryID=CT101426031033
While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different<< Date/Time formats in the *same* table.
Below is the date range I've entered from January 2007 to May 2007 and below has been copied and pasted directly from the datasheet. Trying to do a date sort in any fashion is out of the question until I resolve this.
I am consistent in my date input... April 6, 2007 is entered as 4/6/07, January 18, 2007 is entered as 1/18/07, February 21, 2007 is entered as 2/21/07 and so on.
However, some dates are showing as mm/dd/yyyy format while others are showing as dd/mm/yyyy format.
Note: if I choose a general date or long date format Access reads these as July, September and October dates in some cases.
Here is January (as copied and pasted from the datasheet)
01/03/2007 (these first 7 show a mm/dd/yyyy format)
01/03/2007
01/02/2007
01/10/2007
01/09/2007
01/05/2007
01/11/2007
18/01/2007 (these last 6 show a dd/mm/yyyy format)
24/01/2007
27/01/2007
27/01/2007
02/01/2007
31/01/2007
Here is February's
05/02/2007
02/07/2007
26/02/2007
02/09/2007
21/02/2007
28/02/2007
15/02/2007
04/02/2007
And March's... (the only month that appears correct...)
14/03/2007
17/03/2007
21/03/2007
14/03/2007
19/03/2007
22/03/2007
14/03/2007
15/03/2007
April's....
04/11/2007
04/05/2007
04/10/2007
04/10/2007
04/11/2007
04/11/2007
04/11/2007
04/11/2007
13/04/2007
04/12/2007
04/12/2007
13/04/2007
13/04/2007
13/04/2007
04/04/2007
20/04/2007
04/06/2007
23/04/2007
24/04/2007
17/04/2007
17/04/2007
17/04/2007
27/04/2007
04/10/2007
04/10/2007
13/04/2007
24/04/2007
May's...
05/06/2007
05/01/2007
05/03/2007
05/08/2007
05/09/2007
05/09/2007
05/10/2007
05/11/2007
14/05/2007
14/05/2007
14/05/2007
15/05/2007
15/05/2007
16/05/2007
16/05/2007
17/05/2007
25/05/2007
Any help would sure be appreciated!
Thanks in advance.
Rod
View 4 Replies
View Related
Jul 2, 2013
I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:
Code:
Select .*, [here the new field]
From MyTable1
Union
Select .*, [here the new field]
Form MyTable2
The Union statement isn't really relevant, just for completion's sake.
View 6 Replies
View Related
Sep 20, 2013
I have a checkbox that determines whether or not to display certain form controls. How can I also hide the resulting white space that comes from hiding the form controls?
Can I put all of the controls in some sort of container and hide the container? The form objects are all displayed in order, so it shouldn't cause issue.
Can I create a subform for the objects and hide it? This would make the main and subforms based off of the same table.
Occam's Razor would be the preferred philosophy in this case. Nothing too fancy is needed.
View 4 Replies
View Related
Apr 22, 2015
I am trying to dlookup a field (field name [client]) which is on a form (form name bkpo)
The code i use is this
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BKPO]![Client] & "'")
It works just perfect however if I use the form BKPO as a subform then the code doesn't work
The form is BK1b
and the subform is BKPO
I tryed this but doesn't work
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BK1b].[BKPO]![Client] & "'")
I have tryed several other options like [Forms]![BK1b]!BKPO.[Client] or [Forms].[BK1b]![BKPO]![Client] but nothing
View 7 Replies
View Related
Nov 5, 2013
I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:
Code:
Dim matchCriteria As String
matchCriteria = "LIKE 'TW*'"
If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then
MsgBox "Success"
Else
MsgBox "FAILURE"
End If
I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.
View 5 Replies
View Related
Aug 4, 2015
I have a form that has a field that needs to get its data from a query based on two fields in the form. The following is my DLookUp statement:
DLookup("[COMEX]", "OptionMetalsListQ", "[Metals] = '" & [cboMetals].[Column](1) & "'" And "[DateOfPrice] = " & Me.txtDateOfPrice)
Comex is a metal market. the bound column in cboMetals is text and The date is a date.I am getting the error "Run-time error '13': Type Mismatch.
View 3 Replies
View Related
Jun 26, 2015
I have a command button on a Access 2010 form that i am using as my switchboard. On this form i have a hidden unbound text box that captures the users environ"username". When the user hits a command button on the form the code looks at the name in the hidden textbox that captures the environ"username" and then DLooks up a table to see if there is a match. If yes then it will open the next form and if not then a message box appears.
Code:
Private Sub Command6_Click()
Dim TxtUsername As String
If Me.TxtUsername = DLookup("[OneLondon Login]", "TblAccessUsers") Then
DoCmd.OpenForm "Bakerloo_Main_Form"
Exit Sub
Else
MsgBox "You do not have permission to access this database"
End If
End Sub
This works fine apart from the fact it will only read the first name entered in the table. This table(TblAccessUsers) could have up to 50 names in it and possibly have names removed and re added at a later date. Is there a way i can get the code to look up every name in the table ??
View 3 Replies
View Related
Jul 31, 2013
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Code:
SQLstock1 = "UPDATE TBL_STOCK SET Stock = '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"
View 1 Replies
View Related
Aug 18, 2013
Why does this work when the text box is used and not when the variable is used directly?
Code:
Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub
Text62 returns the correct value
Text64 failes on everyone of the examples
View 7 Replies
View Related
Mar 26, 2014
Here is my VBA Dlookup Code:
Code:
Public Sub test()
Dim frm As Form
Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
View 4 Replies
View Related
Aug 15, 2013
I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible? I'm declaring a variable as follows:
Dim Ref as string
Ref = [lead_id]
This is from a form.What I'd like to be able to do is go to the table [list], reference the lead ID in the table via the variable then change the field [status] to "INCALL".Can this be done in a similar way to Dlookup?
UPDATE - here is the code I am trying to use
Dim ref As String
ref = [lead_id]
Dim MySQL As String
MySQL = "UPDATE vicidial_list SET"
MySQL = MySQL & "vicidial_list.status = 'INCALL' "
MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))"
DoCmd.RunSQL MySQL
Which gives me an update clause error
View 4 Replies
View Related
Feb 26, 2014
If I type 2014 instead of b everything works out.
Code:
Dim a As Double
Dim b As String
b = Right(RepMonth, 4)
a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth
View 4 Replies
View Related
Mar 5, 2014
At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.
However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.
I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.
This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:
Code:
=DLookUp("[Total Input]","DailyDataQry","[Work Date] = #" & [Forms]![InputFrm]![WorkDateAtxt] & "#")
The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.
Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?
View 3 Replies
View Related
Feb 15, 2015
I set up an Access 2003 database where this code works OK:
Private Sub Street_Exit(Cancel As Integer)
Me.Suburb = DLookup("Suburb", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.StreetID = DLookup("ID", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Postcode = DLookup("Postcode", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Town = DLookup("Town", "Streets", "StreetName = Forms![Add A New Member]!Street")
End Sub
I've now migrated the tables to a back-end in Azure using ODBC to connect. The linked Streets table in my list displays as dbo_Streets but when I change the table name in my code I get an error.
View 4 Replies
View Related
