Modules & VBA :: DLookup Missing First Record In Table
Sep 23, 2014
I am having an issue with DLookup, which, for some reason which I do not understand appears to missing the first record in the table.
What I am trying to do is to extract the e-mail address of all users who have 'Admin' status to the database. In the table ('LogIn') there are 3 users who have 'Admin' status and this is confirmed by the DCount function in the following code. There respective ID values in the table are 1, 49 and 51.
Yet when I run the Loop to get all e-mail addresses it misses ID 1 and just returns records for ID 49 and 51.
I have tried changing other users status to 'Admin' and it finds them but always misses the first record.
I have a database which has one main form linked to a table which has 325 records in it. The problem is when you open the form it says there are only 324 records :confused: I have gone through the table comparing the records with the form records, I found what I thought was the 'missing' record. But when I did a find on the form I managed to retrieve the record. Can anyone help as I'm a bit baffled.
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
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.
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
I would like to make a DLookup that check 2 criteria in two different columns in the same table. The reason to use a DLookup is that I would like to check if two parts already have been linked together. The user therefore selects two values in to different comboboxes (lstLinkPart and lstLinkToPart). In this case the value of the first combobox is column: ComponentPN in tblProductLinkComponent. For the second (lstLinkToPart) column ProductPN, in the same table, has to be checked.
If both values from the comboboxes match the values in both (and only) columns I don't want to continue and made a code to be executed.
I found multiple examples on the internet for using multiple criteria but something similar to what I require.
I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"
Code: Sub boo() Dim result As Integer result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'") End Sub
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code> Private Sub txtloan1_AfterUpdate() If IsNull(DLookup("[loan1]", _ "settlement", _ "[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then Cancel = True MsgBox "Test", vbOKOnly, "Warning" End If End Sub </code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
I have a weird situation and I was wondering if anyone has had an issue with this. I have a record missing from a table. Normal Users are not allowed to delete records from the Form view. There is a blank record where the record should be. The auto number counts from 37, 38, 40. Record 39 is missing and there are blanks where it should be. The autonumber field is my Primary Key and it is blank just like the rest of the fields in the record. I know that the record existed at one time because one of the DB users has a report referencing that record. Any Ideas?
I made a query that calculates the sum of from a table that contains defect types and numbers.
The problem is when after the inspection there's no defect no values will be entered in the mentioned table. And running the query the result is 'NOTHING'.
I tried to deal with this 'nothing' putting this in another query:
I want to set up that if there is missing value of the fields, then we cannot go to next record or exit the form when we click to do next action.
I tried to add the code in the After update event, or Exit event,
Example Code: if (isnull([Zip]) or len([zip]) = 0) then msgbox "missing" [zip].setforce end if
when I click to go to next record, or exit the form, it give out message "missing", it work fines, but, it still go to next record, or exit the form after the message.
i have made a query.. when executed it returns 4 results..when i view the report however (made using the wizard based on that query) only 3 results are displayed..i then add a record to the database... the query returns 5 results.. and again the report only displays 4 results...i believe that the first record entered into the database is missing from the report.. but it is present in the query.. could i have accidentally deleted the first record from the report when i was altering the layout in design view??anyone come across this before?is there a general rule with reports based on queries that only display results with certain criteria or something??
I got a problem regarding query work week in database. The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :
Code: SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code FROM Contacts WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));
Is there anyway to count how many fields are missing for each record ?
I have a report that is fixed at one page for one record. However, there is a subreport with many transactions ("CanGrow"=No). If this goes to a new page I have to close the report and start a new one. This all works fine, except for the last transaction - if the second-to-last transaction record is at the bottom of a page then the last one does not print.
Access seems to format the data, then it realizes that it won't fit on the page so doesn't print it. I am using record id number to know which transaction record to start the next report on, so when it gets to the end my logic thinks it has reached the end as the id number is the last one in the set.
How can I know if the last record has printed or not?
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:
I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.
What I thought would work was do a Left Join For Diversity and Employee such as:
SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];
Then, do a Left Join for Gender and Employee such as:
SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];
And then do a Union. But that doesn't work.
Any thoughts or comments would be much appreciated!
I am working on a database where the records are numbered sequentially. On a regularly basis, records will be deleted leaving a gap in the record number sequence. I would like to have my db recognize that a number(s) is number in the sequence and use the missing number (filling in the gaps) when a new record(s) is added until all spaces are filling and then to continue from the largest number sequentially.
I have a form and was wondering if it was possible to set it so that a user is not able to click the submit [Submitcmd] unless certain text boxes are filled (however SampleOuttxt needs to be able to remain blank).
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.
VBA:
Private Sub List1_DblClick(Cancel As Integer) DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name" End Sub
I have an application where I have to read a big VCF file in VBA, extract the information and place it into records of a table. Of course I can read as a text file, but the structure is quite cumbersome to implement with many fields missing in different records and it is not a one time job.
