Modules & VBA :: Query A Table To See If Record Exists With Particular Field Blank
Mar 14, 2014
I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.
In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.
At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.
View Replies
ADVERTISEMENT
Aug 25, 2015
I have 2 tables:
Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
The desired out put should be:
Unit
R_Date
Sold_Rate
X
25-AUG-15
200
View 3 Replies
View Related
Jul 9, 2014
I have a form with a drop down that links to a table where a user can select or type a record, If they type a record that isn't in the table, the form goes completely blank.
Is there a afterupdate event I can do so that if the record doesn't exist, then a msg prompts and the form isn't completely blank?
View 2 Replies
View Related
Mar 27, 2015
I have designed a database that has two forms as inputs to a table. The first form is a checklist and when it is completed it saves all fields except the ManagerID field. I then use the blank ManagerID, clientID and Date to pull onto a form for the manager to complete. On completion I want the ManagerID to save into the current records so they do not show up in the manager checklist forms and I then have a complete record. I have been searching online and cant seem to see how the best avenue is. I have an append query, see below
Code:
INSERT INTO ChecklistResults ( ManagerID )
SELECT ChecklistResults.ManagerID, ChecklistResults.ClientID, ChecklistResults.DateCompleted
FROM ChecklistResults
WHERE (((ChecklistResults.ClientID)=[Forms]![TeamLeader]![ComClientNotFin]) AND ((ChecklistResults.DateCompleted)=[Forms]![TeamLeader]![ComDateSelect]));
Code:
Private Sub CmdAppend_Click()
Dim dbsNorthwind As dao.Database
Dim rstAmend As dao.Recordset
Dim qdfAmend As dao.QueryDef
[code]...
View 1 Replies
View Related
Jan 2, 2014
I have created a form which enbles users to enter data into the fields which will add a new record to my table. What I am trying to do now is to create some kind of validation rule that will check whether the record already exists.
I need it to work this way; there are four input fields- 3 text and 1 is a date filed. When the user enters a new record they are not allowed to create a record that conatins the same combination of values. So if one record has for example filed 1 = 1 field 2 = abc field 3 = def; then no other record can have the same combination of values.
If the user enters an already existing combination there would be an error message saying which field is incorrect. If the combination doesn't exist it would add the record and display a message that the record was successfully added.
How can I do this?
View 11 Replies
View Related
Sep 10, 2014
I have a form, which has a combo box which a user can select an employee - the combo has three columns (Employee_Number, Surname, Initials) although the Employee_Number column is set at a 0 width, so not visible.
The form is bound to a query which only shows records that are 'Active'
When a user selects and employee in the combo box, I want the AfterUpdate event to check and see if there is already an active record - if there is, throw up an error message and then reveal a couple of buttons giving choices what to do next; if there isn't an active record, reveal three textboxes and then populate those textboxes with the values from the three columns from the combo.
The bit that is sticking me is the search - - I have tried DCount, but can't seem to get it to work
Here is the section of code that I am battling with:
Code:
Dim EmpNo As String
EmpNo = cboEmp_Check.column(0)
If DCount("Employee_Number", "Incomplete_Training", "Employee_Number=" & EmpNo) > 0 Then
MsgBox "Existing", vbOKOnly
View 9 Replies
View Related
Jul 17, 2014
I am trying to check if a record exists, but I keep getting a error!!!
Private Sub Command10_Click()
If DCount("*", "tbl", "[ID] = "") <> 0 Then
MsgBox "This record already exists.
Else
stDocName1 = "McrAddNewRecord"
DoCmd.RunMacro stDocName1
End If
End Sub
View 5 Replies
View Related
Feb 6, 2013
I am trying to check if a record exists in a table.
Dim cert As String
Dim existingRec As Variant
existingRec = DLookup("[Certificate]", "[Fire]", "'cert' = 'cert'")
If existingRec = Null Then
Else
MsgBox "The Certificate " & existingRec & " is in the database"
End If
Note - Certificate is a column in the table "Fire"
Problem is that existingRec only finds the first value in the Certificate column, How do I get to check the entire Certificate column?
View 4 Replies
View Related
Jan 7, 2013
table A has 10 records where 1 field is set as REQUIRED/UNIQUE. the table B does not have any record but have a same field like table A. what I want is that if user insert the record in table B it should first check whether record is already present in table A or not, if already exists it should not accept insertion. and if not already there in table A it should insert record in table B.
View 3 Replies
View Related
May 27, 2014
I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.
I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.
Code:
dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value
[code]...
View 9 Replies
View Related
Aug 28, 2013
I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.
View 5 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ....
View 2 Replies
View Related
Jun 13, 2014
I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ...
View 1 Replies
View Related
Jul 23, 2005
Hi,
In the current db there are some tables and queries, forms designed around them
With one table Test
http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them
http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage
http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
View 5 Replies
View Related
Nov 25, 2014
I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
View 12 Replies
View Related
Aug 22, 2013
I currently need a way to check to see if a a field already exists. To be exact I have an access application that imports data from multiple sources and saves the file name of these imports into an import tracker table. The table has four fields, file name, Date Imported, Import Type, and Number of Records. I am writing some code that checks a Boolean given by a form. If the Boolean is true It skips over checking.
If it is false It needs to check my import tracker file names to see if the file was already imported. If it was then it will skip over the file and the algorithm will check the next file given by an array. So I was wondering..How would I go about making it check if the file already exists in the table. I was thinking of turning the File Name Field into a primary key, since there will only ever be one File Name of every type.
View 4 Replies
View Related
Dec 4, 2006
Hello all,
I have a form that looks at my "master table" where users put in general info about a sheet of material.
There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s).
After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run.
This is probably something very easy....... It was a very long weekend and I'm tired of thinking.
Thanks
Scott
View 2 Replies
View Related
Mar 10, 2006
Hi All,
Say you've got 2 tables and you want to extract data from table 1 but exclude all records from table 1 which have a field value existing in table 2?
Table 1 fields:
Test_Code
Test_Desc
Table 2 fields:
Test_Code
Therefore, I would like to query out from Table 1 all Test_Code values which do not exist in Table 2. Any ideas please?
View 2 Replies
View Related
Nov 16, 2004
I have a report that prints invoices, and in the page header area it contains most of the invoice information such as labor cost, customer info, etc. In the detail section of the report i have the line items, and a line contains a part, vehicle charge, etc. Some invoices dont have any line items though, and it seems that when there are no lines(ie. nothing in the 'detail' section), the header information will not appear either. Any ideas on how i can fix this?
View 2 Replies
View Related
Aug 15, 2013
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
View 14 Replies
View Related
Sep 8, 2013
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
View 3 Replies
View Related
Sep 8, 2014
I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.
Code:
Sub TestIt2()
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("QryUpdateRevisionHistory")
[code]....
View 1 Replies
View Related
Sep 10, 2014
I have the following module in my database to allow for multiple alarms.
It all works great, except before showing the actual alarm record called, the form first pops up showing the first record in its record source.
I.e. it flashes up showing the first record in the table, then changes to the 'correct' record.
I would just like to change it so it either doesn't show until the record is loaded, or it shows blank to begin with.
Here's the module...
Code:
Option Compare Database
Option Explicit
Public clnPopUpAlarm As New Collection 'Instances of frmPopUpAlarm
[Code].....
View 2 Replies
View Related
Jan 19, 2015
I am creating a log in form, it checks the user name which is unique. if the user has entered a password, it shows only one password text box, they enter the password, if it is correct they enter the database, otherwise they return to the text box.
But if they have not entered a password before the form opens with two text boxes, one for the password and one to confirm the password is typed correct, if the are different a message box shows telling them that they are different, now is where i having problems, when they have typed the two passwords and they are correct i want them to save this password in the same record "Password" as the selected "username" record, I can find the "username" record by doing a Dlookup, easy, but i am stuck how to then save the password from the text box where the selected username record is.
My table "staff" has fields of "IDStaff", "FirstName", "Surname", "Password", "Username".
View 3 Replies
View Related
Jun 6, 2014
I have a table called login and inside that table is three columns: username, password and admin.
I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.
View 2 Replies
View Related
Feb 27, 2014
Ok i have a customer form with a button that opens a job form the customer and job tables are linked with a customerid field.
When the job form opens the records are filtered to show the ones matching the customerid displayed on the customer form.
Problem is when i then go to add a new record on the job form it leaves the customer id field blank instead of knowing to pass on the customer id ?
View 2 Replies
View Related
